Financial Modelling in Excel

5 days 4-8 Jun 2018, Kigali Rwanda $3,000.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).

{{alternativeRequestSuccess}}
{{alternativeRequestError}}

* Claim back your VAT
Find out more

Overview

 

 

 

Course Overview

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 delegates will not need an advanced knowledge.

Aims

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.
 
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 as a module in a traditional class style, but the real learning experience will be found in the exercises within each module. Delegates will do the exercises themselves or in small groups, under supervision and guidance of the instructor. Suggested solutions to each exercise will be provided and discussed.  Participants will be encouraged to review their work independently, and to ask questions individually or in group discussions.

Instructors

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.

Kigali
Alan Brooke

Alan has 30 years’ experience in a wide range of roles in finance. He has delivered training courses on behalf of Euromoney Training since 2006.

Alan 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 19 years, Alan has worked as an independent consultant specialising in financial modelling and financial analysis 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, Alan brings accounting knowledge and analytical skills to transactions and financial modelling.

Alan 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 Alan has delivered training on behalf of Euromoney in Africa include PTA Bank (Kenya), Access Bank (Nigeria), Grindrod Shipping (South Africa), the Ministry of Finance (Egypt) and Steward Bank (Zimbabwe).

He is course director of the Euromoney course Financial Modelling in Excel, Financial Modelling for PPP Projects and Project Finance Modelling, and delivers in–house training for a range of clients on behalf of Euromoney and Euromoney Training’s sister firm, DC Gardner Training, in many parts of the world.

Venue

Kigali

Radisson Blu Kigali

Related Courses

Inhouse


 

Do you have five or more people interested in attending this course? Do you want to tailor it to meet your company's exact requirements? If you'd like to do either of these, we can bring this course to your company's office. You could even save up to 50% on the cost of sending delegates to a public course.

To find out more about running this course in-house:





Our Tailored Learning Offering

If you 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. We'll guide you through the whole process, from the initial consultancy to evaluating the success of the full learning experience. Our learning specialists ensure you get the maximum return on your training investment.

inhouse-learn-more

We can offer any of our public courses delivered at your office or we can devise completely tailored solutions:


Read more about our offering or complete a call back request to speak to a learning specialist.

 

Agenda

Day 1


Introduction & Course Objectives

  • Brief overview of 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

Exercise – creating a simple model with an input sheet with built-in flexibility

Modelling Techniques for Revenue and Cost Forecasts

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

Day 2

Modelling Techniques for Revenue and Cost Forecasts (continued)
Exercise - Use IF statements to build a flexible capital costs 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

Exercise – model multiple, variable inflation rates, including a rate that begins after the start of the model

Cash Flow Modelling

  • Brief revision of:
     - 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, interest rate ratchets
     - Debt repayment profiles
     - Rate switches or refinancings
     - Debt repayment profiles and built-in options
     - 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


Day 3

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 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
  • Model summaries
  • Use of charts & graphics

Exercise – from a given set of outputs, create consolidations to enable flexible reporting, and prepare a model summary

Day 4

Model Auditing

  • Use of the formula auditing toolbar
  • Checks, totals and error reporting
  • Logic trees & flowcharts
  • Non-Excel tools available

 
Exercise
– from a given model, audit and uncover errors and inconsistencies

Using the Model for Valuations

  • 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 valuations on different bases

Review of Financial Models

  • Structure for reviewing a model
  • Where do we start?
  • Which are critical areas of calculations?
  • Model auditing
  • Exercise – review a selection of models in a variety of industries

 

Day 5

Advanced Excel Functions

  • Advanced financial mathematics – PPMT, XIRR, XNPV, MIRR
  • 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

Case Study
For an agricultural-based business, build a cash flow model for a number of forecast years, including revenues, costs, capital expenditure, debt drawdowns and repayments, and dividends to shareholders.

 
Wrap-Up

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

 

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