Financial Modelling in Excel

3 days 21-23 Sep 2016, Singapore Singapore $4,650.00 Download brochure Add to basket
3 days 13-15 Dec 2016, Dubai UAE £3,545.00 Download brochure Add to basket

* Claim back your VAT
Find out more

Request a different date or location for this course (we regularly add courses following requests).


* Claim back your VAT
Find out more


Course Description


  • Model design and structure
  • International spreadsheet best practice
  • Testing and auditing a model
  • Forecasting techniques
  • Sensitivity and risk
  • Optimisation and targeting
  • Management dashboards and reporting

On completion of this 3-day training programme delegates will be able to:

  • Understand the different types of financial models and their application
  • Construct models that are robust and scalable
  • Incorporate elements such as risk, sensitivity, optimisation and forecasting into models
  • Produce management reports, summaries and meaningful charts

Course Structure

Day 1: Overview of modelling techniques
Day 2: Forecasting, risk and scenarios
Day 3: Optimisation, data analysis and reporting

Teaching Methodology

The programme is taught using demonstrations combined with practical and interactive case studies. The exercises in each session reinforce the concepts covered in each 0f the units. Emphasis is placed on delegates gaining practical, hands-on experience of the design and construction of financial models in Excel. Comprehensive product notes and modelling software are provided for future reference.

Course Background

When looking at any financial opportunity, the ability to perform accurate and realistic analysis is imperative. In today’s ever-changing business environment, the capability to just manipulate spreadsheets is not enough. You have to be able to incorporate all the “what if” scenarios and stress test any proposal to its limits. A working financial model can facilitate and improve the reliability and quality of your decision-making.

Modelling techniques are used in many different areas, such as investment appraisal, capital planning, budgeting, valuation, financial analysis and forecasting. Banks and businesses worldwide rely on the information produced by financial models – for many it is the key to success.

The course begins by concentrating on the advanced use of Excel. These skills will be applied to the construction of financial, valuation and investment models. Delegates will then learn how to incorporate forecasting, optimisation, risk assessment and sensitivity scenarios into these models.

The course is taught using a step-by-step approach so delegates will be able to construct financial models for a wide range of practical scenarios.

Who should attend

  • CFOs
  • Financial Controllers and Managers
  • Financial and Business Analysts
  • Risk Managers
  • Accountants
  • Corporate Treasury Managers
  • Middle Office Staff
  • General Managers


We work with a series of expert instructors, please select the course location of interest to review the credentials of who will be delivering the programme.

Alan Brooke

The course instructor has over 20 years’ experience in a wide range of roles in finance. He has delivered training courses on behalf of Euromoney Training since 2006.

He trained as a Chartered Accountant at KPMG in South Africa and New Zealand, before moving into industry with Ford Motor Company. He held various positions there in financial analysis, budgeting and forecasting, until 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 15 years, he has worked as a freelance financial modeller, trainer and analyst for a range of blue-chip clients. Assignments have included structured financing for a large-scale property development, multi-billion pound franchise bids in the UK rail industry, forecasting models for private equity investment in the waste management sector, and a number of PFI transactions in the utilities, health and support services sectors.

With an extensive accounting background, the instructor brings accounting knowledge and analytical skills to transactions and financial modelling.

He has built up a great deal of experience in financial modelling in different sectors, including property development, insurance market, outsourcing and utilities in the transport, gas, electricity and water sectors, as well as building financial models in central government departments. He has built, developed and used models to support commercial negotiations, analyse risk, test scenarios and forecast results.

Clients for whom the instructor has delivered training on behalf of Euromoney Training include Marfin Popular Bank (Cyprus), Finansbank and AK Bank (Turkey), Sace SPA (Italy), BayernLB Bank (Germany) and Access Bank (Nigeria). He also delivers training courses for Euromoney Training’s sister firm, DC Gardner Training, in many parts of the world.

Alastair Day

Alastair is a finance professional with extensive experience of finance and leasing. Alastair was previously a director of a start-up leasing company where the directors expanded the business rapidyly and eventually sold it to a public company.

Later, Alastair established an independent lessor and consultancy where he conducts consulting and contracting assignments on financial modelling.

Alastair designs and delivers courses covering aspects of financial modelling, credit and leasing.

