Financial Modelling, Budgeting and Forecasting with MS Excel Course

This practical 2 or 3 day Financial Modelling Training Course leads delegates through the advanced MS Excel functions required when modelling, explaining the functions and techniques to use to build reliable, robust financial models. It is run on a monthly basis in Midrand to facilitate delegates from Pretoria and Johannesburg.

It is suitable for Intermediate and Advanced Excel users, but the best way to check if this is the best MS Excel course in Johannesburg for you is to do the Free MS Excel Skills Assessment first.

The next available course dates are 14- 15 February 2019 and 14-15 March 2019. For more dates have a look at the training calendar for the various MS Excel courses we offer

Some of the key outcomes of the Financial Modelling course will include:

  • Understanding HOW a spreadsheet should be built to make it robust and safe
  • Best ways to set up templates to easily obtain the information required for the financial model
  • How to run comprehensive ‘What If’s through the spreadsheet
  • Understanding time value of money where relevant.

Delivery Methods- Public Microsoft Excel Advanced Training Course

Live MS Excel Training options

  • Public courses for Johannesburg and Pretoria
  • Onsite- We can provide the training onsite to corporate groups- for more information click the request for information button below.
  • Note that this 2 day course is part of the 5 day course called Corporate Financial Modelling

Online MS Excel Intermediate Training

Course Outline

Spreadsheet Best Practice

  • Why should you follow best practice guidelines for financial models
  • Consistency
  • Separating the inputs, calculations and outputs
  • Spreadsheet Integrity

Other Spreadsheet Best Practice Suggestions

  • Determine the business drivers or triggers of the model
  • Single input vs a row of inputs
  • Contentious Issues in Spreadsheet Best Practice Theory
  • Other best practice suggestions
Learn how to model for changes in ‘fixed’ costs when growth changes

Excel related tips and tricks for Financial Modelling

  • Viewing many parts of the spreadsheet (at the same time)
See 2 sheets FROM THE SAME SPREADSHEET side by side for comparisons or for (easily) building your formula.
  • Doing Quick Calculations in a Financial Model
  • Where should you start building your formula?
  • Which cell should you check if there is an error?
Make sure any formula you build can be easily (and safely) copied and reused
  • How to build nested formula in Excel
Learn how to (easily) build nested IF formulas. Technique can be used for any nested formulas
  • Determining which Excel function to use
Use safe, efficient formula rather than building your own
  • The flow of a financial model (but this does not mean it is only for accountants- we use financial terminology to explain how all models should have checks and balances in them)
  • Data Validation
See what numbers make up a formula without jumping all around the spreadsheet

Financial Modelling Essential Knowledge

  • Building buckets for financial models
  • Function Wizard
  • Auditing a formula in financial models
Find errors in your formulas
  • Using $ signs (absolute and relative referencing)
  • IF function in Financial Models

Headers for Financial Models

  • Headers in a Financial Model
Build your spreadsheets in such a way that the formulas you create are the easiest possible
  • Building the Headers
  • Making use of the headers

Aggregation

  • Trick for aggregating in Financial Models
Create consolidation spreadsheets that include sheets depending on where they are placed. Easily move branches from one division to another without changing formula
  • Useful for the aggregation trick
  • Practical examples to be completed

Dates in Financial Models

  • How Excel Handles Dates
  • Key DATE functions for Financial Models
  • Practical examples to be completed
How many working days between 2 dates (taking holidays into account)

OFFSET in Financial Models

  • OFFSET in Financial Models
  • Practical examples to be completed
Learn how to allow for time dependencies and critical paths in your spreadsheet models

Working Capital in a Financial Model

  • Working Capital in Financial Models
  • Practical examples to be completed

Summary Example up to this point

  • Practical examples to be completed

Some more Financial Modelling Tips and Tricks

  • Change many sheets at the same time
  • Protecting specific cells in Financial Models
Protect various aspects of your spreadsheet models from unwanted changes by users
  • Handling Error Messages- IFERROR, ISERROR and AGGREGATE
Remove errors from Formulas
  • Show numbers in millions or with kg in the number
Control the formatting of cells and include the key metric within the cell without upsetting formulas
  • Include a mini graph in a cell for trends
Insert mini charts to show the trend of data
  • Easily convert months into years, quarters or anything else

VLOOKUP in Financial Models

  • VLOOKUP in Financial Models- The TRUE option
  • Practical examples to be completed
Learn how to (easily) summarise monthly information into annual, semi annual, quarterly or any period you want.

