Personal tools

Course Outline

General Excel skills review and refresher             Download Course Brochure

This session will review various features of excel, in the context of manipulating data as opposed to application in problem-solving.

For some it will be a refresher, for others a skills enhancer in preparation for the use of the features in the practical model-building exercises throughout the remainder of the course.

Tools covered

Naming conventions and methods, user forms, simple macros, locating hidden work, transposition, concatenation, array formulas and array functions, subtotals, filters, pivot tables, data tables, data validation, Goal Seek, conditional IF, conditional formatting, hyperlinks, audit, custom views, lookups, charting results and summary worksheets

Model design fundamentals

  • Identifying the purpose and mode of use of the model
  • Designing the analysis worksheet
  • Planning the logic flow and the interface of the worksheets
  • What causes models to be slow
  • Actions to speed up models that are unacceptably slow
  • Best practice issues
    • Consistent timelines; consistent formulae; hard coding; organisation and accessability of model inputs; organisation of summary outputs; NW to SE workflow; organisation of worksheets within a workbook; maintaining a log – version control; naming conventions, formatting conventions
  • Circular references
    • Which are permissible and which not
    • Work-arounds if the model needs to be audited
  • Macros
  • Editing, to minimise complications
  • Uncollapsing, navigation, formatting, printing, break-even analysis

Valuation models

  • A quick refresher of theory - risk, FCF, ROC
  • Price versus value
  • How to treat risk within the model
  • Modelling logic - planning a valuation model
  • The importance of separating volume and price drivers
  • Implementation of sensitivity to determine the value range
  • Common errors - the treatments of
  • Inflation, capital expenditures and tax
  • The use of generic valuation models

Exercise: Cashflow forecasting

The implementation of cashflow forecasting in the context of a valuation model

Transaction structuring models

  • When are transaction structuring models required?
  • The importance of quantifying cashflow volatility
  • Understanding the origins of corporate cashflow volatility - company dynamics
  • How the model need to be built to capture the volatility accurately
  • Suggested layout of the worksheets within the model
  • The design and implementation of scenarios
  • Generic models versus modular models
  • Methods of structuring the volatility once quantified
  • The modelling of currency exposures in the base case model
  • Implementing schism risk and divergence risk
  • Illustrations

Exercise: Implementing a macro-driven worksheet to summarise the outputs across a range of key performance indicators for a range of scenarios

IRR and NPV

  • The defects of the IRR and NPV functions in excel
  • Comparing and contrasting IRR, MIRR, XIRR and XMIRR
  • Which method of calculation should be used in different types of model
  • The importance of semi-annual timelines for structured financings
  • Comparing and contrasting NPV, EXP and XNPV
  • Illustration of implementations

Exercise: Correct implemention IRR in a semi-annual model

Exercise: Modelling currency exposures and implementing scenarios

Exercise: Various different methods of implementing scenario analysis

Statistical probability analysis

  • When should simulation models be used
  • The limitations of such models
  • Explanation and illustration of how simulation models are built
  • Break-even analysis as an alternative aproach

Exercise: Implementation of break-even analysis

Receiving models from external sources

  • Model review versus model audit
  • The sequence of steps in the model review process
  • The architecture of the model - the interaction of the worksheets
  • The use of auditing software - illustrated
  • Creating a logic tree - a logic flow diagram
  • The 'trunk' of the model versus the 'branches'

Exercise: Implementation of model review

Financing

  • Different amortisation structures for different types of transaction
  • The modelling of interest expense without circularity

Exercise: Implementation of P&I financing worksheet

Exercise: Implementation of straightline worksheet with cash sweep

Examining other modelling types

  • Pricing models
  • The objectives of the model
  • The analytical adjustments that need to be made to the data
  • Designing and structuring the model
  • Examples of analysis worksheets for pricing models
  • M&A models
  • The sequence of analytical steps
  • The debt capacity calculations
  • Issues in valuing synergies
  • Modelling the compliance with financial assistance laws
  • The consolidation model
  • Leveraged Finance models
  • Exit timing and exit multiples
  • Modelling multiple tiers of financing interactively
  • The effect of leverage on the bidding price
  • Why LBOs can out-bid trade buyers using DCF valuation
  • Illustration of the effect

Exercise: Implementation of analysis worksheet of either pricing, LBO or M&A (as relevant to the participants attending)

Keep updated with the latest news and happenings  Follow us on Linkedin  Follow us on Twitter  Featured speaker presentations  Watch event highlights and exclusive interviews  Google+  Flickr-Informa Australia