Analytical models

Many of the articles on The Footnotes Analyst include embedded excel models. These are either the main focus of the article, such as our target EV multiple calculator, or to illustrate a particular analytical issue, such as the impact that a change revenue recognition accounting may have on subsequent revenue growth.

This page contains a short description of each model with links to the model itself and to the article where you can find more explanations. All models are also available for download.

Remember that all models are a simplification of the real world. They help in better understanding practical issues and to estimate real world effects, but they are just models. Always be aware of the assumptions, simplifications and omissions.


DCF valuation: Financial leverage and the debt tax shield

Consistency is crucial when it comes to DCF valuation. Combining the wrong cash flow with the wrong discount rate, not consistently allowing for leverage changes, or combining an enterprise to equity bridge with an inconsistent cash flow, will quickly produce incorrect valuations. This model shows how the three main approaches to DCF valuation should all give the same result – but only if done consistently and correctly. The model also illustrates the leverage and tax shield calculations we discussed in the related articles: ‘Valuing the debt interest tax shield’ and ‘Equity beta, asset beta and financial leverage’.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

The calculation and analysis of equity beta

This model uses the stock history data that is available in Excel to calculate an equity beta and related risk statistics for a selected stock, based on a selected index. You can modify the time period and return frequency inputs for the beta factor calculation. The model also provides confidence interval data for beta and each component, together with charts and further analytics to help you investigate the historical risk profile for a company, and inform estimates for current and forward looking risk.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

Alternative approaches to loan impairments

This model is designed to illustrate the three different approaches applied under US GAAP and IFRS to recognise provisions for credit losses (loan impairments). The predominant methods applied in US GAAP and IFRS differ, but both create a ‘day 2 loss’ effect that can distort performance metrics, particularly following an acquisition or where a bank has significant growth in a loan book. Both systems apply an alternative approach to a subset of loans that, in our view, better reflects the economic of lending.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

Discounted cash flow versus residual income

Residual income (economic profit) based valuations are mathematically equivalent to discounted cash flow and should always give the same result – assuming consistent input assumptions are used. In this model, we demonstrate this equivalence and illustrate a common mistake in residual income valuations regarding the calculation of a terminal value at the end of an explicit forecast period.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

DCF value analysis

A DCF valuation is commonly presented as the sum of the present value of cash flows over an explicit forecast period plus the present value of a terminal value at the end of that period. However, this is not the only, or the best, way to disaggregate DCF values. A better approach is to analyse value based on the periods where that value is created and not when the related cash flows happen to materialise. In our DCF value analysis model we illustrate this alternative approach.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

Supplier finance arrangements impact on cash flow and leverage

Cash flow, leverage and working capital metrics may be affected if a company engages in supplier finance (reverse factoring). The impact depends on whether the arrangements provides finance for the purchaser or its suppliers and further depends on how the arrangement is reflected in financial statements. Operating cash flow in particular can be distorted and may need adjustment before used in analysis or DCF valuations. This model shows the impact of supplier finance on key metrics and how these can be adjusted.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

DCF terminal value calculations

In this model we illustrate five different approaches to the calculation of the terminal value component of a DCF valuation. Four of these illustrate versions of a perpetuity growth approach and how this can be improved by adding a return input. The final approach illustrates the application of forward priced multiples in a peer group comparable company analysis.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

Intangible adjusted return on invested capital

Many internally generated intangible assets are not recognised in financial statements. This understates reported capital and (usually) overstates return on invested capital. Immediate expensing of intangible ‘investment’ also distorts profit due to the difference between the expenditure in the year and what would have been the amortisation charge had that investment been capitalised. In this model we demonstrate how an intangible adjusted ROIC can be estimated.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

Cost of capital for a convertible bond

The cost of capital for a convertible does not equal the stated coupon rate. Convertibles should be analysed into their debt and conversion option components with the cost of capital the weighted average of the costs of debt and the conversion option. The cost of a conversion option is higher than the cost of equity.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

Opco-Propco analysis

