Financial Modelling and DCF Valuation

data2impact
the data automation experts

Leipzig, December 3rd, 2013

Trainer introduction Peter Albert

Vita

Head of data2impact Dresden office

Founder and former CEO of data2impact Scandinavia K12/13 alumnus Former Engagement Manager with McKinsey & Company, Zurich Project Manager and Developer with Intershop AG, Jena Evaluation models for Equities Research department of a global investment bank Online tool to create healthcare scorecard for countries Simulation of impact from pricing change for a medical company Daily performance management tool for fleet management of a national parcel services Weekly project reporting for a national ministry of roads and traffic

Recent projects

Technologies

Excel PowerPivot

Access Visual Basic

PowerPoint thinkcell

SQL Server ASP.NET MVC/C#

Contact details

www.data2impact.com [email protected] +49 (0) 151 6730 1525

data2impact the data automation experts
1

Content

Modelling basics Profit & Loss Net Working Capital Depreciation & Capex Pensions Debt financing

Discounted Cashflow and Terminal Value

Sensitivities Sanity checks

data2impact the data automation experts

What is a model?

XX
Profit & Loss statement
(in M) Sales COGS Gross profit SG&A Rent Other expenses EBITDA Depreciation Amortization EBIT Interest expenses Pension interest expenses Interest income Financing costs PBT Tax Net income Dividend Previous periods 2009 2010 619 620 (449) (444) 171 176 (43) (42) (14) (14) (6) (6) 107 114 (38,6) (38,6) 69 75 (20) (20) 1 1 (1) (1) 48 55 (14) (15) 35 39 2011 598 (440) 158 (49) (14) (5) 91 (41,7) 49 (21) 0 (1) 27 (7) 19 2012 620 (454) 167 (38) (18) (8) 102 (51,0) 51 (22) 1 (1) 29 (8) 21 (52) Current Projection 2013 2014 633 652 (477) (491) 156 161 (40) (41) (22) (23) (6) (7) 88 91 (53) (59) 35 31 (17) (11) 0 0 (2) 16 20 (5) (6) 12 15 (8) (10) 2015 684 (515) 169 (43) (24) (7) 95 (66) 29 (10) 0 20 (5) 14 (10) 2016 719 (541) 178 (45) (25) (7) 100 (73) 27 (8) 0 19 (5) 13 (9) 2017 747 (563) 185 (47) (26) (7) 104 (81) 23 (7) 0 17 (5) 12 (8) 2018 770 (580) 190 (48) (27) (8) 107 (88) 19 (5) 0 14 (4) 10 (7) CAGR 2019 09-12 13-19 789 0,1% 3,7% (594) 0,4% 3,7% 195 -0,8% 3,7% (50) -3,6% 3,7% (28) 8,5% 3,7% (8) 7,4% 3,7% 110 -1,6% 3,7% (96) 9,8% 10,6% 13 -9,4% -15,0% (4) 2,1% -22,2% 0 -20,6% 3,5% -0,3% -100,0% 10 -15,9% -8,2% (3) -15,9% -8,2% 7 -15,9% -8,2% (5) data2impact data2impac t Financial Modelling 3 - TRAINING MODEL (12/07/13) C:\Users\Peter\Documents\d2i\Training\Finance\New training\Exercises\[Full model.xlsb]M odel

Balance Sheet
(in M)

Cash, petty cash, temporary investments Accounts receivable Inventory Other current assets Current assets Properties, plant & equipment Intangible assets Non-current assets Assets Accounts payable Other current liabilities Current liabilities Pension liabilities Existing debt tranche Term loan Liabilities Paid in capital Net income Dividends Retained earnings Equities Liabilities & equity Balance check

Previous periods 2009 24 51 38 5 119 326 1 327 446 48 48 40 230 318 1 35 91 127 446

2010 69 51 39 5 164 320 1 322 486 48 48 40 230 319 1 39 126 167 486

2011 86 48 37 4 174 328 1 329 503 46 46 41 230 317 1 19 166 186 503

2012 56 48 32 10 146 325 1 326 472

