1. Forecast the majority of income statement items as a percentage of revenue.
2. Forecast working capital accounts using efficiency ratios and link most other balance sheet items with operating costs, COGS or revenue.
1. Income Statement
Projecting a firm’s pro forma financial statements can initially be overwhelming. It is important not to get embroiled in the details and lose sight of the bigger goal, which is to determine the effect on cash flows. Financial statement models typically begin with a projection of revenue. Most other financial statement items can be estimated as a function of sales.
As a general rule of thumb, all three financial statements should be kept on the same page. Model assumptions, such as ratios and growth rates, should be placed above or between statements and run across time as they may need to change year-to-year. It is advisable to follow industry best practice and format hard-coded values in blue and formulas in black. Forecast time horizons should allow enough time for key value drivers to converge with industry averages. After the financial statements have been established for the explicit forecast period and model drivers are expected to remain constant thereafter, a terminal value can be estimated to capture all subsequent periods into perpetuity.
Whereas disclosures in the footnotes of a company’s financials are fairly detailed about revenue, this is often not the case with operating costs. It is therefore common for analysts to consider costs at an aggregated level. (If available, segment-level margins should be used). In forecasting expenses, examine the ratios of mature industry peers and consider if (and how fast) the firm’s own ratios will converge to the average. Mean reversion is commonly greatest in the earlier years and then tapers off. Consider deflating revenue and expense figures to estimate the real changes by line item. Future expected inflation should then be introduced to forecast nominal changes.
- Revenue: Common approaches to forecasting revenue include analyzing historical trends (here) and modeling revenue using a top-down approach (here).
- Cost of Goods Sold: Forecasting COGS as a percent of revenue is usually a good approach since it has a direct link with sales. Because COGS is a relatively large expense item, small errors in its estimation can have a material impact on the firm valuation. If possible, COGS should be analyzed by segment, product category, or volume and price components. Gross margins of competitors provide a useful cross-check for a estimating a realistic projection. Note that a firm’s hedging strategies (as revealed in the footnotes of an annual report) may have an impact on COGS.
- Selling, General & Administrative: SG&A has less of a direct relationship with revenue than other expenditures such as COGS. It is therefore important to analyze the different components of SG&A, if disclosed in the footnotes. Selling and distribution expenses, for example, can usually be estimated as a percent of revenue while personnel costs cannot (which are related to headcount). To better understand if economies of scale are being realized, SG&A can further be analyzed by estimating the percent of costs that are variable (=%Δ SG&A / %Δ Revenue). The remainder (=1 ─ % variable costs) are fixed. (This same technique can be used for other line items).
- Depreciation: Depreciation (and amortization) is best estimated as a percent of the average value of assets on the books during the year. If deprecation is not disclosed on the income statement, it is likely a part of COGS and will need to be forecasted on the statement of cash flows.
- Research & Development: R&D expenditures are often forecasted as a percent of revenue. They are however somewhat fixed and tend to change only gradually over time. Comparing a firm’s R&D expense as a percent of sales against other competitors can be useful in estimating a target ratio.
- Interest Income: Interest income is calculated as the assumed interest rate multiplied by the average cash position. This rate can be estimated by looking at the historical interest earned on cash, cash equivalents, and short-term deposits. Since interest income depends on future investments it should be left blank for now and determined once the balance sheet is complete.
- Interest Expense: Interest expense is a function of the debt level on the balance sheet. Notes to the financial statements provide detail about the maturities of the company’s debt and corresponding interest rates. Leave interest expense blank initially and link to the balance sheet once complete.
- Taxes: The effective tax rate (=taxes / earnings before taxes) should be used for projecting earnings on the income statement. One-time events should be adjusted for to obtain a normalized tax rate. In determining an average historical rate, sum up all taxes over the period and divide by the sum of all earnings before taxes. Be aware that differences between taxes reported on the income statement and statement of cash flow result in changes to deferred tax assets or liabilities.
- Dividends: Dividends can be forecasted as a portion of earnings (i.e. payout ratio) or set as a constant dollar amount.
2. Balance Sheet
In determining how to project a balance sheet item, consider what the item represents and what other variable(s) it is tied to. Consider linking balance sheet items to consolidated income statement items (e.g. COGS and operating expenses together). As a general rule of thumb, balance sheet assumptions have less of an impact on a firm valuation than do income statements assumptions. Any other items not explicitly listed below can be linked to revenue, matched with some other income statement item, or held constant.
In order to close a balance sheet model and ensure that assets and liabilities are equal, it is necessary to use a plug figure. The plug can either be cash and marketable securities, debt (i.e. notes payable or long-term debt), or stock. Deciding which plug to use depends on assumptions about how the firm will finance itself. Be sure to note how a firm’s financial ratios (e.g. debt-to-equity) are impacted throughout the explicit forecast period depending on the choice of plug. Then confirm that the balance sheet balances in each year.
Working capital accounts are commonly modeled through the use of efficiency ratios (see below). It is also possible to estimate a composite number for non-cash working capital rather than projecting each component individually. This method is of course easier and often more accurate under certain conditions (i.e. if individual accounts are not expected to materially diverge from historical trends). As companies grow and mature, they will usually reduce their net new investment in operating assets. Note however that decreases in working capital should only occur for short periods.
- Cash & Marketable Securities: Cash can serve as a plug figure or be derived from the statement of cash flows.
- Accounts Receivable: Future A/R can be projected by assuming a number of Days Sales Outstanding and combining that assumption with a sales projection (e.g. if revenue is projected to be $10M and it is estimated to take 30 days to collect, then A/R would be $100M * 30/365 = $8.2M).
- Inventory: Inventory is projected using Days’ Sales in Inventory and combining it with a COGS projection.
- Prepaid Expenses: Prepaid expenses can be linked with operating expenses or COGS (or both combined).
- Property, Plant & Equipment: A simple forecast of net PPE can be generated as a percentage of revenue. This can be estimated by looking at the average sales-to-PPE ratio for the firm’s past and competitors in the industry. Because net PPE changes are a result of capital expenditures and deprecation, gross PPE can also be estimated as a function of sales and accumulated depreciation as a percentage of gross PPE. Net PPE is left blank until capital expenditures and depreciation are projected on the statement of cash flows. Be aware that historical net PPE is affected by write downs and asset sales.
- Goodwill and Intangible Assets: These items should generally be kept constant into the future.
- Accounts Payable: Future A/P is commonly forecasted by assuming a number of Days Payable Outstanding and combining that assumption with a COGS projection.
- Deferred Revenue: Deferred revenue should be linked with sales as a percentage of revenue.
- Accrued Expenses: Accrued expenses are generally related to operating expenses or COGS (or both), though it of course depends on what is being accrued.
- Accrued Income Taxes: This should change in relation to the tax expense on the income statement or be held constant.
- Accumulated Retained Earnings: Retained earnings is the previous year’s accumulated retained earnings, plus the current year’s net income, less dividends.
With the estimates for the income statement and balance sheet in place, the cash flow statement is almost automatically generated. Be aware that historical changes in balance sheet items may at times differ from changes reported in the statement of cash flows. This can arise due to acquisitions or divestitures and any currency translations of non-domestic subsidiaries. Future debt and equity levels should be based within a broader analysis of the company’s future capital structure. Leverage ratios are typically used for forecasting purposes.
Given the uncertainty of forecasts, analysts should apply sensitivity and scenario analyses to their models of financial statements. Sensitivity analysis involves changing one assumption at a time to see the impact on firm value. Scenario analysis involves changing multiple assumptions at the same to create worst-case (i.e. downside), base-case, and best-case (i.e. upside) scenarios. It is also possible to value a company using a probability-weighted average of the various scenarios.
Street of Walls. Three Statement Financial Modeling.