This is a comprehensive financial modelling programme developed to equip participants with practical knowledge and skills in modelling financial statement, valuation and DCF using Excel, and M&A (Accretion/Dilution)
Using hand-holding approach, participants will develop a complete and comprehensive financial statements (three-statement) model from scratch using various supporting schedules.
Participants will learn the conceptual underpinnings of the valuation framework followed by step-by-step training for building a professional, robust discounted cash flow (DCF) model in Excel from scratch, using real case studies, industry best practices, and sensitivity analyses.
Participants will build a merger model in Excel to reflect the pro forma impact of various acquisition scenarios. A small class size is maintained to ensure close faculty attention and foster individual learning.
Programme:
- Building financial models from scratch the way it is done at financial institutions
- Excel and formatting best practices, efficient formula construction, and appropriate driver selections
- Fixing circularity problems, iteration, and other common modelling troubleshooting
- Advanced Excel functions to present various sensitivities to projected financial metrics
- Advanced Excel functions to present various sensitivities to projected financial metrics
- Create a performance analysis model; Income Statements, Balance Sheet, Cash Flow Statement and Ratio Analysis
- Balancing the balance sheet accounts, including excess cash and revolver
- Balance sheet / cash flow statement crosschecks
- Treatment and adjustments for options, preferred stock, minority interests, debt, cash and marketable securities
- Creating models to compute depreciation, leases, rentals and tax
- Analysing risk with the use of standard deviation, coefficient of variation models
- Investment analysis using Payback Period, Accounting Rate of Return, Net Present Value, Internal Rate of Return.
- M&A modelling overview
- Building a professional, robust discounted cash flow (DCF) model in Excel
- Valuation models; Differences between enterprise and equity valuation, unlevered and levered free cash flow estimation, and intrinsic vs. market-based valuation
- Purchase accounting; accretion/dilution overview and exercise
- Building the accretion/dilution model
- Sensitivity analysis using data tables and breakeven & contribution analysis
The programme will follow a workshop format to encourage an interactive, 'learning by doing' approach to developing practical modelling skills. Real life case studies will be used to allow participants to apply their new skills, with additional exercises introduced to enhance the learning process.
This is a hands on course structured with a high tutor to student ratio, to ensure that delegates can gain the maximum benefit.
Day 1 – Basic model build up
The purpose of day one is to establish some of the key techniques for spreadsheet modelling, using financial mini-case study examples. A central focus is on best practice spreadsheet design and modelling techniques to produce robust, transparent and auditable spreadsheet models.
- Obtaining source data
- Coding input
- Structuring assumptions and anticipating scenario analysis
- Modelling and formatting best practice
- Financial modelling
- Good model structure
- Starting to forecast the P&L from key assumptions
- How far can we progress?
- What's stopping us from continuing?
- Key drivers for modelling
- Key ratios driving the forecast
- Drivers on revenues
- Drivers on costs
- Sources of data
- Forecasting assets/li>
- Key drivers on asset intensity
- Capital expenditure
- Depreciation
- Forecasting depreciation
- Drivers for balance sheet items
- Which creditors can we stretch, and by how much?
- How quickly can we collect debtors?
- Forecasting the balance sheet
- Impacts on cash flow
- Is growth good?
- Linking to other statements
- Balancing the balance sheet
- Forecasting a simple debt schedule
- Linking to other statements
- Tools for resolving circularity
- Setting debt paydown
- Modelling the cash flow statement
- Key linkages to other statements
- Presenting the cash flow statement
- Forecasting cash flow to equity
- Forecasting unlevered cash flow
Day 2 – Understanding a model
Developing deal structure – sources & uses of funds
- Developing a "first cut" debt structure
- Calculating refinancing needs
- The role of working capital and extra cap ex requirements
- Typical financing and transaction fees
- Determining the equity gap
- The impact of equity rollover
- The impact on the model: calculating goodwill and the pro-forma balance sheet
- Absolute vs. relative valuation techniques
- What is debt free cash free? What's the link to deal structure?
- Relative valuation – typical valuation metrics
- Which multiples should we use?
- What are the pros and cons of different multiples?
- Determining debt capacity and structuring debt
- Clear, simple and concise explanation of different debt instruments
- Senior debt
- High-yield debt
- Payment-in-Kind
- Understanding the nature of different financial instruments and risk profiles
- Modellng waterfall structures
- Estimating and optimising debt capacity
Day 3 – scenario analysis and Excel modelling best practice
Defining key outputs
- What are the most important outputs?
- How can they be presented clearly?
- How can we put for example, anticipated sales, capital expenditure and working capital plans into context?
- Modelling scenarios
- Scenario management-the LOOKUP school
- Different methods to select scenarios-number and data validation
- Building a model framework that will accommodate multiple scenarios
- Instant scenario switching with drop down boxes
- How can we stress test the model?
- Delegates are provided with the opportunity to ask for help with particular Excel functions
- Use of these functions is demonstrated in class and supplemented with exercises which the class works through together
- During the course delegates work to create their own models, establishing and observing spreadsheeting best practice as they progress
- Good modelling techniques are observed throughout the course, discussed in groups and demonstrated during the program
ADVANCED modelling in Excel" course – 3 days
The objective of this course is to provide delegates with the practical skills to build the key elements of integrated financial models. The models will be appropriate for a range of situations building from analysis of current data to forward projections.
The content will focus on the staged development of the models using a structured, best practice approach to model design so that the models produced are robust, transparent and auditable.
The course will develop modelling techniques suitable for a range of applications. These techniques will be developed using realistic examples and will form the building blocks for implementing models of complex financial situations.
Delegates will improve their:
- ability to produce well-designed, transparent and auditable models
- efficiency in the use of Excel
- understanding of the modelling techniques necessary to prepare robust, reliable models
- skills in preparing models for a wide range of financial modelling problems.
- Introduction to the course
- Excel modelling style
- A systematic design method for model building
- Case study outline
- Financial modelling: planning
- Using lookup functions
- Hands-on exercises: scenario modelling, flexible timing of a data set, manipulation of data.
- Hands –on- exercise: switching data sets for model input without rewriting formulae
- Layout methods
- Formats, numbers and colours
- Flexible timing and date functions
- Useful Excel techniques and shortcuts
- Operational revenue
- Volume drivers
- Counters
- Efficient coding techniques
- Fixed and variable cost
- Real and nominal considerations
- Operating margin
- Debt funding
- Fixed asset schedule
- Avoiding links and circular references
- Basic income and balance sheet
- Adding cash flow
- Cash flow schedule
- Model integrity and self checks
- ISERROR functions
- Accounting returns
- Terminal value
- Cumulative and simple discounting
- Single answer NPV, IRR, MIRR
- Common sensitivity errors
- Comprehensive scenario manager
- Sensitivity analysis
- Dashboard analysis
- Excel audit checks
- Checking the current model
- Integrity checks
- Circular references
- Links
- Stress testing
- Recording macros
- Checking macros
- Amending macros
- Using Goal Seek and Solver
- Efficient use of optimisation
- LOOKUP functions
- Pivot tables
- Reporting tools
- Financial modelling documentation
- Printing the model
- Charts in Excel
- Executive summary
This course is directed at those delegates working in or alongside the project finance, who need to develop the skills to built or interpret integrated Excel-based models. Delegates should have some experience of the fundamentals of modelling
Day 1
Session 1: the role of the project finance model
- Key elements of a project finance model
- Typical project finance contract structure
- Implications for modelling
- Starting to build the model
- Sources of revenue
- Assumption setting: identifying and separating key assumptions
- Escalating revenues: pitfalls in escalation factors
- What do we conclude about good model structure and design: right from the start?
- What have we discovered already about good modelling practice?
- Coping with delay
- How can we build in flexibility e.g. changes in financial close, construction period, start date and operations?
- How might we model expenses in this project?
- What about in other projects?
- Escalating expenses
- Modelling the cash flow
- Key linkages to other parts of the model
Day 2
Session 6: modelling capex
- Phasing up front cap ex within the model
- Project delay: how could we design the model so that it accommodates delay?
- What contractual arrangements need to be reflected in the model?
- Incorporating ongoing operational cap ex
- Sources and uses of funding
- Fees on the deal
- Mapping out a capital structure for the deal
- Incorporating a simple funding structure within the project model
- Forecasting a simple debt schedule
- Linking to other statements and adjusting cash flow
- Modelling a more complex and realistic debt structure
- Discussion around the role of different debt instruments
- How might we model debt different debt instruments?
- The influence of different debt structures in projects
- Estimating and optimising debt capacity
- Looking at the project model, which items are likely to flow straight through to the tax return?
- Which are going to be spread over time?
- How do we model the tax expense?
- How do we model tax losses?
Day 3
Session 10: defining key model outputs
Note: the time spent on this session depends on time available on day 3 and the extent of delegate interest.
- What are the most important outputs?
- DSCR, LLCR, PLCR
- How can they be presented clearly?
- How is your deal doing?
- Any breaches?
- Conditional formatting
- Investment appraisal crash course
- How is DCF calculated in a project model?
- Why do we discount?
- How is the discount rate determined?
- How is NPV calculated?
- Elephant traps: using Excel to calculate NPV
- Understanding IRR
- 3 ways of understanding/ calculating IRR
- Elephant traps: using Excel to calculate IRR
- Introduction to real Harvard Business School case study
- The challenge: investment appraisal for a real and complex project finance deal
- There is a lot of information here!
- Who are the key players? What are their roles?
- What is the likely contractual structure? What are key contractual risks?
- What are the financing options for the project?
- Peculiarities of this deal
- Pros and cons of the financing options
- How would you structure a project model for this deal?
- How would you conduct your investment appraisal?
- What is the role of the boutique advisory firm involved?
- What really happened?
- Excel tips and help
- Delegates are provided with the opportunity to examine and get help with key Excel functions that are new to them
- Good modelling practice guidelines
- Model audit
- How might we audit a model?
- Working with the model
- What scenarios could we run?
- How could we structure the model to cope with them?
Note: the time spent on this session depends on time available on day 3 and the extent of delegate interest.
- Delegates who have improved their understanding of and have had experience of modelling project finance deals
- Simple and clear reference Excel models - providing participants with a platform for future analysis and decision making
- Participants who, at the end of the course, understand the drivers on project finance deals and how project parameters can be structured to suit the various parties
