Financial Modeling

1. Financial Modeling

2. Valuation, Decision Making and Risk Every major decision a company makes is in one way or another derived from how much the outcome of the decision is worth. It is widely recognized that valuation is the single financial analytical skill that managers must master. • Valuation analysis involves assessing • Future cash flow levels, (cash flow is reality) and • Risks in valuing those cash flows, whether it be the cash flow from assets, debt or equity • Measurement value – forecasting and risk assessment -- is a very complex and difficult problem. Reference: Chapter 4

3. Teaching Objectives of Model Construction • The best and perhaps the only real way to learn modeling is under the tense pressure of a real transaction – when a model must be created and audited under a tight deadline. • Notwithstanding this, the exercises and lecturers are intended to provide: • A head start for those who have not created models and will have to learn the hard way. • Helpful ideas to experienced model builders in designing and structuring more efficient, stable, transparent and accurate models. • The discussion covers how to build a well structured financial model that clearly delineates inputs, effectively presents key value drivers, uses separate modules to organize various components, accurately computes cash flow that is available to different debt and equity investors, and presents results of the analysis that accurately display risks of the investment.

4. Financial Modelling Outline • Developing the structure and layout of alternative types of models • Notes on model structure, programming practices and model periods • Organizing time periods in a model • Value drivers and model inputs • Debt modules -- sweeps, traps, defaults and debt IRR • Fixed asset modules and depreciation and amortization • Income statement and tax schedule • Cash flow and waterfall • Balance sheet and other auditing tools • Presenting key valuation outputs of a model • Performing sensitivity and scenario analysis on model outputs

5. Model Objectives

6. The fundamental issue in any valuation problem is how to assess the risk of future cash flow projections. Financial theory Financial theory dictates that the CAPM should be used to compute the WACC, that the un-levered beta should be used to estimate equity returns, that options pricing models should be used for credit spreads, debt capacity and covenants. Mathematical Models Mathematical models include beta adjustments for the CAPM, statistical models for credit analysis, Monte Carlo simulation and value at risk. Practical Market Information Practical market information can be used to gauge required equity returns, required credit spreads, required financial ratios to achieve investment grade rating and other issues. Direct Evaluation with Financial Models Use of financial models to directly assess risks through sensitivity, scenario and simulation analysis. Consider Investment Alternatives A and B, where A has a higher project IRR than B. Assume A has a return of 11% and B has a return of 9%. Project A or Project B would be selected through assessing the return on the projects relative to the weighted average cost of capital for each project. If the WACC for A is 10% and for B is 9.5% then A is selected. One must computed beta for each investment. Compute the distributions in cash flow of project A and project B to equity holders. If the standard deviation is lower for project B, then assess the risk relative to the return. Compute the achieved rate of return from the ability to raise debt and then assess the return earned on equity. If the return on equity is greater for B then A, select project A. Use judgments with respect to different variables to evaluate different scenarios. Measurement of Risk in Financial Models

7. A Financial Model is a Statistical Tool • In developing a financial model, the basic thing you are doing is summarizing a complex set of technical and economic factors into a number (such as value per share, IRR or debt service coverage). • Forecasting has become an essential tool for any business and it is central to statistics -- in assessing value, credit analysis, corporate strategy and other business functions, you must use some sort of forecast. • Some believe economic forecasting has limited effectiveness and worse, is fundamentally dishonest because uncertain unanticipated events such as the internet growth, high oil prices, sub-prime crisis, falling dollar continually occur. • The whole idea of modeling, like statistics, is quantification. If a concept cannot be quantified, it is a philosophy. The fundamental notion of statistics is presenting and summarizing information, this is the same as a financial.

8. Danger of Believing too Much in Models • Alan Greenspan, Financial Times. • “The essential problem is that our models – both risk models and econometric models – as complex as they have become – are still too simple to capture the full array of governing variables that drive global economic reality. A model, of necessity, is an abstraction from the full detail of the real world.” • Nicholas Taleb: • In the not too distant past, say the pre-computer days, projections remained vague and qualitative, one had to make a mental effort to keep track of them, and it was a strain to push scenarios into the future. It took pencils, erasers, reams of paper, and huge wastebaskets to engage in the activity. The activity of projecting, in short, was effortful, undesirable, and marred with self doubt. • But things changed with the intrusion of the spreadsheet. When you put an Excel spreadsheet into computer literate hands, you get projections effortlessly extending ad infinitum. We have become excessively bureaucratic planners thanks to these potent computer programs given to those who are incapable of handling their knowledge.

