21 Nov 2014

Financial Modelling: Model Design

Best Practice

Ultimately, the best practice is to invest time, thinking about a model before it is built, focusing on what it is needed for and what is the best way of structuring the model in order to achieve its objectives. The more time spent thinking about a model at the outset will save time later in the production process; remember that computers do not think, so we have to do the thinking for them. Once the objectives of the model have been thought about and the structure of the model set, the next most important rule to bear in mind when building a model is to keep it as simple as possible! Ask the following questions constantly when building any financial model:

Although a model will always reflect the way that the designer thinks, there are a few steps that can be taken to ensure that somebody else could use, and improve the understanding of, any model. Utilising these steps will also help to minimise errors:

12 Nov 2014

Financial Forecasting & Modelling


In order to value a company or understand its credit position, it is necessary to have a view about its future profitability, financial position and growth.

The assessment of future profitability, financial position and growth is based on assumptions about how fast a company will increase its sales, how much it will need to invest in order to be able to produce its goods, and how efficiently the company will be at turning sales into profits. 

Making assumptions is not an exact science: assumptions are reasonable or unreasonable, aggressive or conservative. On the other hand, producing an integrated set of financial statements based on agreed assumptions should only give one potential outcome: the answer is either right or wrong.

In building any financial forecast model, it is easy to become too engrossed in the details and lose focus on the aggregate result. It is often the case that once a model is built, it is better to improve a valuation by matching a decline in future ROCE (Return On Capital Employed) against a reduction in competitive advantage, rather than precisely modelling accounts receivable in 20 years’ time. There is value in thinking about how to go about forecasting and what level of detail is required in the model.

This manual covers how to build a fully integrated Excel model to forecast the financial performance of a company, based on reasonable assumptions and on the correct mechanics. It discusses how to achieve realistic forecasts and provides guidance in terms of model design, structure and formatting best practices.

7 Nov 2014

Types of Valuation

Standalone Valuation

As discussed above, there are two main types of valuation used to value a company, either an absolute valuation tool which attempts to arrive at a nominal valuation, or a relative valuation tool, which uses the value of other companies in the market to determine the value of the company being valued. The two types of tool and approaches have been neatly summarised by the following quotes by two successful investors:

An absolute valuation approach produces a nominal valuation, i.e. the value in pounds, dollars or euros that the company’s shares should trade at if fairly valued. This type of valuation is often called an “intrinsic value”, because it is highly unlikely that the company’s shares do trade at this value. An absolute valuation approach assumes that the market is inefficient at valuing companies. 

A relative valuation approach produces a valuation based on how other similar companies are valued by the market and assumes that the market, in aggregate, is correctly valued but that inefficiencies in valuation exist across the market or individual sectors. Both absolute and relative valuation tools are generally used to value a company on what is called a standalone basis, i.e. the company being valued is being valued in isolation.

Market Practice and Combination Valuation

In practice, a combination of absolute and relative valuation tools would be used to value a business. Often an absolute valuation tool such as DCF would be used as the first pass at valuation and then a cross-check would be performed using relative valuation tools such as multiples to gauge how the market was valuing similar companies. Other absolute valuation methodologies would be Economic Value Added (EVA) or Adjusted Present Value (APV).
In addition to valuing companies on a standalone basis, in practice valuation tools are often used that assume an element of corporate activity will take place. This can take the form of a comparable transaction (comptrans) valuation, where recent corporate transactions are used as a benchmark to determine the value of the company being valued. The assumption made is that because other companies have been acquired at certain valuation levels that these transaction values are relevant to the value of the company being valued. Corporate activity can also be incorporated by way of a combination valuation, where the target company’s value in a transaction will incorporate elements of value that would only arise as the result of a transaction, such as the synergy benefits from combining two companies.
Other specialised valuation tools include Leveraged BuyOut analysis (LBO) where a valuation based on the restructuring of a company’s capital structure in the form of a private equity, venture capital or Management BuyOut (MBO) transaction is carried out.

