Sensitivity Analysis in Excel (Financial Modelling) and Advanced Excel Formulas

Excel formulas are the engine of every credible financial model, management report and board pack. This BOTI corporate training course teaches your finance and management teams to master the advanced Excel formulas and functions that drive sensitivity analysis, scenario planning and world-class financial modelling. Below is the full course content, plus an expanded guide to the Excel formulas every manager should know.

This blended learning programme combines the training expertise of international experts with hands-on local facilitation. It is built as a step-by-step guide using real-life templates and examples, and learners can download a working sensitivity analysis template to keep on completion. The course is delivered for teams across Johannesburg, Cape Town, Durban and Pretoria, on-site/in-house at your premises, or live online for remote teams nationwide.

Who this Excel formulas course is for

The material is designed for staff who are already proficient in the basics and need to reach expert level. It suits finance and management professionals in:

  • Investment banking and equity research
  • Financial planning and analysis (FP&A)
  • Corporate development and private equity
  • Management accounting, reporting and budgeting teams

It is ideal for HR and L&D buyers upskilling a finance department, and for managers who want their teams producing reliable, dynamic models rather than static spreadsheets.

In practice, the buyers who get the most value are organisations where reporting still depends on a handful of “spreadsheet heroes.” When formula skills sit with one or two people, models become fragile, hard to audit and impossible to hand over. Training the whole team in a consistent set of Excel formulas and functions spreads that capability, reduces key-person risk and makes month-end faster and more defensible. That is why this programme is frequently booked as in-house training scoped to a department’s actual reports.

Course structure

This is a two-part programme that takes learners from foundational Excel into advanced formula work and full sensitivity analysis.

Part 1: Sensitivity Analysis in Excel (Financial Modelling)

Based on many years of practical experience in model sensitivity analysis, Part 1 shows your team how to build a comprehensive sensitivity analysis system into an Excel financial model — enabling rapid changes and live information processing during real transactions.

Key topics include:

  • Introduction to sensitivity analysis and “why perform a sensitivity analysis?”
  • Financial model integration using direct and indirect methods
  • Measuring financial sensitivity in line with both operating and financial stresses
  • Data table setup and linking
  • Results analysis using gravity sort tables and tornado charts
  • Presentation techniques for sensitivity findings
  • Downloadable sensitivity analysis template for ongoing use

Part 2: Advanced Excel Formulas

Part 2 builds on the basic module and takes learners to the most advanced functions, formulas and types of financial analysis.

Key topics include:

  • Template setup with dynamic dates, IF statements and scenarios
  • Dynamic totals using OFFSET functions
  • Scenario creation with VLOOKUP and CHOOSE
  • Advanced functions: INDEX/MATCH, OFFSET, INDIRECT, CHOOSE
  • IF statements with AND/OR logic
  • Financial calculations: XNPV, XIRR, PMT and IPMT
  • Dynamic financial statements and debt schedules
  • CELL, COUNTA and MID function combinations
  • Data tables and pivot table creation
  • Charts: column, line, stacked column, combination, waterfall and gauge charts
  • Macro recording and form controls

Learning outcomes

On completion, participants will master functions and formulas, build a complete sensitivity analysis system into an Excel model, and produce sophisticated outputs such as tables, graphs and charts — the capabilities needed to become a world-class financial analyst across investment banking, corporate development, private equity, equity research and FP&A.

Excel formulas and functions every manager should know

You do not have to be a financial analyst to benefit from strong Excel formulas. Managers who can read and build the right formulas make faster, better-evidenced decisions and rely less on others to interpret the numbers. The table below summarises the core Excel formulas and functions we cover, what each does, and where a manager actually uses it.

Excel formula / function What it does Manager use case
SUMIFS / COUNTIFS / AVERAGEIFS Totals, counts and averages against multiple criteria Revenue by region and product; headcount by department
XLOOKUP / INDEX-MATCH Looks up values across tables in any direction Pulling price, rate or status from a master list
IF with AND/OR Tests logical conditions and returns outcomes Flagging variances, approvals or KPI breaches
IFERROR Replaces error values with clean output Tidy dashboards and reports for exec audiences
XNPV / XIRR Net present value and return on dated cash flows Evaluating capex, projects and investment cases
PMT / IPMT Loan and finance repayment calculations Asset finance, lease and debt scenario checks
OFFSET / CHOOSE / INDIRECT Builds dynamic ranges and scenario switches Toggling best/base/worst case in one model
Data Tables (What-If) Runs one- and two-variable sensitivity Stress-testing margin, price or volume assumptions
PivotTables Summarises large data sets without formulas Quick management reporting from raw exports

