Financial Modelling in Excel

Day One

Introduction & Course Objectives

Financial modelling in Excel training begins by setting clear foundations and covering the following areas:

• Overview of course objectives
• Review of models and their objectives

Overall Model Structure & Design

• 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
• This Excel for financial modelling course explores building assumptions off term sheets or other external inputs
• Using the assumptions sheets as a sign-off document
• Restricting ranges of inputs and validation criteria
• Version control
• Use of the corkscrew technique
• Tracking changes
• Documentation
• Exercise – creating 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 in 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 course for financial modelling builds on core concepts by exploring 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

• Differences between the income statement and the cash flow
• Organising cash flow statements
• What is working capital?
• 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
• Revolving facilities and their role in cash flow forecasting are covered during this financial modelling Excel course
• 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 of this Excel modelling course explores sensitivity analysis tools and techniques to test assumptions, compare outcomes, and understand model risk, including:

• Stress-testing the model
• 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 effect of varying inputs to a given degree, and stress-test model to break-even.

Reporting Outputs

• Design techniques to enable optimisation
• Consolidation techniques:
  • 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 reporting

Advanced Excel Functions

• Advanced financial mathematics – PMT, PPMT, IPMT, NPER
• Summation & counting functions in Excel: COUNT, COUNTA, COUNTIF, AVERAGE, AVERAGEIF, SUMIF, SUMIFS
• This Excel course for financial modelling covers 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
• 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

Financial modelling in Excel training concludes with:

• Overall review
• Key points to reiterate
• Brief introduction to further exercises and reading
• Final questions and issues to discuss

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

With over 30 years of experience in various finance-based roles, our Excel for Financial Modelling course specialist has delivered sessions for several international training companies since 2005.

Having trained as a Chartered Accountant at KPMG in South Africa and New Zealand, he moved into the industry with Ford Motor Company. Holding various positions in financial analysis, budgeting and forecasting, he was appointed sales planning manager responsible for forecasting models, production planning and supply logistics. He joined a multinational private consultancy group in Australia as their general manager (finance); in this role, he guided the group through a period of major change and financial turnaround.

For the past 21 years, our financial modelling Excel course leader has worked as a freelance financial modeller, analyst and trainer for a range of blue-chip clients. Assignments included financial modelling for two major LNG projects in the Middle East, numerous renewable energy projects in both Europe and developing countries, structured financing for a large-scale property development, restructuring and outsourcing projects in the retail gas sector, and PPP transactions in the utilities, health and support services sectors.

With an extensive accounting background, this expert brings accounting knowledge and analytical skills to transactions and financial modelling. He has built up a great deal of experience in financial modelling across numerous sectors, including gas production, electricity generation, energy retailing, waste processing and property development. This specialist has built financial models in central government departments, retail enterprises and high-volume online environments. Having built, developed and used models to support commercial negotiations, analyse risk, test scenarios and forecast results, this Excel modelling course is knowledge-led.

• Build structured and logical financial models that are flexible, transparent, and easy to update.
• Apply advanced Excel functions and techniques for forecasting, analysis, and reporting.
• Translate assumptions and inputs into accurate revenue, cost, and cash flow projections.
• Perform sensitivity and scenario analysis to test models under varying conditions.
• Present outputs effectively using charts, pivot tables, and consolidated reporting tools.

At Redcliffe Training, our Excel Financial Modelling course encompasses practical learning methods. You will become proficient in the practice of newly-learned techniques for a deeper and more effective skills-building journey.

Each topic is introduced through a concise, classroom-style module, with primary learning taking place through the hands-on exercises found within each section. Participants practice what they have learned under the supervision of the facilitator. Suggested model solutions to each exercise are provided and discussed. Participants take away course notes, exercises and model answers.

The Financial Modelling in Excel course at Redcliffe Training is for professionals with some knowledge of Excel who want to take their skills to the next level:

• Analysts
• Accountants
• Model-builders
• Budget controllers
• Professionals who prepare forecasts and business plans will all benefit from the new knowledge and best practices gained from sessions

This Financial Modelling in Excel course teaches intermediate skills in using Excel, not just technically. Covering many productivity-enhancing functions and features, sessions also delve into the way spreadsheets can be structured and organised logically, making them user-friendly, time-saving and easily transferable.