Advanced financial modelling - December 2019
days to go
London Stock Exchange
A detailed insight into advanced financial modelling
This one day workshop is designed to give participants exposure to advanced techniques in order to give more flexibility to their financial models.
Who should attend?
This workshop is designed for all finance and non-finance professionals who are already comfortable using Excel and who want to learn additional techniques to render their models more flexible.
By the end of the course, the participants will be able to:
- Apply modelling best practices in their models
- Add flexibility to their models using form controls
- Work with large volumes of data using pivot tables
- Create customised charts
- Model for debt and pension planning
- Run sensitivity analysis
- Use Excel’s tools for model optimisation
The use of computers with Microsoft Excel installed (or similar software) is essential for this course, as all the case studies will be done on Excel.
Session 1: Introduction
- Review of course objectives
- Reminder of modelling best practices
Session 2: Form controls
- What are form controls and where can they be used?
- Inserting and modifying form controls
- Macros and form controls
Activity: Participants will be required to complete an exercise requiring the use of form controls. They should also use this opportunity to become familiar with the various form controls available and their settings.
Session 3: Using tables in Excel
- Using tables
- Creating pivot tables
- Modifying pivot tables
- Using calculated fields for pivot tables
- Using the GETPIVOTDATA function
Activity: Participants will create a report by summarising and organising information using pivot tables.
- What makes a table effective?
- Effective table design tips
Activity: Participants will be required to redesign a report based on the principles of effective design discussed.
Session 4: Advanced charts
- Tips for creating advanced charts
- Football field charts
- Clustered stacked column charts
- Fan charts
- Waterfall charts
Activity: Participants will be required to create and format a couple of advanced charts from a set of data.
Session 5: Modelling a debt waterfall
- What is a debt waterfall?
- Modelling a cash sweep
Activity: Participants will be required to complete the missing steps of a debt waterfall model.
Session 6: Modelling for pension planning
- Using DCF techniques for pension planning
Activity: Participants will be required to complete a model to calculate the monthly pension payment expected based on certain key assumptions.
Session 7: Sensitivity analysis
- Purpose of sensitivity analysis
- Using the Watch Window (Note: not available in the Mac version)
- Using Excel’s Data Tables
Activity: Participants will be required to use the Watch Window to track the effect of changes to the model’s assumptions and use the Data Tables to analyse the sensitivity of assumptions.
Session 8: Model optimisation
- What is model optimisation?
- How can optimisation be achieved?
- Using Solver and Goal Seek to achieve specific results
Activity: Participants will review the result of a model and make adjustments to achieve optimisation.