9. Financial Models • A good financial model should: • Be relatively simple • Focus on key cash flow drivers • Clearly convey assumptions and conclusions • Evaluate Risks through sensitivity analysis, break-even analysis, scenario analysis • Alternative Models • Back of the Envelope • Check Overall Return on Model • Check EV Relative to Cost of New Assets • Deterministic • Set a number of assumptions and translate into financial ratios and cash flow • Stochastic • Develop a range of possible inputs using Monte Carlo simulation. Used where there is a good and predictable history for value drivers. This is not easy but very important

10. Example of Outputs From a Participant • This is an example of an completed output

11. Layout and Structure of Alternative Types of Financial ModelsA good model allows decision makers to focus on appropriate risks and summarizes data in an efficient way – the key valuation issues should pop out at you

12. Four Different Model Types -- Corporate Models, Project Finance Models, Acquisition Models And Merger Integration Models • Corporate model • A corporation has a history and it is assumed to last indefinitely (although they probably won’t in reality.) This means that valuation of a corporation begins with historic analysis and the models must include some kind of terminal value assumption because the cash flows are not projected forever. • Project finance model • The investment is characterized by different phases and the fact that there is no history (no matter how many times a similar new combined cycle plant is built, you don’t know how it will work until you switch it on.) The project finance models focus on cash flows and generally cover the entire defined lifetime of the project. • Leveraged buyout model • The transaction is defined by an entry price, the holding period and exit price and the manner in which the acquisition is financed. Leveraged buyout models define manner in which alternative financing sources are repaid and compute the return earned by equity investors. • Integrated consolidation model • Computes earnings per share and other financial ratios before and after an acquisition. This type of model considers the specific financing and accounting of the transaction as well as cost savings generated by the transaction.

13. Structure of Alternative Models

14. Alternative Types of Models

15. Credit Analysis – Combination of Historic Financial Ratios and Modeling in Establishing Credit Ratings • Financial Model Outputs for Very Low Credit Risk (AA) • Growth and low leverage, positive cash generation, consistent dividend payments – in a downside case, the company should still be able to pay dividends and have good financial ratios. • Financial Model Outputs for Low Credit Risk (A) • Similar to above, except that debt leverage can be moderately increasing due to acquisitions and capital expenditures. • Access to debt markets as evidenced by financial ratios • Financial Model Outputs for Moderate Risk (BBB) • Strong capacity to service debt in next three years • Modest dividend payments, ability to survive next business cycle – in a downside case, the company should be able to pay back debt and reduce leverage if dividends are cut. • Financial Model Outputs for High Risk (BB) • Tight but positive debt service coverage in two years • Cash flow volatility and high leverage; little discretionary cash flow – in a downside case leverage increases and the company has weak financial ratios • Financial Model for Very High Risk (B) • May have to cut costs to maintain debt service • No dividend payments; highly leveraged capital structure

16. Architecture of Alternative Models

17. Sheet Ordering and Layout – Corporate Model • Base Historic Financial Data • Balance Sheet as Anchor • Input Sheets • Different colors • Arranging of inputs • Set-up Sensitivity • Working Sheets • Arrangements by revenues, expenses, capital expenditures and working capital • Arrangements by capacity, demand, and cost structure • Working Capital Analysis • Depreciation Schedule (Book and Tax) • Vintage of asset classes and Tax Depreciation • Debt Schedule • Issue by issue and sum the totals • Financial Statements • Income Statement • Tax Calculation • Cash Flow • Balance Sheet • Output Sheets • Valuation (Market data on Beta etc) • Financial Ratios

18. Structure of a Standard Corporate Model Inputs: Historic Financials Operating Drivers, Financing, Tax Profit and Loss Fixed Interest Changing Interest Revenue, Expense and Capital Expenditure Analysis Working Capital Analysis Taxes Paid, Taxes Paid and Taxes Deferred Balance Sheet Free Cash Flow Debt Schedule of Existing Issues Cash Flow Statement Fixed Asset Schedule Book and Tax Depreciation Cash Balance, Debt Balance Surplus Cash Balance Equity Balance Initial Balance Sheet