Current Projection 2013 2014 84 72 43 45 33 34 6 7 166 157 316 303 1 1 318 304 484 461 46 46 44 236 326 1 12 (8) 153 158 484

2015 63 47 35 7 152 285 1 286 438

2016 59 49 37 7 153 262 1 263 416

2017 61 51 39 7 158 233 1 234 393

2018 68 53 40 8 168 199 1 200 368

CAGR 2019 09-12 13-19 81 31,8% -0,5% 54 -2,2% 3,7% 41 -5,6% 3,7% 8 25,5% 3,7% 184 7,0% 1,7% 157 -0,1% -11,0% 1 159 -0,1% -10,9% 343 1,9% -5,6% 57 57 47 59 163 1 7 (5) 176 180 343 -3,1% -3,1% 2,6% -0,1% -15,9% 26,4% 6,7% 1,9% 3,7% 3,7% 1,3% -20,6% -10,9% -8,2% -8,2% 2,3% 2,1% -5,6%

44 44 43 230 317 1 21 (52) 185 155 472

47 47 44 207 298 1 15 (10) 157 163 461

49 49 45 177 271 1 14 (10) 161 167 438

52 52 45 148 245 1 13 (9) 166 171 416

54 54 46 118 218 1 12 (8) 170 175 393

56 56 47 89 191 1 10 (7) 173 177 368

Oxford Dictionary: "A simplified description, especially a mathematical one, of a system or process, to assist calculations and predictions"
data2impact the data automation experts
3

Spreadsheet development & usage can be divided up into 3 stages

Stages of spreadsheet model development & usage Preparation & Structure Building & Formatting Analysis

Planning Structure Assumptions

Best practice
guidelines

Sources Naming Units Format

Sanity checking Documentation

data2impact the data automation experts

Plan the structure and analytical approach of the spreadsheet modle before building it

Preparation & Structure

Building & Formatting

Analysis

Do not switch on the machine! Agree models requirements with team Decide what type of model you are building Plan the analytical approach using Schematic diagrams Back of envelope calculations Decide model structure, paying special
attention to separating assumptions from inputs and outputs

Choose the right tool

data2impact the data automation experts

Find out as much as possible about the purpose and requirements of the model before you start
Questions to ask before you start

General What is the purpose of the model? How will the results be validated/syndicated? What software and which version? Data What data will be required? What are the data sources? What proxies/samples could be used if there is a
delay? Which data is likely to change?

Calculations What calculations will be required? Will scenarios be needed and if so, which? What kind of back-of-the-envelope calculations
could be used?

Outputs What outputs should the model produce? What stakeholders will use the model? What format should the outputs be in?
data2impact the data automation experts 6

Structure of a model
Input
Assumptions
Start year Financial assumptions Corporate tax Dividends payout ratio DCF assumptions Debt Target capital structure - debt/market value40,0% Debt spread (at 0% debt ratio) 0,5% Abs. increase per 10%pt debt ratio 0,4% Levered debt premium 1,9% WACC Tax rate 30,0% Risk free rate 3,0% Market risk premium 5,0% Resulting WACC 7,8% Perpetual growth EBITDA exit multiple Start year cash flow share Refinancing of existing debt Year of refinancing Tenor Amortization offset Loan amount Interest rate Financing fee Interest rate cash account 2013 8 1 230 5,0% 1,0% 0,3% 2,2% 8,0x 50% 28,0% 70,0% 2013 Operating assumptions Capex/depreciation Capex (% of sales) Useful life (years) 2013 Working capital Accounts receivable (in d) Inventory (in d) Other current assets Accounts payable Other current liabilities Growth assumptions Sales Costs as % of Sales COGS SG&A Rent Other expenses Pensions Service costs Interest costs (in years) (in years) (in M) Net debt adjustment Pension liability PV multiple Operating lease multiple 25 25 1,0% 35 2014 25 25 1,0% 35 -