Key learning points:

  1. Standalone valuation approaches include absolute valuation and relative valuation. An absolute valuation approach assumes that the market is inefficient and a relative valuation approach assumes that, although the market in aggregate is efficient, inefficiencies exist across the market.
  2. Other forms of valuation include comparable transaction valuation, combination valuation and leveraged buyout valuation.

5 Nov 2014

Cleaning the Numbers – Arriving at Operating, Financing and Non-core Items

Unless privileged access to inside information on a company has been given, the information available to produce a valuation will be limited to public data, which primarily means the historic report and accounts plus other regulatory filings. Historic information is often used to forecast forward trends in profitability and cash flow that form the basis for a valuation or to compare market valuations across sectors. However, before the information in historic accounts can be used to compare or value companies, it is necessary to ensure that the information being used is relevant for the valuation being produced.

Cleaning the Numbers

Historic accounts are produced for a wide variety of interested parties, from employees of the company through to investors and industry regulators. Accounts therefore include a lot of “noise”, items that are not strictly relevant for valuation or analysis. Accounts are also required by law to include all the transactions that have taken place during the period, whether they relate to the underlying activities of the company or not. It is therefore necessary to decide which items included in the accounts relate to the underlying performance of the company (operational items), which items are being used to finance the company (financing items) and which items do not relate to the company’s underlying activities (non-core items). This exercise must be performed to get to any starting point for comparison or valuation.

What is required is an exercise to “clean up” the accounts, using knowledge of the company to identify the underlying profitability and cash flow of the business. A relative valuation, such as a comparative company (“comps”) analysis where comparisons across a sector are made, will allow us to produce an analysis of the group of companies where the accounting data is truly comparable. In an absolute valuation exercise, the clean ongoing profitability and cash flow calculated can be used as the starting point or “base camp” for a forecast that will allow the extrapolation of historic trends to produce an income and cash flow statement if there are no detailed forecasts.

As well as the annual report and accounts, data from stock market filings such as the 10K or 20F (US filings) are available. A 10K filing is filed by quoted US companies and is similar to an annual report, but covers much more detail about the business. As such, it is a very good source of information for making adjustments. A 20F filing is completed by a non-US company that has securities listed in the US (say through an ADR, an American Depository Receipt) and the 20F covers much the same information as a 10K. Broker forecasts can be used for adjustments if the broker has a particular insight into the way a company operates and Bloomberg is generally used as a source of market information.

Income Statement Adjustments

There are three main types of adjustment to the income statement:

Adjustments to Operating Income

Fortunately, under IFRS there is now complete disclosure of the continuing and discontinuing operations of the company. The decision to sell is the determinant of the split between the two, so as soon as management decides to sell part of a company its results must be reported separately. Discontinued business performance can therefore be excluded when making adjustments. 

The management of a company will often try to massage various items to improve the reported profitability of the company. Losses, which reduce profitability, will be treated as exceptional and excluded from highlighted performance lines or ratios (such as net income, EPS, etc.). Gains will be classified as part of the normal operations and therefore boost profitability. Adjustments must be made for this manipulation to get back to core, repeatable profitability. 

Goodwill is another item for which an adjustment must be made. Goodwill impairment now reflects over payment for an acquisition/asset and does not reflect the economic wasting of an intangible asset (which is now separately reported under IFRS). If goodwill impairment has been included in EBIT, it must then be removed.

Adjustments to Financing Items

There are various accounting issues that may have to be adjusted for in financing items, but the main items requiring adjustment would be pension costs and leases. Pension costs as reported will sometimes include the interest and investment return element of the pension cost, which are financing items. As pension costs are often reported in the EBIT number (i.e. pre-financing costs), if the pension cost includes an interest/investment return element, this should be removed from EBIT line and included in financing costs line. Operating lease expenses not capitalised may also be included in EBIT. These will include the interest portion of the lease payment, which again should be separated out of operating costs and included in financing costs.


