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 29 51作业君版权所有