Calculation
Profit & Loss statement
(in M) Sales COGS Gross profit SG&A Rent Other expenses EBITDA Depreciation Amortization EBIT Interest expenses Pension interest expenses Interest income Financing costs PBT Tax Net income Dividend Previous periods 2009 2010 619 620 (449) (444) 171 176 (43) (42) (14) (14) (6) (6) 107 114 (38,6) (38,6) 69 75 (20) (20) 1 1 (1) (1) 48 55 (14) (15) 35 39 Current Projection 2013 2014 633 652 (477) (491) 156 161 (40) (41) (22) (23) (6) (7) 88 91 (53) (59) 35 31 (17) (11) 0 0 (2) 16 20 (5) (6) 12 15 (8) (10) CAGR 2019 09-12 13-19 789 0,1% 3,7% (594) 0,4% 3,7% 195 -0,8% 3,7% (50) -3,6% 3,7% (28) 8,5% 3,7% (8) 7,4% 3,7% 110 -1,6% 3,7% (96) 9,8% 10,6% 13 -9,4% -15,0% (4) 2,1% -22,2% 0 -20,6% 3,5% -0,3% -100,0% 10 -15,9% -8,2% (3) -15,9% -8,2% 7 -15,9% -8,2% (5)

Output
Sensitivity analysis (using EBITDA exit multiple method)
2018 770 (580) 190 (48) (27) (8) 107 (88) 19 (5) 0 14 (4) 10 (7)

7,0% 7 2015 25 25 1,0% 35 2016 25 25 1,0% 35 2017 25 25 1,0% 35 2018 25 25 1,0% 35 2019 25 25 1,0% (% of sales) 35 - (% of COGS)

2,0%

3,0%

5,0%

5,0%

4,0%

3,0%

2,5%

75,3% 6,3% 3,5% 1,0%

75,3% 6,3% 3,5% 1,0%

75,3% 6,3% 3,5% 1,0%

75,3% 6,3% 3,5% 1,0%

75,3% 6,3% 3,5% 1,0%

75,3% 6,3% 3,5% 1,0%

75,3% 6,3% 3,5% 1,0%

2011 598 (440) 158 (49) (14) (5) 91 (41,7) 49 (21) 0 (1) 27 (7) 19 -

2012 620 (454) 167 (38) (18) (8) 102 (51,0) 51 (22) 1 (1) 29 (8) 21 (52)

2015 684 (515) 169 (43) (24) (7) 95 (66) 29 (10) 0 20 (5) 14 (10)

2016 719 (541) 178 (45) (25) (7) 100 (73) 27 (8) 0 19 (5) 13 (9)

2017 747 (563) 185 (47) (26) (7) 104 (81) 23 (7) 0 17 (5) 12 (8)

Enterprise value sensitivity to perpetuity growth rate and WACC

(in M) 1.038 6,8% 7,3% 7,8% 8,3% 8,8% 6,0x 900 880 860 840 822 EBITDA exit multiple 7,0x 8,0x 995 1.089 971 1.063 949 1.038 927 1.014 906 991

Enterprise value sensitivity to debt ratio and WACC

(in M) EBITDA exit multiple 7,0x 8,0x 943 1.032 947 1.036 949 1.038 949 1.038 946 1.035

9,0x 1.183 1.155 1.128 1.101 1.075

10,0x 1.278 1.247 1.217 1.188 1.160

1.038 20,0% 30,0% 40,0% 50,0% 60,0%

6,0x 855 858 860 859 857

9,0x 1.121 1.125 1.128 1.127 1.124

10,0x 1.209 1.215 1.217 1.217 1.213

Enterprise value sensitivity to WACC and debt ratio 1.400 1.200 1.000
800

Enterprise value sensitivity to WACC and debt ratio 1.400 1.200 1.000
800 600

600
(0,6) (0,6) (0,6) (0,6) (0,6) (0,6) (0,6) -

400

400 200 6,0x 7,0x 8,0x (base case) 9,0x 10,0x Base case WACC (+/-1,0%) EBITDA exit multiple (+/2,0x) Debt ratio (+/20,0%)

200
1,35x 17,50x

2,2% 8,0x 40,0% 7,0%

Sensitivity support Perpetual growth EBITDA exit multiple Target capital structure - debt/market value Capex (% of sales)

Growth rates Multiples Tax rate

