Corporate Financial Modeling Training

corporate financial modelingThis practical 5 – day course leads delegates through advanced Excel functions and Financial modeling techniques, explaining the function and its uses.

Is this the correct course for your needs? Use this quick ‘Which Excel Course should I do‘ questionnaire to establish which course is correct for you.

In order to do this course we also recommend a skill level of >4.5 (We offer a free Skills Assessment to make sure you get 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 advanced functions.


Related: Have you looked at the online training we provide? Instant answers to your questions!


Course Outline

Day 1

MORNING: Pivot Tables

  • How to (quickly) convert non-compliant data into Pivot friendly data
  • Pivot Tables – Step by Step
  • Manipulating the Data Portion of a Pivot Table
  • Manipulating the Rows of a Pivot Table
  • Manipulating the columns in a pivot table
  • Combining rows and columns to create meaningful Pivots
  • Report Filter
  • Grouping Data in the rows and columns
    • Manual
    • Dates
    • Numbers
  • Simple Pivot Charting
  • Creating Calculated Fields within a pivot table
  • Creating Calculated Item within a pivot table
  • GETPIVOTDATA function
  • Multiple Consolidation ranges in Pivot Tables
  • Conditional Formatting on Pivot Tables (Excel 2007 and higher)
  • Linking to an external database via Pivot Tables

AFTERNOON: Advanced Excel

  • Advanced Excel Techniques for use in modelling
  • How errors happen in models and how to avoid them
  • Financial Model and spreadsheet design principals
  • Essential Excel Knowledge
  • Importing data into a model
  • Data Cleanup using the Data Autofilter and the Advanced Filter
  • GOTO Special tool for working with cells
  • Using the text to column tool
  • Common pitfalls / problems at this stage

DAY 2

  • Working with imported text data
    • CLEAN, CONCATENATE, DOLLAR, EXACT, FIND/SEARCH, LEFT/RIGHT/MID, LEN, LOWER/UPPER/ PROPER, REPLACE, T, TEXT, TRIM, VALUE
  • Handling Dates in your models
    • EOMONTH, DATE, DATEVALUE, DAY, EDATE, MONTH, NETWORKING DAYS, WEEKDAY, WORKDAY, YEAR
  • Manipulating the data
    • (SUMIF, COUNTIF, SUMIFS, COUNTIFS, VLOOKUP)
  • Ranking Results
  • Error checks and spreadsheet integrity

DAY 3

MORNING: Completion of Advanced Excel

  • Analysing Results
    • Data Filter
    • Speech to Text
    • Database Functions
  • Reporting results
    • Conditional Formatting
    • Cell formatting
    • Data Protection

AFTERNOON – Data visualisation

  • Getting the Data right for Graphing
  • Understanding Excel graphs and incorporating them into your model
  • Adding another series to a graph
  • Creating a secondary axis in a chart
  • Changing default colouring to match corporate colours
  • Create a chart template for consistent use
  • Different Chart Types
    • Column Charts
    • Bar Charts
    • Pie Charts
    • Area Charts
    • Radar
    • Waterfall graphs
    • Combination Charts
  • Trendlines in models
  • Goalseeking straight off the graph
  • Linking your Word documents directly to the Excel graph
  • Conditional Formatting Graphs
  • Using a custom picture for your graphs
  • Creating flexible graphs

DAY 4: Financial Modelling

  • Good Financial Model Practice
  • Types of financial models
  • Consistency in models
  • Separation in models
  • Integrity of the models
  • Structural issues
  • Creating trigger and helper cells
  • Summing through sheets for aggregation and consolidation
  • Custom formatting for ease of reporting and input controls
  • Grouping periods e.g. months into quarters or years
  • Modelling macro-economic factors

Day 5

  • Modelling Inflation
  • Modelling Exchange Rates
  • Modelling Interest rates
  • Handling fixed, semi fixed/ variable and variable items
    • Revenue Modeling
    • Costs
    • Capital expenses
  • Working Capital in Financial Models
  • Depreciation calculation methods
  • Tax and Assessed losses in financial models
  • Modelling debt in a financial model
  • Valuation within a financial model covering the accepted valuation techniques
  • Running sensitivities in a financial model- Scenarios, Tables, Goalseek and Solver
  • Reporting- Methods to get your message across based on the results of the financial model

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)

Excel knowledge required:

  1. Basic Excel skills
  • Opening & closing files
  • Moving around the spreadsheet
  • Inserting & deleting rows and columns
  • Inserting text & formulas
  • Deleting cells
  • Copying formulas
  • Printing
  1. Basic formulas (+-*/) e.g. =C5+C7+sum(C9:C12), and =D5*D6/12 3. Formatting
  2. Formatting