The five formula skills that change how a team works

For managers and their teams, prioritise these:

  1. Lookup mastery (XLOOKUP / INDEX-MATCH) so data is never re-keyed by hand.
  2. Conditional logic (IF, IFS, nested AND/OR) to automate decisions and flags.
  3. Conditional aggregation (SUMIFS family) to slice performance any way leadership asks.
  4. What-If and Data Tables for true sensitivity analysis, not guesswork.
  5. PivotTables and clean charting to turn raw exports into board-ready visuals.

When a whole team shares these skills, reports become consistent, audit-friendly and far faster to produce — which is exactly what funded, in-house Excel training is designed to deliver.

From formulas to financial models

Strong formulas are the foundation; sensitivity analysis is where they pay off. Once a team can build robust lookups, conditional logic and dynamic ranges, they can wire a model so that changing one assumption — price, volume, interest rate or cost of capital — instantly flows through to every output. Part 1 of this course shows exactly how to structure those links using What-If data tables, then present the results clearly with gravity sort tables and tornado charts. The result is a model leadership can actually interrogate in a meeting, rather than a static spreadsheet that has to be rebuilt every time the question changes.

This matters most during live transactions and budgeting cycles, when assumptions shift quickly and decisions cannot wait for an overnight rebuild. A team trained to integrate sensitivity analysis directly into the model can answer “what happens if?” in seconds, with outputs that are consistent, traceable and ready for a board pack.

Why train your Excel formulas team with BOTI

  • Practical, template-driven learning built on real financial models, not theory.
  • Course content scoped to your team’s actual reports and data.
  • Flexible delivery: in-house/on-site, public schedule, or live online for remote teams.
  • National reach across Johannesburg, Cape Town, Durban and Pretoria.
  • Part of BOTI’s wider MS Office, Excel, Power BI and data training cluster, so you can build a full upskilling pathway.

In-house Excel training can also support your wider skills development planning. Employers that pay the Skills Development Levy (SDL, 1% of payroll) can count accredited training towards their B-BBEE skills development spend, where the target is 6% of the leviable amount on the relevant scorecard. Treat this as general guidance and confirm specifics with your skills development facilitator.

Build a full Excel and data skills pathway

This advanced course works best alongside foundational and applied training. Explore related BOTI courses:

Ready to schedule your team? Request a quote or a 15-minute callback and ask for our free Excel formulas quick-reference guide for managers. Call BOTI on 011-882-8853 or enquire via boti.co.za.

Frequently asked questions

What Excel formulas should every manager know? At a minimum: lookups (XLOOKUP or INDEX-MATCH), conditional logic (IF with AND/OR), conditional aggregation (SUMIFS, COUNTIFS, AVERAGEIFS), error handling (IFERROR), and What-If Data Tables for sensitivity analysis. These cover most day-to-day reporting and decision-making needs. All are taught in this BOTI course.

Do staff need prior Excel experience for this course? Yes. This is an advanced, template-driven programme aimed at staff already proficient in the basics. For teams starting from scratch, BOTI offers beginner and intermediate Excel courses that build up to this level — see the Essential Excel courses pathway.

Is BOTI’s Excel training accredited? Yes. BOTI’s Excel and computer-skills training is accredited through the MICT SETA (and Services SETA 12582) as a unit-standard qualification (IT End User Computing, SAQA ID 61591). These legacy unit-standard qualifications are migrating to the new QCTO system, with last enrolment on 30 June 2026 — accredited enrolment is available now, so please confirm current accreditation when you book.

Can the course be delivered in-house for our whole team? Yes. BOTI delivers in-house/on-site at your premises, on public schedules, or live online for remote teams, across Johannesburg, Cape Town, Durban and Pretoria. In-house delivery also lets us tailor examples to your own reports and data.