In a finance and investment banking interview, candidates will almost certainly be asked questions that test their understanding of the relationship between the balance sheet income statement, and cash flow statement. The reason is that on-the-job modeling is heavily predicated on a deep understanding of this relationship.
In our self study programs and live seminars, we spend a lot of time talking about how to build DCF, Comps, M&A, LBO, and Restructuring Models effectively in Excel. We spend a lot of time making sure that our trainees understand the inter-relationship of the balance sheet, income statement, and cash flow statement because it is so vital to properly understanding these models.
Accordingly, we decided to list some basic best practices for projecting balance sheet line items below. As a warning, what you’ll read below is inevitably a simplification but we hope that it is a helpful one for many of you. For complete training on this program, please enroll in our self study program or a live seminar.
2017 Update: Click here for the new Balance Sheet Projections Guide
Imagine that you are tasked with building a financial statement model for Wal-Mart. Based on analyst research and management guidance, you have projected the company’s revenues, operating expenses, interest expense and taxes – all the way down to the company’s net income. Now it is time to turn to the balance sheet. Now unless you have a thesis about a company’s accounts receivable (often you will not), the default assumption should be to link receivables to your revenue growth assumptions. In other words, if revenues are expected to grow 10% next quarter, so should receivables UNLESS you have a thesis to the contrary. Effective modeling is all about building in default assumptions, and incorporating features that enable modelers to sensitize away from those default assumptions. Below is a list of balance sheet line items, along with guidance on how they should be projected. Enjoy!
Assets
Accounts receivable (AR)
- Grow with credit sales (net revenues)
- Using an IF statement, model should enable users to override with days sales outstanding (DSO) projection, where days sales outstanding (DSO) = (AR / Credit Sales) x days in period
Inventories
- Grow with cost of goods sold (COGS)
- Override with inventory turnover (Inventory turnover = COGS / Average inventory)
Prepaid expenses
- Grow with SG&A (may include COGS if the prepaids are cycled through COGS)
Other Current Assets
- Grow with revenues (presumably these are tied to operations and grow as the business grows)
- If reason to believe that they are not tied to operations, straight-line projections
PP&E
- PP&E – beginning of period (BOP)
- + Capital expenditures (grow historicals with sales or use analyst guidance)
- – Depreciation (function of depreciable PP&E BOP divided by useful life)
- – Assets sales (use historical sales as guide)
- PP&E – end of period (EOP)
Intangibles
- Intangibles – BOP
- + Purchases (grow historicals with sales or use analyst guidance)
- – Amortization (amortizable intangibles BOP divided by useful life)
- Intangibles – EOP
Other non-current assets
- Straight-line (unlike current assets, lower likelihood these assets are tied to operations – could be investment assets, pension assets, etc.)
Liabilities
Accounts payable
- Grow with COGS
- Override with payables payment period assumption
Accrued Expenses
- Grow with SG&A (may also include COGS depending on what is actually accrued)
Taxes Payable
- Grow with the growth rate in tax expense on income statement
Taxes Payable
- Grow with the growth rate in tax expense on income statement
Other current liabilities
- Grow with revenues
- If reason to believe that they are not tied to operations, straight-line projections
