This 3-day course will enable you to accomplish:
- Best practice in structuring models
- Building models using best practice Excel techniques
- Building inflation into the model
- Modelling taxes
- Creating cash flow waterfalls
- Using the model for analysis, including banking ratios and valuations
- Sensitivity analysis tools
- Advanced Excel functions
Who should attend
- Financial analysts
- Finance managers
- Bank economists
- Relationship managers
- Corporate accountants
- Credit analysts
- Financial controllers
- Budget and forecasting controllers
- Deal structuring analysts
- M&A specialists
- Financial planning and analysis managers
Course Overview
Financial Modelling in Excel is designed to support 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 do not need an advanced knowledge.
Methodology
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.
Day 1
Introduction and Course Objectives
- Brief overview of course objectives
Overall Model Structure and 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 and 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 and revenue assumptions
- Use of lookup functions to change expenditure timings
- Building in sensitivities into the model
Day 2
Inflation and 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
- Introduce exercise to do outside class – model multiple, variable rates and analyse a separate set of actual rates to compare to forecast
Brief Overview of Modelling Taxes
- Differences between P&L and tax treatment for costs and 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
Interest, Debt Fees and Circularity
- Circularity and consequences
- Solutions to circularity – advantages and disadvantages of each
- Calculations of interest and fees
- Cash flow payment vs amortisation in the P&L
- Capitalising / rolling-up fees and interest
Example – demonstrate various methods to overcome circular references
Cash Flow Modelling
Brief revision of WACC and discounting of cash flows
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 modelled 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 and 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
Reporting Outputs
- Design techniques to enable optimisation
- Pivot tables
- Consolidation techniques
- Creating simple macros
- Use of charts and graphics
- Introduction to macros
- Demonstration – creating a macro without using VBA
Exercise – from a given set of outputs, create output tables, pivot tables and consolidations to enable flexible reporting
Model Auditing
- Use of the formula auditing toolbar
- Checks, totals and error reporting
- Logic trees and flowcharts
- Non-Excel tools available
Exercise – from a given model, audit and uncover errors and inconsistencies
Risk Reviews
- Categories of risk and their relationship to the model
- Use of risk matrices
- Using sensitivity analysis to understand risk
- Probability analysis and use of statistical techniques, introduction to Monte Carlo analysis
- Risk-adjusted returns
Exercise – from a given model, calculate risk-adjusted returns from potential risks
Wrap-Up
- Overall review
- Key points to reiterate
- Brief introduction to further exercises and reading
- Final questions and issues to discuss
Course summary and close
-
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 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 11 years, the course instructor has worked as a freelance financial modeller and analyst for a range of blue-chip clients. Assignments have included structured financing for a large-scale property development, multibillion 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, he brings accounting knowledge and analytical skills to transactions and financial modelling.
The course instructor has built up a lot of experience in financial modelling in different sectors, including property, insurance, outsourcing, utilities, transport, energy sectors, as well as central government departments. He has built, developed and used models to support commercial negotiations, analyse risk, test scenarios and forecast results.
Courses run by this instructor
Interested in holding this course in-house? Please fill out your details and a member of our team will be in touch with more information.
3-5 Dec 2013 (Johannesburg, South Africa)
A three-day financial training course on the valuation of mining companies.
23-25 Jul 2013 (Johannesburg, South Africa)
3-5 Dec 2013 (Johannesburg, South Africa)
23-25 Jul 2014 (Johannesburg, South Africa)
3-5 Dec 2014 (Johannesburg, South Africa)
A 3 day financial training course on financial modelling in excel.
24-27 Jun 2013 (Nairobi, Kenya)
The course provides participants with a working knowledge of the principles and practices of applied corporate finance including: financial analysis, modelling and valuation. This will enable participants to acquire a sufficiently thorough understanding of the subject in order to be able to use the tools and techniques in an effective manner.
22-24 Nov 2013 (Johannesburg, South Africa)
The aim of this intensive 3-day training course is to provide participants with exposure to leveraged and mezzanine financing techniques in the context of M&A and private equity structures. Delegates will be introduced to international practice in executing leveraged transactions, which will include due diligence, accounting issues and valuation techniques. Case studies will include opportunities to structure leveraged finance transactions.
25-29 Nov 2013 (Johannesburg, South Africa)
A 5-day case study based workshop exploring more advanced issues in company valuation and financial modelling.
23-27 Sep 2013 (Nairobi, Kenya)
A 5-day case study based workshop exploring more advanced issues in company valuation and financial modelling.
26-28 Aug 2013 (Johannesburg, South Africa)
27-29 Jan 2014 (Johannesburg, South Africa)
25-27 Aug 2014 (Johannesburg, South Africa)
A practical and highly interactive 3-day training course, incorporating numerous case studies to enhance delegates understanding on the production and transformation of agricultural products and their distribution.
13-15 Aug 2013 (Johannesburg, South Africa)
A 3–day training course dedicated to the valuation, structuring, financing and negotiating of merger and acquisition transactions.
7-9 Oct 2014 (Accra, Ghana)
This comprehensive 3-day workshop builds a financial analysis and valuation model through a series of practical stages.
3-5 Dec 2013 (Nairobi, Kenya)
This 3-day programme focuses upon how the toolkit of conventional business analysis can be linked sequentially and logically with the principles of financial economics. It will provide critical insight into how the tools and techniques of conventional business analysis can and must be adapted.
4-6 Mar 2014 (Lagos, Nigeria)
9-12 Jul 2013 (Nairobi, Kenya)
This comprehensive 4-day management course will improve the effectiveness and success of your banking branch network.
8-11 Jul 2013 (Johannesburg, South Africa)
This 4–day Corporate Finance Applications course is
a practical "how to" programme dealing with numerous disciplines within the subject of corporate finance.
19-21 Jun 2013 (Nairobi, Kenya)
18-20 Jun 2014 (Nairobi, Kenya)
22-25 Jul 2013 (Lagos, Nigeria)
This 4-day intermediate programme equips investment professionals with practical knowhow of new approaches and new asset classes for today’s investment conditions.
5-8 Aug 2013 (Nairobi, Kenya)
A comprehensive 4-day training course, featuring case studies across a range of industries to help course participants practice corporate valuation techniques.
15-17 Oct 2013 (Lusaka, Zambia)
A 3-day financial training course on corporate loan restructuring. With tough liquidity conditions finance professional are required to quickly identify what is causing borrowers problems and provide the most appropriate and costeffective finance solution.
19-21 Aug 2013 (Johannesburg, South Africa)
A comprehensive 3- day course for all financial professionals in banking, corporate finance,
strategic and financial advisory, consulting, M&A departments and private equity houses.
23-26 Sep 2013 (Johannesburg, South Africa)
This program covers how to design a successful corporate banking strategy and provide the adequate products and services to the different customers groups. It applies to banks in developed and emerging markets.
21-24 Oct 2013 (Johannesburg, South Africa)
This 4-day workshop takes a hands-on approach to financial analysis and cash flow techniques.
23-25 Jul 2013 (Johannesburg, South Africa)
This 3-day course has been designed to provide the participants with a complete overview of the venture capital industry and its workings.
12-14 Aug 2013 (Harare, Zimbabwe)
11-14 Nov 2013 (Johannesburg, South Africa)
This 4-day intermediate programme equips investment professionals with practical knowhow of new approaches and new asset classes for today’s investment conditions.
7-9 Oct 2013 (Lagos, Nigeria)
This comprehensive 3-day workshop builds a financial analysis and valuation model through a series of practical stages.
4-6 Aug 2014 (Accra, Ghana)
This practical and highly interactive 3-day financial training course, which incorporates numerous case studies, provides delegates with a thorough understanding of the syndicated loan market, its process and potential.