Projecting the 3 Financial Statements: The Balance Sheet

Projecting the 3 Financial Statements: The Balance Sheet



hello and welcome to our next free youtube tutorial this time around once again I'm going to go back to a question that came in the other day show you what the question is and then show you how you can easily answer this when it comes to your own financial models and valuations so here's the question how do you decide what item to project as a percentage of such as accounts receivable as a percent of revenue or inventory as a percent of cogs does it depend on how the line item you are trying to project is used by the company what the line item you're trying to project is composed of or is there another way I am missing now the way this question is phrased right now maybe a little bit unclear to you especially this top part so I'm going to rewrite it a bit and show you what the students intended meaning here actually is what is really asking is when you project balance sheet line items how do you decide what to link each item to so for example if you look on a company's balance sheet or statement of financial position under IFRS how do you decide what you should link accounts receivable and inventories and other assets to and then on the liabilities and equity side what about items like accounts payable accrued expenses deferred revenues and then these other type line items as well so that's what he's asking about what should you link accounts receivable to what should you link prepaid expenses to what should you link deferred revenue to so our plan for this lesson is that first off I will give you an answer to this question sort of it's not going to be a universal rule that you can always use but I will explain how to think about it then I'll give you some rules of thumb you can use for projecting specific line items I'll give you an example for Atlassian which is a software company that we use in one of our case studies and I'll show you how to use it to check your work and then I'll show you a bit more about how to handle random line items on the balance sheet that do not seemingly fit into other places and then we'll do a recap and summary of everything year so the answer to this question is that there are some general rules of thumb that apply to common items but you're never going to know exactly what to use for every single line item on the balance sheet for every single company out there there are just too many different variations and too many different line items so what I would encourage you to do instead is to focus on why you are projecting these items in the first place which is namely the cash flow impact because remember if you really wanted to for a three statement projection model you can actually skip the whole balance sheet if you really wanted to and you could just track the company's cash balance and then you could go down and if they had debt they don't have debt here but if they had it you could track their debt balance maybe keep track of equity and you could skip everything else in between and the reason why you can do that is because ultimately these items like accounts receivable and inventory and other assets and then payables accrued expenses and so on their ultimate impact is on the cash flow statement they're going to impact the company's cash flows and depending on the direction that they change in the company's cash flows are going to go up or down or stay about the same so if you keep that mind you can figure out what to do in most cases here the problem in my opinion is that a lot of people tend to lose sight of the forest for the trees with this question and will get really detailed questions from people saying how do I project this line item how do I project this one which this one be linked to should this be linked to operating expenses or total expenses or cogs and they get a little bit too wrapped up in the specific line items to link everything to when in a lot of cases it doesn't really matter at all or at least nearly as much as you think it does the real reason why it matters as I just showed you is that it all goes back to the company's working capital and how that impacts their cash flow and free cash flow so does a company spend in advance of its growth if that's the case then the change in working capital is going to be negative as a percent of the change in revenue so the classic example here is a retailer a retailer has to buy inventory before it sells it to customers and so it is going to spend money on working capital before it can actually realize sales from spending that working capital as a result it's going to need more cash to grow as its sales grow on the other hand some companies have the opposite situation where they actually get extra money as a result of this growth the classic example is a subscription software company or any type of subscription company that collects money from customers upfront in cash and then delivers the service over time now if that's the case the change in working capital is going to be positive as a percent of the change in revenue because as a company's revenue grows it actually gets additional cash from those business policies namely collecting cash from customers up front so that is what you really have to think about when it comes to this point and that is what a lot of people lose track of when they get wrapped up in the minutiae of how to project different line items so here are the general rules of thumb for these items and I've been referring to this example for Atlassian throughout so we're actually going to go back and take a look at a few of these examples here for their balance sheet and cash flow statement drivers but let me give you the general rules of thumb first with these you really have to think about what the balance sheet line-item really means and then once you do that it's usually pretty simple to figure out what you should link it to so for example accounts receivable and deferred revenue you're pretty much always going to link these to revenue because both of these are going to trend with sales now for some companies you'll do variations of this it may not be linked exactly to revenue you may split it into subscription versus one-time revenue or credit sales versus total sales or something like that but these are both generally going to move with the company's top-line sales because accounts receivable represents what the company has recorded as revenue but not collected in cash yet and then deferred revenue is the opposite it represents what they have received in cash but have not yet recorded as revenue inventory is almost always going to be linked to cost of goods sold or cogs because for a company that sells physical products if they have inventory then when they buy the inventory it's not going to show up on the income statement initially it's only going to show up listed as a cost of goods sold when the product is actually sold and delivered to the customer so inventory is pretty much always going to be linked to costed with souls and then items such as accounts payable accrued expenses and prepaid expenses these could go in many different ways you could link them to cost of goods sold or operating expenses or both cogs and op X combined it really depends on what's in them and what type of company it is so for example if it is a manufacturing company and they spend a lot on buying inventory from suppliers you're probably going to linked accounts payable to cost of goods sold in that case because it probably trends pretty closely with their inventory needs and what they're recording as expenses upon selling products on the other hand if you are dealing with a software company you might not do that you might like this to operating expenses instead accrued expenses and prepaid expenses tend to be linked to operating expenses in most cases but again it depends on the company and exactly what's in these items but these are some general rules of thumb as you're thinking about it and then there may be other lotta items here as well so for Atlassian for example if you go down and look at their balance sheet they do have other non current assets and then they have other non current liabilities and you will see similar items for a lot of other companies out there you have a few different options for these if they're relatively small you might just hold them constant and not worry about it if you really don't know what to do and they are changing by some amount you might just link these to revenue and make them trend with the company sales and then there are some more random line items so with these maybe they don't fit into one of these categories above but they do match up to one certain item on the income statement or if you cannot find a matching item on the income statement then you might just simplify and make these a percent of revenue as well because remember it goes back to our rule from before that what really matters is the change in working capital as a percent of the change in revenue so if you really can't figure out what to do you can never really go wrong by linking one of these items to revenue it may not be the best way to do it but as long as you get this part right the rest of your projections and your cash flow analysis will be fine so let's look at an example of a real life projection for Atlassian we'll go up here and you can see that I've projected tree and other receivables as a percent of revenue inventory is a percent of cogs payables accounts payable as a percent of effects accrued expenses is a percent of op x and then deferred revenue is a percent of revenue and those are the only balance sheet light items that we're actually projecting there so the others were holding constant or we're linking in from elsewhere or we're doing a simple average as a percent of revenue or something like that but the others are all much smaller and less significant so you might look at all that and say okay so receivables is linked to revenue that makes sense inventories is linked to cogs payables we're not really sure but we're looking into operating expenses seem for accrued expenses and then deferred revenue we're going to link to revenue because it corresponds to the company's subscription revenue that they have received in cash but not yet recognized as revenue how do we know that any of this is correct though in other words how can we check our work and make sure that the numbers we have and the way we're projecting this actually makes sense so to check this what you can do is sum up the change in working capital from the cash flow statement so once you already have your balance sheet income statement and cash flow projections set up and laid out you can simply go down to the cash flow statement and take the changes in opera assets and liabilities also known as the change of working capital from the bottom part of the cash flow from operations section sum up everything here and then you can copy this across and then you can look at this as a percent of the change in revenue so in this case I'm going to take our revenue this year or subtract it from the old year we have that and then in the prior year revenue was around fifty nine million so I'm just going to enter that as a hard-coded number copy this across and then we can copy it across for this future period as well we can also look at this as a simple percent of revenue and use that to check our work as well so we have that set up and if you look at the historical numbers it actually jumps around quite a bit here if you take the average it comes out to around 23% and then the average as a percent of just revenue comes out to around 2.8 percent now in the future period if you look at this the change of working capital as a percent of the change in revenue goes from around 3% up to more like 11% to 19% and as a percent of revenue it stays in around the 1% to 2% range both of these numbers strike us as reasonable because they are in line with the historical averages 2013 was a bit of an exception but other than that it seems like this company has a slightly positive change in working capital as a percent of the change in revenue so as I say here the historical average is 23% mostly because they are switching to a subscription-based business model and they have very little in terms of inventory requirements so we think our estimates of 10 to 20 percent in future periods are reasonable we are getting some cash flow benefit as we grow but it's not a huge benefit we're not getting a 30 percent or 40 percent benefit it's more like 10 to 20 percent of the increase in revenue each year now you might be looking at this and saying okay well what if we got this raw so let's say for example that we linked reading other payables to revenue rather than operating expenses and in most cases when you do this it is pretty obvious that you've made a mistake so here for example let's say I linked treating other payables to revenue instead so we're linking to our revenue light item down here we're still taking the historical average but look at our trend lines over here our change in working capital as a percent of the change in revenue seems to not make as much sense it drops to a negative number in the first year and then it becomes positive but it never quite reaches anything close to the old historical average and then the change of working capital as a percent of revenue also becomes negative in the first year and become slightly positive after that but in general we do not get that same good trend line that we had before so that's an indication that we might have done something wrong here so when you get something like that and it starts jumping around by a lot especially in future periods it is really not ideal and it is a sign that your assumptions might be off and so that's how you can check your work here now the last thing I want to cover briefly is what to do when you have a more random type of line item I'll pull up this model for easyJet which is another company that one of our case studies is based on on its balance sheet they have some fairly standard line items but on the liabilities and equity side they have this line item for maintenance provisions they have a long-term version and then a current or short-term version we don't know exactly what to link it to upon first glance although I would point out that it is a very minor item in the grand scheme of things in total it adds up to around 250 million pounds out of about two to three billion pounds in liabilities so we're looking at an item that comprises maybe 10% of a company's liabilities in this case if you go up to the income statement you'd see that they have this line item for maintenance representing the maintenance expense for aircraft in the current period and so by looking at that you could probably infer that these line items are going to be linked maintenance on the balance sheet representing their future expected payments and then maintenance on the income statement representing what they paid in the period shown for maintenance on their aircraft so based on that you could then go up and you would probably do a we did right here which is to link the maintance provisions to the line item on the balance sheet and so that's exactly how we set that up and to check your work once again you could do the same exact thing you could look at the change in working capital as a percent of the change in revenue and see if it comes out to a reasonable number and in this case this is such a small line item that we would probably say who cares we could make this a percent of revenue if we really wanted to I probably wouldn't even make that big of a difference because it's so small relative to some of the other line items on the balance sheet so let's do a recap in summary now when you build three statement projections like this you really want to start with the end goal in mind that end goal is usually measuring the company's cash flow and seeing how it changes over time especially as its revenue grows or possibly as its revenue declines the general rules of thumb here are that you link revenue related line items like accounts receivable and deferred revenue to revenue because both of these are going to trend with sales when sales increases these are probably going to increase when sales decreases these are probably going to decrease inventory is pretty much always linked to cogs because that is what it represents and that's how the inventory expense is shown on the income statement when it's finally sold to customers accounts payable accrued expenses and prepaid expenses are generally linked to cogs operating Spence's or possibly both depending on the company other type line items you may hold constant or you might even link to revenue and then random items that don't fall into one of these categories you can generally find a matching income statement light item if you really can't find it you could hold these constant or as always you can just make these as simple percent of revenue to check your work you have to think about whether the company spends in advance of its growth in which case the change in working capital will be negative as a percent of the change in revenue if that's not true if it's not consistent in future periods you have a problem or think about whether the company gets extra money as a result of its growth and if that's the case the change of working capital will be positive as a percent of the change in revenue and you have a problem there if it's not so that's a bit about how you can project the three financial statements and decide what to link each item here to you