Summary example to this point

  • Practical examples to be completed

Comparing Budgets and Forecasts to Actuals

  • Best way to compare actuals with your budgets/ forecasts
  • Conditional Formatting
  • Practical examples to be completed
  • CHOOSE function
  • Practical examples to be completed
  • Waterfall Chart
  • Practical examples to be completed
Add Waterfall charts to your spreadsheet models to better explain what is happening in the budget or forecast
  • Variance Calculation
  • Practical examples to be completed
Let Excel take your historical data and forecast forward, either simply, or including seasonality.

 

Create comprehensive lookups that run across and down

 

Let Excel find the correct sheet to use based on the sheet name. You can add sheets and the formulas will adapt

Running What Ifs

  • Adding a last minute Sensitivity
  • Multiply inconsistent cells with a sensitivity percentage
  • Watching what happens to results when you change inputs
  • Goal seeking a solution in Excel
  • Practical examples to be completed
Use Excel’s capabilities to work backwards. No more making small changes to a cell to see when the spreadsheet gives you the answer you want
  • Single Variable Data Table in Excel
  • Two Variable Data Table in Excel
  • Practical examples to be completed
Build automated What If matrices so that you can see the impact of changes on the outcome without having to re run the spreadsheet
  • Excel Scenario Manager
  • Practical examples to be completed
  • Goal Seek Multiple Cells (Solver)

Depreciation (time and need dependant)

  • Alternate Method to Model Depreciation
  • Practical examples to be completed

Time Value of Money- Debt, NPV, IRR

(time and need dependant)

  • Nominal vs Effective Rates
  • Debt Functions in Excel- Same as your Financial Calculator
See how Excel can mimic your Financial Calculator for debt and time value of money calculations and do more
  • Calculate the Interest vs Capital portion of debt repayment
Build detailed debt calculators with options like interest and capital repayment holidays
  • Allow for Ad hoc Withdrawals and Prepayments
  • Allow for changes in Interest rates
  • Practical examples to be completed
  • NPV and IRR
Learn how Time Value of Money can be used for PE Ratios
  • Excel NPV functions
  • Excel IRR functions
  • Multiple Discount rates in NPV?
  • Terminal Cash Flow Discounting
  • Compound Annual Growth Rates (CAGR) in Excel
  • Practical examples to be completed

Other Financial Modelling, Budgeting and Forecasting Considerations

(time and need dependant)

  • Dealing with Exchange Rates
  • Assessed Tax Losses
  • Model with inflation or without (real vs nominal numbers)?

Feedback & Who Should Attend

  • It has been one of the best course and clearly deserving of the title “Advance Business Case Modeling”. The instructor has real life experience and he is able to articulate the instructions quite clearly. Eli M- Cell C
  • Just a quick note to say your blog is brilliant. Full of useful bits of info. After your courses, I am by far the best modeller in our office, and we regularly get comments from our business partners on the quality of our models. Thanks for all the help. – Mike S
  • He is well experienced and it showed in the way he presented course and how he answered questions.- Deloitte’s Consulting
  • This was one of the best training sessions I’ve ever attended in my three year career at Deloitte. Excellent service provider, very knowledgeable and helpful.-Deloitte’s Consulting

Typical attendees

  • All persons using Excel spreadsheets. Professions that have attended include accountants, actuaries, auditors, business analysts, chartered accountants, civil engineers, construction, consulting, corporate finance, engineers, farming, finance, hr function, landlords, lawyers, quantity surveyors, research analysts, retail, scientists and engineers
  • All persons involved in the construction and use of spreadsheet models, including budgets, projections, evaluations, cash flows, projects, etc.Other areas that would benefit include budgeting, business, data analysis, debt, logistics, non profit, project management, property management, real estate, statistics, and trading
  • All delegates should have a score of above 4.5 on our Excel Skills Assessment
  • Delegates should be familiar with Microsoft Excel (though not necessarily expert in the use thereof, as any relevant Excel function pertinent to an exercise will be discussed prior to tackling the exercise).

Pre Requisite (s)

We recommend that all delegates should, using our free MS Excel Skills Assessment, have a skill score of  >4.5 with a level of Intermediate  or above (get your delegates to do the  free MS Excel Skills Assessment to make sure this is the correct level of training).

This is a very hands-on, non-intimidating, course – delegates work through various exercises under the supervision, and with the help, of the facilitator to get to grips with the nuances of Excel’s functions.