P&L, Balance sheet, Cash flow Working capital (NWC) Properties, plants & equipment
(PPE)

Etc.

Debt financing Etc.

Summaries Sensitivities Output sheets for other models

Charts/tables to be embedded in presentations and reports

data2impact the data automation experts

Debt ratio

WACC

It is helpful to document the layout of the model schematically, particularly for larger analysis
Example plan of a model
Scenario 1 Volume growth Price growth VC growth FC growth Years to forecast 5% 5% 0% 10% 5

ILLUSTRATIVE

2
10% 5% 0% 10% 5

3
15% 5% 0% 10% 5

Inputs: Company data P&L data base year (1999) Revenues Volume Price Costs Fixed Variable

Calculation: Economic profit Scenario 1 Company Current Forecast

Outputs: Economic profit tree Economic profit 2001 and 2008 EBIT Economic profit Scenario 1 Cost of capital Fixed costs Margin Contribution margin Price Variable costs

Volume

A
B C D

Documentation

1. Assumptions 2. Format conventions 3. Explanation of variables 4. Explanation of complex

calculations

data2impact the data automation experts

It is essential to separate assumptions, inputs, calculations, and outputs

Bad practice Sheets contain mix of base data, pivot, and calculations Only one worksheet used for all sections of model Model moves in all directions Time series go both horizontally and vertically in same model Values are hard-coded in formulae The developer assumes model will Never be seen by anybody else Be improved after having finished Be irrelevant in a years time Never be looked at again Example schematic of bad model structure Data Calculations Good practice

Separate sections for input data, pivot, calculations, and outputs Separate sheet for each section of model Time series all go in one direction (usually horizontally) The developer assumes model will Never be worked on by the developer again Be pulled out in a years time for quick reference Be passed around the team, and to other teams

Example schematic of good model structure Input Scenarios

Documentation Calculations

Output 1

Output 2

data2impact the data automation experts

Multi-worksheet vs. single worksheet models

Multi-worksheet model Single-worksheet model

Every module (e.g. P&L, DCF

Description analysis) in individual worksheet

All modules in same worksheet

below each other

+
-

Allows for larger complexity Slightly easier to extend/modify

Forces clear structure for each

module, ensures that each module has the same layout (e.g. year in the same column)

Easier auditing using "Precedents"

and "Dependents" arrows

Data tables can be based on

assumptions in other modules

Precedents and dependents arrows

often point outside worksheet

Large, complex models are difficult

to "squeeze" into one worksheet

Data tables force to rearrange

assumptions into output (or require "hack")

Merging modules from different

versions (e.g. in team environment) involves higher risk

data2impact the data automation experts

10

Building of the model needs to be a systematic process

Preparation & Structure

Building & Formatting

Analysis

Get into the habit of being disciplined about Indicating sources for every input Clear naming of fields, sheets, and
models

Indicating the units in use Consistent, clear, and simple formatting

Use the expertise within your company/

department/team for help and advice

data2impact the data automation experts

11

Sources and types of fields should be clearly labeled

Bad practice Good practice

No sources Multiple sources listed in a laundry list rather than against each section of data Estimates not explained or indicated Difference between inputs and calculations not clear Reliance on Excels note function (as they require a special effort to view/print)

Every number sourced visibly on the same sheet as it appears Estimates clearly indicated Calculations vs. raw data clearly indicated Complex formulae explained

Example Working sheet showing P&L

Example Revenue Forecast for a Satellite operator

Source: Team Analysis; Annual Report, Accounting system; Fred Cooper (BL)

data2impact the data automation experts

12

The models filename and sheet headings should clearly explain its purpose and structure
Bad practice Good practice

Never keeping separate copies of old versions Filenames like swaps3old.xls Incorrect titles (e.g., out-of-date ones) Titles only used on output sheets Ranges and constants never named Year and scope unclear (e.g., Battery market size)

Separate files for different versions/revisions of the model Filenames like 101013 Regulatory Model v0.xls All sheets, and sections within sheets have clear title Titles which are the first thing the eye sees Constants, e.g., WACC, given defined names Year and scope clear (e.g., U.K. consumer battery market size, 2007)