15 thoughts on “Projecting the 3 Financial Statements: The Balance Sheet

  1. It is an important lecture that help me a lot. Thank you. May I request one thing that doesn't cost you nothing and will be more beneficial to me. Should you share the excel case study you've used in this video ?

  2. Thank you for the video 🙂
    I have a few queries in mind. I am currently trying to project working capital in offline retail industry that is similar to Walmart (not this huge retailer) and i'm new to financial modelling. I should link AP to COGS? As the company owes the supplier for inventories.

    Another questions is that, for retail business their prepaid expenses are Rent for new outlets, insurance, and bulk order of supplies. Should i link these items to OPEX? As these items are needed for running the business daily.

  3. Hey Brian! I learned so much from your videos. I had a specific video request bout a concept. I understand SE + Liabilties = Assets and a balance sheet must always balance. But when you project things into the future, or decide to keep things constant, or even zero them out, how do the future projections still balance? Because if you're projecting higher numbers for one thing on the liabilities side how is it countered on the assets side? It always remained a mystery to me.

  4. Thanks for making this!
    I think the most confusing thing to do is the interest expense projection on the income statement. Also, days payables/receivables confuse me as well.

  5. I am preparing normalized financial statements just before I start preparing different schecules and projections. Thank you

  6. while preparing normalized balance sheet, I took some non recurring items out of balance sheet and now my balance sheet for that particular year does not matches. it that okay for normalized balance sheet or does it still should match? by matching I mean A=L+E

  7. Hi Brian, thanks a lot for your videos! It really helps me advance my modeling skills and explains to me things I would not think about myself.

    However, I am confused. You say that if a company invests into its growth (spends before it incurs sales), then the change in NWC is negative and vice versa. But does not a negative change imply higher free cash flows? After all, FCFF = EBIT (1-t) + D&A – CAPEX – change in NWC. If we have a negative change in NWC, then, according to the formula, our FCFF increases. This is counter-intuitive because you can't pocket cash that is set away for driving sales growth. Am I missing something here?

    I watched your NWC tutorial, where you explained that Cash is usually not considered a part of NWC. Hence, if we, for example, increase Def. Revenue, out current liabilities will increase, while current assets excl. cash will stay the same. Hence, we decreased NWC, yet we did not invest anything to drive sales. This makes sense. Our FCFF, other things remaining constant, went up.

    On the other hand, if we are a retailer, we need to invest before we incur sales. We get inventory, we get A/P, we get prepaid expenses, etc. We generally don't have A/R. So, here, we get an increase in NWC (current assets excl. cash go up, A/P goes up but not as much as current assets). We don't have deferred revenues. In total, NWC increases, we lock away cash into these assets. This is a drag on FCFF. How do I understand what you said through the prism of my example above?

  8. Thank you for making these! They're very helpful in keeping the knowledge fresh in my head. Please keep making them!

Leave a Reply

Your email address will not be published. Required fields are marked *