Financial Modelling in Excel

Day One

Introduction & Course Objectives

Financial modelling in Excel training begins by setting clear foundations and outlining the objectives of robust financial modelling. This opening session covers:

• 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
• 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 modelling course
• 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 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 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 Excel financial modelling 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 and financial 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 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 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 are covered in this financial modelling Excel 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

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.

Redcliffe’s financial modelling in Excel training is delivered by a specialist with over 30 years of experience in various finance-based roles. He has also 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), where he guided the group through a period of major change and financial turnaround.

For the past 21 years, our Excel modelling course leader has worked as a freelance financial modeller, analyst and trainer for a range of blue-chip clients. Assignments have 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 modelling. He has built highly valuable 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, financial modelling in Excel training is knowledge-led.

• Redcliffe’s Excel for financial modelling course builds structured and logical 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 modelling course is perfect for professionals with some knowledge of Excel who wish 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 the sessions

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