If any of the items that have been included as an adjustment have a tax effect, this must be adjusted for. One of the key problems with reported numbers is that sometimes pre-tax items have a tax element to them. For example, gains or losses on sale or restructuring costs may be included net of tax, but above the tax charge line in the income statement. Unless the amount of tax is disclosed in the notes to the accounts, it is often impossible to figure out what the tax charge would have been, so it is best not to make any adjustments. This will lead to a slightly incorrect EBIT number if the item is included above EBIT, but the amounts involved are usually immaterial. If the amount of tax is disclosed in the accounts, then both the EBIT, tax charge and the net income figure can be adjusted.

What type of item is taxation? The answer is a bit of operating and a bit of financing, as tax has to be paid on profits, but the financing charge relating to interest is deducted from profits to calculate the income statement tax charge. In practice, tax is treated as an operating item when producing a valuation, but the tax charge in the income statement is re-calculated to strip out the effect of interest. Operating returns are calculated as EBIT − Marginal tax (calculated as EBIT × Marginal tax rate), the marginal tax cash paid for each £1 or $1 of operating profit earned. In terms of the balance sheet, the current tax asset/liability is treated as an operating item but the deferred tax asset or liability, being an estimate and a provision which may or may not arise, is treated as a financing item.


Key Adjustments Summary – Income Statement

There are three main areas where adjustments may be made to an income statement, the main one being one-off items. This will include restructuring costs, that will not be part of operating revenues/costs going forward, impairment of goodwill, which reflects overpayment for goodwill and is therefore not really an operating expense (more of a financing cost) and finally profit/loss on sales of assets, which again is not an operating income/cost (as depreciation should reflect the effect of changes in value of assets).

Adjustments for accounting issues mainly relate to pension and lease accounting. Finally, adjustments must be made for results from discontinued operations which, by their very nature, are not part of the underlying operations of the business.

Cash Flow Statement Adjustments

The cash flow statement will not be a focus for adjustment as a historic cash flow statement is not included in a financial forecast. If the adjustment is part of a comps analysis, then the adjusted profit for the period from the income statement would be the starting point for the operating cash in the cash flow statement. This way, the operating cash flow incorporates all income statement adjustments. The only other adjustment that may be required is the removal of any cash flow from non-core assets, which would be included in financial income.

Balance Sheet Adjustments

In the balance sheet, the key adjustments to be made are the separation of non-core assets from underlying capital. The three main non-core items are associates, asset/liabilities held for sale (now separated out under IFRS) and investments, in effect anything that is not consolidated or that is identified as for sale. Joint ventures are difficult to judge and should probably be included in noncore assets, but this will depend on the terms of the joint venture (buyout clauses, etc.).

Generally, associates (or any investment that is not consolidated) would be treated as a
non-operating item. As such, any income or cash flow from associates is not included in underlying returns and the investment in associates is excluded from the core assets in the balance sheet. If associates are included in the core operations of the business (e.g. if there is a very strong trading relationship with the associate), then associate income would be incorporated in operating income and the investment in an enterprise value calculation. Depending on the degree of disclosure (it helps if the associate is quoted), the associate results can be incorporated to varying degrees of detail.

Key learning points:

  1. Historic accounts include various items that do not relate to the underlying performance of a company. Historic accounts must therefore be “cleaned” up before they are used to compare companies or forecast forward returns.
  2. The main adjustments made to the income statement ensure only continuing items are included, that one-off items are excluded and that accounting anomalies such as pension costs are adjusted for.
  3. The main adjustments to the balance sheet relate to non-core assets being removed.

3 Nov 2014

Matching Returns and Capital – Operating, Financing and Non-core Items

As discussed above, returns and capital are used in various valuation approaches to produce either an equity or enterprise valuation. Depending on the approach used and the valuation produced, there will be a number of adjustments to be made to the valuation to arrive at the estimated value of the share price of a company. Assuming that an operating enterprise value is produced, this would have to be adjusted as follows:

