Day 1 and Day 2

Session 1 – Revision of Principles of Financial Modelling

• Best financial modelling practice
• Overall structure of the model
• The need for flexibility, and the required model structure to accommodate change
• Separation of inputs, calculations and outputs
• Use of flags for control of timing factors
• Multi-sheet formulas and nested IF statements
• Switches to allow options to be selected, and use of choice boxes
• Use of the corkscrew technique
• Check balances and error controls, use of conditional formatting
• Ease of hand-over to / use by another user
• Exercise – creating a cashflow from an assumptions input sheet with built-in flexibility

Session 2 – Review of Financial Functions in Excel

• Revision of financial mathematics and discounted cashflow principles
• Review of the most commonly-used financial functions:
  • PV, FV and financial maths functions
• Statistical functions in Excel
• Depreciation functions in Excel
• Date and Time functions in Excel
• Logical functions – using TRUE, FALSE, IF, AND & OR in combination
• Lookup and Reference formulae: Lookups, OFFSET and MATCH formulae
• Using INDEX and MATCH together
• Error trapping functions
• Exercise – use the newly-learned functions to analyse data flexibly

Session 3 - Modelling Taxes

• Distinction between value-added taxes, customs duties and corporate tax on profits
• Recognition of revenue for tax purposes
• Tax treatment of costs
• Non-deductible costs
• Interest deductibility restrictions
• Capital allowances and depreciation
• Tax loss carry-forwards and how to model them
• Restrictions on use of tax losses
• Example - Review of an example of tax modelling for an investment project
• Exercise – add corporate tax costs and payments into a pre-prepared income statement and cash flow model

Session 4 – More Advanced Cashflow Analysis

• Brief revision of:
  • Time value of money
  • WACC
  • Capital Asset Pricing Model
• Concept of funding from sequential sources
• Modelling debt drawdowns, headroom limits and constraints
• Modelling debt repayments in a hierarchy
• Dividend distributions under constraints
• Cash flow metrics: NPV, IRR, XIRR, XNPV, MIRR
• Exercise – create a cashflow forecast drawing multiple sources of funding, repaying out of future cashflows and calculating returns

Session 5 – Modelling the Balance Sheet

• Link between cashflow and income statement
• Key balance sheet items and the assumptions required to be made
• Non-cash items: depreciation, deferred tax
• Use of existing figures & opening balance sheets
• Ensuring the result always balances and always makes sense
• Exercise – create a balance sheet from cashflow and income statement figures, with flexible assumptions

Session 6 - Model Auditing

• Use of the formula auditing toolbar
• Checks, totals and error reporting
• Logic trees & flowcharts
• Non-Excel tools available
• Exercise – from a given model, audit and uncover errors and inconsistencies

Session 7 - Using the Model for Valuations

• 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
    • Transaction Comparables: Exit multiples
• Exercise – from a given cash flow and balance sheet, calculate valuations on different bases

Session 8 - 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

Session 9 – Wrap-Up

• Overall review
• Key points to re-iterate
• Introduction to further reading and exercises

Final questions and issues to discuss

Our Tailored Learning Offering

Do you have five or more people interested in attending this course? Do you want to tailor it to meet your company’s exact requirements? If you’d like to do either of these, we can bring this course to your company’s office. You could even save up to 50% on the cost of sending delegates to a public course and dramatically increase your ROI.

If you want to run this course at a location convenient to you or if you want a completely customised learning solution, we can help.

We produce learning solutions that are completely unique to your business. We’ll guide you through the whole process, from the initial consultancy to evaluating the success of the full learning experience. Our learning specialists ensure you get the maximum return on your training investment.

We have a combined experience of over 60 years providing learning solutions to the world’s major organisations and are privileged to have contributed to their success. We view our clients as partners and focus on understanding the needs of each organisation we work with to tailor learning solutions to specific requirements.

We are proud of our record of customer satisfaction. Here is why you should choose us to help you achieve your goals and accelerate your career:

• Quality – our clients consistently rate our performance ‘excellent’ or ‘outstanding’. Our average overall score awarded to us by our clients is nine out of ten.
• Track record – 10/10 of the world’s largest banks have chosen us as there training provider and we have delivered training across the largest banks and have trained over 25,000 professionals.
• Knowledge – our 100+ strong team of industry specialist trainers are world leading financial leaders and commentators, ensuring our knowledge base is second to none.
• Reliability – if we promise it, we deliver it. We have delivered over 25,000 events both in person and online, using simultaneous translation to delegates from over 99 countries.
• Recognition – we are accredited by the British Accreditation Council and the CPD Certification Service. In an independent review by Feefo we scored 4.2/5 on service and 4.7/5 on Coursecheck