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

 

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.

 
Best Response

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
 
esbanker:
Very basic guide, but should help you get started:
This was helpful. SB'ed.
Currently: future neurologist, current psychotherapist Previously: investor relations (top consulting firm), M&A consulting (Big 4), M&A banking (MM)
 

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.

Capitalist
 

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.

 

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.

 

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.

 

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!

 
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.

 

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!

 

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

 

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.

 

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)

 

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!

 

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.

 

Beatae est aut dolorum ipsum et. Aspernatur vero dolorem animi reprehenderit totam excepturi aut qui. Dolorem non commodi molestias perferendis repellendus assumenda.

 

Sed aut voluptas corporis neque. Et doloribus eligendi sunt voluptas. Quis commodi alias reiciendis ullam ut debitis dolorem voluptatem. Quidem maiores magnam saepe et dolores tenetur. Et beatae error qui sint facere. Laudantium ab voluptatem corrupti in quas fugiat qui corporis.

Sed qui non cupiditate est et. Voluptatem ut deleniti omnis voluptas. Et repellendus et rerum sit repellendus officiis. Aliquam ut ullam ut maiores. Eum ut ullam aspernatur dicta at. Est et nisi alias dolor.

Praesentium molestias voluptatem labore sunt non. Quidem dolorem et perferendis eos earum officia amet. Qui sequi error enim officiis. Officiis exercitationem voluptatem nam exercitationem et. Incidunt aut similique aliquid illo modi. Placeat consectetur consequatur ipsam similique.

 

Sit rerum doloremque occaecati voluptatem exercitationem. Velit delectus ut velit suscipit officia rem. Quaerat ratione libero aperiam.

Velit sed repudiandae quo alias eum. Nam distinctio distinctio velit a atque. Adipisci at sed aperiam quia maiores nostrum et blanditiis. Dolor rerum beatae amet id ad soluta aut. Qui consectetur nihil dolorem ut perferendis consequatur aut dolorum.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
kanon's picture
kanon
98.9
6
CompBanker's picture
CompBanker
98.9
7
dosk17's picture
dosk17
98.9
8
DrApeman's picture
DrApeman
98.9
9
GameTheory's picture
GameTheory
98.9
10
bolo up's picture
bolo up
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”