Financial Modelling in Excel

This course will enable you to accomplish: Best practice in structuring models Building models using best practice Excel techniques Building inflation into the model Modelling taxes Creating cash flow waterfalls Using the model for analysis, including banking ratios and valuations Sensitivity analysis tools Advanced Excel functions Summary of course content Best practice in structuring models Building models using best practice Excel techniques Building inflation into the model Modelling taxes Creating cash flow waterfalls Using the model for analysis, including banking ratios and valuations Sensitivity analysis tools Advanced Excel functions Course overview The course is designed to support financial analysts, modellers and their managers in creating financial models on a consistent and focussed basis. Some previous use of Excel is assumed, but delegates do not need an advanced knowledge. Methodology The principal aim of the course is enable participants to use Microsoft Excel to prepare logical and easy-to-use financial models to support transactions, forecasts and planning for ongoing business streams. These skills can also be used to support credit approvals and reviews by lenders and to support the organisation to run or sponsor projects. The course will review best practice in model structures and logic, and using tools to highlight areas of risk, particularly in sensitivity analysis. Who should attend Financial analysts Finance managers Bank economists Relationship managers Corporate accountants Credit analysts Financial controllers Budget and forecasting controllers Deal structuring analysts M&A specialists Financial planning and analysis managers
Day 1 Introduction and course objectives Brief overview of course objectives Overall model structure and design Best financial modelling practice Overall structure of the model Logic flow within the model Separation of inputs, calculations and outputs Defining desired outputs Setting-up required inputs Use of switches to allow option selection Use of flags to control timing factors Set-up for flexibility Consistency in the model Accommodating multiple options Building assumptions off term sheets or other external inputs Using the assumptions sheets as a sign-off document Restricting ranges of inputs and validation criteria Version control Use of the corkscrew technique Tracking changes Documentation Exercise – creating a simple model with an assumptions / input sheet with built-in flexibility Modelling techniques for revenue and cost forecasts Translating assumptions and inputs into a model forecast Build-up of construction or other capital costs Correct matching of units Treatment of fixed and variable costs Modelling pricing and revenue assumptions Use of lookup functions to change expenditure timings Building in sensitivities into the model Day 2 Inflation / escalation factors Creating inflation indices Controlling start time of inflationary pattern Applying multiple rates to different cost and revenue items Varying inflation rates over life of the model Introduce exercise to do outside class – model multiple, variable rates and analyse a separate set of actual rates to compare to forecast Brief overview of modelling taxes Differences between P&L and tax treatment for costs & revenues Allowing for deductibility and non-deductibility Capital allowances vs depreciation Modelling tax losses and their effect Example - Review of an example of tax modelling for an investment project Interest, debt fees and circularity Circularity and consequences Solutions to circularity – advantages and disadvantages of each Calculations of interest and fees Cash flow payment vs amortisation in the P&L Capitalising / rolling-up fees and interest Example – demonstrate carious methods to overcome circular references Cash flow modelling Brief revision of WACC and discounting of cash flows Use of the cash flow waterfall technique Modelling for: Timing of debt and equity funding Fee costs, upfront and ongoing Interest costs, capitalised interest, interest rate ratchets Debt repayment profiles Rate switches or refinancings Debt repayment profiles and built-in options Dividends, other equity returns, constrained dividend payments NPV, IRR and other DCF measures Exercise: creation of simple cash flow waterfall model to reflect debt costs, repayment profiles, and returns to equity under constraints. Day 3 Comparing a model to previous versions of the model Separate runs and variation of inputs Comparison of actuals to forecast Comparing results of different versions of same model Reviewing future implications of variances Example – from different versions of a modeled forecast, calculate variances and review future assumptions Sensitivity analysis Stress-testing the model Varying inputs to assess effect on results Use of built-in sensitivity inputs Use of goal seek and solver Version control to allow comparison of outputs Use of Excel tools to support sensitivity analysis: data tables scenario manager watch window function scenario tables Exercise – from a given model of cash flows, P&L and balance sheet, calculate effect of varying inputs to a given degree, and stress-test model to break-even. Reporting outputs Design techniques to enable optimisation Pivot tables Consolidation techniques Creating simple macros Use of charts and graphics Introduction to macros Demonstration – creating a macro without using VBA Exercise – from a given set of outputs, create output tables, pivot tables and consolidations to enable flexible reporting Model auditing Use of the formula auditing toolbar Checks, totals and error reporting Logic trees and flowcharts Non-Excel tools available Exercise – from a given model, audit and uncover errors and inconsistencies Risk reviews Categories of risk and their relationship to the model Use of risk matrices Using sensitivity analysis to understand risk Probability analysis and use of statistical techniques, introduction to Monte Carlo analysis Risk-adjusted returns Exercise – for a given model, calculate risk-adjusted returns from potential risks Wrap-up Overall review Key points to re-iterate Brief introduction to further exercises and reading Final questions and issues to discuss Course summary and close