Financial Modelling in Excel
The principal aim of 'Financial Modelling in Excel' is to enable participants to use Microsoft Excel to prepare logical and easy-to-use financial models to support transactions, forecasts and planning for ongoing business needs.
Course dates
Course overview
'Financial Modelling in Excel' 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.
Summary of course content
- Best practice in structuring models
- Building models using best practice Excel techniques
- Building inflation into the model
- Modelling taxes
- Dealing with circular references
- Creating cash flow waterfalls
- Using the model for analysis, including banking ratios
- Sensitivity analysis tools
- Creating simple macros
- Advanced Excel functions
Methodology
As with all Euromoney Training courses, this programme uses practical exercises as this enables a deeper and more effective building of skills. Each section will be covered as a module in the traditional class style, but the real learning experience will be found in the exercises within each module. Suggested solutions to
each exercise will be provided and discussed, and participants will be encouraged to review their work
independently. This will ensure you leave the course ready to apply your new knowledge.
Who should attend this training course
- Middle-ranking financial analysts
- Finance managers
- Bank economists
- Relationship managers
- Corporate accountants
- Model auditors
- Junior financial analysts
- Credit analysts
- Financial controllers
- Vice Presidents finance
Supporting publications
DAY ONE
Introduction and course objectives
- Brief overview of objectives
- Review of models and their objectives
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
- 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
DAY TWO
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 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 nondeductibility
- 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
- Debt Service Reserve Account (DSRA) – what it is and how to model it
- Capitalising / rolling-up fees and interest
Example: demonstrate various methods to overcome circular references
Exercise: model interest costs, debt fees and DSRA balances for a project
Cash flow modelling
- Brief revision of
- 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, 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 THREE
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
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
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 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 manage
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
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, Monte Carlo analysis
- Risk-adjusted returns
Exercise: for a given model, calculate risk-adjusted returns from potential risks
DAY FOUR
Reporting outputs
- Design techniques to enable optimisation
- Pivot tables
- Consolidation techniques
- Use of charts and graphics
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
Using the model to derive ratios and valuations
- Critical importance of cash available for distribution and free cash flow
- Project cash flows and ratios vs. equity cash flows and returns to equity
- Debt service coverage reserve ratios
- Interest cover ratios
- Loan life cover ratios
- Equity returns
- Use of WACC
- Issues arising from the use of NPV and IRR calculations
- 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 – 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
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
Wrap-up
- Overall review
- Key points to re-iterate
- Brief introduction to further exercises and reading
- Final questions and issues to discuss
InterContinental Grand Stanford Hotel, Hong Kong, Hong Kong
This programme takes place on a non-residential basis at the InterContinental Grand Stanford Hotel. Non-residential course fees include training facilities, documentation, lunches and refreshements for the duration of the programme. Delegates are responsible for arranging their own accomodation, however, a list of convenient hotels (many at specially negotiated rates) is available upon registration.
-
Alan Brooke
Alan Brooke, MBA, CA(SA), CA(NZ)
Consultant
Alan has over 20 years’ experience in a wide range of roles in finance. He has delivered training courses on behalf of Euromoney since 2005.
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 11 years, Alan 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, Alan brings accounting knowledge and analytical skills to transactions and financial modelling.
Alan 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.
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.
Course dates