Financial Modelling in Excel

Day One

Introduction & Course Objectives

Financial modelling in Excel training begins by building a strong foundation and introducing the key principles behind effective model construction. This opening session covers:

• Overview of course objectives

• Review of models and their objectives

Overall Model Structure & Design

• The best financial modelling practice

• Overall structure of the model

• Logic flow within the model

• Separation of inputs, calculations and outputs

• Defining desired outputs

• Setting up required inputs

• Use of switches to allow option selection

• Use of flags to control timing factors

• Set up for flexibility

• Consistency in the model

• Accommodating multiple options

• Building assumptions from term sheets or other external inputs

• Using the assumptions sheets as a sign-off document

• Restricting ranges of inputs and validation criteria

• Version control is covered within this Excel for financial modelling course

• Use of the corkscrew technique

• Tracking changes

• Documentation

• Exercise – Create a simple model with an assumptions/input sheet with built-in flexibility

Modelling Techniques for Revenue and Cost Forecasts

• Translating assumptions & inputs into a model forecast: Overall growth

• Split volume and price

• Split volume into market growth and market share

• Top-down market sizing, market share and price

Build-up of construction or other capital costs

Correct matching of units

Modelling pricing & revenue assumptions

Use of lookup and INDEX/MATCH functions to change expenditure timings

Building sensitivities into the model

Exercises:

• Build up revenue forecasts with different techniques and granularity levels

• Build a capex forecast with flexible timing

Day Two

Inflation / Escalation Factors

Day Two of this Excel and financial modelling course builds on core concepts. This session explores how inflation and escalation factors are modelled and controlled, including:

• Creating inflation indices

• Controlling the start time of the inflationary pattern

• Applying multiple rates to different cost & revenue items

• Varying inflation rates over the life of the model

• Exercise – model multiple variable inflation rates, with different starting points and changing rates over the life of the model

Cash Flow Modelling

• The differences between the income statement and the cash flow

• Organising cash flow statements

• What is working capital?

• Review modelling changes in receivables, inventory, and payables

• Income statement items that are not cash: adjustments needed

• Cash items not in the income statement: adjustments needed

• Financing cash flows: changes in debt & equity

• Financial modelling in Excel training covers revolving facilities and their role in cash flow forecasting

• Exercise: create a cash flow model to forecast cash inflows and outflows for a trading business, drawing on revolving facilities as needed.

Comparing a Model to Previous Versions of the Model

• Separate runs and variation of inputs

• Comparison of actuals to forecast

• Comparing the results of different versions of the same model

• Reviewing future implications of variances

• Example – from different versions of a modelled forecast, calculate variances and review future assumptions

Day Three

Sensitivity Analysis in a Model

Day Three focuses on sensitivity analysis tools and techniques for testing key assumptions, evaluating alternative scenarios, comparing potential outcomes, and gaining a deeper understanding of model risk. Participants will learn how to assess the impact of changing variables on model performance and support more informed decision-making throughout this Excel course for financial modelling, including:

• Model stress-testing

• Varying inputs to assess the effect on results

• Use of built-in sensitivity inputs

• Use of goal seek & solver

• Version control to allow comparison of outputs

• Use of Excel tools to support sensitivity analysis: watch window function

• data tables

• scenario manager

Exercise – from a given model of cash flows, P&L and balance sheet, calculate the effect of varying inputs to a given degree, and stress-test the model to break-even.

Reporting Outputs

• Design techniques to enable optimisation

• Consolidation techniques covered in this Excel modelling course: Add-through

• Sum-through

• Sum & Group

• Consolidate function in Excel

• Use of charts & graphics

• Using pivot tables: Pivot table wizard

• Setting up tables

• Changing parameters

• Analysing pivot table data

• Drill-down function

• Exercise – from a given set of outputs, create output tables, pivot tables and consolidations to enable flexible reportin g

Advanced Excel Functions

• Advanced financial mathematics – PMT, PPMT, IPMT, NPER

• Summation & counting functions in Excel: COUNT, COUNTA, COUNTIF, AVERAGE, AVERAGEIF, SUMIF, SUMIFS

• Statistical functions in Excel

• Depreciation functions in Excel

• Date and time functions in Excel

• Lookup and Reference formulae: Lookups, OFFSET and MATCH formulae

• Logical functions – using TRUE, FALSE, IF, AND & OR in combination are covered in this Excel for financial modelling course

• Text functions in Excel: LEFT, RIGHT, MID, LEN and wildcard

• Exercises: Model a loan using some of the advanced functions learned

• Summarise messy data without cleaning it, using text functions

Wrap-Up

• Overall review

• Key points to reiterate

• Brief introduction to further exercises and reading

• Final questions and issues to discuss

Please note - Training can be tailored to your needs, combining a flexible mix of modules and topics to fit your team’s priorities and preferred duration.

Training Objectives

• Sessions build structured and logical models that are flexible, transparent, and easy to update.

• Participants will be able to apply advanced Excel functions and techniques for forecasting, analysis, and reporting.

• Training aids the translation of assumptions and inputs into accurate revenue, cost, and cash flow projections.

• Attendees will perform sensitivity and scenario analysis to test models under varying conditions.

• This Excel and financial modelling course presents outputs effectively using charts, pivot tables, and consolidated reporting tools.

Training Course Summary

This Excel course for financial modelling teaches intermediate Excel skills, not just technical. Covering many productivity-enhancing functions and features, sessions explore how to structure and logically organise spreadsheets, making them user-friendly, time-saving and easily transferable.