Financial Modeling PDF

Financial Modeling Integrated Financial Management Oct 20, 2024  Teaching Objectives 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 vario...

Financial Modeling Integrated Financial Management Oct 20, 2024  Teaching Objectives 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. The exercises and lecturers are intended to provide:  A head start for creating Models.  Helpful ideas for designing and structuring more efficient, stable, transparent and accurate models. Financial Modelling 2 Oct 20, 2024  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  Fixed asset modules and depreciation and amortization  Income statement  Cash flow  Balance sheet  Presenting key valuation outputs of a model  Performing sensitivity and scenario analysis on model outputs Financial Modelling 3 Oct 20, 2024  Model Objectives Integrated Financial Management Oct 20, 2024  Financial Models Attributes 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 Financial Modelling 5 Oct 20, 2024  Layout and Structure of Alternative Types of Financial Models A 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 Integrated Financial Management Oct 20, 2024  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. 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. M&A Integrated 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. Financial Modelling 7 Oct 20, 2024  Structure of Alternative Models Valuation Analysis in Alterative types of Models Corporate Project LBO M&A Integration Model Finance Model Model Investment Decision and Implied Value Entry Multiple and Acquisition Premium Present Value of depends on Equity Acquisition Premium Depends on Earnings Valuation from Discounted Free Cash IRR versus Market Depends on Equity per Share Acretion Model Flow or Multiples Hurdle Rate IRR and Hurdle Rate and Debt Ratios Weighted Average Senior and Sources of Funds Base Case Risk Cost of Capital, Debt Capacity, Debt Subordianted Debt Used for Tranasction Measurement Multiples, Terminal Terms Financing and Exit and Assessment of Growth Multiple Credit Quality Sensitivity Analysis Sensitivity Analysis Traditional Risk Sensitivity Analysis Sensitivity Analysis and Scenario Analysis and Scenario Analysis Assessment from and Scenario Analysis and Scenario Analysis of DCF and Multiple of EPS Accrection and Equity Perspective of Equity IRR of Equity IRR Value Credit Quality Break-even Analysis Break-even Analysis Break-even Analysis Break-even Analysis Traditional Risk to Determine Ability to to Determine at what to Determine IRR on to Determine Assessment from Re-finance and Point Cash Flow Senior and Prospective Credit Debt Perspective Maintain Credit Rating Cannot Service Debt Subordinated Debt Rating Probability Distribution Monte Carlo Probability Distribution Probability Distribution of Equity IRR and Analysis with of EPS and DCF of Equity IRR, Senior Probability of DSCR Model Valuation IRR and Junior IRR below 1.0 Financial Modelling 8 Oct 20, 2024  Alternative Types of Models Structure of Alternative Models Project Corporate LBO M&A Integration Finance Model Model Model Contracts and Historical financial Historical financial analysis of Historical financial statements; Analysis statements; Analysis Commodity Prices and statements; Analysis of value drivers; of value drivers; Information Base other value drivers of value drivers Transaciton Terms Transaciton Terms Sources and Uses and Sources and Uses and Model Starting Sources and Uses Historic Balance Sheet Pro-Forma Balance Pro-Forma Balance Point Analysis Sheet Sheet Cash flow waterfall Net cash flow after Cash flow changes Cash flow waterfall that ultimately dividends that result in that result in changes Cash Flow Process that ends in dividends measures dividends changes in short-term in short-term debt or paid to equity paid to equity debt or surplus cash surplus cash Existing Debt Issues; New Debt Issues from New Debt Issues from Debt Analysis New and Existing Retired Debt Issues; Transaction Transaction New Debt Issues Arbitrary terminal Transaction holding Model Termination End of project life EPS analysis period period period NOL; cash traps and NOL; cash sweeps; Pro-forma balance sweeps; construction NOL; target capital interest capitalization Model sheet; minority interest period issues; debt structures; circularity; on sub debt; debt Complexities changes; new debt service reserves; debt depreication vintage service reserves; issues sculpting terminal period DCF Valuation; EPS Project EPS and Other Equity IRR; Project Equity and Debt IRRs; Model Output projection; Implied Ratios on Standalone IRR; DSCR Debt/EBITDA P/E; Credit Quality vs Combined Basis Financial Modelling 9 Oct 20, 2024  Architecture of Alternative Models Integrated Financial Management Oct 20, 2024  Sheet Ordering and Layout – Corporate Model Base Historic Financial Data  Balance Sheet as Anchor Input Sheets  Different colors  Arranging of inputs Working Sheets  Arrangements by revenues, expenses, capital expenditures and working capital  Arrangements by capacity, demand, and cost structure Working Capital Analysis Depreciation Schedule Debt Schedule  Issue by issue and sum the totals Financial Statements  Income Statement  Cash Flow  Balance Sheet Output Sheets  Valuation  Financial Ratios Financial Modelling 12 Oct 20, 2024  Structure of a Standard Corporate Model Inputs: Historic Profit and Loss Financials Revenue, Expense and Fixed Interest Capital Expenditure Analysis Changing Interest Operating Drivers, Working Capital Analysis Balance Taxes Paid, Taxes Sheet Financing, Paid and Taxes Deferred Tax Free Debt Schedule of Cash Existing Issues Cash Flow Statement Flow Fixed Asset Schedule Cash Balance, Depreciation Debt Balance Surplus Cash Balance Equity Balance Initial Balance Sheet Financial Modelling 13 Oct 20, 2024  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 Financial Modelling 14 Oct 20, 2024  Modelling Value Drivers Integrated Financial Management Oct 20, 2024  Real World Modelling Process – Corporate Models  The following six step process: Step 1: Gather Historic Financial Statements and read Step 2: Change the Arrangement of Financial 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 Financial Modelling 16 Oct 20, 2024  Use of History to Determine Drivers in Corporate Modeling Translate value drivers such as price, the cost of new capacity and cost structure to financial statement projections You often need minimal operating data – one measure of capacity and one measure of sales Evaluate historical relationship between value drivers and financial variables  There is no generic formula for establishing value drivers  Value drivers should incorporate some kind of capacity, capacity utilization and cost structure assumptions Determine how the financial structure – the outstanding debt – affects financial performance Financial Modelling 17 Oct 20, 2024  Results of Arranging Inputs When you are finished arranging items:  You should have an opening balance sheet  You should have a debt schedule  Aggregate of debt issues should tie to balance sheet  You should have a history of revenues, expenses and depreciation Financial Modelling 18 Oct 20, 2024  Operating Assumptions in Model Once the working sheet data has been developed compute the three basic operating inputs: Capital expenditures Revenues Operating expenses History should be presented along with forecasts for the value drivers Financial Modelling 19 Oct 20, 2024  Workings Analysis Issues Combine historic financial statement data with selected operational data For each line item in the financial statements, show ratios for the items and show assumptions for the ratios The key is to isolate real economic drivers such as price, capacity utilization, market share and other things that really drive value Arrange by Revenues, Expenses, Capital Expenditures, Working Capital and Depreciation Compute change in working capital for the cash flow analysis If deferred taxes are present, compute book and tax depreciation Financial Modelling 20 Oct 20, 2024  Revenue Drivers- Demand vs. Capacity Begin with demand and then express the demand in terms of required capacity Relate the capacity to demand  Use a ratio of demand to capacity  Reserve margin that relates demand to required capacity  Class rooms needed at capacity  Max towers per subscriber  Retail outlets per level of sales  Once you have the maximum capacity, test the capacity against the level of sales. Financial Modelling 21 Oct 20, 2024  Value Drivers and Starting Point of Forecast Demand Driven Forecast (Telecommunications)  Begin with market size, industry demand and derive volumes  Volume = Industry Demand x Market Share  Capacity requirements come from volume and maximum utilization  Drivers and Market Size, Market Penetration, Market Share and Price Capacity Driven Forecast (Commodity Markets)  Begin with capacity instead of demand and determine volumes from capacity utilization multiplied by capacity  Inputs driven by technical efficiency parameter  New capacity driven by corporate strategy  Drivers are capacity, capacity utilization, and price Financial Modelling 22 Oct 20, 2024  Value Drivers and Starting Point of Forecast Asset Driven Forecast (Retail Banks, Investment Companies)  Begin with asset and liabilities  In banks, use deposit growth and loan to deposit ratios  Investments (like capital expenditures) are increases in loans Financial Modelling 23 Oct 20, 2024  Examples of Value Drivers Economic and business variables that directly affect cash generation:  Price per unit sold  Volumes sold  Penetration rates in theme park  Market share of telecommunications firm  Sales per square foot  Cost per ton  Occupancy rates  Cost of capacity per new subscriber  Cost of replacing oil reserves per bbl Main drivers that should be utilized to prepare sensitivity analysis Correlation with macro-economic variables may be useful Financial Modelling 24 Oct 20, 2024  Working Through Drivers Use historical revenue components from income statement  Relate revenue components to available volume data & find the realization  Relate volume data to capacity data to find capacity utilization  Example – Airline planes and passenger traffic related to passenger revenues; number of planes is capacity; passenger miles is volume Use historical operating expense components from income statement  Relate to same volume and capacity data as revenues  Split into fixed and variable costs if possible Use corporate Plan/estimates for capital expenditures  Determine cost of capital expenditures per capacity  Split between maintenance capital expenditures and expenditures for new additions Financial Modelling 25 Oct 20, 2024