19. Components of a Corporate Model – Historic Financial Data, Debt Structure, Working Sheet, Financial Statements and Free Cash Flow Working Sheet – Revenues, Expenses, Cap Exp, WC Depreciation Historical Financials Free Cash Flow Beginning Balance Sheet & Financial Statements Outputs – Value Credit Quality Existing Debt Inputs

20. Basic Model Logic of Standard Corporate Model • The basic logic in a financial model is simply determining what happens to cash flow. • Something must be done with the deficient or surplus cash flow – retiring or issuing debt, issuing or retiring equity etc. • The model must account for operations as well as the financial structure of the company (the financial structure is primarily the existing debt of the company) • The model should compute free cash flow, earnings and other financial ratios for valuation • Focus of the model should be on value drivers and development of assumptions

21. Cash Flow Process of a Corporate Model Income Statement EBIT, Taxes, etc. Fixed Interest Short-term Interest Interest Income Net Income Analysis of Fixed Debt Issues Fixed Interest Expense Debt Maturities New Issues Debt Balance Cash Flow Statement Net Income, Depreciation etc. Debt Maturities Net Cash Flow Net Cash Analysis Net Cash (Cash – Short-term Debt) Beginning Balance Add: Cash Flow Ending Balance If Ending Balance > 0, Cash If Ending Balance < 0, Std Interest Income = Cash x Rate Interest Expense = STD x Rate Balance Sheet Surplus Cash Plug Other Assets. Short-term Debt Fixed Debt Other Liabilities Common Equity

22. Sheet Layout – Project Model • Plant cot contracts and market drivers • Input Sheets • Different colors • Arranging of inputs • Working Sheets • Arrangements by revenues, expenses and capital expenditures • Arrangements by capacity, demand, and cost structure • Uses and Sources of Funds (Monthly Construction Expenditures) • Conversion from Annual • Computation of Interest During Construction • Debt Schedule (Sources of Funds) • Depreciation Schedule • Financial Statements • Source and Use of Funds • Income Statement • Balance Sheet • Cash Flow -- Waterfall • Output Sheets • Valuation - IRR • Debt Service Coverage Ratios

23. Basic Project Finance Model Components In a project finance model, the dividends = cash flow

24. Structure of a Project Finance Model Profit and Loss Revenue, Expense and Capital Expenditure Analysis Inputs: Operating Drivers from Contracts and Other, EPC Contract, S-Curve, Interest Rate Tax Taxes Paid, Taxes Paid and Taxes Deferred Working Capital Analysis Debt Schedule Cash Flow Statement With Waterfall, Debt Defaults, Sweeps etc. Sources and Uses of Funds During Construction Including Interest Roll-up Fixed Assets Interest Capitalized Fees and Other Cash Balance, Debt Balance Equity Balance Balance Sheet Equity IRR DSCR, LLCR

25. Project Model versus LBO Acquisition Model • No detailed capital expenditure budget by months and interest during construction • Modeling of terminal value and debt outstanding remaining explicit forecast period that is covered by the terminal value • Development of pro-forma balance sheet to begin the model from the sources and uses statement • Cash flow to debt ratios and valuation ratios to establish terminal value

26. Model Sheets in Project Finance Model Debt Schedule – Debt Balance From Drawdown Debt Balance, Interest Expense Inputs – Prices, Costs, Capacity, Technical Parameters Outputs – Free Cash Flow, Equity Cash Flow Value (IRR), DSCR Working Sheet to Derive Revenues Expenses and Working Capital Depreciation – Depreciation Expense Plant Balance Annual Financials – Income Statement, Cash Flow – CASH WATERFALL and Balance Sheet Source and Use of Funds – Draw down, IDC, Equity Issues and Capital Expenditures

27. LBO Model Structure • Model Structure is Essential in Modelling Acquisition • Begin with history and drivers as in corporate model • Work through acquisition transaction and compute • Purchase Price • Consideration • Sources and Uses of Cash • Transaction Multiples • Goodwill • Pro Forma Balance Sheet • Terminal Proceeds and Exit Multiple • Compute debt schedule from the uses and sources of funds • Profit and loss statement is relatively simple • Cash flow statement has waterfall