Example Input sheet for Financial swaps

Example Output sheet for Regulatory model

data2impact the data automation experts

13

Units must be clear and consistent

Bad practice Good practice

Not documented on the sheet Varying unnecessarily 5% shown as 0.05 (or even 0.1) Real or nominal growth not indicated

Clearly documented, either Per section/sheet, or Against every number 5% shown as 5% Real or nominal is clearly indicated Consistent use of currencies and decimals

Example Calculation sheet on gas field Production

Example Input sheet showing P&L account

data2impact the data automation experts

14

Formatting should be simple and consistent

Bad practice Lots of formatting Using formatting to indicate structure there are no conventions, and it may not print! Particular headaches: Multiple colors Italics Different font sizes Complicated borders No indication on printouts of dates, version numbers, etc. Wasting time on it format will not fix a model Example Output sheet showing P&L and cashflow Good practice

Minimal use of formatting Formatting restricted to: Chart titles Column titles Subtotals and totals Inputs/drivers often a different color Potentially: calculation and model structure Consistency Time/date/version/author stamp on all printouts

Example The same P&L and cashflow

data2impact the data automation experts

15

Best practice: Define and use styles

Description:

Instead of formatting each cell individually, define styles up front and apply them rigorously! Each cell can only have one style, i.e. styles cannot be combined Therefore, set up styles as combination of "cell type" (input, calculation), format (%, multiple, number)
and number of digits

Example:

Shortcut to styles selection dropdown:

A-h-j (English Excel)

A-r-l

(German Excel)
16

data2impact the data automation experts

Additional best practise / Dos & Donts for Financial Models

No hard-coding of values in formulas Dont use circular references Protect every cell but data input/assumption cells Avoid manual formatting as much as possible, use styles instead Use error-checks Model contains P&L, balance sheet and cash flow statement
All three are interlinked

Make the balance sheet balance from the start without a fudge!!!

KISS "Keep It Simple, Stupid!"

Assume that other users/the client only understands Excel basics Must be easy to audit/review by most users Better to avoid great Excel tricks/hacks for the sake of simplicity and understandability

Dont hide rows or columns, use grouping instead and "fold" cells
away

Use the same formula in a row and/or column as much as possible

avoid individual changes

Don't use built-in scenarios

data2impact the data automation experts
17

Perform sanity checks and track changes while using the spreadsheet model

Preparation & Structure

Building & Formatting

Analysis

Sanity check Put yourself in manager mode What numbers/order of

magnitude change would I expect?. Investigate potential deviations!

Does the comparison to back-of-the-envelope calculation look

reasonable? (e.g. are the savings approximately xM DKK.) Do the totals and subtotals look reasonable? (e.g. does the sum of Sales in 2008 make sense?)

Be creative, as you wont be able to check each formula once you get beyond the one-pager

Set assumptions to extremes (e.g. 0% or 100%) and see if result

is in line with expectations Documentation of the tool

Track reasons for changes in value Update documentation

data2impact the data automation experts

18

Content

Modelling basics Profit & Loss Net Working Capital Depreciation & Capex Pensions Debt financing

Discounted Cashflow and Terminal Value

Sensitivities Sanity checks

data2impact the data automation experts

19

Structure and features of the training model

Single sheet approach (limited complexity, data tables) Each module is 19 columns x 44 rows Print settings optimized to display each module on one page (on A4 and Letter) Central "document properties" to steer header and footer of each module/page:
- TRAINING MODEL (12/07/13) C:\Users\Peter\Documents\d2i\Training\Finance\New training\Exercises\[Full model.xlsb]M odel

Module header
Module content data2impact data2impac t Financial Modelling 1

Module titles are linked into Table of Content

Table of content
Page 2 Page DCF analyses DCF analysis Sensitivity analysis (using EBITDA exit multiple method) Sensitivity analysis (using Perpetuity growth rate method) DCF support Weighted average cost of capital Beta Calculation Sanity check: Gordon-Shapiro Model 10 11 12 Assumptions Financial statements Profit & Loss statement Balance Sheet Cash flow Detailed calculations Working capital Properties, plant & equipment Net debt adjustments (pension costs and operating lease) Debt refinancing