He is the author of a number of finance publications e.g. Mastering Financial Modelling, Mastering Cash Flow and Valuation Modelling, Mastering Risk Modelling and Mastering Financial Mathematics in Excel together credit and leasing books such as Advanced Lease Finance and Lease and Finance Evaluation.

Alastair has a degree in German and Economics from London University and an MBA. He has served as an associate lecturer at the OUBS and as an examiner at the ifs School of Finance.



4-5 Star Hotel in Singapore

All of our courses are held in 4 – 5 star hotels, chosen for their location, facilities and level of service. You can be assured of a comfortable, convenient learning environment throughout the duration of the course.

Due to the variation in delegate numbers, we will send confirmation of the venue to you approximately 2 weeks before the start of the course. Course fees include training facilities, documentation, lunches and refreshments for the duration of the programme. Delegates are responsible for arranging their own accommodation, however, a list of convenient hotels (many at specially negotiated rates) is available upon registration.


Dubai Finance

This programme takes place on a non-residential basis at a central 4 to 5* Dubai hotel. Non-residential course fees include training facilities, documentation, lunches and refreshments for the duration of the programme. Delegates are responsible for arranging their own accommodation, however, a list of convenient hotels (many at specially negotiated rates) is available upon registration.

Related Courses


We can bring this course to your company's office.

If you simply want to run this course at a location convenient to you or if you want a completely customised learning solution, we can help.

We produce learning solutions that are completely unique to your business. Our tailored learning solutions are designed specifically for your organisation’s needs.

We’ll be here to support you every step of the way. From the initial consultancy through to evaluating the success of the full learning experience. We'll ensure you get the maximum return on your training investment.

Find out more


Agendas are localised, please select your preferred location.

*FTS Eligible - Financial Training Scheme for Singaporean citizens and PRs only (more information)

Day 1

Introduction and course objectives

  • Brief overview of objectives
  • Review of models

Overall model structure and design

  • Best practice in financial modelling
  • 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

Exercise: Creating a simple model with an assumptions / input sheet with built-in flexibility

Modelling techniques for revenue and cost forecasts

  • Translating assumptions and inputs into a model forecast
  • Build-up of construction or other capital costs
  • Correct matching of units
  • Build-up of operating and ongoing costs
  • Modelling pricing and revenue assumptions
  • Use of lookup functions to change expenditure timings
  • Building in sensitivities into the model

Day 2

Inflation/escalation factors

  • Creating inflation indices
  • Controlling start time of inflationary pattern
  • Applying multiple rates to different cost and revenue items
  • Varying inflation rates over life of the model

Exercise: Calculate costs and revenues using sets of assumptions and exchange rates, then apply inflation indices to model nominal amounts.

Cash flow modelling

  • Brief revision of
    • Impact of leverage on shareholder returns
    • Weighted Average Cost of Capital (WACC)
    • Discounted cash flows
    • Capital asset pricing model
  • Use of the cash flow waterfall technique
  • Modelling for:
    • Timing of debt and equity funding
    • Fee costs, upfront and ongoing
    • Interest costs, capitalised interest
    • Debt repayment profiles
    • Dividends, other equity returns, constrained dividend payments
    • NPV, IRR and other DCF measures

Exercise: Creation of simple cash flow waterfall model to reflect debt costs, repayment profiles, and returns to equity under constraints

Comparing a model to previous versions of the model

  • Comparing results of different versions of the same model
  • Reviewing future implications of variances

Example: From different versions of a modeled forecast, calculate variances and review future assumptions Building balance sheets in Excel

Building balance sheets in excel

  • Link between modeled cash flow and P&L
  • Using corkscrews to determine balances
  • Key balance sheet items and their calculation
  • Non-cash items: Depreciation, deferred tax
  • Assumptions required to be made
  • Use of existing figures or opening balance sheets
  • Creation of check totals

Exercise: From a given P&L and cash flow statement, compile a balance sheet which updates when assumptions change and which remains in balance

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 probabilities in sensitivities
  • Use of goal seek and solver
  • Version control to allow comparison of outputs
  • Use of Excel tools to support sensitivity analysis:
    • Data tables
    • Watch window function
    • Scenario 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 stresstest model to break-even, and create scenario summaries for presentation

