ESCP Business School Module 1 PDF

Module 1: The demystification of the (financial & operational) modelling Jan Caspar Hoffmann Module 1: The demystification of the (financial & operational) modelling Table of Contents Jan...

Module 1: The demystification of the (financial & operational) modelling Jan Caspar Hoffmann Module 1: The demystification of the (financial & operational) modelling Table of Contents Jan Caspar Hoffmann I. Financial statements and interdependencies of income statement, balance sheet and 3-16 cash flow statement II. Operating Model 17-23 III. Detailed modelling instructions 25-38 II. Financial Statements and Interdependencies of Income Statement, Balance Sheet and Cash Flow Statement Module 1: The demystification of the (financial & operational) modelling All decisions made by a corporate are interwoven with each other and need to be considered concurrently… Jan Caspar Hoffmann COMMENTS § Shareholder value maximisation, i.e. increase the wealth of the shareholders by paying dividends and/or increasing the stock price KEY OBJECTIVE OF § Returns to shareholders need to outperform comparable investment alternatives underlying the same amount of risk A CORPORATE § Stakeholder management should be optimized in order to achieve maximum profit § Stakeholders include e.g. customers, suppliers, creditors, media, communities, etc. §1 Investment decision MEANS TO §2 Operating decision ACHIEVE THIS OBJECTIVE §3 Financing decision §4 Dividend payment decision INVESTMENT § Only positive net present value investment projects should generally be considered, i.e. where the return on invested capital is higher than the cost of capital 1 DECISION § If no such opportunity exists, the management should return excess cash to shareholders OPERATING 2 § All decisions related to the running of the business which are not directly linked to investments or financing DECISION § Choose a financing mix (debt and equity) that will lead to a maximisation of the investment and to an optimal capital structure/cost § Debt financing – raising money through borrowing from banks or investors with the promise to pay back the principal amount in addition to agreed-upon level of interest FINANCING 3 § Equity financing – capital raising through the issuance of shares of stock DECISION § Both forms of capital can be raised from private as well as public sources ¾ Public financing: raising capital from the public through the issuance of stocks or bonds – typically regulated process and market ¾ Private financing: raising capital from banks (as bank loans) or individual investors in a private manner (business angels etc.) – custom-made solutions § If there are not enough investments that earn the required return on capital, managers should return cash to shareholders through paying dividends or buying DIVIDEND back stocks 4 PAYMENT DECISION § Especially mature companies tend to generate cash flows that are greater than the funding needs for further investments and therefore can pay out stable dividends Module 1: The demystification of the (financial & operational) modelling … and this is being reflected by the interdependencies of the 3 key Financial Statements Jan Caspar Hoffmann A Income Statement/Profit and loss account § A “flow statement” depicting the company’s revenues and expenses during a particular period (e.g. fiscal year) § Indicates how generated sales are transformed into net income § Captures a sale when the product or service is delivered irrespective of actual cash flow (i.e. payment of bill) - Costs are directly linked to the sales generated during the defined period as the benefit and costs ought to be matched – “Matching or Accrual concept” B Balance Sheet § A “snap shot statement” measuring a company’s financial position at a single point in time § It depicts the assets the business controls, the company’s liabilities as well as ownership equity § Total assets have to equal total liabilities and shareholder’s equity – therefore it has to “balance” ¾ The liabilities and equity section depicts how the business is financed while the assets show how the funds have been utilized C Cash Flow Statement § A “flow statement” explaining how and why a company’s cash position has changed over a period of time § Generally split into three categories that depict the flow of cash: operating, investing and financing cash flows § One can derive the cash flow statement entirely from the other two statements  Module 1: The demystification of the (financial & operational) modelling A The Income Statement — exemplary structure and logic Jan Caspar Hoffmann INCOME STATEMENT INCOME STATEMENT Sales Sales of goods and services Directly attributable to products/services sold (e.g. materials, electricity, labor). Mainly variable costs – but e.g. Cost of goods sold (COGS) labor costs partly fixed, etc. Gross profit Development of gross profit margins important indicator of business model health Sales, general & admin (SG&A) Costs not directly attributable to products (“overhead”) e.g. rent, marketing, etc. Not dependent on production output but e.g. selling expenses often fluctuate with sales Other operating expenses/income Typically extraordinary income-/expenses outside ordinary cause of business Earnings before interest and tax (EBIT) Operating profit from ongoing operations Interest income/expenses Net interest payments on financial debt Other financial result E.g. income from financial investments Earnings before tax (EBT) Tax Taxes on taxable income Net income Note: EBIT Depreciation & amortisation (D&A) Accounting depreciation, non-cash expense, mostly included in COGS EBITDA Indication of operational profitability, proxy for pre-tax cash flow, unaffected by financial/accounting decisions  Module 1: The demystification of the (financial & operational) modelling B The Balance Sheet —exemplary structure and logic Jan Caspar Hoffmann BALANCE SHEET COMMENTS Cash Liquid assets (e.g., cash, commercial paper) Receivables Claims resulting from not yet paid goods / services sold Inventory Finished/unfinished goods, raw materials, etc., Other current assets Prepaid expenses, advances, tax assets, etc., Total current assets Expected to be converted into cash within one year Fixed assets Tangible and intangible long-term assets less accrued depreciation Other assets E.g. long-term investments Total fixed assets Total assets Short-term debt Financial obligations due within one year including current portion of long-term debt Payables Obligations arising from received but not yet paid goods/services Other current liabilities Liabilities expected to be redeemed within less than one year including financial leases Current liabilities Long-term debt Long-term financial obligations (> 1 year) Pension accruals Accrued pension obligation Other accruals Incurred expenses that have not been paid yet Other liabilities Other liabilities Long term liabilities Common stock Paid-in capital at par value Additional paid-in capital Paid-in capital in excess of par value Retained earnings Retained portion of net income Shareholders’ equity Total liabilities and shareholders’ equity  Module 1: The demystification of the (financial & operational) modelling C The Cash Flow Statement — exemplary structure and logic Jan Caspar Hoffmann CASH FLOW STATEMENT COMMENTS Net income D&A Reversal of non-cash expense in the income statement Increase in working capital Increase in inventory, receivables, payables Increase in other assets Change in respective balance sheet items Decrease in other liabilities Change in respective balance sheet items Operating cash flow Capital expenditure Investment in tangible/intangible assets Investing cash flow Dividend Regular payout of company earnings to shareholders Share repurchase Return of excess cash to shareholders usually in times of depressed stock valuation Sale of shares Equity financing through IPO/rights issue Repayment of debt Scheduled/accelerated repayment of debt (e.g. bonds, loans) Issuance of debt Debt financing through issuance of e.g. bonds, loans Financing cash flow Net cash flow Module 1: The demystification of the (financial & operational) modelling Every line item flows into or is coming from another statement... Jan Caspar Hoffmann … which explains the interdependent nature. And makes modelling a very dynamic exercise PROFIT AND LOSS STATEMENT CASH FLOW STATEMENT The cash flow statement is the most important statement in order to get Sales Net income an understanding of the operational Cost of Goods sold Depreciation and financial health of a company Sales, General & Admin Amortization Increase / Decrease in working capital EBITDA Increase / Decrease in other assets Depreciation Increase / Decrease in other liabilities Amortization 1 3 OPERATING CASH FLOW EBIT § Net income (after § Investments in payment of any inventory, increases in dividends) increases receivables and Capital expenditure Net financial expense retained earnings and decrease s in payables thereby equity of the firm eat up cash, reduction INVESTING CASH FLOW Other financial result of working capital § Net income is usually the releases cash; sustainable level of Dividend EBT staring point in the cash flow statement and also a working capital is Share repurchase 2 direct addition (deduction important to Tax understand Sale of shares in case of loss) to § Depreciation is an shareholders’ equity Repayment of debt accounting non-cash Net Income Issuance of debt expense reducing taxable earnings and asset base (PP&E); it is being reused FINANCING CASH FLOW in the cash flow statement. Investments in PP&E NET CASH FLOW result in cash outflow and increase in PP&E on the balance sheet 5 BALANCE SHEET § Debt issuance/repayment results in increased/ decreased financial Cash Payables liabilities 4 Receivables Short-term debt Inventory Long-term debt § Sale of new shares and PP&E / Fixed assets Total liabilities repurchasing of 6 Other assets Common stock outstanding shares directly influences § Net cash flow directly Retained earnings common stock translates into the cash Total Assets Total Shareholders’ position on the balance Equity and Liabilities sheet  Module 1: The demystification of the (financial & operational) modelling 1 Key links between the 3 statements: Net Income/ Total Equity/ Dividends Jan Caspar Hoffmann INCOME STATEMENT CASH FLOW STATEMENT Sales Net income Depreciation COGS § Net income can be used to Amortisation pay dividends or increases SG&A retained earnings and Increase/decrease in working capital thereby equity of the firm EBITDA § Net income is usually the Increase/decrease in other assets starting point in the cash Depreciation flow statement and also a Increase/decrease in other liabilities direct addition (deduction Amortisation in case of loss) to Operating cash flow shareholders’ equity Capital expenditure EBIT Investing cash flow Interest income/expense Dividend BALANCE SHEET Other financial result Share repurchase Cash Payables EBT Sale of shares Receivables Short-term debt Inventory Long-term debt Repayment of debt Tax Total current assets Total liabilities Issuance of debt Net income Fixed assets Common stock Financing cash flow Other assets Retained earnings Net cash flow Total fixed assets Total equity Total liabilities and Total assets shareholders’ equity  Module 1: The demystification of the (financial & operational) modelling 2 Key links between the 3 statements: Capex/ Fixed Assets/ D&A Jan Caspar Hoffmann INCOME STATEMENT CASH FLOW STATEMENT Sales Net income Depreciation COGS Amortisation SG&A § Depreciation and amortisation is an accounting non-cash expense reducing Increase/decrease in working capital taxable earnings and asset base (property, EBITDA plant and equipment (PP&E) and intangible Increase/decrease in other assets assets (e.g. patents, licenses); it is being Depreciation reversed in the cash flow statement. Increase/decrease in other liabilities Investments in PP&E and intangible assets Amortisation result in cash outflow and increase in fixed Operating cash flow assets on the balance sheet Capital expenditure EBIT Investing cash flow Interest income/expense Dividend BALANCE SHEET Other financial result Share repurchase Cash Payables EBT Sale of shares Receivables Short-term debt Inventory Long-term debt Repayment of debt Tax Total current assets Total liabilities Issuance of debt Net income Fixed assets Common stock Financing cash flow Other assets Retained earnings Net cash flow Total fixed assets Total equity Total liabilities and Total assets shareholders’ equity [ 10 ]  Module 1: The demystification of the (financial & operational) modelling 3 Key links between the 3 statements: Net Working Capital Jan Caspar Hoffmann INCOME STATEMENT CASH FLOW STATEMENT Sales Net income § Investments in inventory, increases in receivables and decreases in payables use up Depreciation COGS cash, reduction of working capital releases cash; sustainable level of working capital is Amortisation SG&A important to understand § Inventory constitutes raw materials for Increase/decrease in working capital EBITDA production of goods, finished and unfinished goods Increase/decrease in other assets Depreciation § Receivables are a result of sales not being Increase/decrease in other liabilities paid immediately Amortisation § Payables are a result of goods and services Operating cash flow consumed (to produce sales) but not paid immediately Capital expenditure EBIT Investing cash flow Interest income/expense Dividend BALANCE SHEET Other financial result Share repurchase Cash Payables EBT Sale of shares Receivables Short-term debt Inventory Long-term debt Repayment of debt Tax Total current assets Total liabilities Issuance of debt Net income Fixed assets Common stock Financing cash flow Other assets Retained earnings Net cash flow Total fixed assets Total equity Total liabilities and Total assets shareholders’ equity [ 11 ]  Module 1: The demystification of the (financial & operational) modelling 4 Key links between the 3 statements: Common Stock Jan Caspar Hoffmann INCOME STATEMENT CASH FLOW STATEMENT Sales Net income Depreciation COGS Amortisation SG&A Increase/decrease in working capital EBITDA Increase/decrease in other assets Depreciation Increase/decrease in other liabilities § Sale of new shares and Amortisation repurchasing of Operating cash flow outstanding shares directly influences Capital expenditure EBIT common stock Investing cash flow Interest income/expense Dividend BALANCE SHEET Other financial result Share repurchase Cash Payables EBT Sale of shares (capital increase) Receivables Short-term debt Inventory Long-term debt Repayment of debt Tax Total current assets Total liabilities Issuance of debt Net income Fixed assets Common stock Financing cash flow Other assets Retained earnings Net cash flow Total fixed assets Total equity Total liabilities and Total assets shareholders’ equity [ 12 ]  Module 1: The demystification of the (financial & operational) modelling 5 Key links between the 3 statements: Debt/ Cash/ Interest Income/ Expense Jan Caspar Hoffmann 6 INCOME STATEMENT CASH FLOW STATEMENT Sales Net income Depreciation COGS Amortisation SG&A Increase/decrease in working capital EBITDA Increase/decrease in other assets Depreciation Increase/decrease in other liabilities Amortisation Operating cash flow Capital expenditure EBIT Investing cash flow Interest income/expense Dividend BALANCE SHEET Other financial result Share repurchase Cash Payables EBT Sale of shares Receivables Short-term debt Inventory Long-term debt Repayment of debt Tax Total current assets Total liabilities Issuance of debt Net income Fixed assets Common stock Financing cash flow Other assets Retained earnings Net cash flow Total fixed assets Total equity Total liabilities and Total assets shareholders’ equity [ 13 ] III. Operating Model Module 1: The demystification of the (financial & operational) modelling An operating model constitutes the basis for a comprehensive valuation analysis Jan Caspar Hoffmann § Operating model reflects future financial development of a company under assumed external factors (e.g., macroeconomic environment, market development, competition) as well as internal parameters (e.g., cost structure development, capex and working capital requirements) § As basis for an operating model a business plan is typically used, if available § Given the uncertainty regarding development of key external and internal factors, the model can be enhanced by a scenario analysis based on a different set of assumptions for these factors in accordance with a general view on possible future developments (e.g., base case scenario, worst case scenario, best case scenario, etc.) § Ideally an operating model starts with volumes and prices for all separate products (product classes) to derive sales. COGS should also be calculated on a per product (product class) basis. From gross profit to net income the line items are based on values for the full firm § Typically, P&L, BS and CF are integrated in one interdependent model § An operating model can cover anything from 3 to 25 years depending on industry/sector specifics. For most companies 3-7 years are sufficient. Companies active in infrastructure activities may warrant longer periods depending on their investment cycle [ 15 ] Module 1: The demystification of the (financial & operational) modelling The operating model, aside from forming the basis for the valuation exercise… Jan Caspar Hoffmann … also is an important tool to understand, challenge and verify the business model LBO xls sheets DCF INPUT 1 SCENARIOS/CALCULATIONS 2 OUTPUT/INTERFACE MERGER MODEL §... §... §... COMPS § Typically, a business plan (e.g., § Includes key drivers for the § Consolidation of calculated received from seller) model (e.g., sales growth, margins, financials in a summary etc.) and assumptions for the overview sheet different scenarios § Interface to cash flow based § Can be calculated either for valuation models: LBO, DCF, also the entire company or for to merger model relevant company segments/divisions separately (in multiple Excel sheets) [ 16 ] Module 1: The demystification of the (financial & operational) modelling 1 There is no “one fits all” approach to modelling, rather in practice it is driven by… Jan Caspar Hoffmann … the purpose, data availability and how reasonably assumptions can be validated 2019A 2020E 2021E 2022E 2023E 2024E 2025E CAGR Option I: Simplified Option II: Bottom Up Revenues … … … … … … … …% Examples X% Revenues Material Expenses … … … … … … … …% Personell Expenses … … … … … … … …% Division A Product A Region A Price Other Expenses … … … … … … … …% Divison B or Product B or Region B or x Operating Income … … … … … … … …% Divison C Product C Region C Volume Y1 % Margin in % … … … … … … … …% Y2 % Capex as % of Sales … … … Z… % … … … …% Maintenance Capex … … … … … … … …% Option III: Hybrid Expansion Capex … … … … … … … …% NWC as % of sales … … … … … … … …% Expenses Inventory … … … … … … … …% Material Expenses (assumption: 70% of cost) Days outstanding … … … … … … … …% Material A Subsidiary A Receivables … … … … … … … …% Material B or Subsidiary B Days outstanding … … … … … … … …% Material C Subsidiary C Payables … … … … … … … …% Days outstanding … … … … … … … …% All other expenses à flat as % of sales Module 1: The demystification of the (financial & operational) modelling 1 Equally important as the pure technical modelling, clearly is the validation Jan Caspar Hoffmann Validation can take many forms, and in practice a variety of approaches is used in any given project Value Impact § Ceteris paribus analysis § Allows to focus in-depth analysis on key value -1% Sales Growth +1% Sales Growth drivers Sensitivities § Can be performed on many aspects: CF, value, -1% EBITDA Margin +1% EBITDA Margin etc. +1% Capex -1% Capex EBITDA Margin Sales Growth CAGR 4 4 § Operational benchmarking usually comes out of Benchmarking 3 3 the comps exercise 2 2 § Frequently applied by Private Equity to identify growth potentials and to perform reality checks 1 1 0 0 Peer 1 Peer 2 Peer 3 Peer 4 Comp 1 Comp 2 Comp 3 Comp 4 16 Region C 16 others 14 14 12 12 10 Region A 10 Sales Growth § Looking at the effect e.g. for applying Sales Bridging 8 8 6 6 CAGR from another perspective à “so what Region B Cost reduct. 4 4 2 0 2 0 does that really mean?“ Sales 2020 Sales 2025 EBITDA… EBITDA… Market share 2020 Market share 2025 § Is sales growth realized when considering Market Size: Market Size: reality market share gains Triangulating 1,000 1,100 § Triangulating sales growth also vs. capacity utilization is a common tool Company A Sales of 100 Sales of 150 § In conjunction with benchmarking, this analysis quickly reveals whether assumptions are realistic 1 0 1 0 “Over the Cycle” Average 9 9 8 8 7 7 § And requires thorough understanding of the 6 6 5 5 4 4 business model. Or looking at it the other way 3 3 2 2 1 1 around: promotes a better understanding of the 0 0 EBITDA Margin last 10 years NWC last 10 years business [ 34 ] Module 1: The demystification of the (financial & operational) modelling 1 Scenario analysis is a staple in any modelling exercise Jan Caspar Hoffmann Project XYZ Scenario: Management case Scenario Editor Management case 1 Management case Upside case 2 Upside case Downside case Current scenario 1 3 1 Downside case Assumptions Company ABC Historicals Forecasts 2014A 2016A 2015A 2017A 2016A 2018A 2017A 2019A 2018E 2020E 2019E 2021E 2020E 2022E 2021E 2023E Sales 2,779 3,456 4,351 5,688 6,578 7,512 8,654 Sales growth 35.6% 24.4% 25.9% 30.7% 15.6% 14.2% 15.2% 2 Scenarios sales growth 3 35.6% 4 1 Scenario selection Management case 24.4% 25.9% 30.7% 15.6% 14.2% 15.2% Upside case 35.6% 24.4% 25.9% 32.2% 17.1% 15.7% 16.7% Downside case 35.6% 24.4% 25.9% 29.2% 14.1% 12.7% 13.7% COGS (1,190) (1,554) (2,123) (2,465) (3,870) (4,325) (4,965) (5,687) COGS as % of sales (58.1%) (55.9%) (61.4%) (56.7%) (68.0%) (65.7%) (66.1%) (65.7%) Scenarios COGS as % of sales 2 Scenarios Management case (58.1%) (55.9%) (61.4%) (56.7%) (68.0%) (65.7%) (66.1%) (65.7%) Upside case (58.1%) (55.9%) (61.4%) (56.7%) (63.0%) (60.7%) (61.1%) (60.7%) Downside case (58.1%) (55.9%) (61.4%) (56.7%) (73.0%) (70.7%) (71.1%) (70.7%) Personnel costs (147) (243) (454) (702) (652) (800) (851) (988) Personnel costs as % of sales (7.2%) (8.7%) (13.1%) (16.1%) (11.5%) (12.2%) (11.3%) (11.4%) 3 Historical numbers Scenarios personnel costs as % of sales Management case (7.2%) (8.7%) (13.1%) (16.1%) (11.5%) (12.2%) (11.3%) (11.4%) Upside case (7.2%) (8.7%) (13.1%) (16.1%) (9.5%) (10.2%) (9.3%) (9.4%) Downside case (7.2%) (8.7%) (13.1%) (16.1%) (13.5%) (14.2%) (13.3%) (13.4%) Other operating costs (132) (212) (78) (289) (91) (106) (111) (99) Other operating costs as % of sales (6.4%) (7.6%) (2.3%) (6.6%) (1.6%) (1.6%) (1.5%) (1.1%) 4 Assumptions Scenarios other operating costs as % of sales Management case (6.4%) (7.6%) (2.3%) (6.6%) (1.6%) (1.6%) (1.5%) (1.1%) Upside case (6.4%) (7.6%) (2.3%) (6.6%) (1.1%) (1.1%) (1.0%) (0.6%) Downside case (6.4%) (7.6%) (2.3%) (6.6%) (2.1%) (2.1%) (2.0%) (1.6%) Capex (289) (871) (345) (96) (285) (330) (375) (430) Capex as % of sales (14.1%) (31.3%) (10.0%) (2.2%) (5.0%) (5.0%) (5.0%) (5.0%) Scenarios Capex as % of sales Management case (14.1%) (31.3%) (10.0%) (2.2%) (5.0%) (5.0%) (5.0%) (5.0%) Upside case (14.1%) (31.3%) (10.0%) (2.2%) (4.0%) (4.0%) (4.0%) (4.0%) Downside case (14.1%) (31.3%) (10.0%) (2.2%) (6.0%) (6.0%) (6.0%) (6.0%) [ 19 ] Module 1: The demystification of the (financial & operational) modelling 2 The output consolidates the various inputs… Jan Caspar Hoffmann … which at times can be multi-megabyte exercises ASSUMPTIONS/CALCULATIONS OUTPUT By divison Aggregation DIVISON B of information Project XYZ xls sheets... § Scenario: Management case DIVISION A BUSINESS PLAN €m 2018A 2019A 2020E 2021E 2022E Revenues x x x x x or Cost of goods x x x x x By product sold (COGS) PRODUCT B SG&A x x x x x... EBITDA x x x x x PRODUCT A D&A x x x x x EBIT x x x x x or Capex x x x x x By country COUNTRY B Working capital x x x x x... COUNTRY A LBO, DCF, Merger model Trading/Transaction multiples etc. [ 20 ] Module 1: The demystification of the (financial & operational) modelling The numbers derived from an operating model are used as a basis for the 2 various valuation models Jan Caspar Hoffmann Project XYZ Scenario: Management case Financials in €m 1 P&L in EURm - 31-Dec 2016A 2014A 2017A 2015A 2018A 2016A 2019A 2017A 2020E 2018E 2021E 2019E 2022E 2020E 2023E 2021E Total revenue 2,049 2,779 3,456 4,351 5,688 6,578 7,512 8,654 Grow th (%) n.a 35.6% 24.4% 25.9% 30.7% 15.6% 14.2% 15.2% COGS (1,190) (1,554) (2,123) (2,465) (3,870) (4,325) (4,965) (5,687) as % of sales (58.1%) (55.9%) (61.4%) (56.7%) (68.0%) (65.7%) (66.1%) (65.7%) Personnel costs (147) (243) (454) (702) (652) (800) (851) (988) as % of sales (7.2%) (8.7%) (13.1%) (16.1%) (11.5%) (12.2%) (11.3%) (11.4%) Other operating costs (132) (212) (78) (289) (91) (106) (111) (99) as % of sales (6.4%) (7.6%) (2.3%) (6.6%) (1.6%) (1.6%) (1.5%) (1.1%) EBITD A 580 770 801 895 1,075 1,347 1,585 1,880 EBITDA m argin (%) 28.3% 27.7% 23.2% 20.6% 18.9% 20.5% 21.1% 21.7% D &A (232) (477) (212) (77) (172) (205) (102) (110) as % of capex 80.3% 54.8% 61.4% 79.8% 60.2% 62.1% 27.2% 25.6% EBIT 348 293 589 818 903 1,142 1,483 1,770 EBIT m argin (%) 17.0% 10.5% 17.0% 18.8% 15.9% 17.4% 19.7% 20.5% 2 Cash flow statement in EURm - 31-Dec 2016A 2014A 2017A 2015A 2018A 2016A 2019A 2017A 2020E 2018E 2021E 2019E 2022E 2020E 2023E 2021E EBITD A 580 770 801 895 1,075 1,347 1,585 1,880 Change in Working capital (358) (752) (653) (765) (530) (467) (560) (723) Capex (PPE and Intangibles) (289) (871) (345) (96) (285) (330) (375) (430) Other assets/liabilities 7 110 18 (0) - - - - Operating cash flow before interest and taxes (60) (743) (179) 33 260 550 650 727 [ 21 ] IV. Detailed modelling instructions Module 1: The demystification of the (financial & operational) modelling Financial statements and interdependencies — modelling drivers Jan Caspar Hoffmann § Types of income statement item drivers ¾ Sales growth dependent on market growth Which market regional/product wise? Above/in line with/below market growth? Why? ¾ Variable costs (those that do change with sales volume) can be modeled on a percentage of sales basis Variable costs usually include purchase of raw materials, energy usage, distribution costs, etc. ¾ Fixed costs can grow in line with inflation Fixed costs include salaries, rent, insurance, property taxes, etc. and do not depend on the level of goods or services produced Semi-fixed relates to the fact that some fixed costs can become variable over time (adjustment of personnel, new production facilities etc.) ¾ Certain costs are exceptional such as restructuring provisions. Because of their exceptional nature, no forecasts necessary § Business model – operating leverage ¾ Operating leverage: defined in terms of the relationship between fixed costs and total costs the greater the proportion of fixed costs in relation to total costs, the greater the operating leverage of a company businesses with high operating leverage will have higher variability in operating income than firms with a low operating leverage – airlines and hotels are typical business examples with a high operating leverage Examples HIGH OPERATING LEVERAGE – AIRLINE EXAMPLE LOW OPERATING LEVERAGE – INVESTMENT BANK EXAMPLE “AIR FRANCE-KLM” “GOLDMAN SACHS” In €m 2012 2013 2014 2015 2016 2017 Moderate sales In $m 2012 2013 2014 2015 2016 2017 drop... Sales 25,439 25,530 24,930 25,691 24,846 25,784 Sales 34,163 34,206 34,528 33,820 30,608 32,073 Significant sales % growth 4.2% 0.4% (2.4%) 3.1% (3.3%) 3.8% % growth 18.6% 0.1% 0.9% (2.1%) (9.5%) 4.8% drop… EBIT (414) 130 (83) 557 983 1,586 EBT 11,655 12,699 13,471 12,788 10,700 11,320...has significant % margin (1.6%) 0.5% (0.3%) 2.2% 4.0% 6.2% impact on EBIT % margin 34.1% 37.1% 39.0% 37.8% 35.0% 35.3% indicating high EBITDA 1,133 1,706 1,513 2,155 2,554 3,199 operating … has little influence on leverage EBT, indicating low % margin 4,5% 6.7% 6.1% 8.4% 10.3% 12.4% operating leverage [ 23 ] Module 1: The demystification of the (financial & operational) modelling Financial statements and interdependencies — modelling drivers (cont’d) Jan Caspar Hoffmann § Two main types of Balance Sheet assets from a modelling perspective ¾ Mainly assets that grow and fluctuate with the business: Inventories, receivables, payables and accruals – typically relatively stable assets in relation to sales over time PP&E are also relatively constant assets in relation to sales ¾ Assets which do not fluctuate and do not grow in line with the business – require proper analysis and thinking and most likely three statement modelling Non-recurring items which are one-off in nature, e.g. environmental provisions Tax assets and liabilities – usually linked to PP&E patterns or ad-hoc factors § Two options regarding financing ¾ Debt Debt typically gets repaid in accordance with known repayment schedule short-term revolver draw down/repayment resulting from temporary shortage of cash (e.g. working capital requirement) or surplus cash generation ¾ Equity Typically develops with earnings reinvested in the business (i.e. retained earnings) less paid dividends [ 24 ] Module 1: The demystification of the (financial & operational) modelling Three statements: what items do we model? Jan Caspar Hoffmann INCOME STATEMENT INCOME STATEMENT INCOME STATEMENT Sales Cash 6 Net income Receivables 3 2 D&A COGS 1 Inventory Other current assets 4 Increase in working capital 2 SG&A Total current assets Increase in other assets EBIT Fixed assets 3 Other assets Decrease in other liabilities Interest income/expense 4 Total fixed assets Operating cash flow 6 Other financial result Total assets 3 6 Capital expenditure Short-term debt EBT Payables 2 Investing cash flow Tax 5 Other current liabilities 4 Dividend Current liabilities 6 Share repurchase 7 Net income Long-term debt Other liabilities Sale of shares Note: 4 Long term liabilities Repayment of debt EBIT Common stock 6 7 Issuance of debt Additional paid-in capital D&A 3 Retained earnings Financing cash flow EBITDA Shareholders’ equity Net cash flow Total liabilities and shareholders’ equity Key Modules 1 Sales and cost items Sales forecast based on % growth vs. previous years/individual forecast of cost items 2 Working capital/changes in working capital Based on current assets and current liabilities which are forecast by specific metrics 3 Fixed assets/capex/depreciation Waterfall schedule 4 Other assets/liabilities Individual item basis 5 Tax expense/cash taxes/tax assets and liabilities Tax schedule 6 Cash and debt/interest/debt repayment Debt schedule 7 Shareholders’ equity Dividends forecast based on a % of net income basis [ 25 ] Module 1: The demystification of the (financial & operational) modelling 1 Sales and Cost Jan Caspar Hoffmann Sales § Can either be modelled by applying an assumed current year growth rate to previous year sales figure (simplified approach) § Alternatively, detailed forecast on a e.g. product basis using price-volume grid Cost items ALTERNATIVE 1 FYE 31 ACTUAL FORECAST EXTRAPOLATION DECEMBER 2018A 2019A 2020F 2021F 2022F 2023F 2024F § Cost items can be modelled Sales 5,286.8 5,492.9 5,685.2 5,855.7 6,031.4 6,212.4 6,398.7 individually on a percentage of sales % growth 3.0% 3.9% 3.5% 3.0% 3.0% 3.0% 3.0% basis COGS 3,367.7 4,284.5 4,263.9 3,689.1 3,811.9 3,944.8 4,063.2 § SG&A is usually a fixed cost item; % of sales 63.7% 78.0% 75.0% 63.0% 63.2% 63.5% 63.5% however, may significantly increase Gross profit 1,919.1 1,208.4 1,421.3 2,166.6 2,219.6 2,267.5 2,335.5 in a high growth scenario % of sales 36.3% 22.0% 25.0% 37.0% 36.8% 36.5% 36.5% SG&A 1,152.5 1,181.0 1,193.9 1,241.4 1,296.8 1,348.1 1,388.5 § D&A will be implicitly included in COGS or SG&A, thus making a % of sales 21.8% 21.5% 21.0% 21.2% 21.5% 21.7% 21.7% forecast less accurate EBIT 766.6 27.5 227.4 925.2 922.8 919.4 947.0 % of sales 14.5% 0.5% 4.0% 15.8% 15.3% 14.8% 14.8% ALTERNATIVE 2 FYE 31 ACTUAL FORECAST EXTRAPOLATION DECEMBER § The separate modeling of cost items 2018A 2019A 2020F 2021F 2022F 2023F 2024F can be avoided by directly Sales 5,286.8 5,492.9 5,685.2 5,855.7 6,031.4 6,212.4 6,398.7 forecasting EBITDA on a margin % growth 3.0% 3.9% 3.5% 3.0% 3.0% 3.0% 3.0% basis EBITDA 930.6 191.8 406.6 1,120.8 1,137.1 1,155.4 1,208.5 § Depreciation and amortisation margin % 17.6% 3.5% 7.2% 19.1% 18.9% 18.6% 18.9% expenses will be forecasted on the D&A 164.1 164.4 179.2 195.6 214.3 236.0 261.5 basis of the D&A schedule. As a % of sales 3.1% 3.0% 3.2% 3.3% 3.6% 3.8% 4.1% shortcut, margin delta between EBIT 766.6 27.5 227.4 925.2 922.8 919.4 947.0 EBITDA and EBIT can be kept % of sales 14.5% 0.5% 4.0% 15.8% 15.3% 14.8% 14.8% constant Sourced from depreciation schedule [ 26 ] Module 1: The demystification of the (financial & operational) modelling 2 Working Capital / Changes in Working Capital Jan Caspar Hoffmann in € million, otherwise indicated ACTUAL FORECAST EXTRAPOLATION FYE 31 DECEMBER COMMENTS 2018A 2019A 2020F 2021F 2022F 2023F 2024F § Measures the average number of days the COGS 3,367.7 4,284.5 4,263.9 3,689.1 3,811.9 3,944.8 4,063.2 company holds its inventory before selling it (i.e. Inventory 606.2 762.6 750.4 641.9 655.6 670.6 682.6 company turns inventory c. 6x per year) Days of COGS 65.7 65.0 64.2 63.5 62.8 62.1 61.3 § Measures the average number of days the Accounts receivable 612.2 627.3 641.3 653.8 666.6 679.8 693.4 company needs to collect its receivables from the Days of receivables 42.3 41.7 41.2 40.8 40.3 39.9 39.6 clients (i.e. company is able to collect payment from customers within c. 1.5 months) (Accounts payable) (772.5) (902.2) (879.0) (756.4) (775.8) (784.8) (792.9) Days of payable (83.7) (76.9) (75.2) (74.8) (74.3) (72.6) (71.2) § Measures the average number of days a company takes to pay its suppliers (i.e. company pays its Net working capital 445.9 487.7 512.7 539.3 546.4 565.6 583.1 suppliers 2-3 months after invoice received) % revenues 8.4% 8.9% 9.0% 9.2% 9.1% 9.1% 9.1% (Increase)/decrease net working § Ideally, days of COGS and days of receivables are 93.3 (41.8) (25.0) (26.6) (7.1) (19.2) (17.5) small and days of payables are high to decrease capital financing need of company § Inventory: days of COGS (% of COGS x 365) or % of COGS or inventory turnover (COGS inventory) § Receivables: days receivable (% of sales x 365) or % of sales § Payables: days payable (% of COGS x 365) or % of COGS (or COGS SG&A) § Change in net working capital: an increase in net working capital will be a negative C/F item, a decrease in net working capital will be a positive C/F item In general, a business requires continuous increases in net working capital as it grows which may put a lot of pressure on the cash generation of a growing business [ 27 ] Module 1: The demystification of the (financial & operational) modelling 3 Fixed Assets, Capex and Depreciation Jan Caspar Hoffmann Capex and PP&E schedule in € million, otherwise indicated ACTUAL FORECAST EXTRAPOLATION FYE 31 DECEMBER COMMENTS 2018A 2019A 2020F 2021F 2022F 2023F 2024F § Normalized level of maintenance capex usually Capex 222.0 131.8 142.1 158.1 180.9 211.2 249.6 assumed towards end of forecasting period % revenues 4.2% 2.4% 2.5% 2.7% 3.0% 3.4% 3.9% % depreciation 152.2% 90.4% 88.8% 89.9% 93.3% 98.2% 103.9% § Expansion capex is usually not planned in the outer years Net PP&E – BoP1 1,458.9 1,535.1 1,521.0 1,503.1 1,485.2 1,472.2 1,468.3 Capex 222.0 131.8 142.1 158.1 180.9 211.2 249.6 § If capex < depreciation, asset base of company shrinks. If capex > depreciation, company expands Depreciation (145.9) (145.9) (160.1) (175.9) (194.0) (215.1) (240.1) its asset base Net PP&E – EoP2 1,535.1 1,521.0 1,503.1 1,485.2 1,472.2 1,468.3 1,477.7 § Capex: % of sales ¾ Maintenance capex: investments made to keep existing operations going at their current levels (e.g. maintenance of existing plants) ¾ Expansion capex: investments in new assets to support business growth (e.g. building of new plant) § Depreciation: from depreciation "waterfall“ (see next page) § Gross assets ¾ Accumulated capex of all assets not fully depreciated § Net assets ¾ Gross assets less accumulated depreciation 1. BoP: Beginning of period 2. EoP: End of period [ 28 ] Module 1: The demystification of the (financial & operational) modelling 3 Fixed Assets, Capex and Depreciation (cont’d) Jan Caspar Hoffmann Depreciation schedule in € million, otherwise indicated ACTUAL FORECAST EXTRAPOLATION FYE 31 DECEMBER COMMENTS 2018A 2019A 2020F 2021F 2022F 2023F 2024F 2017 existing PP&E average life (years) 10.0 § Depreciation of existing and new assets using Depreciation factor 10.0% 10.0% 10.0% 10.0% 10.0% linear depreciation for simplicity purposes Depreciation from existing 2017 PP&E 1,458.9 145.9 145.9 145.9 145.9 145.9 New Capex average life (years) 10.0 2018 142.1 14.2 14.2 14.2 14.2 14.2 14.2 2019 158.1 15.8 15.8 15.8 15.8 15.8 2020 180.9 18.1 18.1 18.1 18.1 2021 211.2 21.1 21.1 21.1 2022 249.6 25.0 25.0 Depreciation due to add. Capex post 2017 14.2 30.0 48.1 69.2 94.2 Depreciation from existing 2012 PP&E 145.9 145.9 145.9 145.9 145.9 Total depreciation 145.9 145.9 160.1 175.9 194.0 215.1 240.1 % Revenues 2.8% 2.7% 2.8% 3.0% 3.2% 3.5% 3.8% % Capex 65.7% 110.7% 112.6% 111.3% 107.2% 101.9% 96.2% § Depreciation of existing assets: Using a linear depreciation (for simplicity purposes) § Depreciation of new assets: Depreciate over assumed average life [ 29 ] Module 1: The demystification of the (financial & operational) modelling 4 Other Assets and Liabilities Jan Caspar Hoffmann § Look out for ¾ Pension assets and liabilities ¾ Non-recurring items, e.g. restructuring provisions ¾ Non-cash revaluations ¾ Contingent liabilities, e.g. deferred consideration ¾ Nature of drivers – steady or erratic? ¾ Do not miss out on any line items [ 30 ] Module 1: The demystification of the (financial & operational) modelling 5 Tax Expenses, Cash Taxes as well as Tax Assets and Liabilities Jan Caspar Hoffmann Simplified tax schedule in € million, otherwise indicated ACTUAL FORECAST EXTRAPOLATION COMMENTS FYE 31 DECEMBER 2018A 2019A 2020F 2021F 2022F 2023F 2024F § NOLs can be utilized in a given year against EBT 662.8 (72.5) 130.2 832.9 836.4 838.2 870.9 positive taxable earnings generated NOLs used 0.0 0.0 (72.5) 0.0 0.0 0.0 0.0 § The remaining part of NOLs can be carried Taxable earnings 662.8 (72.5) 57.7 832.9 836.4 838.2 870.9 forward to the fol