3 4 5

6 7 8 9

13 14 15

Output Executice Summary

16

data2impact the data automation experts

20

Exercise 1 Populate P&L

Populate P&L for 2013-2019 Use assumptions from assumptions sheet If no assumption is provided, assume constant Familiarize yourself with the model structure
Additional task

Main task

Useful keyboard shortcuts

Quick navigation in worksheet with C and arrow keys to corner or border of

current table

Selection with S and arrow keys Combination of quick navigation and selection to select ranges

data2impact the data automation experts

21

Content

Modelling basics Profit & Loss Net Working Capital Depreciation & Capex Pensions Debt financing

Discounted Cashflow and Terminal Value

Sensitivities Sanity checks

data2impact the data automation experts

22

Exercise 2 NWC
Calculate forecast for Current assets (rows 265:269) and Current liabilities (rows
Main task 270:272)

To calculate Accounts receivable and Accounts payable, assume 365d for a year Integrate result into Balance sheet Calculate Net Working Capital and link into Cashflow statement Investigate implementation of central switches in Y35:Y47
Additional task

Useful keyboard shortcuts

CE Populate selection with the same formula (without changing format!) @ Edit existing formula

data2impact the data automation experts

23

Content

Modelling basics Profit & Loss Net Working Capital Depreciation & Capex Pensions Debt financing

Discounted Cashflow and Terminal Value

Sensitivities Sanity checks

data2impact the data automation experts

24

Using flags to ease modelling

Formula for depreciation itself is trivial "Complex" check, if depreciation occurs in the period is shifted to a helper/flag cell (purple cell below)

Flag cell calculates check, if period has depreciation Often, flag cells can be verified visually without checking the formula ("triangle shape" in this example)

data2impact the data automation experts

25

Exercise 3 Properties, plants & equipment (PPE)

Main task

Calculate Capex in L310:S310 using assumption in H299 Copy rows 319:329 to row 333 and label as "Support matrix"

Group the new rows 334:344 (Data tab->Group)

Populate L336:S343 with one formula to be 1 if Capex from row 310 is written off in the year indicated in column E, else 0. Try to use one formula! Tipp: Instead of "=IF(Condition1;IF(Condition2;1;0);0)" use "=(Condition1)*(Condition2)" and link result to L311:S311

Calculate total depreciation for each year in T322:T329, transpose to L331:S331

Useful keyboard shortcuts

S+space Select row C+space Select column Cc Copy

S++ CSR CSQ $ SA0

Additional task

Investigate solution in Full Model without support matrix and using TRANSPOSE
function

AutoSum (creates SUM formula to contingent range of numbers top or left of cell) Look at custom format of E329

Insert (row/column/cell/copy selection) or move cut selection Group Ungroup Toggle cell link fixing (A1$A$1A$1$A1A1)

Investigate alternative solution in Full Model with array formulas

data2impact the data automation experts
26

Content

Modelling basics Profit & Loss Net Working Capital Depreciation & Capex Pensions Debt financing

Discounted Cashflow and Terminal Value

Sensitivities Sanity checks

data2impact the data automation experts

27

Exercise 4 Pensions

Populate L345:R347, using central assumptions Link L347:R347 into Balance sheet Define names for multiples in M105 and M106 Use names to populate and calculate H355:H357 and M355:M357

Main task

Useful keyboard shortcuts

When in formula: Evaluate current selection

Else: Recalculate model (if Manual Calculation is activated)

Exit Formula editing, discarding input Insert name from dialog A>i>d>n Name manager
X #

Investigate implementation of "semi-automatic" TOC in D37:D38

Additional task

data2impact the data automation experts

28

Content

Modelling basics Profit & Loss Net Working Capital Depreciation & Capex Pensions Debt financing

Discounted Cashflow and Terminal Value

Sensitivities Sanity checks

data2impact the data automation experts

29

Exercise 5 Debt financing

Situation

