Professional Course

Financial Modelling School: Module 1 - Best Practice Financial Modelling in Excel

Euromoney Learning, In London (+2 locations)
Length
3 days
Price
3,895 GBP + tax
Next course start
22 April, 2024 (+9 start dates)
Delivery
Classroom
Length
3 days
Price
3,895 GBP + tax
Next course start
22 April, 2024 (+9 start dates)
Delivery
Classroom
This provider usually responds within 48 hours 👍

Course description

Financial Modelling School: Module 1 - Best Practice Financial Modelling in Excel

The principal aim of the course is enable participants to use Microsoft Excel to prepare logical and easy-to-use financial models to support transactions, forecasts and planning for ongoing business streams. These skills can also be used to support credit approvals and reviews by lenders and to support the organisation to run or sponsor projects. The course will review best practice in model structures and logic, and using tools to highlight areas of risk, particularly in sensitivity analysis.

Upcoming start dates

Choose between 9 start dates

22 April, 2024

  • Classroom
  • Dubai
  • English

22 April, 2024

  • Classroom
  • Dubai
  • English

22 April, 2024

  • Classroom
  • Dubai
  • English

1 July, 2024

  • Classroom
  • London
  • English

1 July, 2024

  • Classroom
  • London
  • English

1 July, 2024

  • Classroom
  • London
  • English

25 November, 2024

  • Classroom
  • Paris
  • English

25 November, 2024

  • Classroom
  • Paris
  • English

25 November, 2024

  • Classroom
  • Paris
  • English

Training content

Day 1

Introduction & Course Objectives

  • 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 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 capex forecast with flexible timing

Day 2

Inflation / Escalation Factors

  • Creating inflation indices
  • Controlling start time of inflationary pattern
  • Applying multiple rates to different cost & revenue items
  • Varying inflation rates over 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 income statement and cash flow
  • Organising cash flow statements
  • What is working capital?
  • Modelling changes in receivables, inventory, 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
  • Exercise: create a cash flow model to forecast cash inflows and outflows for a trading business, and 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 results of different versions of same model
  • Reviewing future implications of variances
  • Example – from different versions of a modelled forecast, calculate variances and review future assumptions

Day 3

Sensitivity Analysis in a Model

  • Stress-testing the model
  • Varying inputs to assess 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
    • Analyzing 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
  • 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:
    • 1) model a loan using some of the advanced functions learned
    • 2) summarise messy data without cleaning it, using text functions

Wrap-Up

  • Overall review
  • Key points to re-iterate
  • Brief introduction to further exercises & reading
  • Final questions and issues to discuss

Costs

Course fee: £3645 + VAT

Certification / Credits

The course is designed to support junior and middle financial analysts, modellers and their managers in creating financial models on a consistent and focussed basis. Some previous use of Excel is assumed, but Participants will not need an advanced knowledge.

Why choose Euromoney Learning?

4.6/5 rating on course check for service

60,000 professionals trained across public courses

80+ countries where training is delivered

Contact this provider

Contact course provider

Fill out your details to find out more about Financial Modelling School: Module 1 - Best Practice Financial Modelling in Excel.

  Contact the provider

  Get more information

  Register your interest

Country *

reCAPTCHA logo This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Euromoney Learning

Euromoney Learning

At Euromoney Learning, we understand that learning doesn’t start and end when you leave the classroom. We know that the financial markets never stand still, and that technology has both simplified and added complexity at a break-neck pace. That’s why...

Read more and show all training delivered by this supplier

Ads