Course provider
A financial training course from Financial Training Associates Ltd. Financial Training Associates supplies experienced finance trainers to companies who wish to provide financial markets training for small groups of their employees. See www.financialtrainingassociates.com for more info.
Course purpose
• This course is designed for people who would like an opportunity to practise and improve their ability to model cash flows in Excel.
• The course is conducted in a work shop format, with the emphasis on “learning by doing”.
• During the programme delegates create a cash flow model from scratch. Delegates are guided through a model build up and are shown all the steps they need to take. Plenty of guidance and help is given by the course instructor.
• As part of their work on the course, starting from a blank spreadsheet, delegates create a full set of forecast financial statements for the business. Delegates can then choose a scenario to see how the business performs under stress and, for example, how key financial ratios and bank covenant tests change with a change in circumstances.
Detailed modelling work: as part of their work on this course delegates “build up” integrated financial statement models
• At the end of the course participants will have a record of their own work and a permanent record of the steps they have taken to create their own cash flow forecast.
The course covers:
o Stages in forecast build up
o Assumption setting
o P&L forecasting
o Modelling fixed assets, working capital and other balance sheet items
o Cash flow build up
o Linking statements
o Scenario analysis
o Creating an output sheet
o Guidelines for good modelling practise
o Helpful spreadsheet conventions
o Model design and structure
• The three days is designed to provide participants with the tools they need to continue with their own modelling efforts as they return to their workplace.
Day one – beginning to build a core model
Session 1: planning assumptions
• Obtaining source data
• Coding inputs
• Structuring assumptions and anticipating scenario analysis
• Modelling and formatting best practice
• Good model structure
• Good model design
Modelling: delegates are introduced to a case study and a set of financial statements. Participants use that to start creating their own model.
Session 2: starting to forecast the income statement
• 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
o Drivers on revenues
o Drivers on costs
o Sources of data
Modelling: delegates add to their model and forecast out the income statement as far as pre-tax earnings
Session 3: modelling fixed assets
• Forecasting assets
• Key drivers on asset intensity
o Capital expenditure
o Depreciation
• Forecasting depreciation
Modelling: delegates analyse and forecast fixed assets, depreciation and capital expenditure
Session 4: completing the balance sheet
• Key drivers for balance sheet items
o Which creditors can we stretch, and by how much?
o How quickly can we collect debtors?
• Forecasting the balance sheet
• Impacts on cash flow
o Is growth good?
• Linking to other statements
• Balancing the balance sheet
Modelling: delegates use their model to forecast a balance sheet for the case study
Day two – completing the core model
Session 5: modelling debt
• Forecasting a simple debt schedule
• Linking to other statements
• Tools for resolving circularity
o Setting debt paydown
o Iterating
• Forecasting a more complex debt structure
• Modelling a debt waterfall
o Using “max”, “min” and “if” functions to model a debt waterfall
Modelling: delegates forecast a debt pay-down schedule for their case study
Session 6: cash flow
• Modelling the cash flow statement
• Key linkages to other statements
• Presenting the cash flow statement
• Forecasting cash flow to equity
• Forecasting unlevered cash flow
• The link to valuation
Modelling: using their model, delegates forecast levered and unlevered free cash flow
Session 7: 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: delegates complete a new sheet within their model - something that contains key outputs and credit statistics and is quickly and easily readable
Session 8: scenario analysis
• What scenarios make the most sense?
• How can we structure the model to run those scenarios easily?
• What happens to our outputs as the business is stressed?
• How can we best present the information?
Modelling: delegates develop a suite of scenarios for their model, setting the model up so that it contains a full record of scenarios and the user can switch very quickly between them
Day three – how could the model be used?
Session 9 – developing deal structure – sources & uses of funds
• Introduction to the fundamental principles of deal structuring
• Exploring “sources & uses” - a key learning concept for the course
• Concentrating on the key levers without getting bogged down in complex models
Case study: delegates develop their own deal structure for a transaction conducted by the case business
Session 10: the link to valuation
• Absolute vs. relative valuation techniques
• Defining and refining firm value: enterprise vs. equity value
• What about debt free cash free?
Modelling exercise: DCF valuation. How has a major investment bank constructed a DCF valuation? What’s wrong with the analysis?
• Relative valuation – common multiples
• Which multiples should we use?
• What are the pros and cons of different multiples?
Exercise: Relative valuation of a bid target
Session 11: determining debt capacity
• Clear, simple and concise explanation of different debt instruments:
o Senior debt
o High-yield debt
o Mezzanine
o Payment-in-Kind
• Understanding the nature of different financial instruments and risk profiles
• Modelling waterfall structures
• Estimating and optimising debt capacity
Modelling – debt structure: delegates develop a debt structure for the case study and start to flex the structure within given constraints. How much debt could the business support? How big a target could it contemplate acquiring? What impact does changing the debt structure have on debt capacity?
• Key considerations for debt holders - keeping finance providers happy
o Typical covenant tests for a bank
o Conditions of default
o Covenant trends
o Trends across different businesses
o Typical tests employed by rating agencies
Exercise: - delegates consider how S&P would rate debt in a real business
Session 12 – structuring equity
• The nature of equity instruments used in buy out structures
• The different risks and rewards accruing to different parties
• Key drivers for equity investors
• The impact of loan stock & preference shares
• The impact of mezzanine
• Iterating to optimise rewards to key participants
Case study: delegates iterate with a “back of the envelope” deal structure to optimise returns
Session 13: more advanced modelling topics
• Modelling revolving credit facilities and more complicated debt structures
• How could we model debt instruments?
• Modelling tax losses
• Modelling returns to debt and equity providers
• What can we conclude about good modelling practice?
(Note: time available for each topic on day 3 will depend on the progress made by participants on days 1&2)
Course pre-requisites
• Familiarity with basic Excel functionality is assumed on this course (e.g. formatting cells, inputting basic formulae)
Course conclusion: best practice in financial statement modelling
• Delegates who are able to model, forecast and interpret financial statements
• A set of simple and clear Excel models – for future reference, providing a platform for further financial statement modelling endeavours
• Participants who fully understand the principles of good model design and have first-hand experience of creating a model structured around in-house best practise guidelines
What others say
• What others have said about this workshop methodology:
“Thanks for your time and patience. I discovered that it is not so difficult at all to do it myself. Thanks for helping me overcome that hurdle.”
Senior Vice President, major international bank
“Thank you for making the course so interactive”
Export and trade credit analyst