Existing debt (230 m) is refinanced mid 2013 with a term loan The amortization period of the new loan is assumed to be 1 year but must be
modelled flexible to assess CF impacts

After the amortization period, the loan will be repaid in fixed rates over a tenor of 8
years (to be also flexible)

Populate L397:R407
Main task

(in M)

Current Projection 2013 2014

2015

2016

2017

2018

2019

Term loan Amortization period Loan period Draw down Financing fee (rate) Financing fee Amount outstanding Interest rate Interest accrued Interest payment Amortization Amount remaining Summary Interest payments Amortization

TRUE 230,4 1,0% (2,3) 230,4 5,0% (5,8) 236,2

FALSE 8 1,0% 236,2 5,0% (11,1) (29,5) 206,6

FALSE 7 1,0% 206,6 5,0% (9,6) (29,5) 177,1

FALSE 6 1,0% 177,1 5,0% (8,1) (29,5) 147,6

FALSE 5 1,0% 147,6 5,0% (6,6) (29,5) 118,1

FALSE 4 1,0% 118,1 5,0% (5,2) (29,5) 88,6

FALSE 3 1,0% 88,6 5,0% (3,7) (29,5) 59,0

(17) (230)

(11) (30)

(10) (30)

(8) (30)

(7) (30)

(5) (30)

(4) (30)

Useful keyboard shortcuts Additional task

Sd,Sr Filling down/right CAv

Paste special dialog

Investigate link back to TOC in each header row

data2impact the data automation experts

30

Content

Modelling basics Profit & Loss Net Working Capital Depreciation & Capex Pensions Debt financing

Discounted Cashflow and Terminal Value

Sensitivities Sanity checks

data2impact the data automation experts

31

Exercise 6 DCF
Understand setup of Beta Calculation sheet and WACC sheet Populate L431:S451
(in M) Current Projection 2013 2014 Sales 632,9 651,8 Growth 3,0% EBITDA 88,0 90,6 Operational lease adjustment 22,2 22,8 EBITDA adjusted 110,1 113,4 % of sales 17,4% 17,4% Depreciation (53) (59) EBIT 57,4 54,2 % of sales 9,1% 8,3% Tax (17) (16) Tax rate 30,0% 30,0% NOPAT 40,2 37,9 % of sales 6,3% 5,8% + Depreciation 53 59 - Capex (44) (46) + Change NWC (10) 1 Unlevered Free Cash Flow 39 53 Discount power 0,5 1,0 WACC 7,8% 7,8% Discount factor 1,0x 0,9x PV of unlevered Free Cash Flow 37 49 2015 684,4 5,0% 95,1 24,0 119,1 17,4% (66) 53,0 7,7% (16) 30,0% 37,1 5,4% 66 (48) 2 57 2,0 7,8% 0,9x 49 2016 718,7 5,0% 99,9 25,2 125,0 17,4% (73) 51,8 7,2% (16) 30,0% 36,2 5,0% 73 (50) 2 61 3,0 7,8% 0,8x 49 2017 747,4 4,0% 103,9 26,2 130,0 17,4% (81) 49,3 6,6% (15) 30,0% 34,5 4,6% 81 (52) 2 65 4,0 7,8% 0,7x 48 2018 769,8 3,0% 107,0 26,9 133,9 17,4% (88) 45,5 5,9% (14) 30,0% 31,8 4,1% 88 (54) 1 68 5,0 7,8% 0,7x 46 Terminal 2019 year 789,1 806,4 2,5% 2,2% 109,7 112,1 27,6 28,2 137,3 140,3 17,4% 17,4% (96) (52) 40,9 88,6 5,2% 11,0% (12) (27) 30,0% 30,0% 28,7 62,0 3,6% 7,7% 96 52 (55) (56,5) 1 71 57 6,0 7,8% 0,6x 45

Main task

Useful keyboard shortcuts

Cz Undo latest action(s) Cy After Undo: Redo

Else: Repeat last action but apply to current selection

Investigate MAX formula used for page numbers

Additional task

data2impact the data automation experts

32

