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