Home / Non-Accredited / Microsoft Office & Computer Skills / Advanced Excel and Financial Modelling for Accountants (3 days)
Quick Look Course Summary:Advanced Excel and Financial Modelling for Accountants (3 days)
-
Length: 3 day(s)
-
Price (at your venue): 1 Person R 21,125 EX VAT 3 Person R 13,658 EX VAT 10 Person R 9,155 EX VAT
-
Certification Type:Non-Accredited
-
Locations & Venues: Off-site or in-house. We train in all major city centres throughout South Africa.
Get Free & personalised
Training Advice
Advanced Excel and Financial Modelling for Accountants (3 days)
Build, test and present an integrated financial model that colleagues can follow, audit and trust.
Accountants and finance teams spend much of the week in Excel, yet many models are hard to follow, easy to break and slow to update when the assumptions change. This three-day advanced Excel and financial modelling course gives finance professionals the formulas, structure and review habits that reliable models depend on – whether the model supports a budget, a forecast, a funding application or an investment decision.
Day one sharpens the Excel a model relies on: INDEX and MATCH, XLOOKUP, IF with AND and OR, OFFSET, dynamic arrays and SUMIFS, plus Power Query and pivot tables to turn trial balance and general ledger exports into clean inputs. Day two builds a fully integrated, dynamic income statement, balance sheet and cash flow statement, with working capital, fixed asset and loan schedules and a one-page ratio summary. Day three adds XNPV, XIRR and the loan functions, direct and indirect sensitivity analysis, scenario switches, model auditing and board-ready presentation.
Delegates build one case model across the three days and leave with a working template they can adapt to their own organisation, from a monthly management pack to a five-year forecast.
Why this course
For your organisation
- Models that are structured, documented and easy for a colleague to review
- Faster budget and forecast updates when assumptions change
- Fewer formula errors reaching management packs and board reports
- Better-tested decisions on funding, capital spend and pricing
For your delegates
- A working three-statement model template to adapt at work
- The lookup, logical and dynamic formulas finance work really needs
- A repeatable method for sensitivity and scenario analysis
- A model review checklist to catch errors before others do
What delegates will be able to do
- Set up Excel and structure workbooks for financial work, with clear inputs, calculations and outputs
- Use INDEX and MATCH, XLOOKUP, IFS, OFFSET, LET and dynamic arrays in financial calculations
- Summarise trial balance and general ledger exports with SUMIFS, Power Query and pivot tables
- Build an integrated, dynamic income statement, balance sheet and cash flow statement that balances
- Calculate and present profitability, liquidity, solvency and efficiency ratios
- Apply XNPV, XIRR, PMT, IPMT and PPMT to investment, loan and lease decisions
- Test a model with data tables, scenario switches and Goal Seek, and present the results in a tornado chart
- Audit a model with the tracing tools, Evaluate Formula, the Watch Window and built-in error checks
Who should attend
Accountants, financial and management accountants, FP&A and budget analysts, auditors and finance managers who already use Excel confidently and need to build, review and present reliable financial models. A working knowledge of Excel formulas and of financial statements is expected; delegates still building their Excel skills should start with Microsoft Excel Advanced (2 days) or Excel Super Users (2 days).
Course outline
Day 1 – Advanced Excel for finance
Setting up Excel for financial work
- Customising the Quick Access Toolbar, options and calculation settings
- Keyboard shortcuts and efficient navigation in large workbooks
- Excel Tables and structured references
- Number formats, cell styles and colour coding for inputs, formulas and links
Logical and lookup formulas
- IF with AND and OR, nested IF and IFS
- IFERROR and handling missing data
- INDEX and MATCH, including two-way lookups
- XLOOKUP and XMATCH, and choosing the right lookup
Dynamic formulas
- OFFSET with other functions for dynamic ranges and rolling periods
- Dynamic arrays: FILTER, SORT, UNIQUE and SEQUENCE
- LET for readable, faster formulas
- Why volatile functions such as INDIRECT are best avoided in models
Summarising large data sources
- SUMIFS, COUNTIFS and AVERAGEIFS on trial balance and general ledger exports
- Importing and cleaning system exports with Power Query
- Pivot tables for ledger analysis
- Turning summarised data into model inputs
Named ranges and input controls
- Creating and managing named ranges
- Drop-down lists for scenario and period selectors
- Input messages and error alerts
- Protecting input sheets and the model structure
Day 2 – Building the three-statement model
Financial modelling principles
- What the model is for: budget, forecast, valuation or decision
- Model structure: inputs, calculations and outputs
- Good practice: consistent formulas, one formula per row, no hard-coded numbers
- Building a monthly and annual timeline with period flags
The income statement
- Revenue drivers: volume, price and growth
- Cost of sales and gross margin
- Fixed and variable operating expenses
- Depreciation, interest and tax
The balance sheet
- Fixed asset and depreciation schedule
- Working capital: debtors, stock and creditors days
- Loan schedule and interest
- Equity and retained earnings
The cash flow statement
- The indirect method, starting from net profit
- Operating, investing and financing activities
- Linking cash back to the balance sheet
- The balance check, and how to trace an imbalance
Dynamic statements and ratios
- Integrating the three statements into one dynamic model
- Handling the circular reference from interest on average balances safely
- Profitability, liquidity, solvency and efficiency ratios
- A one-page ratio summary
Day 3 – Testing, auditing and presenting the model
Financial functions
- PMT, IPMT and PPMT for loan and lease amortisation schedules
- NPV and IRR compared with XNPV and XIRR for irregular cash flows
- PV, FV, RATE and NPER
- Discounted cash flow basics: free cash flow, discount rate and terminal value
Sensitivity and scenario analysis
- Operating and financial stresses: what to test and why
- Direct method: one- and two-variable data tables on key drivers
- Indirect method: Scenario Manager and scenario switches with CHOOSE or INDEX
- Goal Seek and break-even analysis
- Presenting sensitivity with a tornado chart
Auditing and reviewing the model
- Tracing precedents and dependents
- Showing formulas on the sheet
- Formula errors and the Trace Error and Error Checking tools
- Evaluate Formula and the Watch Window
- Built-in error checks and a model review checklist
Analysing and presenting results
- Combo, waterfall and variance charts for financial data
- Conditional formatting and sparklines
- A management summary sheet
- Printing and saving a board-ready PDF pack
Capstone modelling exercise
- Completing the integrated model for the case company
- Running sensitivities and scenarios
- Reviewing the model and presenting conclusions
- Adapting the model to your own organisation
How it is delivered
Three days, 08:30-16:00. In person at your offices anywhere in South Africa, at a BOTI venue, or live online. One delegate or a group of up to 20. Hands-on throughout in Excel for Microsoft 365 (XLOOKUP, LET and dynamic arrays need Excel 2021 or later); laptops with the software can be supplied on request. Case-study workbook, model template and course notes included. We deliver it at your offices in Johannesburg, Pretoria, Durban, Cape Town and anywhere else in South Africa, at a BOTI venue in Johannesburg, or live online for teams in different places.
What our clients say
“The facilitator effectively encouraged participation and used practical examples, discussions and interactive activities to ensure that the concepts were understood and could be applied in the workplace.”
“Business Optimization Training Institute facilitated Microsoft Excel Training for our company. The course was efficiently scheduled and planned around our timetable. We were impressed by the quality of training.”
“The training was very informative and practical, leaving our employees feeling confident in their ability to leverage Excel more effectively.”
Enrol or ask a question
Frequently asked questions
Can you run this course at our offices?
Yes. Most finance teams are trained at their own premises anywhere in South Africa; we can also host it at a BOTI venue or deliver it live online.
How many people can attend?
From one delegate to groups of 20. The price per delegate drops as the group grows – see the price table, or ask us for a quote.
Is it accredited?
No – it is a non-accredited short course and delegates receive a BOTI certificate of attendance. Members of professional bodies can usually record the hours as CPD; check your body's rules.
Which version of Excel do we need?
Excel for Microsoft 365, or Excel 2021 or later, for XLOOKUP, LET and dynamic arrays. On older versions the facilitator shows the INDEX and MATCH equivalents.
How is this different from Data Analysis and AI for Accountants?
This course builds and tests financial models. Data Analysis and AI for Accountants (2 days) concentrates on cleaning and analysing ledger data, exception testing and using AI tools responsibly in finance work. Many finance teams take both.
Can the case study use our own figures?
Yes. The case model can be adapted to your sector, and delegates may bring anonymised data to use in the capstone exercise.
Related courses
Get your price – the more delegates, the less each pays
Change the options to see your price per person, then email yourself the quote and course outline.
Contact us now – we reply within 15 minutes during office hours
Monday to Friday, 08:00–17:00. After hours, we reply on the next working day – or call 011 882 8853.