Available Course

Advanced Financial Modelling & Dashboards with Excel

>> Click here to book this training as an In-House course <<

Duration: 3 Day
CPD Hours: Attendance at this seminar will secure 19.5 hour/s verifiable CPD points including other professional bodies (SAICA, SAIBA, ACCA, IACSA, IRBA & etc)
Course Facilitator: Nerissa Singh
T:  011-886-1395
E:  nerissa@probetatraining.co.za
Back

This intensive and highly practical workshop will teach delegates how to design and construct and then effectively use robust financial models, using the unique branded system developed by Gerald Strever, the “Financial Modelling Quadrant system. The four Quadrants are: • Identifying the Drivers of the intrinsic value of the company • Model the Financial Statements - Balance Sheet, Income Statement and Cash Flow • Model the Sensitivity of the intrinsic value of the company by flexing the Drivers • Simulate the possible variability of the intrinsic value of the company using Monte Carlo Simulation. This is a Research-Based Training Course: the course curriculum has been developed and designed from research with actual industry practitioners and will in just 3 days give you all the tools and techniques needed to move from basic techniques to becoming a “blackbelt” in Financial Modelling. Other courses in the market cover only one Quadrant – the Financial Statements.

• DISCOVER the most efficient ways in planning, designing, structuring, cross-checking and auditing your financial models • CONSTRUCT robust, reliable financial models that are easy to amend and maintain integrity • UNDERSTAND advanced techniques and applications in financial modelling • LEARN to measure, interpret and predict a company’s performance using advanced Excel financial models • IMPROVE decision-making processes with better risk evaluation, sensitivity analysis and scenario modelling • SAVE time on the analysis and manipulation of your financial models with keen insights from our expert • SOLVE your real-life financial modelling issues and challenges • APPLY powerful visualization techniques to deliver maximum impact when presenting your financial models • OPTIMIZE financial models to ensure they are easy-to-use and well-documented in accordance with spread-sheet modelling best practices • PREPARE financial models to address a variety of financial modelling scenarios

DAY 1 QUADRANT 1 - DRIVERS 1. Creating Practical Forecasts and Projections for the Model • Forecasting with Excel’s regression functions • Excel’s smoothing functions. • Different approaches to business planning including deterministic and probabilistic models. • Trends • Regression analysis • Smoothing techniques • Seasonal analysis Exercise: You will build a Sales Forecasting Model and assess its accuracy using the Mean Squared Error test QUADRANT 2 – FINANCIAL STATEMENTS 2. Building a Financial Planning Model from Scratch • Identifying potential Driver variables • Initial Accounting Statements for a Financial Planning Model • Building a Financial Planning Model • Extending the Model to Year 2 and beyond Exercise: You will reverse-engineer a conventional 3 statement financial model to include the Drivers tentatively identified. DAY 2 3. Calculating the Intrinsic value of the Company • Internal Rate-of- Return (IRR) and Modified Internal Rate of Return (MIRR) metrics that are required for informed financial decision-making. • Using XNPV and XIRR • Using XMIRR • Mid-Year discounting Exercise: You will add to the Model – calculating the intrinsic value of the company modelled 4. Analysing Key Financial Data and solving ‘What If’ problems to Optimise your Model • Various approaches to what-if analysis, including: data tables, sensitivity analysis and goal seeking. • Working on data tables - one-way and two-way • Using Goal-Seek. • Use of data tables in a marketing context. • The power of the Solver function • LP Simplex, GRG Non-Linear and Evolutionary Solver Exercise: You will use the Data Table and Solver functions to optimise the Model QUADRANT 3 - SENSITIVITY 5. Sensitivity Analysis Using SensIt • SensIt Overview • One Input, One Output • Many Inputs, One Output Tornado • Many Inputs, One Output Spider chart Exercise: You will use the SensIt Excel add-in to test the sensitivity of the modelled company’s intrinsic value to changes in the Input Variables DAY 3 QUADRANT 4 – MONTE CARLO SIMULATION 6. Monte Carlo Simulation Using SimVoi • SimVoi Overview • Using SimVoi Functions • Monte Carlo Simulation • Random Number Seed • One-Output Example • SimVoi Output for One-Output Example Exercise: You will use the SimVoi Excel add-in to perform simulations on the Model 7. Creating Dashboards • Creating Fit-For-Purpose Dashboards by Identifying Relevant Metrics and Drivers for Different Target Audiences • Tying the Dashboard back to the Model Mapping and Objectives • Presenting and Visualizing Model Outputs • Dynamic Dashboard Options • Conditional Formatting • Hiding and Grouping • Using Appropriate Charts, Diagrams and a Summary Page

• CFO / Finance Director / Financial Controller / Head of Finance • Financial Managers and Analysts • Director/Head/Manager of Strategic Planning • Director/Head/Manager of Business Development • Financial/Equity/Treasury Analysts • Project Managers • Project Financiers and Lenders • Accountants and Auditors Your computer must be Windows based with a full installation of MS Office 2010 or later, as well as Adobe Reader

• CFO / Finance Director / Financial Controller / Head of Finance • Financial Managers and Analysts • Director/Head/Manager of Strategic Planning • Director/Head/Manager of Business Development • Financial/Equity/Treasury Analysts • Project Managers • Project Financiers and Lenders • Accountants and Auditors Your computer must be Windows based with a full installation of MS Office 2010 or later, as well as Adobe Reader

Participants should have a working knowledge of Financial Statements and a good knowledge of Microsoft Excel.

Free Takeaways Excel Add-Ins – SenSit and SimVoi plus a User Defined Function, XMIRR. These Add-Ins are not time-limited and can therefore be used after the workshop. They represent what is arguably the richest set of software available with any Financial Modelling workshop in the market.