This course covers the practical usage of Excel to create financial model for budgets and forecasts. It is designed for users who use Excel fairly regularly, and are comfortable with commonly used Excel tools and techniques. Whilst a brief overview of tools used in this course is provided, it is assumed that delegates are reasonably confident Excel users, and will be involved in financial modelling in their organisation.
This course will be taught using a combination of formal instruction, combined with practical and interactive exercises and case studies used to reinforce the concepts taught in each section of the course. Case studies include critique of conventional budgeting techniques and real-world examples of problems and latest budgeting techniques and financial modelling best practice guidelines.
This course is demonstrated mostly using the latest Microsoft Excel 2019/365, however course materials covers prior versions as well as Excel for Mac. This course is very practical and hands-on. In order to complete the exercises, delegates are required to bring their own laptops with their preferred version of Excel installed.
Each participant receives a revised edition of ‘Using Excel for Business Analysis’ by your course presenter, Danielle Stein Fairhurst. Delegates are provided with over 50 Excel templates and examples with step-by-step instructions for case studies.
About your Presenter
Danielle Stein Fairhurst is the Principal of Plum Solutions, a Sydney-based consultancy specialising in financial modelling and analysis. She is the author of “Financial Modeling in Excel for Dummies” published in 2017 and “Using Excel for Business Analysis; a Fundamental Approach to Financial Modelling”, revised edition published by Wiley Finance in 2015. With many years’ experience as a financial analyst, she helps her clients create meaningful financial models in the form of business cases, pricing models and management reports. She has hands-on experience in a number of industry sectors, including telecoms, information systems, manufacturing and financial services.
Short-cuts and techniques to build a financial model in less time
Become super-efficient in Excel through intensive use of keyboard shortcuts and best practices to efficiently build an effective and robust model.
Experience designing solutions to real-world examples
Create a financial model based on real-world financial modelling challenges. Incorporate current economic uncertainties into the model in order to take more calculated risks and make more informed business decisions.
Analytical thought on fluctuating economic assumptions in financial modelling
Identify correct economic inputs to model and deal with fluctuations. Quantify uncertainty in forecasting models with stress-testing, what-if analysis and risk analysis techniques.
Knowledge on how to deal with uncertainty and risk in financial modelling
Explore the several different ways to perform scenario and sensitivity analysis in Excel and learn to model in detail the complex but most commonly used methods of showing scenarios using a combination of formulas and drop-down boxes
In addition, you will learn how to:
- Streamline model building by applying best practice functions, tools and techniques
- Apply commonly used formulas in new and different ways
- Deal with risk and uncertainty by creating scenarios and performing sensitivity analysis
- Build stress-tests, what-if and sensitivity analysis into your model by incorporating economic fluctuations
- Minimise manual labour and automate common tasks by building macros into your models
- Measure and interpret the performance of your company using Excel modelling
- Master key steps to get the most out of Excel functionality to improve your financial modelling
- Learn how to expedite and enhance the decision-making process
- Maximise your analytical abilities by learning conventional and new approaches of modelling
- Translate business concepts into logically structured models and formats
- Select which advanced formulas are appropriate in different situations
- Perform various advanced scenario modelling techniques
Day 1 – Tools for Financial Modelling
Must-know Tools and Techniques
- Key points on upgrading to the latest version of Excel
- Absolute referencing
- Named Ranges
- Time saving shortcuts
Formulas you simply can’t live without
- Lookup and Logical functions
- Aggregation functions
- Financial Functions
- Formula Nesting
Bullet-proofing your Model
- Protect data by locking cells
- Password Protection
- Restricting incorrect data entry with data validations
- Creating error validation messages
- Using worksheet protection to prevent entry
- Protecting your file using “Read Only” and password protection
- Creating and modifying a chart
- Making your charts look fabulous
- Charting tips, tricks and shortcuts
Essential Advanced Tools
- Goal Seek
- Pivot Tables
- Array formulas
Day 2 –Financial Modelling Theory & Practice
Financial Modelling Best Practice
- Overview of best practice financial modelling techniques
- Model planning structure and steps in building your model
- The technical, design, business and industry knowledge required for financial modelling
- Selecting the formula or tool which is most appropriate for each modelling situation
- Attributes of a good financial 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
Advanced Financial Modelling Techniques
- Maintaining model integrity and avoiding Excel errors
- Rebuilding an inherited model
- Building self-balancing error checks
- Correcting and suppressing errors
- Fixing circular references
- Goal seeking to calculate break-even point
- Create a more efficient model with internal links
- Dealing with external links and the potential errors they cause
Advanced Financial Modelling Tools and Functions
- Pros and cons of using array functions
- Nesting formulas
- Learn how to have Excel automate variance analysis using complex conditional formatting
- Automate your financial model for the user with Macros. Recording and modifying VBA code and creating macro buttons
- Pivot tables – summarise, dissect and analyse large amounts of data
- Shortcuts for data manipulation in Excel
Day 3 – Scenarios and Sensitivity Modelling
Stress testing to deal with uncertainty and risk in Financial Modelling
- Economic inputs to model and modelling fluctuations in external factors
- Use of stress testing to validate your underlying assumptions and risk calculations
- Advanced What-if analysis
- Importance of assumptions when assessing risk
Scenarios and sensitivity analysis
Learn different methods of what-if and scenario analysis in Excel using:
- Show multiple scenario outcomes simultaneously with one and two-dimensional Data Tables
- Using the Scenario Manager
- Monte Carlo Simulation in Excel
- 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.
Day 4 – 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 statements with particular emphasis on linking the profit & loss statement, cash flow statement and balance sheet together.
One of the more complex and widely used calculations in financial modelling. Application of two different methods of calculations; a simple and progressive table.
Create a model which calculates profitability and break even points at various prices using Goal Seek
Create a user-friendly report with a dynamic chart based on drop-down selections. Apply this to a financial portfolio pricing comparison model.
Practical Exercises: Utilising the tools and techniques covered throughout the course, participants will build their own financial model case study, performing stress testing, scenario and sensitivity analysis on their model.
Who Should attend?
This hands-on course is designed for middle and senior business and finance professionals and general managers who need to use financial models to measure business performance, including:
- Finance Managers and Controllers
- Strategy Directors and Managers
- Budget, Corporate, Business and Financial Analysts
- Project Managers and Risk Analysts
- Investment and Management Accountants
- Heads of Business Units and Business Planners
- Financial Advisors and Corporate Analysts
Comments from past students
- Great course – well worth the time. Wish I’d done this course years ago!
- The course was a good mix of theory & practice, with a good coverage of topics. The presenter had a good knowledge, was easy to understand and follow and the venue was great.
- This was a jam-packed course with practical content and very well-presented.
- Evaluating this course is one of the few times I could tick “strongly agree” on all the boxes!