Many of the articles on The Footnotes Analyst include embedded excel models. These are either the main focus of the article, such as the DCF valuation model that illustrates two approaches to including capitalised leases, or to illustrate a particular analytical issue, such as the impact that supply chain finance may have on reported cash flow and leverage.
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 free download.
Discounted cash flow valuation
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
This DCF model is designed to illustrate how trade payables and trade receivables can be treated as either a financing claim and non-operating asset, or alternatively as components of net operating assets. Each method should give the same result (assuming the input data for each model is consistent). In practice, only one approach is necessary, but understanding both methods will help in better understanding the principles and application of DCF valuation, and make it less likely that inconsistencies are inadvertently embedded in your valuation models.
Please enter your email address to receive an excel version of this model
It is important to separate operating and financing cash flows when deriving DCF models. Operating flows determine enterprise value and changes therein, financing flows affect the allocation of that value between claimholders. This model illustrates operating and financing flows arising from new investment that is funded by new equity finance, and the resulting pre-money and post-money valuations.
Please enter your email address to receive an excel version of this model
Residual income valuation models must be based on forecast financial statements that follow ‘clean surplus’ accounting to produce a valid equity valuation. This model illustrates the clean surplus requirement using the example of foreign currency translation differences that are recognised in other comprehensive income (OCI). It also illustrates the equivalence of DCF and residual income valuations.
Please enter your email address to receive an excel version of this model
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
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
This model illustrates the valuation date adjustments that are necessary in DCF models when the valuation date does not coincide with an accounting year end. It shows how to roll-forward values from the beginning of the first forecast period to the valuation date, when to apply the enterprise to equity bridge, and how to derive a 12-month price target.
Please enter your email address to receive an excel version of this model
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
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’.
Please enter your email address to receive an excel version of this model
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
Other valuation technique models
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.
We have produced a revised target EV multiple model that features additional disaggregation of growth. The links below are to the revised model (which includes both the original and revised versions) and related article. To see the original article click here.
Please enter your email address to receive an excel version of this model
The relative value of stocks may appear to differ, depending on which valuation multiple is used. This model shows how valuation multiples EV/EBITDA, EV/EBIT, EV/NOPAT and P/E are linked, and how they can be reconciled through a series of adjustment factors. Understanding these factors can help in selecting and interpreting valuation multiples used for relative valuation.
Please enter your email address to receive an excel version of this model
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.
Please enter your email address to receive an excel version of this model
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
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
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
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
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
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
Equity analysis examples and illustrations
This simple model illustrates the 4 alternative methods of accounting that may be applied to commodity purchase contracts and related derivatives. The objective is to show that, although the aggregate impact on profit is the same (and equal to the cash flow), the timing of the expense recognition and how the expense is described is very different depending on the method applied.
Please enter your email address to receive an excel version of this model
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
Under IFRS 17, the timing of recognition of profit from an insurance business, and its analysis between the insurance service result and net financial result, is significantly impacted by the illiquidity premium. In this model we illustrate these effects for a portfolio of annuity contracts. In the related article we explain why this matters for the valuation of insurance stocks.
Please enter your email address to receive an excel version of this model
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
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
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
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 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
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
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.
Some of the models may take a few seconds to load