USER GUIDE - McKINSEY DCF VALUATION MODEL

USER GUIDE McKINSEY DCF VALUATION MODEL

Introduction to the McKinsey DCF Valuation ModelThe model contains preformatted financial statements and analytical reports forevaluating performance and valuing projected performance using both the enterprise DCFand economic profit approaches described in the book.The model ensures that all important measures, such as return on invested capital andfree cash flow, are calculated correctly so that the user can focus on analyzing acompany’s performance instead of worrying about computational errors.The model follows the three-forecast time period methodology described in the book,comprising five years of detailed forecasts, ten years of key driver, or Phase 2 forecasts,and a continuing value period.The model has been prepared in Excel 2004 and requires the installation of the AnalysisToolPak for successful operation. The model is in read-only format. Any changes youmake must be saved under a different name.1

What is the structure of the model ?The model comprises four worksheetsMcKinsey DCF Valuation ModelWorksheets Historical dataEnter all incomestatement, balancesheet, and otherhistorical dataForecast driversResultsValuationEnter forecastassumptions foreach of thethree timeperiodsCalculates:Income statementCash flowBalance sheetNOPLATInvested capitalFree cash flowEconomic profitProvides a onepage valuationsummaryINPUTSOUTPUTS2

Getting started The McKinsey DCF valuation modelopens at the Valuation Summary sheet,one of the two output sheets.Before using the model, check thatthe Analysis ToolPak “Add-In” isactive (see Tools, Add-Ins menu).Apart from the Valuation Summarysheet, the model follows a standardconvention for the use of columns:A Row titlesB Range names (if used)C Check calculationsD Non time-dependent inputs/outputsE:N Historical period (10 years)0:S Detailed forecasts (5 years)T: AC Phase 2 forecasts (10 years)AD Continuing value periodNote: The time periods can be altered(see further instructions).TIP: Choose Custom views, Show“Normal” before saving - this returnsyou to the starting view when you nextopen the model.3

Getting started Inputs are contained in two sheets:Historical Data and Forecast Drivers.The model uses the “Group” facility inExcel to divide each sheet into sections.Use 1/2 to expand or collapse the entiresheet. Use /- to expand or collapseindividual sections.The model uses range names to makethe relationships between items moretransparent. All names are shown ingrey boxes in Column B.All input cells are highlighted in yellow.All inputs are in the same units (asdefined by the Currency and Unitsentered) unless otherwise stated.Enter data in the General section ofthe Forecast Drivers sheet first. Thisenters dates and names throughoutthe model.Note: Exact formats for dates andnumbers may vary depending on defaultsettings in Excel.4

Historical inputsThe Historical Data sheet is groupedinto four sections.Up to 10 years of data can be included.If fewer years of data are available,simply leave the earlier columns blank.The model calculates cash flows in theResults worksheet, from the incomestatement and balance sheet input data.The statement of changes in equityincludes a balancing item on row 41 forother non-P&L items.Other items required to calculatehistorical economic profit and somevaluation adjustments are included in“Off Balance Sheet Items.”Enter the goodwill write-off at the startof the first historical period here.Enter number of shares in thousands.5

Forecast inputs (1 of 8)The Forecast Drivers sheet contains 10sections (including the General sectionreferred to earlier).The following pages run through eachsection, starting here with “Operations.”Operations includes operating itemsfrom the income statement and workingcapital.Each line used in further calculations hasa range name (e.g., Rev.). The forecastscan be driven by: Using the simple growth or % formulagiven. Typing in manual entries. Linking to other calculations (either oninserted rows or other worksheets).TIP: If you change any formulas to inputsor links, change formatting to maintainthe same color convention: yellow forinputs, blue for links, and white forcalculations.Note: All default percentages link toOperating Revenue, not Total Revenue.6

Forecast inputs (2 of 8)The third section “Balance Sheet Items”contains all assets and operating liabilities(except for working capital items includedabove in Trading).The model includes three standard optionsfor capital expenditure:1. Net PPE as percent of revenue,2. Capex as percent of revenue,or3. Manual input.Property, plant, and equipment is a singleline item shown on a net basis.If further detail is required, such asseparate categories of assets or grossvalues and cumulative depreciation, thenadd a separate schedule and link to Option3: Manual input.In the forecast period, it is assumed thatthere are no asset revaluations. Anyhistorical events are calculated as abalancing item in FA Hist.Intangibles are treated in the same way asgoodwill.7