28. Sheet Layout – LBO Model • Purchase price premium, operating cash flows, terminal value • Input Sheets • Different colors • Arranging of inputs • Working Sheets • Arrangements by revenues, expenses and capital expenditures • Arrangements by capacity, demand, and cost structure • Uses and Sources of Funds (Monthly Construction Expenditures) • Conversion from Annual • Computation of Interest During Construction • Debt Schedule (Sources of Funds) • Depreciation Schedule – Include asset write-up and amortisation of intangibles • Financial Statements • Source and Use of Funds • Income Statement • Balance Sheet • Cash Flow -- Waterfall • Output Sheets • Transaction Multiples • Valuation - IRR • EV/EBITDA and Debt to EBITDA

29. Model Sheets in Leveraged Buyout Acquisition Model Debt Schedule – Debt Balance From Drawdown Debt Balance, Interest Expense Inputs – Purchase Price, EBITDA, Terminal Value Outputs – Free Cash Flow, Equity Cash Flow Value (IRR), Debt./EBITDA Working Sheet to Derive Revenues Expenses and Working Capital Depreciation – Depreciation Expense Plant Balance Annual Financials – Income Statement, Cash Flow – CASH WATERFALL and Balance Sheet Source and Use of Funds – Purchase Price and Capital Structure

30. Structure of an Acquisition Model Inputs: Operating Drivers from History, Acquisition Price and Financing Sources, Tax Profit and Loss Revenue, Expense and Capital Expenditure Analysis Taxes Paid, Taxes Paid and Taxes Deferred Working Capital Analysis Debt Schedule Cash Flow Statement With Waterfall, Debt Defaults, Sweeps etc. Sources and Uses of Funds Fixed Assets Fees and Other Cash Balance, Debt Balance Equity Balance Goodwill and Purchase Price Allocation Balance Sheet Pro-Forma Balance Sheet Equity IRR Debt IRR

31. M&A Integration Model • Inputs for transaction • Consolidated tax rate, interest rate on new financing, dividend payout ratio, other financing parameters on consolidated basis • Synergies • Transaction assumptions (transaction price, debt retirement, new debt financing) • Sources and Uses of Funds • Goodwill • Pro-forma Balance Sheet Including Shares • Target Financials • Buyer Financials • Debt Issues • Depreciation and Tax Adjustments • Consolidated Financials • Outputs

32. Structure of an Integrated Consolidation Model Profit and Loss Target Company Financials Inputs: Operating Drivers from History, Acquisition Price and Financing Sources, Tax Taxes Paid, Taxes Paid and Taxes Deferred Acquiring Company Financials Debt Schedule Cash Flow Statement Sources and Uses of Funds Fixed Assets Fees and Other Cash Balance, Debt Balance Equity Balance Goodwill and Purchase Price Allocation Balance Sheet Pro-Forma Balance Sheet EPS Accretion Credit Measures

33. Sheets in M&A Consolidation Transaction & Source and Use Analysis Target Financials Consolidated Financial Statements Acquirer Financials Outputs – Value Credit Quality Goodwill & Pro-Forma Balance Sheet Debt Issues Including Debt For Financing

34. Computation of Consolidation in Model versus Standalone Model • The basic principle in consolidation include: • The starting point is the pro-forma balance sheet instead of a base historic balance sheet • For free cash flow items (EBITDA, Cap Exp, Deferred Tax, Working Capital) -- Use the data from the individual model runs. • For fixed debt items, use the aggregation of the debt issues as with normal corporate models • For new financing, dividends, taxes and equity issues compute the amounts for the new model.

35. Common Features in All Models • Models require a starting point • Corporate model – balance sheet • Project finance and LBO – sources and uses • Merger model – sources and uses and pro-forma balance sheet • Keep free cash flow assumptions separate from financing assumptions in a working sheet • Keep a separate page for existing debt facilities

36. Notes on Good Modelling Practices

37. Best Practices and Good Practices • It is dangerous to become obsessed with best practices in modelling • You can become bureaucratic and waste time • There are almost always exceptions to best practices • Example • Keep formulas the same, even in base year • Use range names in all cells • Ernst and Young: Rarely use range names • It is much easier to define bad practice • Long formulas are the worst single problem • Keep inputs together and logical

