Corporate Financial Modeling Training Course

This practical 5 – day Corporate Financial Modeling Training Course combines the Advanced Excel Course with the Financial Modelling, Budgeting and Forecasting course to create a comprehensive Excel for Business course.

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 11- 15 February 2019 and 11-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 5 days will be that the delegate will be able to:

  • Build easy to use Budgets and Forecasts
  • Aggregate the budgets and forecasts in the easiest possible way
  • Run sensitivities on their models
  • Understand and make use of Pivot Tables
  • Report their findings graphically via charts and other visualisation techniques
  • Identify/ remove duplicates in a spreadsheet
  • Perform fuzzy logic ( find cells that are similar to xyz)
  • Change the colour of cells depending on what is in the cell
  • Add risk robots to their reports
  • Understand what formulas are doing and easily see where they come from
  • Handle text issues i.e. joining cells, splitting them, finding key words in cells
  • Joining/ comparing databases to identify duplicates and merge spreadsheets
  • Understand and use dates correctly
  • Avoid errors in their spreadsheet work

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.

Online MS Excel Corporate Financial Modelling Training Course

Course Outline

MS Excel Advanced (Day 1-3)

Ultimate Excel Tool

  • Flash Fill- Your new best friend?
Split key text into their own columns

MS Excel Tips and Tricks

  • Viewing many parts of the spreadsheet (at the same time)
  • Don’t hide rows- group them rather
  • See sheet names in portrait style
  • Selecting ranges with mouse and arrow keys- short cuts
  • Enter date and time stamps- unchanging
  • Doing Quick Calculations in a spreadsheet
  • The Quick Access Toolbar

Filtering and Sorting

  • Data sorting-Revision, colour and icon sorting
  • Sorting the columns instead of the rows
  • Create a custom sorting list
  • Data Filtering
  • Remove rows with the Filter
  • Remove duplicate rows
  • Use Advanced Filter for repeating filters
Move columns around or sort based on the colour of a cell
Easily delete unwanted rows

The Specials- GOTO and Paste

  • GOTO Special- find comments, values, formulas and more
  • Find errors, conditional formatting and data validation
  • Copy Visible Cells Only
  • Fill in all the blanks
  • Paste Special- Values, Formulas with/without formatting
  • Paste Special- convert negatives to positives
  • Switch rows to columns and columns to rows
  • Paste without overwriting blank cells
  • Re Use the Format Painter
Fill in Gaps (easily)

Tools for text

  • Find Replace- standard uses
  • Find Replace- Change formatting on many cells
  • Remove between brackets or characters
  • Single Column to Multiple Columns
  • Import CSV’s into the correct columns
  • Find partial names or between characters (fuzzy logic)
  • Change the $ signs on multiple cells with FIND/ REPLACE
  • Import CSV but correct for ‘numbers’ that start with zero e.g. credit card numbers
Learn complex data cleanups. Remove brackets and whatever is in between (even if it is not consistent)

 

Replace unwanted parts of cells

 

Split columns but decide how it must handle leading zeros. Bank Account numbers/ Employee Numbers/ ID numbers- you must keep the zeros. Payments- you can lose the zeros
Put imported data into its own Excel columns

Pivot Tables

  • Required data format for Pivot tables
  • Create a Pivot Table- Step by Step
  • Inserting Values into a Pivot Table
  • Enhancing the values e.g. percentage of another column
  • Show the details of a number in the Pivot Table
  • Laying out the Pivot Report in completely separate columns
  • Working with the rows in a Pivot Table- dropdown
  • Working with the rows in a Pivot Table- field settings
  • Normalize Pivot Reports to use elsewhere as a copy paste
  • Working with the Columns in a Pivot Table
  • Working with the Filter in a Pivot Table
  • Automatically duplicate your Pivot Tables with different selections
  • Slicers in a Pivot Table
  • Control all Pivot Tables and Charts at once
  • Timelines in a Pivot Table
  • Grouping Dates into days, months, quarters and years in a Pivot Table
  • Grouping Dates into weeks in a Pivot Table
  • Grouping Text in a Pivot Table
  • Grouping Numbers in a Pivot Table
  • Pivot Charts in Excel
  • Problems with grouping in Pivot Tables
  • Protect the source data of a pivot table
  • Add a calculation INSIDE a Pivot Table
  • Conditional Formatting within a Pivot
  • Get information out of a Pivot into a cell
  • Switching off the GETPIVOTDATA
  • Add up ITEMs within a Pivot Table Field
Turn data into reports (in seconds)

 

Create a dashboard type feel for your spreadsheets
Pull out only the parts of the Pivot Table that interest you

 

Building formula outside the Pivot Table looking into the Pivot Table? Rather build the formula directly into the Pivot Table

Conditional Formatting

  • Change cell colour based on value
  • Change color based on benchmark in another cell
  • Highlight all above average cells
  • Add comparative bars into cells
  • Create heat maps in Excel
  • Add risk robots, milestone, up and down icons to Excel cells
  • Control the heat maps, icons and data bars in conditional formatting
  • Conditional Formatting- Linking to different cells
  • Control what is entered in an Excel cell- Data Validation
  • Control the inputs in a cell based on another cell
Improve the visuals on your spreadsheets
Want to easily see where the numbers change month on month? Let them automatically change colour if the previous cell is a different number to the current cell.

 

Make spreadsheets easier to read by changing cell colours where there is a significant change in data.

 

Create intelligent dropdown lists that check whether the user is sure about their entry

