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.

Marcus_Halberstram - 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.

esbanker - Private Equity Analyst:

Cash and Cash Equivalents: Flows in directly from Cash Flow Statement

Prepaid 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

esbanker - Private Equity Analyst:

Retained Earnings: Retained earnings from previous year + Net Income - Dividends

Other 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

esbanker - Private Equity Analyst:

Accounts Receivable can be forecasted in two ways:

Simple: % of revenue

More Advanced: (AR/Revenue) * Days in period

Forecasting Accounts Payable

esbanker - Private Equity Analyst:

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:

Attack_Chihuahua:

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.

Financial Modeling Training Course

Comments (59)

Nov 26, 2012

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.

Learn More

Side-by-side comparison of top modeling training courses + exclusive discount through WSO here.

Best Response
Nov 26, 2012

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

Capitalist

    • 14
Nov 26, 2012
esbanker:

Very basic guide, but should help you get started:

This was helpful. SB'ed.

Currently: clinical psychologist (in training)
Previously: investor relations (top consulting firm), M&A consulting (Big 4), M&A banking (MM)

Apr 14, 2015

Please put some light on why depreciation and capex calculated as % of revenue

thanks

May 9, 2016

Can anyone suggest a reading material that further elaborates on what Esbanker has outlined. excellent answer by the way.

Nov 27, 2012

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.

Nov 27, 2012

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.

Capitalist

May 9, 2016

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.

    • 2
May 9, 2016

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.

May 9, 2016

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.

May 9, 2016

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

May 9, 2016
Marcus_Halberstram:

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 ?

May 9, 2016

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.

May 9, 2016

Thanks Marcus, but I gave up on the BS, no time left for it, I'm gonna go with the DDM like you said

May 9, 2016

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.

May 9, 2016

When does the DDM work better versus the DCF?

And isn't the DDM just another name for the Gordon (Constant) Growth Model?

May 9, 2016

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!

May 9, 2016
beatallica:

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!

  1. 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...
  2. 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.
Learn More

Side-by-side comparison of top modeling training courses + exclusive discount through WSO here.

May 9, 2016

is this a homework assignment?

twitter: @CorpFin_Guy

May 9, 2016

% of revenue it.

Capitalist

May 13, 2016

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.

May 25, 2017

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!

    • 1
May 25, 2017

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).

May 25, 2017
CHItizen:

A balance sheet could theoretically stay the same each year, even if a company's performance (and thus its IS and CF) highly vary.

Think about what items on the balance sheet the IS and CF affect.....

May 25, 2017

Just ask Marty Whitman - NAV from the B/S. Focus on what is, not what you think it will be.

'Before you enter... be willing to pay the price'

May 25, 2017

This is a common interview question and the only correct answer is the CF statement.

    • 1
May 25, 2017

You can value a company using NAV, that is all.

'Before you enter... be willing to pay the price'

May 25, 2017

Would you change your answer if it was just a VP?

May 25, 2017

How far out do you need to forecast it? Through March close or some period further into the future?

May 25, 2017

I don't have a specific period. But my CFO wants to know for example: Monday WK2 of April cash balance.

May 25, 2017

I guess for the current close month. Any ideas?

May 25, 2017

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.

May 25, 2017

I think i have the general idea. I guess the hard part is to estimate expected inflows/outflows. Thanks!

May 25, 2017

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.

May 25, 2017

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)

May 25, 2017

Thanks. I re-did the formatting. How can I check if interest expense is the problem?

May 25, 2017

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

May 25, 2017

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!

May 25, 2017

.

May 25, 2017

anyone else getting an error message?

May 25, 2017

Take your Fy2015E NI less YTD 7/31/15 NI or back into it using a percentage of the remaining year.

May 25, 2017

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.

May 25, 2017

Back into the appropriate stub period using a quick % for the year remaining. multiply your FYE NI by 5/12 to reflect the remaining 5 months - I would recreate another stub IS to reflect the change.

May 25, 2017

Pmed you

May 25, 2017

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.

May 25, 2017

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.

May 25, 2017

Check the recent management call and see if they mention something about it. If not, go with the four year trend. I would go with the 4yr trend if you find nothing. I would believe they are trying to see your modeling and analysis skills. Maybe footnote that the company had a large paydown in payables and no explanation was provided in the MD&A

May 25, 2017

Also, make sure the business has no seasonality. Do they historically have large paydowns in payables in 2Q?

May 25, 2017

Thanks so much!! this really helped

May 25, 2017

1-Click to Unlock All Comments - 100% FREE

Why do I need to be signed in?
WSO is a knowledge-sharing community that depends on everyone being able to pitch in when they know something.
+ Bonus: 6 Free Financial Modeling Lessons with 1-Click Signup ($199 value)

stunt on these hoes

    • 1
May 25, 2017