Course dates
Course overview
'Financial Modelling in Excel' supports financial analysts, modellers and their managers in creating financial models on a consistent basis. Some previous use of Excel is assumed, but delegates do 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
- Creating cash flow waterfalls
- Using the model for analysis, including valuations
- Use of Excel tools to support sensitivity analysis
- Goal seek
- Watch window function
- Data tables
- Scenario manager
Methodology
Practical exercises enable 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
- Financial analysts
- Finance managers
- Bank economists
- Relationship managers
- Corporate accountants
- Model auditors
- Credit analysts
- Financial controllers
- Vice Presidents finance
Supporting publication
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: 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
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 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
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
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 output tables, 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
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 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.
-
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.
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.
Course dates