Companies that use property assets in their business may adopt very different real-estate strategies. Ownership versus leasing and the choice of different lease structures can significantly impact key performance and valuation metrics. We show that separating the operating and property components, using ‘Opco-Propoc’ analysis, improves comparability.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

Pension liabilities in enterprise and equity FCF DCF models

Defined benefit pension liabilities create leverage affects that impact both the free cash flow and discount rate components of DCF valuations. It is important that these effects are dealt with in a consistent manner and taking account of whether the model is based on enterprise or equity free cash flows. This model illustrates four possible approaches and includes detailed calculations of free cash flow, equity and asset beta factors, and costs of capital.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

Forecasting the stock-based compensation expense

For most companies, stock-based compensation is a ‘sticky’ expense that is only indirectly or partially affected by current period changes. Limited disclosure in financial statements makes forecasting this expense a challenge. Use this model to derive a forecast that takes into account past SBC grant growth, the forecast change in the value of new grants, the vesting period and the expected change in forfeit rate.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

Option adjusted valuation of debt and equity claims

The model uses an underlying option pricing methodology to price debt and equity claims on enterprise value. It illustrates how changes in enterprise value are shared between different claim holders and how this sharing varies depending on factors such as leverage and business risk or volatility. Three different claims are included: debt, common equity shares and share warrants (call options on the equity).

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

Calculate forward priced multiples

Valuation multiples based on forecast profit further in the future such as the year 3 forecast can be useful in achieving greater relevance and comparability. However, to make these multiples useful one needs to ‘forward price’ and allow for both the cost of capital and cash flow yield effects. This model illustrates two approaches to calculating forward priced multiples.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

DCF valuation adjusted to incorporate new lease accounting under IFRS 16

The adoption of new IFRS and US GAAP lease accounting standards in 2019 had a significant effect on the balance sheet and profit measures of many companies. However, a change in accounting does not in itself change the underlying economics and so should not affect value. But if you are using DCF many of the metrics used in your model will have changed. This model shows how capitalised leases should be included in a discounted enterprise cash flow calculation.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

Target enterprise value multiples

The model uses a value driver based approach to determine a range of target enterprise value multiples. It is the same underlying methodology as used to determine price earnings ratios in the model above. The core multiple in the model is EV/NOPAT (where NOPAT is post tax operating profit, commonly referred to as Net Operating Profit after Tax), other multiples are derived from this, based on the relationship between NOPAT and the relevant metric.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

Convert US GAAP lease accounting to IFRS

Although both IFRS and US GAAP lease accounting was changed in 2019 such that (most) former operating leases are on balance sheet under both systems, unfortunately there are significant differences that particularly the impact profit and loss and cash flow presentation. This model illustrates the differences by converting a US GAAP presentation to IFRS 16. It also illustrates how growth in lease financing impacts the extent of net income and shareholders’ equity differences.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

IFRS 16 lease accounting transition impact

IFRS 16 lease accounting was implemented for the first time by most companies in 2019. In this model we show the impact of the new accounting on both profit and loss and the balance sheet. The model provides two solutions that illustrate the impact of al least some of the transition choices that are available to companies. These choices have an ongoing effect and do not just impact the transition year.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

Price earnings ratios implied by value drivers

This very simple model uses a two stage discounted equity cash flow calculation to derive an implied price earnings ratio from specified value drivers. It illustrates how valuation multiples are closely related to discounted cash flow and how they are determined by the same underlying fundamentals. The model also demonstrates the importance of incremental return on capital in valuing growth opportunities.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.

The impact of IFRS 15 revenue recognition on forecast growth

A change in accounting policy such as that arising from the introduction of IFRS 15 revenue recognition may not only impact current reported revenue and profit but also may change forecast growth. This model demonstrates the effect where a company has contracts for which revenue is recognised over time but based on a different pattern after adoption of IFRS 15. While the example used is revenue recognition, the issue of a change in accounting affecting more than just the year of transition also applies in other situations.

DOWNLOAD THIS MODEL

Please enter your email address to receive an excel version of this model

I agree to your privacy policy and to receive email notification of future articles. You can unsubscribe any time.


Some of the models may take a few seconds to load.