Forecast inputs (3 of 8)The fourth section “Off-Balance SheetItems” comprises two separate areas:operating leases and adjustments tooperating value.Adjustments for operating leases areneeded if the amounts involved arematerial to the overall business. Theymay also be required if you arebenchmarking companies with differentfinancing policies.Adjustments to operating value are themarket values of all items that must betaken into account to allow the EquityValue to be calculated.Some of the categories already contain aformula linked to book value or the NPVof forecast cash flows (e.g., Minorityinterest).While book value may be a reasonableestimate of market value (e.g., for debt),always consider whether an alternativevaluation method is more appropriate.8

Forecast inputs (4 of 8)The next section contains non-operatingP&L items, provisions, and minorityinterests.There are three non-operating P&L items:Non-operating income (recurring), Special(pretax) items (non-recurring), andExtraordinary (posttax) items.There are also four categories ofprovisions:1. Income smoothing (NOPLAT adjustedfor changes in these provisions).2. On-going operating (treated likeworking capital).3. Restructuring provisions (noadjustments to NOPLAT, balance treatedas debt).4. Long-term operating provisions (deductoperating portion from NOPLAT, balancetreated as debt equivalent).For further details of the treatment ofprovisions, refer to “Valuation.”9

Forecast inputs (5 of 8)The next section contains assumptionsfor debt and WACC.Main inputs for debt are interest ratesand drawdowns/repayments.In the forecast period, the net cashbalance is included in the balancesheet as excess marketable securities(when positive) and balancing debt(when negative).Scheduled debt is divided into Shortterm and Long term. The long-termdebt can be further subdivided ifnecessary.Interest is calculated on the averagebalance for scheduled debt, and onthe brought-forward balance for cashand balancing debt.WACC values for each year arerequired. It is assumed that WACCcalculations are performed in aseparate model.10

Forecast inputs (6 of 8)The “Equity Finance” section containsdetails of preferred and common (orordinary) share capital, in terms of: Book value Number of shares Dividends declared Dividend creditorsFor common equity, in addition toretained earnings, it is possible toinclude: Share issues/redemptions Goodwill write-offs FX translation effects Other adjustmentsFX translation effects are treated asnon-operating cash flows.Numbers of shares are given on bothan average basis (for calculating EPS)and on a year-end basis (forcalculating value per share).11

Forecast inputs (7 of 8)The “Tax” section includes all the keyinputs to the valuation.It may be necessary to add additionalcoding to reflect national taxation rules orspecific circumstances (e.g., calculation oftax-loss carry forwards).The Tax charge is the total P&L charge(including deferred taxes).Tax payable is the tax falling due in theperiod (excluding deferred taxes).Tax paid is the amount paid during theperiod. The default assumption is that thisequals Tax payable.The effective tax rate is applied toEarnings before tax to calculate the Taxcharge. It may be necessary to adjust fornondeductible items such as goodwillamortization.The model includes rows for bothdeferred tax assets (e.g., tax losses) andliabilities (e.g., differences between taxand accounting depreciation).12

Forecast inputs (8 of 8)The final input section containsinputs for the “Phase 2,” or Key valuedriver, period and the Continuingvalue period.In Phase 2, only 6 inputs are required: Revenue growth EBITA margin Cash tax rate Net PPE as % of revenue Other invested capital as % ofrevenue Cumulative goodwillContinuing value is calculated as aperpetuity using the value driverformula. There are two inputsrequired: ROIC (on new investments) Growth (of NOPLAT)The model includes three options forROIC: WACC ROIC in last forecast period Manual input13

Results sheetThe Results sheet does not contain anyinputs.Items that come from the input sheetsare named and clearly identified.Grouping is used to show/hide thehistorical and Phase 2 time periods.The sheet is in eight sections: Income Statement Balance Sheet Traditional Cash Flow NOPLAT Invested Capital Free Cash Flow Economic Profit Valuation CalculationsThe sheet contains several checks thatare confirmed in cell C3: Balance sheet balances Cash flow reconciles to cash balances NOPLAT reconciliation Invested capital reconciliation EP & FCF valuations are equal14

PrintingBy choosing the Print option from theFile menu, the user can print the entiremodel by selecting the Print EntireWorkbook option.Printing the entire workbook is a 16page report comprising: Historical inputs1-2 Forecast inputs3-6 Financial statements7-9 Results (NOPLAT, etc.)10-14 Ratios15 Valuation summary1615

The McKinsey DCF valuation model opens at the Valuation Summary sheet, one of the two output sheets. Before using the model, check that the Analysis ToolPak “Add-In” is active (see Tools, Add-Ins menu). Apart from the Valuation Summary sheet, the model follows a standard convention for the use of columns: A Row titles B Range names (if used)