38. Good ModellingPractise • Divide the model into separate modules, beginning with an input section. • Compute how the value drivers determine operating revenues, operating expenses and capital expenditures in a separate “working” module rather than in financial statements. • Understand the starting point of the model as it relates to the valuation issue (balance sheet, sources and uses statement or both). • Carefully define the time period of the model using codes that define alternative phases of the analysis. • Work through every single balance sheet item showing the opening balance, changes and the closing balance for each the accounts. This analysis should be made for everything ranging from cash accounts to common equity. • Include separate modules for debt issues, fixed plant assets, working capital and cash balances.

39. Good ModellingPractise • Limit or avoid the use of macros and iterations to resolve circular references as circular references are not present in the real world. • Use the balance sheet as an auditing tool and include a separate “integrity” page of model verification checks. • Assure that no formulas in the output module of a model affect anything in any other section of the model. • Make sure that spreadsheet columns are consistent throughout the model and that the formulas for each column are identical (at least for the forecast period). • Include a “dashboard” at the top of each page of the model to monitor the integrity and key outputs of the model. • Keep formulas in the model as simple as possible and clearly delineate how each formula is derived from the inputs. • Use the positive number convention which holds individual elements as positive numbers and performs additions or subtractions in the subtotal items.

40. Simple Formulas • The modeling practices are discussed in another sheet named spreadsheet conventions. • The most important is keeping the formulas simple and making the sheets transparent and easy to read. • The following should be in many other lines.

41. Balance Sheet as Anchor and Cork-screws • Use the last historic balance sheet to anchor many accounts. In each case, the closing balance in the last historic year should come from the balance sheet. • It is good practice to have accounts for all balance sheet items • Some examples include: • The plant balance • The debt balance • Net “cash bucket” balance • The NOL balance • The Un-amortised debt fee balance • The basis for changes in working capital • Common and preferred equity

42. Corkscrews - Continued • For each account that is modeled, the closing balance of the account should come from the final balance sheet. • For example: • Plant balance • Closing balance  the amount of gross plant in the base year (the final year before the start of the model) • In the case of debt • The sum of the closing balance that anchor the debt facilities should sum to the amount on the balance sheet. • You should include a verification check to make sure that the individual accounts tie to the balance sheet.

43. Sheet and Color Format • Use small columns and then large column • Show units in a column • Use colors to show the sheet derivation

44. Time Period Definitions in Models

45. Switches in Alternative Models • Switches for time periods in alternative models • General Corporate Models • Switch for History versus Forecast • Switch for Terminal Period • Project Finance Models • Switch for Development Period • Switch for Construction Period • Switch for Operation Period • Switch for Debt Repayment Period • Leveraged Buyout Models • Switch for Transaction Period • Switch for Holding Period • Switch for Terminal Period

46. Dates and Length of Period • Standard IRR and NPV calculations in Excel assume that the cash flows occur at the end of the period • To be consistent with this, one would make the formulas for interest, depreciation and other items use the opening balance rather than the average or the ending balance • To be careful, explicitly show the beginning day of the period and the ending day of the period and use XIRR and XNPV • Explicitly show how many month are in each period

47. Length of a Corporate Model • Explicit forecast period should in theory be long enough for a company to reach a steady-state. • Of course, nobody really knows when this steady state will occur. • In a steady-state: • Company grows at a constant rate • Capital expenditures are a constant proportion of operating profits • Company earns a a constant rate of return on new investments • Copeland recommends a forecast period of 10-15 years • In theory the length of the forecast should only affect the distribution between continuing value and the explicit forecast value, but this never really happens

48. Modelling Value Drivers

49. Inputs, Drivers and Working Analysis • Setting-up Sensitivity Analysis • Creating Indices • Working Capital Modelling • Comparison with Historic Data and Other Metrics • Dash Board

50. Real World Modelling Process – Corporate Models • The following six step process • Step 1: Gather Historic Financial Statements and read them (it is not so bad) • Step 2: Change the Arrangement of Financial Statements (See the example on the subsequent slides) • Step 3: Compute Ratios from Historic Financial Statements to develop some of the mechanical assumptions such as A/R to sales and depreciation rate • Step 4: Develop Revenue, Expense and Capital Expenditures by Working through Value Drivers • Step 5: Work through the Income Statement, then the Cash Flow Statement, then the Balance Sheet to Check, only for forecast years • Step 6: Valuation, sensitivity analysis and presentation