Financial Modelling and Forecasting Techniques using Advanced MS Excel Techniques

Build more powerful and more accurate forecasting models to better analyze financial data, predict revenues and costs, assess risks - and justify critical business decisions. Build the model. . .generate the forecast. . .drive the business! Create more powerful and accurate forecasting models that steer you to fast-track, business-improving decision-making!

What Will Participant Gain From The Programme?

• Use mainstream financial concepts and terminology
• Describe fundamental and Advanced statistical methods
• Develop existing financial models in excel®
• View more advanced excel® modeling techniques such as monte carlo add-ins
• Create your own model and provide diagnostics
• Analyze your model and provide diagnostics
• Identify uncertainty in the model and design methods to address it
• Present your models to an executive management audience

You will create effective ­financial models that meet your business needs by;

• Quantifying the uncertainty inherent in forecasting models with various risk analysis techniques
• Making better, more profi­table business decisions using advanced ­financial modeling methods
• Evaluating patterns in your historical data
• Identifying and utilizing key business drivers and integrate them into your forecast
• Effectively communicating the results of your models to team members and senior management

How you will benefit for attending this training:

Finance theory as used in modeling

• Apply the concepts of modern finance
• Use various interest and risk measures in your models
• Develop models that are consistent with the principles of finance

Creating ­Financial models in Microsoft Excel

• Develop a set of guidelines for preparing financial models in Microsoft excel
• Identify key tips for preparing these models
• Apply the pyramid structure to your models
• Use various Microsoft excel functions that will aid in developing models
• Prepare a basic model using these techniques

Model statistics and forecasting

• Calculate mean and standard deviation of a dataset
• Present data for manipulation
• Analyze the data using simple techniques
• Forecast financial data using regression analysis

The art of building models

• Identify the key points necessary to build a model
• Describe the art in creating an effective model
• Create a new model solving a complex business problems

Modeling risk and uncertainty

• Identify uncertainty as it exists in the business world
• Develop simple techniques to handle uncertainty
• Use monte carlo simulations to handle uncertainty
• Use option pricing models to handle uncertainty

Course Content

Module 1: Financial Modelling Theory and Best Practice

• Choosing between Excel versions
• Overview of best practice financial modelling techniques
• Common Excel error values in financial modeling
• Correcting and suppressing errors
• Locating and fixing circular references
• Allowing iterative calculations when circular references are maintained

Module 2: Advanced Financial Modelling Tools and Functions

• Selecting the formula or tool which is most appropriate for each modelling situation
• Nesting formulas
• Useful functions in financial modelling such as COLUMN, CHOOSE, OFFSET, LOOKUP and TRANSPOSE
• Using LOOKUP functions in tiered pricing models (volume break discounting)
• Pros and cons of using array functions and their use in financial modeling
• Use of form controls such as spin buttons and combo boxes

Module 3: Advanced Financial Modelling Techniques

• Rebuilding an inherited model
• How long should a formula be? Deciding when to break a complex formula in several steps to maintain transparency and allowing ease of model auditing
• Comparison of different methods of modelling escalation and growth calculations
• Presenting model output using a chart on two different axes and chart types
• Creating a Waterfall Chart in Excel
• Pivot tables summarise, dissect and analyse large amounts of data
• Why pivot tables are not commonly uses in financial modelling, and other alternative tools
• Automate your financial model for the user with Macros. Recording and modifying VBA code and creating macro buttons

Module 4: Scenarios and sensitivity analysis

• Learn different methods of what-if, scenario and sensitivity analysis in Excel using:
• Show multiple scenario outcomes simultaneously with one and two-dimensional Data Tables
• Learn how to automatically highlight selected scenarios using complex conditional formatting
• Using the Scenario Manager
• Manual scenario building
• In-cell drop-down boxes
• Combo-box drop down boxes

Practical Exercise: At each step during the course, participants build and practice each formula, tool and technique. Record your own macro with buttons, build a pivot table, and create drop-down boxes.

Module 5: Practical Financial Modelling

• Building on the tools and techniques covered, we will learn to apply advanced modelling skills to build complex, yet robust and user-friendly financial models.
• Factory Rollout Model
• Working on a startup funding model from start to finish we will evaluate input assumptions; calculate revenue and costs of production to calculate cash requirements. From this, we build a full set of financial statement with particular emphasis on linking the profit and loss statement, cash flow statement and balance sheet together.
• Practical Exercises: Utilizing the tools and techniques covered in the first part of the course, participants will build their own financial model case study, performing stress testing, scenario and sensitivity analysis on their model.