Exercise 7 TV
Populate H454:H465 and Q454:H465
Main task
Method 1: EBITDA exit multiple
EBITDA EBITDA exit multiple Terminal value Discount factor Discounted terminal value PV of unlevered Free Cash Flow Enterprise value Existing debt Operating lease adjustment Unfundeded pension liabilities Existing cash Implied equity value 140,3 8,0x 1.122,6 0,6x 714,7 323,7 1.038,4 (236) (399) (54) 84 433

Method 2: Perpetuity growth rate

Unlevered Free Cash Flow Perpetuity growth rate Terminal value Discount factor Discounted terminal value PV of unlevered Free Cash Flow Enterprise value Existing debt Operating lease adjustment Unfundeded pension liabilities Existing cash Implied equity value 57,3 2,2% 1.019,9 0,6x 649,3 323,7 973,0 (236) (399) (54) 84 368

Useful keyboard shortcuts

Cb Cu Ci G5

Bold Underline Italics Apply percentage format

Investigate setup of "Check style" (applied in line 230)

Additional task

data2impact the data automation experts

33

Content

Modelling basics Profit & Loss Net Working Capital Depreciation & Capex Pensions Debt financing

Discounted Cashflow and Terminal Value

Sensitivities Sanity checks

data2impact the data automation experts

34

Data tables
Data tables are a great tool to quickly calculate different scenarios based on one or two parameters,
n M) e.g.: 1.038 6,8% 7,3% 7,8% 8,3% 8,8% 6,0x 900 880 860 840 822 EBITDA exit multiple 7,0x 8,0x 995 1.089 971 1.063 949 1.038 927 1.014 906 991 9,0x 1.183 1.155 1.128 1.101 1.075 10,0x 1.278 1.247 1.217 1.188 1.160

Data tables (German: "Mehrfachoperationen") are located in the "Data" tab in the "What-If-Analysis"
dropdown

To apply:
1. 2. 3. 4. 5. Top left cell: must be linked to the result cell that you want to show as result (for visual purposes font color can be white) Left column and top row must provided values for the two input parameters (Attention: the original input parameter cannot be linked directly into the header, this leads to wrong results!) Select the full table (including header) and apply the data table First parameter ("row input cell") will be changed to all elements from header row "Column input cell" will be changed to elements from first column

Attention: Both input cells must be located on the same sheet as the data table! To prevent restructuring of model, create blank parameter cell in data table sheet and use this cell as input to data table. In the calculation, build in one more step/cell: =IF(ISBLANK(DataTableCell);OriginalAssumptionCell;DataTableCell)
data2impact the data automation experts
35

WACC

Exercise 8 Sensitivities
Understand setup of sensitivity analysis for EBITDA multiple on p11 Replicate to sensitivity analysis for Perpetuity growth rate method on p12
C + mouse wheel

Main task

Useful keyboard shortcuts

Change zoom level

Check what happens to the graphs when columns U:AI get folded! Why does it not
Additional task happen to the graphs on p11? (Tipp: Check in "Select data")

Use the "Trace Precedents" and "Trace Dependents" functionality "Formulas" tab to
understand formula structure of a calculation chain (e.g. try "Trace Dependents" multiple times on I478, "Trace Precedents" on Z498)

data2impact the data automation experts

36

Content

Modelling basics Profit & Loss Net Working Capital Depreciation & Capex Pensions Debt financing

Discounted Cashflow and Terminal Value

Sensitivities Sanity checks

data2impact the data automation experts

37

Exercise 9 Sanity check

Calculate the NOPLAT (I652) and implied RONIC (I654) Build the sensitivity analysis in E661:J666 Reformat cells using the "Format cells" dialog without using the mouse!
Additional task

Main task

Useful keyboard shortcuts

C1 CO, CN T, CT

"Format cells" dialog Previous/next sheet

When in dialog: previous/next tab

A+letter
E, X Ct Cb Cl Cr

Activate next/previous element in dialog box Activates the field in the dialog with the corresponding
underlined letter

"Ok" and "Cancel"

In "Border" tab of "Formal cells" dialog:

Top Bottom Left Right

Ch Cv Co Ci Cn

Horizontal Vertical Outside Inside None

38

data2impact the data automation experts