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.


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.

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.

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.

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.

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 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.

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.

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.