In calculating an operating enterprise value, only the operating returns and capital would be used.The value for non-equity finance would be calculated using the financing returns and capital and the value of the non-core assets would be derived from the non-core returns and capital. What is crucially important is that the correct returns and capital items are used in each part of a valuation, e.g. returns from non-core assets should not be used to calculate the value of operating assets, etc.

When producing a valuation therefore, the elements of a company’s returns and capital must be broken down into three key areas:

A calculation of enterprise and equity values will start by analysing the operating and financial items in a set of accounts. Operating items relate to enterprise value, the value of the entirety of the operations of the business, while financing items are deducted from enterprise value to get to equity value. Non-core items are extracted from the accounts and valued separately. As discussed, an example of non-core items would be Nestlé’s investment in L’Oréal, which is a non-core investment (food vs. cosmetics).

Operating Items

Within a set of accounts, operating items in the income statement include EBIT (earnings before interest and tax) and any items above EBIT (profit before interest, which is paid away to non-equity financiers). In the cash flow statement, the operating items are detailed in cash flow from operations (again before financing payments/receipts) and in the balance sheet, operating items are the net operating assets of the company.

Looking in a bit more detail, this means the return items that are operating items include revenue, EBITDA (Earnings Before Interest, Tax, Depreciation and Amortisation) and EBIT, and the capital items include operating assets such as fixed and intangible assets and working capital (current assets less current liabilities).

Financing Items

In a set of accounts, financing items in the income statement include items such as interest in the income statement (to get to profit after interest, which is available to equity holders), cash flow from financing and investment in the cash flow statement and non-equity finance in the balance sheet.

This means financing items will include interest payable and receivable and other financial
income/expense from the income statement. These would be deducted from operating returns to get to equity returns. In the balance sheet, financing items include net financial debt, provisions and minority interests.

Financing items in total make up non-equity finance (as detailed above) and in each area would be defined as follows:

It is worth commenting at this point on the definition of net financial debt. The simplifying
assumption made above is that any cash (or marketable securities) held by a company is not required to finance the operations of the company. However, there will be occasions when a company does require cash to finance its operations, e.g. a retailer that requires a cash float for its tills or a travel company that is required to post a bond as part of its ABTA membership. If cash is identified that is not surplus but an integral part of the operations of the company, then it should not be deducted from financial debt but instead included in working capital as part of the operational assets of the company.

Non-core Assets and Liabilities

Non-operating or non-core items relate to unusual items or investments that the company has made that are not part of the underlying operations of the business. They include assets that are no longer part of the business (discontinued operations, disposed assets) or investments that do not relate to the company’s ongoing operations, e.g. Nestlé’s investment in L’Oréal.

In the accounts of a company, non-core assets relate to anything that is not part of the underlying returns or capital of the company. In the income statement, this would generally include restructuring charges (unless recurring), impairments and profits/losses on disposal of assets/businesses. Disposal profits or losses may be operational for some types of company, e.g. for a car rental business (constantly turning over its vehicle fleet). Under IFRS, assets and liabilities held for sale are separated out in the balance sheet, so these are specifically identified as being non-core. For the purpose of this manual, it is assumed that associates (or any investment that is not consolidated) are non-core. Investments in unrelated businesses are always non-core and joint ventures are generally treated as non core, unless the joint venture is proportionately consolidated. 

The main problem with non-operating or non-core items is the amount of judgement that has to be applied to decide what is non-core, which can be difficult to determine. The key issue is consistency. If, for example, associates are treated as core, then any associate income would be included in operating returns and the value of the associate would be included in operating capital.

Key learning points:

  1. Returns and capital can be broken down into operating, financing and noncore items.
  2. Operating items relate to enterprise value and include returns such as EBITDA and capital.
  3. Financing items relate to non-equity finance and if deducted from operating items, produce equity returns or capital.
  4. Non-core items do not relate to the core activities of the business and should be extracted.