Learning How to Forecast Balance Sheet Accounts
Hi,
I was wondering if anyone knows any reading material that would be good for learning how to forecast current asset, current liabilities, and PP&E type of accounts when creating DCF models. I feel as though I am starting to get a good sense for how to build revenue models and whatnot, but I'm extremely weak on the working capital side and forecasting PP&E/depreciation. If there are good sites or good books to check out that focus on forecasting these items or even if anyone has tips for how they learned how to do this, I'd really appreciate it.
Thanks!
Balance Sheet Forecasting
When doing three statement modeling, the balance sheet can sometimes be to confusing to project and there are two methods for modeling out the statement with the balance sheet driving projections or the statement cash flow driving projections which is explained by @Marcus_Halberstram", an industry CEO.
You're never really forecasting the BS, IMO. Some people have the BS driving the CF statement and some have the CF driving the BS.I typically have the CF driving the BS. Cash is just a minimum cash balance and sweep the rest to revolver/debt service. D&A schedule has amortization schedule + the optional debt repayment from excess cash on the CF. Other one off BS items like pension liabilities, deferred revenue, etc... come from conversations with company management or a reasonable assumption. If its been pretty constant historically just flatline it, if it fluctautes use a reasonable proxy to anchor it to such as CapEx as a % of Sales.
A Brief Overview
Below our users shared their best practices for modeling out the balance sheet.
Cash and Cash Equivalents: Flows in directly from Cash Flow StatementPrepaid Expenses: % Opex or % COGS
Inventory: Simple: % COGS or More Advanced: COGS/Average Inventory
Short-Term Debt: Flows in from Cash Flow Statement or debt schedules
Accrued Expenses: % Opex or % COGS
Deferred Revenue: % of revenue
Projecting Other Balance Sheet Items
Retained Earnings: Retained earnings from previous year + Net Income - DividendsOther Assets/Liabilities: Remain Constant
Long-term Debt: Flows in from Cash Flow Statement or debt schedules
Long-term Investments: Flows in from Cash Flow Statement; usually constant
Intangibles: Subtract amortization and add in purchases
PP&E: + Capex - Depreciation - Asset Sales - Write Downs or full-blown schedule
Forcasting Balance Sheet Accounts Receivable
Accounts Receivable can be forecasted in two ways:Simple: % of revenue
More Advanced: (AR/Revenue) * Days in period
Forecasting Accounts Payable
Accounts Payable can be forecasted two ways:Simple: % COGS
More Advanced: (AP/COGS) * Days in Period
Determining Balance Sheet Assumptions
@esbanker" explained that:
I usually look at the historical metrics and ratios mentioned above going ~3 years back. I then take an average of those years and take it forward, or simply pull forward the ratio for the last historical year.You can then make adjustments to those assumptions if you have a deeper understanding of certain balance sheet items. You can - and should - also create 3 scenarios (upside, base, downside) for your key balance sheet assumptions.
Ultimately, your balance sheet assumptions and the rest of your model will hinge on no more than a few drivers (namely, revenue growth and margins, ie. the drivers of value). That's why making sure your revenue projections are sound has a far greater impact than what formula you use or how far back you trend your balance sheet assumptions.
User @Attack_Chihuahua" offers another perspective:
For a typical revenue/goods sale company, your model will be primarily income statement driven. You will make some assumptions about their operating performance and sales to create an income statement, and the results of your forecasted income statement will be used to generate your balance sheet and cash flow forecasts.A lot of the other items will need to be manually forecasted. If you look at the company historically you will generally be able to notice some clear trends in that account. It may be a consistent % of income, or a % of previous year's inventory, or what have you.
So short answer: Previous year + (Drivers adjusted by assumptions) = new balance sheet account
Modeling Asset Driven Businesses
Banks, real estate companies, and other asset-driven companies are primarily balance sheet driven: this means that their revenue model is the opposite of a basic sales/service company and their income statement depends on the balance sheet...so you analyze those companies by forecasting balance sheet changes first and then utilize the balance sheet to develop income statement and cash flow forecasts. The good news is that you won't need to know any of that unless you end up working in a FIG, energy, or real estate group.
Looking to become a modeling master?
Sign up for our financial modeling training course to learn all this and more. Check out our 15% off discount to Wall Street Prep's Financial Modeling Courses.
working capital - DSO, DPO, DSI, or % of revenue for other cur assets and % of cost of revenue or OPEX for other cur liabilities.
PP&E / depreciation - just build out a schedule. i.e. current asset base, expected life, capex is sometimes forecasted as a % of sales for simplicity, then you have an expected life on assets acquired w/ new capex.
done.
Very basic guide, but should help you get started:
Cash and Cash Equivalents Flows in directly from Cash Flow Statement
Prepaid Expenses % Opex or % COGS
Accounts Receivable Simple: % of revenue More Advanced: (AR/Revenue) * Days in period
Inventory Simple: % COGS More Advanced: COGS/Average Inventory
Long-term Investments Flows in from Cash Flow Statement; usually constant
Intangibles Subtract amortization and add in purchases
PP&E + Capex – Depreciation – Asset Sales – Write Downs OR full-blown schedule
Short-term Debt Flows in from Cash Flow Statement or debt schedules
Accounts Payable Simple: % COGS More Advanced: (AP/COGS) * Days in Period
Accrued Expenses % Opex or % COGS
Deferred Revenue % of revenue
Long-term Debt Flows in from Cash Flow Statement or debt schedules
Other Assets/Liabilities Constant
Retained Earnings Retained earnings from previous year + Net Income – Dividends
Please put some light on why depreciation and capex calculated as % of revenue
thanks
Can anyone suggest a reading material that further elaborates on what Esbanker has outlined. excellent answer by the way.
Hi,
Thanks all for the responses. I think I should have been a bit more clear. I understand the metrics that people use to forecast (i.e. DPO, etc.) the issue I have is figuring out how to trend it out. So for example, let's say I use % COGS for AP, how do I figure out what percentage I should be using? Once I figure out the business model, I feel as though revenue intuitively makes sense to me in terms of how I should trend it, but some of these balance sheet accounts are hard. I have the same issue when building the statement of cash flows. Basically, I feel as though the income statement is fairly straightforward, but I have trouble with the balance sheet/CFS when doing a 3 statement model.
Really? I find projecting revenue to be the hardest and most sensitive piece in a three-statement model.
I usually look at the historical metrics and ratios mentioned above going ~3 years back. I then take an average of those years and take it forward, or simply pull forward the ratio for the last historical year.
You can then make adjustments to those assumptions if you have a deeper understanding of certain balance sheet items. You can - and should - also create 3 scenarios (upside, base, downside) for your key balance sheet assumptions.
Ultimately, your balance sheet assumptions and the rest of your model will hinge on no more than a few drivers (namely, revenue growth and margins, ie. the drivers of value). That's why making sure your revenue projections are sound has a far greater impact than what formula you use or how far back you trend your balance sheet assumptions.
Hope this helps.
His guide is basically correct. Keep in mind that I'm about to say is extremely oversimplified and leaves out some specific situations like how interest payments will be iterative in most models, but I'm talking concepts. It's applicable primarily to
For a typical revenue/goods sale company (e.g. most consumer goods companies) your model will be primarily income statement driven. This means that you will make some assumptions about their operating performance and sales to create an income statement, and the results of your forecasted income statement will be used to generate your balance sheet and cash flow forecasts.
A lot of the other items will need to be manually forecast. I'll use Accounts receivable as a simple example. Obviously you won't be able to directly pull AR from your income statement. However, if you look at the company historically you will generally be able to notice some clear trends in that account. It may be a consistent % of income, or a % of previous year's inventory, or what have you. To forecast future years you will have to write your model so that it looks at data from previous years(relative to the cell in question, NOT the current date), applies the ratio you're looking at, uses that to estimate AR, and adjusts related BS/IS/CF accounts. In this instance, if Accounts Receivable has always been about 5% of revenue then a reasonable forecast method would be to assume that trend continues so you would take your projected revenue, multiply it by 5%, and use that as your forecast AR balance. If your cash flow statement is properly constructed any changes in AR resulting from revenue would also decrease your cash (since you were paid in credit, not cash).
So short answer: Previous year + (Drivers adjusted by assumptions) = new balance sheet account, then make sure that the changes in that account are also balanced by another account. When doing a simple forecast model the T-account logic they teach you in accounting always applies.
***edit: most frequently it's going to be cash that gets impacted, although quite a few (e.g. depreciation) will balance against your impact statement.
Keep in mind it's not this way for business with harder operating models. Banks, real estate companies, and other asset-driven companies are primarily balance sheet driven: this means that their revenue model is the opposite of a basic sales/service company and their income statement depends on the balance sheet...so you analyze those companies by forecasting balance sheet changes first and then utilize the balance sheet to develop income statement and cash flow forecasts. The good news is that you won't need to know any of that unless you end up working in a FIG, energy, or real estate group.
Attack Chihuha,
Would you happen to know of any reading material or books that go further down the rabbit hole? I'd like to delve deeper into an income driven model, the various drivers I can use, and understand their weakness.
I learned from one of those online courses. If you're in school your program SHOULD have a class that will teach you basic financial modeling.
Otherwise make sure you have a solid fundamental in accounting and begin to reverse engineer some of the sample models you can find around the web.
How to forecast Balance Sheet ? (Originally Posted: 05/20/2010)
Any one have a quick and easy way to forecast balance sheet numbers 5 years forward ? This is for a Life Insurance company so I'm not too sure how to proceed. I just need a cheat sheet of sorts.
That is really fucking hard absent an actuary and/or heavy company involvement.
There aint no such thing as quick or easy in FIG-Insurance
So wtf do I do ? Just go with a DCF and earnings model ?
You do a DDM for FIG.
You could just striaghtline all your BS assumptions based on the historical average. But it will likely be full of aberrations if it includes 2007-2009.
Forecast CF and Income Statement and that should drive most of your BS items. For the remaining items... I dunno, use historic as a benchmark and assume thats what it will be going forward. See what the numbers look like maybe 2007-09 won't even look that abnormal. I mean cash reserves and investment securities should obviously be anchored to policies outstanding or something.
It would help if you posted some of the line items that you're having trouble with.
Thanks Marcus, but I gave up on the BS, no time left for it, I'm gonna go with the DDM like you said
Just make sure you're not jacking up your dividends to maximize your DDM value resulting in depleting your cash reserves/reserve requirements based on whateveer relevant regulations apply (its easy to do this if you're modeling a FIG without a balance sheet).
If this is for school that likely wont be an issue as long as you use some sort of reserve assumption. If its for real life you need to talk to management AND read the regulatory documents in order to determine what the appropriate reserve requirements are.
When does the DDM work better versus the DCF?
And isn't the DDM just another name for the Gordon (Constant) Growth Model?
similar question, how to forecast balance sheet for "normal" companies? Do you tend to take ratio of Account receivables to revenue etc and assume them to be constant?
Marcus_Halberstram, 2 Qs for you: 1. You mentioned about forecasting CFs. But isn't cash flow statement just a reconciliation of the balance sheet? How can you forecast cash (which is formed with B/s) and then forecast B/s? 2. unrelated, I've seen your posts on almost all the forums on WSO. How do you manage to efficiently read all forums and post? Use RSS or something else? I would like to do same but afraid takes too much time. Thanks!
You're never really forecasting the BS, IMO. Some people have the BS driving the CF statement and some have the CF driving the BS. I typically have the CF driving the BS. I know my beginning assets and Im forecasting CapEx, WC, Cash etc... Cash is just a minimum cash balance and sweep the rest to revolver/debt service... D&I schedule has amortization schedule + the optional debt repayment from excess cash on the CF which does your liabilities. Other one off BS items like pension liabilities, deferred revenue, etc... should be informed by conversations with company management... or if you're lacking there, use a reasonable assumption... if its been pretty constant historically just flatline it, if it fluctautes use a reasonable proxy to anchor it to... i.e. CapEx as a % of Sales. I've done lbo models where most of the CapEx for the next 5 years was already planned and they were exact numbers based on what was being purchased/built etc...
I really just look at the forum topics column on the far right and from time to time Ill check out the IB and PE forums.
Balance sheet/income statement forecasting (Originally Posted: 12/02/2011)
Can anyone please help me out with the forecast over 5 years for my Balance Sheet and Income statement? I need to do a forecast + keep in mind a company we acquired in 2010......
is this a homework assignment?
% of revenue it.
Hi Esbanker,
I recall on a previous post many moons ago you were posting a response to a similar question.
Could you elaborate on how these two drivers operate? they don't seem to make sense to me.
Accounts Receivable (AR/Revenue) * Days in period
Accounts Payable (AP/COGS) * Days in Period
I have a forecast on the Income statement which I'm very happy with, but I'm quite a bit stumped with the Balance Sheet.
Hi! Regarding projections:
Sales-you have to rely on Unit measures of what will be sold. i.e hours, units etc. To project the trend you can take rate on price increase, the rate can be country annual inflation rate, country's GDP growth rate,company policy on price increase etc.
Cost of sales-Percentage of sale, first use realistic measures of what will be needed to produce the goods for sale, then project the trend based on rate used to project sales/revenue.
Opex-Just do research of what will be needed in operations with their values. Project the trend according to inflation rate.
Tax-percentage of Operating income (use income tax act guide).
Interest expense-Use loan amortization schedule.
Dividends-use Company's policies as to which year will dividends start to be considered and for how much.
Accounts Receivable-Use credit limit days that your company is willing to provide to her customers, treat the credit limit days as if they are accounts receivable days, multiply these days by annual sales and divide by 365 days to arrive at accounts receivable amount.
Accounts payable-Use credit limit days that your suppliers are willing to give your company, take these days as they were accounts payable days, multiply these days by annual cost of sales and divide by 365 days to arrive at accounts payable amount.
Inventory-Use Lead time that is expected in your business as if it were the inventory turnover days, then multiply these days by annual cost of sales and divide by 365 days to arrive at average aggregate inventory amount.
Prepaid Expenses-use percentage of summation of COGS & OPEX, this will also depend on specific items that your suppliers will be requiring you to pay in advance e.g rental charges, etc.
Accrued Expenses-.use percentage of summation of COGS & OPEX, this will also depend on specific credit days you have been offered by your suppliers, mostly it is equated with one month period of Accounts payable.
I hope at minimum this helps!
Forcasting IS and CF from BS (Originally Posted: 04/28/2013)
If a MD puts a gun to your head and asks you to value a companies intrinsic value with limited resources which allow you to pick either the Balance Sheet, Income or the Cash Flow statement. What would you pick?
I know you can recreate the CF statement if you have BS and IS. But Can you pretty much recreate IS(and therefore CF) if you have the BS spanning several time periods? By looking at the net changes from BS to BS can you recreate all available statements(IS and CF)?
No. A balance sheet could theoretically stay the same each year, even if a company's performance (and thus its IS and CF) highly vary. For example, take PPE - this line item could stay at $10mm year after year after year, and you would never be able to tell how much capex and depreciation a company actually had in a given year (i.e. capex and dep could be at any amount, as long as they equal each other).
Think about what items on the balance sheet the IS and CF affect.....
Just ask Marty Whitman - NAV from the B/S. Focus on what is, not what you think it will be.
This is a common interview question and the only correct answer is the CF statement.
You can value a company using NAV, that is all.
Would you change your answer if it was just a VP?
Forecasting Cash Balance (Originally Posted: 03/25/2016)
I've been tasked to a project where i would need to forecast cash balance from our bank. Any ideas on how to do this?
Information available to me is:
Cash in bank Cash inflow (Sales receipt in cash) Cash outflow (withdrawals & checks cleared) Total outstanding checks AP & AR
How far out do you need to forecast it? Through March close or some period further into the future?
I don't have a specific period. But my CFO wants to know for example: Monday WK2 of April cash balance.
Ok, the quickest way to do that:
Current balance + expected receipts between now and close (inflows) - expected disbursements between now and close (outflows) - outstanding checks = cash balance at close
It won't ever be 100% but should get you close enough for your boss to be happy. Keep in mind that close is 3/31, so the inflows and outflows will be for dates as of 3/31. Sometimes JEs have different post dates than document dates - make sure you focus on things that will have a post date between now and 3/31.
I think i have the general idea. I guess the hard part is to estimate expected inflows/outflows. Thanks!
You might be able to look at your DSO and DPO rates to see if you can calculate the rate at which AR and AP are converted into cash. That should give you some sense of expected inflows and outflows. It may be worthwhile to think about seasonality if it applies to your business.
Need help with Balance Sheet Projections (Originally Posted: 04/28/2016)
Hi all,
I'm fairly new to this and am aware that this model may be littered with mistakes. But can anyone help me out here and see why my projected balance sheet isn't balancing. I am aware that the base balance sheet is a mid year one but that was the only one I was provided with. Also, depreciation is hard coded from the projected financials I was provided with.
Thanks All
Unfortunately, I don't have the time to go through it, but I would highly suggest fixing your formatting. It's almost impossible to audit someone else's model when the fonts are not properly color-coded (see below for colors). Your mistake is most likely due to your interest expense (this is where most mistakes happen with lbo models).
Color coding: -hard-coded cells: blue (RGB 0, 0, 255) -formulas: black (RGB 0, 0, 0) -links to other worksheets: green (RGB 0, 128, 0)
Thanks. I re-did the formatting. How can I check if interest expense is the problem?
You working on an auto manufacturer?
1) you can't have an annual NI flow into a stub CF period - applies to cash and RE for 2015E 2) raw materials is not linked correctly 3) capex is not linked correctly 4) D&A doesn't flow properly through your bs 4) your ebitda calc is incorrect
It's a construction company.
1.) How do I account for the stub period if that was the only bs information I was given? 2.) Fixed it. Thanks 3.) Gross Fixed Assets instead of net right? 4.) Is this because depreciation is hard coded in the CF statement from the projections I received? 5.) Fixed it. Thanks
Thanks again.
EDIT: I think I got it to balance correctly. I still don't know how to account for the stub period though so if you could give me some guidance on that I would really appreciate it. Thanks!
.
anyone else getting an error message?
Take your Fy2015E NI less YTD 7/31/15 NI or back into it using a percentage of the remaining year.
Sorry, I'm still a bit confused. I don't have the YTD NI for 7/31/2015. I only have the Balance Sheet information for 7/31/2015. Again, I apologize it it's a dumb question.
Balance Sheet Projection Question - URGENT!! (Originally Posted: 10/26/2016)
Hey guys, I'm doing a stock pitch for an interview and have questions on projecting some BS items like Accounts Payable and Accrued Expenses.
This company I'm building a model for has had consistent Revenue, COGS, and AP for the past 3-4 years until Q2 when they decided to pay off about half of their AP (think 400 million ->200 million of AP).
So for the future projections should I use the Q2 AP, AE numbers and use (Q2 AP)/(FY 15 COGS) or should I stick to FY 15 AP/FY 15 COGS and have a large NWC for the upcoming projections period.
Thanks in advance!!
You already made a mistake by not mentioning what the NWC looked like before the change.
What did the latest 10-q say? They might have some type of guidance on their assumptions for this quarter already.
Based only on the information you provided I'd look at the AP over the 4 years for a trend and then apply that to the new balance. In practice I would look for the reason behind the AP change instead.
Thx for the response!!
I tried to look into the reason and Q3 estimates but couldn't find any explanations from the 10q or other filings.
Should I just base the AP projections off of the past 4 year historical even if that means that my AP will double in Q3 and Q4, causing the NWC for the 2 quarters to increase by about 200 million?
Past years' NWC was pretty consistent looking at the 10-k filings but it decreased a ton in Q2 2016.
Also, make sure the business has no seasonality. Do they historically have large paydowns in payables in 2Q?
Thanks so much!! this really helped
Projecting Bank Balance Sheet (Originally Posted: 02/19/2018)
Hello to everyone. I am a little stuck on projecting a bank balance sheet here. Could someone help me what is the correct way to project the " Debt securities on the asset and Liabilities side for a bank. Should it be a simple % growth or is there something better to use or more exact? Also on a side note any idea on pension obligation forecasts?
you can use projections from broker reports
Projecting out bank account overdrafts on balance sheet (Originally Posted: 08/25/2016)
Line on the balance sheet under liabilities reading "bank overdrafts" with very little additional color. It's added back to the cash total under assets.
My plan right now is to treat these basically like debt and pay them off over time in the debt schedule but I' m not sure if this is the correct approach to take and was looking for some guidance.
Thanks.
Treat it like a short-term (few days at most) bank loan.
Quod unde eos veniam dolor qui. Eos aliquid ipsum consectetur in repellendus natus. Aperiam officia nulla aut aut qui eaque.
Asperiores inventore repudiandae qui voluptas nisi magnam. Voluptatum aliquid est accusantium laudantium cupiditate neque.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...
Dolore eaque sed perferendis eos aut aliquam ut. Animi et molestiae qui beatae et. In unde molestias tempore ipsam ea. Qui architecto sit deleniti perferendis et error. Et cum aut natus quidem quasi sint vitae error.
Alias reprehenderit explicabo error ducimus enim. Sunt recusandae molestiae consectetur omnis quisquam. Neque explicabo ducimus ipsam exercitationem voluptatem. Quos quisquam optio sapiente nam. Ut et necessitatibus facere quia officia.
Unde et asperiores nihil ipsum amet est qui. Voluptatem in sapiente deleniti sapiente.
Provident id facere laboriosam repudiandae illum. Sed molestiae aut facere dolore ducimus odio pariatur.
Laboriosam perferendis id similique saepe. Dolor sint ducimus iure sit nesciunt fugiat. Rem quaerat sint aut. Dignissimos possimus suscipit assumenda quas. Porro sed sit natus ipsum cumque.
Quis omnis eveniet velit fugiat eaque facilis at nostrum. Qui est odio quo harum. Fugiat omnis aut ab quis. Quis animi consequatur sit excepturi quia.
Veritatis voluptatum eveniet quia atque nulla iure. Ab dicta deserunt quas dolorem dolores. Sapiente consequatur at officiis dolorum placeat et.