Reporting outputs

  • Design techniques to enable optimisation
  • Pivot tables
  • Consolidation techniques
  • Use of charts and graphics

Exercise: From a given set of outputs, create pivot tables and consolidations to enable flexible reporting

Using model to derive valuations

  • Critical importance of cash available for distribution and free cash flow
  • Valuation of project/enterprise and valuation of equity
    • Asset-based valuations
    • Cash flow valuation techniques
      • Discounted cash flow
      • Building in synergies for M&A
      • Discounted dividend model
      • Gordon's growth model
    • Valuation using comparable measures
      • Price - earnings ratios
      • Dividend yield method
      • Exit multiples

Exercise: From a given cash flow and balance sheet, calculate the above ratios and valuations on different bases

Advanced Excel functions

  • Advanced financial mathematics
  • 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

Creating simple macros

  • What is a macro?
  • How macros help in financial modelling
  • Best practice in creating and using macros
  • Use of range names
  • Documentation of macros

Exercise: Creating a macro without using VBA


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

Day 1

Overview of Modelling Techniques

Module 1
Introduction and Overview

Outline of financial modelling
Examples of Excel financial models
Useful Excel features and techniques
Exercise: analysing examples of modelling techniques

Module 2
Spreadsheet Best Practice

Model design and structure – key steps
Financial analysis basics – financial statements
Cash flow and ratios
Case outline - introduction
Exercise: Defining a model plan for a financial analysis model
Model design
Exercise: Techniques for modelling financial statements

Module 3
Auditing and Testing

Examples of spreadsheet errors
Essential testing and auditing techniques
Example: testing financial analysis model with cash flows and ratios
Exercise: debugging and checking a financial model

Module 4
Practical – Project Model 1

Practical project model exercise

Questions and answers

Day 2

Forecasting and Risk

Module 5
Macros and Security

Visual Basic in Excel
Writing and auditing and macros
Spreadsheet security
Exercise: writing and auditing macros

Module 6
Forecasting Models and Time Series Analysis

Review of forecasting methods
Understanding financial ‘drivers’ for analysis, valuation and project models
Exercise: deriving forecast free cash flow and ratios
Linear methods
Smoothing and seasonality
Exercise: producing a forecast with time series analysis

Module 7
Risk Techniques

Valuation model and cost of capital fundamentals
Exercise: calculating the cost of capital
Risk and multiple answers
Scenario techniques
Advanced financial functions
Exercise: Producing a valuation and testing the results

Module 8

Practical – Project Model 2

Review of initial answers and identification of potential risks
Adding risk techniques to the project model in module 4

Questions and answers

Day 3

Optimisation and Reporting

Module 9

Optimisation and Targeting

Overview of optimisation and targeting
Goal seek and Solver methods
Example: targeting using the composite valuation model

Module 10
Practical – Project Model 3

Extend the project model to answer management questions and target specific results
Completing and auditing the project model

Module 11
Management Reporting

Requirement to consolidate and summarise data
Exercise: consolidating data from different sources
Excel report manager
Exercise: pivot tables
Techniques for summarising data
Exercise: producing a management analysis

Module 12
Practical - Management Reporting and Forecasting

Analysis of company financial data
Exercise: extend the valuation model to produce a meaningful management summary report with charts

Questions and answers

Course summary and close

Why us

We have a combined experience of over 60 years providing learning solutions to the world’s major organisations and are privileged to have contributed to their success. We view our clients as partners and focus on understanding the needs of each organisation we work with to tailor learning solutions to specific requirements.

We are proud of our record of customer satisfaction. Here is why you should choose us to help you achieve your goals and accelerate your career:

  • Quality – our clients consistently rate our performance ‘excellent’ or ‘outstanding’. Our average overall score awarded to us by our clients is nine out of ten.
  • Track record – we have delivered training solutions for 95% of worlds’ top 100 banks and have trained over 250,000 professionals.
  • Knowledge – our 150 strong team of industry specialist trainers are world leading financial leaders and commentators, ensuring our knowledge base is second to none.
  • Reliability – if we promise it, we deliver it. We have delivered over 20,000 events both in person and online, using simultaneous translation to delegates from over 180 countries.
  • Recognition – we are accredited by the British Accreditation Council and the CPD Certification Service. In an independent review by Feefo we scored 96% on service and 95% on product