Spreadsheet Modelling
- [o] Explain how to use spreadsheets to create models for predicting future dividend payments and to value common stock.
- 00:07 – Introduction: Simplified vs. Unconventional Growth Patterns
- 00:34 – When to Use Spreadsheet Modelling
- 00:58 – Spreadsheet Modelling in Practice: An Example
- 02:14 – Benefits of Spreadsheet Modelling: Scenario & Sensitivity Analysis
- 02:50 – Summary
Introduction: Simplified vs. Unconventional Growth Patterns
While multi-stage dividend discount models (DDMs) like the two-stage, H-model, and three-stage models are useful, they rely on simplified growth patterns (e.g., constant growth, linearly declining growth). These simplifications are necessary to make calculations manageable, especially in an exam context.
However, in real-world analysis, dividend growth may not follow such neat patterns. For firms with unconventional growth, spreadsheet modelling offers a more flexible and powerful approach.
When to Use Spreadsheet Modelling
Spreadsheet modelling is particularly suitable for valuing firms when an analyst:
- Has access to a great deal of specific information about the company’s future prospects.
- Can project different, specific growth rates for various future periods, rather than assuming a smooth transition.
Example: Construction Firms
A good example is a construction company that works on a few large, long-term contracts. An analyst with knowledge of these contracts can create a more tailored valuation model.
Key information would include:
- The size and profitability of each major contract.
- The expected completion dates for these contracts.
Since construction firms often recognize a large portion of a project’s profit upon completion, this can lead to “lumpy” or irregular dividend payments that are best handled in a spreadsheet.
Spreadsheet Modelling in Practice: An Example
Let’s consider a construction company with an irregular dividend forecast due to its project pipeline. An analyst builds a model by forecasting dividends year-by-year for an explicit forecast period, after which a terminal value is calculated.
Step 1: Forecast Dividends and Calculate Present Values
The analyst forecasts the dividends (Dt) for a set number of years. Then, each dividend is discounted back to its present value (PV) using the required rate of return on equity (r).
The formula for the present value of a single dividend is:
\[ PV(D_t) = \frac{D_t}{(1+r)^t} \]
Let’s assume a required return (r) of 10%.
Step 2: Calculate and Discount the Terminal Value
At the end of the explicit forecast period (Year T), we must estimate the value of all subsequent dividends. This is the terminal value (VT). It is typically calculated using the Gordon Growth Model, assuming dividends will grow at a constant long-term rate (gL) forever.
The formula for terminal value at time T is:
\[ V_T = \frac{D_{T+1}}{r – g_L} = \frac{D_T(1+g_L)}{r – g_L} \]
The present value of this terminal value is then calculated as:
\[ PV(V_T) = \frac{V_T}{(1+r)^T} \]
Step 3: Sum the Present Values
The intrinsic value of the stock (V0) is the sum of the present values of all the explicitly forecasted dividends plus the present value of the terminal value.
\[ V_0 = \sum_{t=1}^{T} \frac{D_t}{(1+r)^t} + \frac{PV(V_T)}{(1+r)^T} \]
Benefits of Spreadsheet Modelling: Scenario & Sensitivity Analysis
A major advantage of using spreadsheets is the ease with which an analyst can perform scenario and sensitivity analysis to test the valuation’s robustness.
Scenario Analysis
This involves changing multiple assumptions at once to model a specific scenario. For example, what if project completions are delayed by two years? The spreadsheet can be quickly updated to reflect the new dividend timeline and recalculate the stock’s value.
Sensitivity Analysis
This involves changing a single input variable to see its impact on the final valuation. For instance, the analyst can see how the stock’s value changes if the required rate of return (r) increases from 10% to 12%.
Summary
Spreadsheet modelling is a practical tool for applying the dividend discount model to companies with complex or irregular dividend growth patterns that don’t fit the simplified assumptions of standard multi-stage models. While it relies on computer processing for efficiency, the underlying valuation principle remains the same: the value of a stock is the present value of its expected future cash flows (dividends). The flexibility of spreadsheets is invaluable for conducting robust scenario and sensitivity analyses, which are critical components of a thorough valuation.
