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.
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.
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
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).
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
Some of the models may take a few seconds to load.