Function and Formula Basics

  • Tip- Opening Frequent files
  • Enter formula into many cells (at the same time)
  • Function Wizard
  • Auditing Toolbar
  • Referring to fixed cells
  • Using the F4 shortcut for $ signs
  • Dollar Sign ($A$1) Examples
  • The basic “IF” function
  • How to (safely) build a nested IF formula
  • IF this AND this AND this is True
  • See the numbers behind a formula
  • Change formula across many sheets at the same time
  • Update the calculations on only one sheet (for large spreadsheets)
Find errors in your formulas
Nested IF’s a problem. Learn how to create them in the easiest, safest way.

 

See the numbers behind a formula without scrambling through the spreadsheet. No need to pull out your calculator to ‘check’ Excel.

Text Functions

  • Fitting text inside a cell
  • Quick entry of rows of data
  • Quick Drop Down List
  • Printing Tips
  • Get Excel to read out the text and numbers
  • Join text from many cells
  • Extract parts of the text from cells
  • Force Excel to see the value (instead of text)
  • Change text to lower, UPPER or Proper case
  • Remove unnecessary spaces
  • Create a find/ replace formula
  • Find where a certain character is in a cell
  • Force excel to treat number as text
Neaten up your spreadsheets
Create ‘Word’ like reports in Excel

 

Join cells together
Lost the leading zeros? Put them back in

Key Lookup Functions

  • VLOOKUP explained in simple terms
  • Build your first VLOOKUP
  • The many uses of VLOOKUP
  • Vlookup approximate match (True instead of False)
  • Vlookup partial text from single cell- fuzzy lookup
  • Automatic Sorting in Excel
  • Handling Error Messages- Remove the impact of #NA, #DIV/0! and more
Merge databases together
Categorise data WITHOUT using complex Nested If’s.

 

(Easily) reconcile lists. What items are missing, what is matching and what appears more than it should?

 

Lookup partial matches like a surname in a list of full names

 

Remove errors from Formulas

SUM, COUNT, AVERAGE IFS

  • Sum, count or average if certain cells match
  • Sum, count or average cells e.g. bigger than
  • Sum, count or average with many conditions
  • Sum, count or average with partial matches
Add up all similar items even if they are not in order.

Date functions and issues

  • How Excel Handles Dates
  • Safest way to capture a date in Excel
  • Pull the day, month or year out of the date
  • Create a VALID Excel date with the day, month and year
  • Calculate the proper end of month
  • Determine what day of the week a date is
  • When is the next workday
  • How many working days between dates
  • What week number is a date
  • Make Excel recognise a date

 

Fix messy system dates

 

Sort out problemsome Excel dates

Charting In Excel

  • Excel Charting introduction
  • Create a chart
  • Align chart to the grid lines
  • Chart doesn’t look right? First thing to try
  • Customise the chart title, area, legend and more
  • Vertical Axis- force the scale, reverse the order, labels and more
  • Horizontal Axis- dates vs text, reverse order, show all labels
  • Series- overlapping, big and small series, gaps
  • Add more series to the chart- 3 ways
  • Show big and small numbers on the same chart (and be able to see them)
  • Show months, quarters AND years on the chart axis
  • Bar Charts
  • Pie Charts
  • Area Charts
  • Radar Charts (to compare projects?)
  • Show mini trend charts for large reports
  • Copy the format of one chart to another chart
  • Remove Zeros from chart labels
  • Combine a column with a line chart (or other combinations)
  • Stop charts stretching when the column width changes
  • Customise the series marker to your own image
  • Add linked commentary directly to the chart
  • Add commentary to the axis
  • Add commentary to labels and call outs
  • Add commentary to labels for older versions of Excel
  • Hiding a series with NA()
  • Create a waterfall chart (pre Excel 2016)
  • Create a waterfall chart (Excel 2016 and higher)
  • Understand and use XY charts e.g. Risk Register
  • Change the default colour scheme in charts
  • Create a chart template for re-use
  • Add a trendline to a chart
  • Benefit of using a table for chart data
  • Link Excel chart to PowerPoint or Word
Insert mini charts to show the trend of data
Show big and small numbers on the same chart

 

Fix date issues on charts

 

Add more information directly to your chart

 

Differentiate between the main key chart information and the ‘Other’ information
Learn how to ‘Format Paint’ a chart. Find the chart you like and simply absorb its format

 

Build a chart series selector. The user can switch on and off any series and the chart will adapt. Great for dashboards

 

Get fancy with your charts. Trick Excel into creating some of those fancy charts you have seen (it is much easier than you think)

 

Add shading to a chart (WITHOUT manually colouring it in) to show where the good and bad areas are

 

Need to explain what happened. Learn how to create a waterfall chart both in the new version of Excel (which has waterfall charts) and the older versions (where you have to trick Excel)

 

Make a line chart change colour at a significant point e.g. actuals in blue and budget in red.

Financial Modelling, Budgeting and Forecasting (Day 4-5)

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

  • Practical examples of how the various tools & functions can be used in different contexts make it quite easy to grasp the concepts and see the benefits of using these functions. The course is very practical and allows you to engage with the various exercises, make mistakes and learn. I was very impressed with Adrian and De Wet’s ability to demonstrate their overall knowledge and experience in a wide range of industries and applications. They were able to make the examples very relevant to a diverse audience from different fields.  – Kgalalelo N. – SEFA
  • We have subsequently seen the difference in how our people use Excel.  They now tackle tasks with confidence, and with an understanding that enables them to find their own solutions.  This is one of the more enlightening and stimulating training interventions we have held.- Alexander Proudfoot Consultants

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 excercise 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.