Projecting the 3 Financial Statements: The Balance Sheet
Vložit
- čas přidán 27. 07. 2024
- In this tutorial, you will learn how to decide which Income Statement line items Balance Sheet accounts such as Accounts Receivable, Prepaid Expenses, and Deferred Revenue should be linked to.
You’ll also see an example of how to check your work, how to tell when you’ve linked something incorrectly, and what to do with more “random” line items.
Table of Contents:
2:23: The Rough Answer to Projecting Balance Sheet Line Items
5:43: Rules of Thumb for Specific Line Items
9:36: Example for Atlassian - Checking Your Work
14:38: How to Handle “Random” Line Items
16:30: Recap and Summary
The Rough Answer to Projecting Balance Sheet Line Items
The rough answer is: “Stop worrying about what each individual item should be linked to, and instead worry about the OVERALL change in working capital as a % of the change in revenue.”
This question represents a case of overlooking the forest for the trees - yes, how you link individual items can make a difference, but it does NOT make a bigger difference than the overall change in working capital.
Remember that all that Balance Sheet items such as Accounts Receivable, Prepaid Expenses, Inventory, and Deferred Revenue really impact is the company’s cash flow - does it spend cash as it grows, or does it earn more cash as a result of growth?
If you understand that bigger picture item, you’ll be well on your way to “getting” this concept.
Rules of Thumb for Specific Line Items
Accounts Receivable: Link it to revenue.
Deferred Revenue: Link it to revenue.
Inventory: Link it to COGS.
Accounts Payable, Accrued Expenses, and Prepaid Expenses: Link these to COGS, or OpEx, or both, depending on the company.
“Other” Assets or Liabilities: Hold these constant or link them to revenue.
“Random” Items: Find a matching Income Statement item and link them there, or make them a percentage of revenue.
Example for Atlassian - Checking Your Work
For Atlassian, we check all the numbers by calculating the historical Change in Working Capital and then comparing the Change in WC as a % of the Change in Revenue to the future numbers there.
Historically, the average Change in WC as a % of the Change in Revenue was around 23%, and going forward it is set to approximately 10-20%, so we feel it is a reasonable estimate.
If we had linked to an incorrect line item instead, such as linking
Trade Payables to Revenue rather than OpEx, it would be fairly noticeable since the Change in WC would not follow a clean trend.
How to Handle “Random” Line Items
For “random items,” such as Maintenance Provisions on a company’s Balance Sheet, you can always try to match them with the relevant Income Statement line items. That works in this example for EasyJet.
If that doesn’t work, you could just set these items to a % of revenue instead and simplify the whole process.
You can check your work with the same method: calculate the
Change in WC as a % of the Change in Revenue and verify that it follows a relatively clean trend line over time.
Recap and Summary
Whenever you build 3-statement projections, always start with the
END GOAL - measuring the cash flow impact - in mind.
Accounts Receivable and Deferred Revenue: Link it to revenue.
Inventory: Link it to COGS.
Accounts Payable, Accrued Expenses, and Prepaid Expenses: Link these to COGS, or OpEx, or both, depending on the company.
“Other” Assets or Liabilities: Hold these constant or link them to revenue.
“Random” Items: Find a matching Income Statement item and link them there, or make them a percentage of revenue.
To check your work, determine if the company spends in advance of its growth (the Change in WC as a % of the Change in Revenue will be negative), or whether it gets extra money as a result of its growth (the Change in WC as a % of the Change in Revenue will be positive).
Thank you for making these! They're very helpful in keeping the knowledge fresh in my head. Please keep making them!
s8glow Thanks for watching!
great video, as always 😺
Thanks a lot! Very informative. :) God bless.
Tony Cipriani Thanks for watching!
Very practical information. Awesome video. Thank you. More please.
Thanks for watching!
Very good video. So professional.
+Sadri Essafi Thanks for watching!
Really insightful. Thank you.
Thanks for watching!
very helpful .... keep posting nice posts
+Ahmad Shaheen Thanks for watching!
Thank you!!
Thanks for watching!
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.
+Tina Ray The interest expense is actually easy, just multiply the rate by the beginning or average debt balance. Days Payable and Receivable are not that confusing if you understand it's really just a way of expressing AR as a % of Revenue or AP as a % of COGS or operating expenses.
Thank you for saving my midterms haha!
Thanks for watching!
I'm learning a lot from Your videos Thank u guys.....Love from India!
Thanks for watching!
I am preparing normalized financial statements just before I start preparing different schecules and projections. Thank you
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.
If it's a retailer, yes, link AP to COGS. Yes, link Prepaid Expenses to Operating Expenses.
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.
+saifulisfree It balances because Cash and Equity act as balancers. Net Income flows into Equity, along with a few other items such as Dividends. And then Cash at the bottom of the Cash Flow Statement reflects *all* the changes to Balance Sheet items, since the changes are all shown somewhere on the CFS. So when cash flows into the top line on the Assets side, it automatically reflects everything that has changed on the Balance Sheet, which keeps it in balance.
What forumula do you use to create the check "ok!" for when both sides of the BS balance?
Custom number formats in Excel... go to Ctrl + 1 and then Custom. You enter what you want for positives, negatives, 0, and text.
thanks for sharing. I have two related questions to this video
1) I know that changes in balance sheet items should be shown in the cashflow statement. What if when looking at the historical values I have from annual reports, the changes in B/S isn't what the CFS shows.
2) what if I have other smaller B/S items that are company specific. What if I want to project those. How do I treat them?
1) Yes, this is a common issue. See the Change in WC video. The short answer is, ignore it and move on with the projections and for the Working Capital line items, focus on projecting the BS versions by linking them to the IS.
2) Make them percentages of revenue or expenses.
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 ?
This one's not available, but there are plenty of other example 3-statement models if you look around this channel or do a search.
Great
Thanks for watching!
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
+Jeff Singh Saini You should not be "normalizing" a Balance Sheet. You can consolidate it and simplify it, but you have to make sure that the Total Assets figure still equals what it was on the original Balance Sheet. So go back and look at your analysis again and make sure that you NEVER delete items. You can combine items, but you can't delete them.
+Mergers & Inquisitions / Breaking Into Wall Street thank you for the feedback. I have put back the item that was under current assets (it was asset held for sale so I though I should take it out as it is non-recurring item).
So do we just "normalize" the income statement only? and leave balance sheet and cash flow as it is under the original financial statements?
+Jeff Singh Saini Yes. Consolidate and simplify the BS and CFS but do not remove items entirely.
+Mergers & Inquisitions / Breaking Into Wall Street thank you sir :) appreciate it.
What about CapEx? Should i just link it to change in revenue?
Thanks in advance
That is one approach, yes. Just depends on how much information you have and how much time you want to spend on projecting that one item.
Would be happy if you could share me the excel file.
Luisa Lui Thanks for your feedback. We don't make this file available, but you can find similar files elsewhere here.
5:26 company spend in advance of growth eg retailer. Change in wc will be negative as a % of change in revenue
Company get extra money as result of its growth
Eg subscription business
Change in WC will be positive as a % of revenue
Can I have the excel model? Thanks!!
It's not available for this one, but you can find similar 3-statement models in the channel.