Basic Financial Modelling in Excel

This course can also be presented face to face in-house or via live in-house webinar.

Excel Financial Modelling Course Objectives:

Aims

The principal aim of the Excel Financial Modelling course is to enable participants to prepare logical and easy-to-use financial models in Excel to support transactions, forecasts and planning for ongoing business streams.  The course will review best practice in model structures, calculations and logic, and using tools to highlight areas of risk, particularly in sensitivity analysis.

Excel Financial Modelling Course Content:

Day One

Introduction & Course 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

Exercise – creating a cash flow model with an assumptions / input sheet with built-in flexibility

Modelling Techniques for Forecasting

  • Translating assumptions & inputs into a model forecast
  • Correct matching of units
  • Treatment of fixed and variable costs
  • Modelling pricing & revenue assumptions
  • Use of lookup functions to change expenditure timings
  • Building in sensitivities into the model

Exercise – add detailed cost calculations, lookup functions and sensitivities into the previous cash flow model

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
  • Using exponential formulas for other forecasting purposes

Exercise – model multiple, variable rates and analyse a separate set of actual rates to compare to forecast

Day Two

Modelling Taxes

  • Differences between P&L and tax treatment for costs & revenues
  • Allowing for deductibility and non-deductibility
  • Capital allowances vs depreciation
  • Modelling tax losses and their effect
  • Example – review of an example of tax modelling for an investment project

Exercise – add tax calculations into the previous cash flow model

Dealing with Circular References

  • Circularity and consequences
  • When does circularity most frequently occur?
  • Solutions to circularity – advantages and disadvantages of each

Example – demonstrate various methods to overcome circular references

Cash Flow Modelling

  • Principles of discounting of cash flows
  • Setting the discount rate
  • Modelling for:
    • Timing of debt and equity funding
    • Interest costs, capitalised interest
    • Debt repayments
    • Debt refinancing
    • Dividends and other equity returns
  • NPV, IRR and other DCF measures

Exercise: add debt costs, interest payments, repayment profiles and dividends to the previous cash flow model, and discount the cash flows at an appropriate rate

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 modeled forecast, calculate variances and review future assumptions

Sensitivity Analysis

  • 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:
    • data tables
    • scenario manager
    • watch window function
    • scenario tables

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.

Wrap-Up

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

Background of the Trainer:

The trainer has over 30 years’ experience in a wide range of roles in finance. He has delivered training courses on behalf of a number of international training companies since 2005.

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 17 years, he has worked as a freelance financial modeller, trainer and analyst 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.

Excel Financial Modelling Course Summary:

The Excel Financial Modelling course is designed to support accountants and analysts working in corporate and SME businesses in creating financial models on a consistent and focused basis. Some previous use of Excel is assumed, but delegates will not need an advanced knowledge.

Methodology

The learning methods used are practical, as practice of newly-learned techniques enables a deeper and more effective building of skills. Each section will be covered briefly as a module in a traditional class style, but the real learning experience will be found in the exercises within each module.  The trainer will support delegates during the exercises, answering questions and providing one-on-one help where needed.  Suggested solutions to each exercise will be provided and discussed, and participants will be encouraged to review their work independently.

Participants will be required to bring a laptop to the course.

 

 

What Redcliffe’s Clients are saying about the course:

“Wide range of Content”

—  IFC

“The examples were good”

— Investment Banki ng Analyst, Oppenheimer

0/5 (0 Reviews)