Financial Modelling in Excel

Learn how to construct different financial models and analyse their applications and uses: Model design and structure Spreadsheet best practice Testing and auditing a model Forecasting Sensitivity and risk Simulation Optimisation and targeting Management reporting On completion of this 3-day training programme delegates will be able to: Understand the different types of financial models and when each should be applied. Construct models that are robust, tested and scalable. Incorporate elements such as risk, sensitivity, optimisation and forecasting into the model. Produce management reports, summaries and meaningful charts. Teaching methodology The programme is taught using formal lectures combined with practical and interactive case studies and exercises to reinforce the concepts covered in each teaching session. Emphasis is placed on delegates gaining practical, hands-on experience of the design and construction of financial models in Excel. Comprehensive product notes and modelling software will be provided for future reference. The course is very practical with a case study in session four of each day, and other exercises in each of the sessions. The investment model started on day one is added to on days two and three. Who should attend? This course has been designed for: CFOs Financial controllers and managers Financial and business analysts Risk managers Accountants Corporate treasury managers Middle office staff General managers Course Level The course begins by concentrating on the advanced use of Excel. These skills will be applied to the construction of financial, valuation and investment models. Delegates will then learn how to incorporate forecasting, optimisation, risk assessment and sensitivity scenarios into these models. The course is taught using a step-by step approach so delegates will be able to construct financial models for a wide range of practical scenarios. Course Background When looking at any financial opportunity, the ability to perform accurate and realistic analysis is imperative. In today's ever-changing business environment, the capability to just manipulate spreadsheets is not enough. You have to be able to incorporate all the "what if" scenarios and stress any proposal to its limits. A working financial model can facilitate and improve the reliability and quality of your decision-making. Modelling techniques are used in many different areas, such as investment appraisal, capital planning, budgeting, valuation, financial analysis and forecasting. Banks and businesses worldwide rely on the information produced by financial models – for many it is the key to success.
Day 1 Overview of Modelling Techniques Introduction and overview Outline of financial modelling Examples of Excel financial models Useful Excel features and techniques Exercise: analysing examples of modelling techniques Spreadsheet best practice 1 Model design and structure – key steps Financial analysis basics – financial statements Case outline - introduction Exercise: Defining a model plan for a financial analysis model Model design Exercise: Techniques for modelling financial statements Spreadsheet best practice 2 Completing financial model Cash flow and ratios Auditing and testing Examples of spreadsheet errors Essential testing and auditing techniques Example: testing financial analysis model with cash flows and ratios Exercise: debugging and checking a financial model Questions and answers Day 2 Forecasting and Risk Practical project model 1 Practical project model exercise Building a project model Macros and security Visual basic in excel Writing and auditing and macros Spreadsheet security Exercise: writing and auditing useful macros Forecasting models Review of forecasting methods Understanding financial ‘drivers’ for analysis and project models Linear methods Exercise: producing a cash flow forecast Risk techniques Financial mathematics and debt cover Risk and multiple answers Data matrices Scenario techniques Exercise: adding debt cover and testing the results Questions and answers Day 3 Optimisation and Reporting Practical project model 2 Review of initial answers and identification of potential risks Adding risk techniques to the project model in module 4 Optimisation and targeting Overview of optimisation and targeting Goal seek and solver methods Example: targeting using the composite financial model Practical project model 3 Extend the project model to answer management questions and target specific results Exercise: completing and auditing the project model Management reporting Requirement to consolidate and summarise data Exercise: consolidating data from different sources Excel report manager Exercise: pivot tables Techniques for summarising data Exercise: producing a management analysis Practical management reporting and forecasting Analysis of company financial data Exercise: extend the financial model with graphical reports Course summary and close