UTS CRICOS 00099F
Building a Financial Model
UTS CRICOS 00099F
Components of a Good Model
Valuation, 7th Edition
McKinsey & Company Inc., Tim Koller, Marc Goedhart, David Wessels
Chapter 13. Forecasting Performance
3Components of a good model
1. Raw historical data
o Collect raw data from the company’s financial statements, footnotes, and external
reports in one place
o Report the raw data in their original form.
2. Integrated financial statements
o Create a set of historical financials that find the right level of detail using figures from the
raw-data worksheet as input
o The income statement should be linked with the balance sheet through retained
earnings.
o This worksheet will contain historical and forecasted financial statements.
Building a Financial Model
4Components of a good model (cont.)
3. Historical analysis and forecast ratios
o For each line item in the financial statements, build historical ratios, as well as forecasts
of future ratios. The forecast ratios:
• are based on past performance and expectations of future changes
• will generate the forecasted financial statements contained on the previous sheet
4. Market data and WACC
o Collect all financial market data on one worksheet. This worksheet will contain:
• estimates of beta, the cost of equity, the cost of debt, and the weighted average cost of capital
• historical market values and valuation/trading multiples
Building a Financial Model
5Components of a good model (cont.)
5. Reorganised financial statements:
o Reorganize financial statements (both historical and forecast) to calculate NOPLAT, its
reconciliation to net income, invested capital, and its reconciliation to total funds
invested.
6. ROIC and FCF:
o Use the reorganised financials to build return on invested capital, economic profit, and
free cash flow
7. Valuation summary:
o Presents discounted cash flows, discounted economic profits, and final results.
o Includes the value of operations, nonoperating assets, nonequity claims, and the
resulting equity value.
Building a Financial Model
UTS CRICOS 00099F
Forecasting Performance
Valuation, 7th Edition
McKinsey & Company Inc., Tim Koller, Marc Goedhart, David Wessels
Chapter 13. Forecasting Performance
7Mechanics of Forecasting | 6 Steps
1. Prepare and analyse historical financials
o Before forecasting future financials, you must build and analyse historical financials.
2. Build the revenue forecast
o Almost every line item will rely directly or indirectly on revenues.
o Estimate future revenues by using either a top-down (market-based) or bottom-up
(customer-based) approach.
o Forecasts should be consistent with historical economy-wide evidence on growth.
3. Forecast the income statement
o Use the appropriate economic drivers to forecast operating expenses, depreciation,
interest income, interest expense, and reported taxes.
Building a Financial Model
7
8Mechanics of Forecasting | 6 Steps
4. Forecast the balance sheet: invested capital
o On the balance sheet, forecast operating working capital; net property, plant, and
equipment; goodwill; and nonoperating assets.
5. Forecast the balance sheet: investor funds
o Complete the balance sheet by computing retained earnings and forecasting other
equity accounts. Use excess cash and/or new debt to balance the balance sheet.
6. Calculate ROIC and FCF
o Calculate ROIC to assure forecasts are consistent with economic principles, industry
dynamics, and the company’s ability to compete.
o To complete the forecast, calculate free cash flow as the basis for valuation
Building a Financial Model
8
9Step 1: Prepare and Analyse Historical Financials
• Input the company’s historical financials into a spreadsheet
• On the raw-data sheet
o record financial data as originally reported
o never combine multiple data into a single cell
• Once you have collected raw data from the reported financials and notes use the data
to build:
o the income statement, balance sheet, and statement of retained earnings
• Aggregate immaterial line items
o Analyzing and forecasting numerous immaterial items can lead to confusion and
introduce mistakes
o Never to combine operating and nonoperating accounts into a single category
Building a Financial Model
9
10
Step 2: Build the Revenue Forecast
• Top-down forecast, in which you estimate revenues by:
o sizing the total market,
o determining market share, and
o forecasting prices
• Bottom-up approach you can use the company’s own
o forecasts of demand from existing customers,
o customer churn, and
o the potential for new customers.
• When possible, use both methods to establish bounds for the forecast.
Building a Financial Model
10
11
Step 3: Forecast the Income Statement
With a revenue forecast in place, forecast individual line items using a three-step process:
1. Decide what economic relationships drive the line item.
o For most line items, forecasts will be tied directly to revenues.
o Some line items will be economically tied to a specific asset or liability: Interest income <= cash and
marketable securities
2. Estimate the forecast ratio
o For each line item on the income statement, compute historical values for each ratio, followed by
estimates for each of the forecast periods.
3. Multiply the forecast ratio by an estimate of its driver
o Most line items are driven by revenues
o Ratios dependent on other drivers should be multiplied by their respective drivers
Building a Financial Model
11
12
Step 3: Forecast the Income Statement
Operating expenses
• For cost of goods sold; selling, general, and administrative; and research and
development forecasts will be based on revenues
Building a Financial Model
12
13
Step 3: Forecast the Income Statement
Depreciation
• As a percentage of revenues: If capital expenditures are lumpy, Depreciation forecasts will be inaccurate
• As a percentage of Net PP&E: Ideally, depreciation would be tied to gross PP&E. But this requires modeling
asset retirements, which can be tricky
• Based on equipment purchases and depreciation schedules (if you are inside the company): For each
asset, project depreciation using an appropriate depreciation schedule, asset life, and salvage value
Building a Financial Model
13
14
Step 3: Forecast the Income Statement
Nonoperating income
• Generated by nonoperating assets, such as customer financing, nonconsolidated
subsidiaries, and other equity investments
Building a Financial Model
14
• For investments in which the
company owns less than 20%,
use historical growth in
nonoperating income
• For investments in which the
company owns more than 20%,
use nonoperating income as a
percentage of the appropriate
nonoperating asset
15
Step 3: Forecast the Income Statement
Interest Expense
• The appropriate driver for interest expense is total debt. Total debt, however, is a
function of interest expense, and this circularity leads to implementation problems
• To avoid this feedback effect, compute interest expense as a function of the prior
year’s total debt
Building a Financial Model
15
16
Step 3: Forecast the Income Statement
Interest Income
• Estimate interest income the same way, with forecasts based on the asset generating
the income
• Interest income can be generated by a number of different investments: excess cash,
short-term investments, customer financing, and other long-term investments
Building a Financial Model
16
17
Step 3: Forecast the Income Statement
Income Taxes
• Keep operating and nonoperating taxes separate
• Operating Taxes
o Use operating tax rate (not statutory tax rate)
o Many companies pay taxes at rates below their
local statutory rate because of low foreign rates
and operating tax credits.
o Statutory rate for this company is 40%
o Operating Tax Rate is then 34.4%
Building a Financial Model
17
9.2
2.0
-1.6
5.6
Tax credits
29.6
34.4%
Operating Tax
Operating Tax Rate
18
Step 3: Forecast the Income Statement
Income Taxes
• Nonoperating taxes
o For each line item between
EBITA and earnings before
taxes, compute the marginal
taxes related to that item
o If the company does not
report each item’s marginal
tax rate, use the statutory
rate
Building a Financial Model
18
Note how Average Tax
Rate changes as leverage
and other nonoperating
items change
19
Step 3: Forecast the Income Statement
Completed Forecast
Building a Financial Model
19
20
Step 3: Forecast the Income Statement
Summary of Forecast Drivers
Building a Financial Model
20
21
Step 4: Forecast the Balance Sheet: Invested Capital
and Nonoperating Assets
• Forecast items in the balance sheet:
o directly (in stocks): e.g. forecasts end-of-year receivables as a function of revenues
o indirectly by forecasting changes (in flows): e.g. forecasts the change in receivables as
a function of the growth in revenues
• The stock approached is preferred
o The relationship between the balance sheet accounts and revenues (or other volume
measures) is more stable.
Building a Financial Model
21
22
Step 4: Forecast the Balance Sheet: Invested Capital
and Nonoperating Assets | Operating Working Capital
• To start the balance sheet, forecast items within operating working capital
• Nonoperating items, such as excess cash, short-term debt, and dividends payable, are
excluded
Building a Financial Model
22
Item Driver Ratio Days
Accounts receivable Revenue AR / Revenue AR / Revenue x 365
Inventories COGS Inv. / COGS Inv. / COGS x 365
Accounts payable COGS AP / COGS AP / COGS x 365
Accrued expenses Revenue AE / Revenue AE / Revenue x 365
23
Step 4: Forecast the Balance Sheet: Invested Capital and
Nonoperating Assets | Operating Working Capital (cont.)
Building a Financial Model
23
Days Ratio
2.08%
50%
22.2%
Operating Cash2010 = 288.0 × 2.08%=6.0
Operating Cash2010 = Τ288.0 × 7.6 365=6.0
24
Step 4: Forecast the Balance Sheet: Invested Capital and
Nonoperating Assets | Property, Plant and Equipment
• Over long periods, net PP&E to revenue ratios tend to be stable.
• These three steps can be used to forecast PP&E, Depreciation and Capital
Expenditures:
1. Forecast net PP&E as a percentage of revenue
2. Forecast depreciation as a percentage of net PP&E
3. Calculate capital expenditures by summing the increase in net PP&E plus depreciation.
Building a Financial Model
24
25
Step 4: Forecast the Balance Sheet: Invested Capital and
Nonoperating Assets | Property, Plant and Equipment
Building a Financial Model
25
Income Statement Balance Sheet
ΤNet PP&E2009 Revenue2009 = 104.2% Revenue2010= 288.0
Capital Expenditure2010 = Net PP&E2010 − Net PP&E2009 + Depreciation2010
Depreciation2010= 23.8
Net PP&E2010 = 288.0 × 104.2% = 300.0
Capital Expenditure2010 = 300 − 250 + 23.8 = 73.8
Estimate Ratio
Forecast Net PP&E
26
Step 4: Forecast the Balance Sheet: Invested Capital and
Nonoperating Assets | Goodwill and acquired intangibles
• Goodwill and acquired intangibles are recorded when the acquisition price exceeds the target’s book
value
• Recommended approach
o Unless you have internal information, it is best not to model potential acquisitions explicitly and hold
goodwill constant at its current level.
o Since adding a sero-NPV investment will not increase the company’s value, forecasting acquisitions is
unnecessary
• If you decide to forecast acquisitions
o First assess what proportion of future revenue growth they are likely to provide
o For example, if a company announced an intention to grow by 10 percent annually—5 percent
organically and 5 percent through acquisitions. In this case, measure historical ratios of goodwill and
acquired intangibles to acquired revenues, and apply those ratios to acquired revenues
Building a Financial Model
26
27
Step 5: Forecast the Balance Sheet: Investor Funds
Retained Earnings
Building a Financial Model
27
Retained Earnings2009 =Retained Earnings2008 + Net Income2009 − Dividends2009
Income Statement Balance Sheet
Estimate Dividend Payout
Retained Earnings2010 =Retained Earnings2009 + Net Income2010 − Dividends2010
Dividends2009 = 22 DPO2009 = Τ22 48 = 45.8%
Retained Earnings =Retained Earnings− + Net Income − Dividends
Retained Earnings2010 =82 + 58.8 − 58.8 × 45.8% = 113.8
28
Step 5: Forecast the Balance Sheet: Investor Funds
The Plugs
• Different combination of the following are used to complete the balance
o Excess cash, Short-term debt, Long-term debt, Newly issued debt, Common stock
• Simple models
o Assume common stock remains constant and existing debt either remains constant or is
retired on schedule
o Set one of the remaining two items (excess cash or newly issued debt) equal to sero
o Then use the primary accounting identity to determine the remaining item
• Mixed models
o In growing firms, use a combination of ST Debt, LT Debt and Common Stock to fund
growth
Building a Financial Model
28
29
Step 5: Forecast the Balance Sheet: Investor Funds
Completed Forecast
Building a Financial Model