COURSE AGENDA

• Model Design & Best Practice

For the model to effectively achieve its analytical objectives, and to be robust and adaptable, care needs to be exercised in both the planning of the model and the way in which the model works. All models are built for the purposes of analysis, and therefore the single worksheet that sits on top of the model and where the user of the model spends all of their time – the Analysis worksheet is critical.

• The Four Types of Financial Modelling

The 4 types of financial models are built in a fundamentally different way. This session explains the reasons why and illustrates the 4 different types of Analysis worksheet that results.

• Modelling Layout

Exercise – The first practical exercise introduces a range of techniques to start the layout with best practice. It involves Sheet Grouping; Array Formulae; Data Validation; Index & Match.

• Valuation Models

All corporate decisions involve a comparison of the cost/price of the decision versus the decision’s value. Valuation models are therefore fundamental to all analyses. However, because the value is in the future cashflow which in turn is not capable of precise determination, value is a range – and skillful application of Sensitivity Analysis is required to identify that range. We start with an overview of the ‘moving parts’ in valuation and then we will build the Analysis Worksheet.

Exercise – Building the Sensitivity Analysis worksheet on a valuation model to determine the value range.

• Pricing Models

Whether it is seeking to establish the market’s appetite for an IPO, or assessing the price that should be paid for the acquisition of an asset or a company, we will need a model that creates a database of market observations, and then subjects that information to data manipulation – i.e. we need an Analysis worksheet that can sort by a range of criteria. Once again we start with an overview and then build the Analysis worksheet.

Exercise – Building a Pricing Model, including the data manipulation Analysis worksheet.

• Breakeven Analysis

This is the reverse of sensitivity. In sensitivity, the input is altered so that changes in the outputs can be observed. With Breakeven, various outputs are set to desired levels and the input that would create those outputs is identified. The process is automated with macros.

Exercise – Building a Breakeven Analysis worksheet.

• Model Review

Any analysis performed with a model is only as good as credibility of the model itself. Upon receiving a model we need to quickly and reliably test the integrity of the mode, and therefore its reliability, from both a business-logic standpoint and its excel implementation

• Volatility Models

In highly stressed financings – where there is little margin for error (e.g. leveraged finance, limited recourse finance; venture capital, and large scale M&A), then critical to the analysis is the volatility of the cashflow. The Base Case never kills, but then the Base Case is not what usually happens. It is Volatility that ‘kills’ – when events other than the Base Case occur. The method of analysis is Scenario Analysis, but for this to be meaningful, the underlying model has to be built in a certain way – to permit volatility to be accurately quantified under a range of possible scenarios.

Exercise – Modelling Operating Costs in a dynamic way.
Exercise – Building a Scenario Analysis worksheet.

• IRR and NPV

One of the worst-taught areas in corporate finance is the correct calculation of IRRs and NPVs.  Two functions in excel that should never be used are IRR and NPV.  Excel now has the technology to make the calculations accurately and correctly.

Exercise – Implementing the IRR calculation on a model.

Exercise – Implementing the NPV calculation on a model.

• Mergers & Acquisitions

M&A provides a useful context to discuss and illustrate the various issues relating to financing.

Exercise – processing debt capacity calculations.
Exercise – debt sculpting to a projected cashflow.
Exercise – the P&I financing worksheet.
Exercise – the straightline financing worksheet with sweep and pricing grid.

• Leveraged Finance

The additional complexities of a leveraged acquisition are the multiple tranches, the cashflow waterfall, and the lockup covenants triggering payment blockage. A particularly useful model is one which permits changes to the types and characteristics of the overall financing to analyse serviceability and price options.

Illustration – walk-through of an LBO model.

• Project Finance Modelling

Construction phase

Exercise – implementing the Construction worksheet

Financing

Exercise – implementing several different styles of financing worksheet – annuity, straightline, sculpted to ADSCR, sculpted to LLCR, cash sweeps.