Excel over-engineering

Some might say that Excel functions are a little bit over-engineered. Like the car manufacturer that brings out a new model each year with a slightly better engine and up-rated suspension, Microsoft (bless it) has been bringing out new versions for years. The result might be a fantastically high performing machine but not one that necessarily makes it easier to get from A to B. In Excel’s case the result is an awesome product but one that can be a little difficult to navigate. A typical challenge in Excel might have four potential solutions and that makes part of the fun just working out which solution to use.

Today’s problem: picking out the right piece of data from a data set

Today we’re going to imagine something that seems like it should be a very simple Excel challenge: picking a piece of data out of a list.  We’re going to give you four solutions, tell you our favourite but, ultimately, leave it to you to make the decision about which you like.  Here are some potential solutions:

·         Sorting the data manually

·         Pivot tables

·         The Sumifs function

·         The Sumproduct function.

For most delegates on our training courses there’d be something a bit new within the list above!

Today’s challenge: picking a piece of data out of a list

Imagine a set of data.  We’ve got a first column for employee name.  We’ve got a second column for expense category (e.g. “travel”).  We’ve got a third column for amount.  It’s a long list but imagine we want to work out what one of our employees (e.g. “John”) spent on travel with multiple trips away – we want to know his total spend on travel.

What if we didn’t have all the power of Excel?

Without knowing what Excel can do for us, what we might think of doing is sorting the data (Data > Sort in Excel).  First by employee, then by category.  That would group the data for us and make it easier to see how much John spent on travel – but Excel can do better than that.

Pivot tables

Some delegates on a financial modelling course know about pivot tables and, if they’re not familiar with them, we’re happy to help them get started.  In this case, you’d need to select the data and go Insert > PivotTable in Excel.

Pivot tables are great for quickly sorting and presenting data.  They will update as your data changes, but only if you right mouse click and click “Refresh”.

For the investigation into John’s travel spend, a pivot table would represent a bit of overkill.  There are some standard Excel functions that will successfully pick out the correct data from the list.

Sumif, Sumifs and Sumproduct

Some of our course delegates know about Sumif and Sumproduct.  Sumif kind of does what it says on the tin – it adds data if it meets a certain condition.  We have multiple conditions here though – that’s why we need Sumifs.  You want to use Sumifs for multiple conditions.

Some of our course delegates know about Sumproduct – it multiplies one list of data by another and sums the result.  Fewer of our delegates know that you can build conditionality into Sumproduct.  For our investigation, we could use that to tell us quickly how much John spent on travel.

Is there a best solution here?  Who’s for pivot tables?

It’s fun trying to navigate Excel, including any over-engineering!  Some people love pivot tables – the way they will sort all your data very quickly and present it nicely.  Others though realise that some of Excel’s standard functions are perfectly competent and capable of picking out the one piece of data you are interested in and plonking it into the cell you want.

Sumifs vs. conditional sumproduct

It’s hard to differentiate between these two formulas though.  Sumifs is designed exactly to do this job – so there’s a vote for that.  But then, if you can understand how the conditional sumproduct works, and pause for a moment to consider how simple that formula ends up being, then you’d probably be forced to vote for that.

Here’s to engineering

Shall we just pause for a moment to celebrate the power of engineering and the way it brings us all the powerful products we love to use?  If that results in a little bit too much engineering sometimes – so what? It’s the small price we all end up paying for fantastic solutions! 

Excel's XNPV formula

Have you come across Excel's XNPV formula before?

It's a bit like Excel's XIRR function. To get it working what you have to do is assign dates to cash flows.

The problem with Excel's standard NPV formula.

The problem with the standard NPV formula is that it discounts the very first cash flow you point the formula to, whether you want it to discount that starting cash flow or not. If you made an investment right at the start of a project (often you would) the standard NPV formula could easily trip you up. You wouldn't normally want to discount that first cash flow.

XNPV has its own problems though

Immediately Excel's XNPV function becomes potentially attractive. It assigns dates to cash flows and, if you experiment with it, you'll find it doesn't discount the first cash flow you point it to. However, XNPV comes with a problem all its own. XNPV never discounts the first cash flow, no matter what date you have assigned to the initial investment. Even if your project is delayed and pushed out further, XNPV stays the same. Effectively the formula is assuming that the date of the first cash flow is your valuation date.

Fortunately there's a simple fix (but you have to know about it)! If you set your XNPV calculation up carefully you can easily discount the cash flows back to today or any valuation date that you choose. All you have to do, at the start of the calculation, is include a zero cash flow with your start valuation date attached.

If you click on the picture below, you can download an example where you can see all that working.

Watch out for Excel's financial functions in your modelling

All of these Excel modelling functions (IRR, XIRR, NPV and the XNPV formula) are designed to be quick and easy to use. Unless you understand exactly how they work though, there is a risk you make a mistake in applying them (and reach the wrong decision).

Adjusting for Goodwill in an Excel Financial Model

In this article we answer a financial modelling question: "how do I integrate sources and uses, debt free cash free enterprise values and goodwill in an Excel model?" This question was asked by one of our delegates on a financial modelling course recently.

Introduction: what is debt free cash free?

Debt free cash free is a term used in the valuation of businesses. The debt free cash free value is the value of a business before it incurs any debt.

Debt free cash free vs. shares value

Where a business carries debts, the DFCF value is higher than the value of the business’s shares. Perhaps it helps to use the analogy of a house which is being purchased for say 100,000 and financed with 75,000 of mortgage. The DFCF value (= the value of the underlying asset) would be 100,000. The value of the buyer’s shares in the house would be 25,000.

Debt free cash free and enterprise value

Debt free cash free is broadly equivalent to another term used in valuation: “Enterprise Value”. With DFCF and Enterprise Value what we’re trying to understand is the value of the business itself (100,000) before it incurs financing liabilities.

Debt free cash free, sources and uses and shares values

One of the occasions business valuation can become confusing is when we are examining both the purchaser’s and the seller’s perspective at the same time. Perhaps when staring at a “sources and uses” of funds table prepared as part of the imminent purchase of a business. Let’s imagine that, as before, we’re buying a business asset for 100,000 funded with 75,000 of debt. Prior to the transaction, the business already carries 65,000 of existing debt. A simplified sources and uses table is shown below:

Sources & uses of funds for purchase of XYZ business:

Sources _ _ _ _ _ _ _ _ _ Uses

25,000 buyer equity _ _ _35,000 purchase of seller’s shares

5,000 new debt _ _ _ _ _ 65,000 refinance of existing debt

100,000 total _ _ _ _ _ _ 100,000 total

In the simplified sources and uses table above, debt free cash free for both purchaser and seller is 100,000, but shares values differ for each party:

- From the seller’s perspective, the business has been valued at 100,000 on a DFCF basis free but the business carries existing debt liabilities of 65,000. The seller would expect to receive 35,000 for their shares;

- The buyer has raised a total of 100,000 to purchase the business, refinancing the 65,000 existing debt and paying the 35,000 balance to the seller for their shares. The buyer has purchased the business on a DFCF valuation of 100,000 but is left with new debt of 75,000 and therefore a face value of 25,000 for their shares.

Although DFCF valuation is the same, shares value for the seller (35,000) differs from post deal shares value for the buyer (25,000) because each party chooses to finance the business with a different amount of debt.

What about goodwill?

Goodwill is a non-cash accounting entry. On acquisition a Goodwill adjustment is made to the purchaser’s balance sheet equal to:

- The surplus of the price paid by the purchaser for the seller’s shares (35,000); over

- The accounting book value of the net assets of the business acquired (= the target business's equity as shown in its balance sheet before any deal).

As mentioned above, Goodwill is an accounting entry made upon acquisition. Goodwill is related to and calculated from information contained in the transaction’s sources and uses table but it is not a cash flow itself.

Adjusting an Excel financial model for goodwill

If we were building an Excel financial model for the acquisition of a business and wanted to integrate all the above, we would expect the model to contain:

- A sources and uses table included in assumptions for the model, just like the sources and uses table above;

- An opening balance sheet for the business being purchased;

- Adjustments to the opening balance sheet, drawing in part on information contained in the sources and uses table. Significant adjustments would relate to goodwill and the increase in new borrowings.

Let’s imagine the balance sheet of XYZ business, being purchased on a 100,000 DFCF valuation, can be represented like this:

Opening balance sheet for XYZ business:

Goodwill = _ _ _ _ __ _ _ _ _ _ _ _ 0

Other assets = _ _ _ _ _ _ _ _ _ _ 150,000

Debt = _ _ _ _ _ _ __ _ _ _ _ _ _ _ 65,000

Other liabilities = _ _ _ _ _ _ _ _ _ 70,000

Net assets = _ _ _ _ _ _ _ _ _ _ _ 15,000

The balance sheet above would have to be adjusted in the model using the following steps:

1. Subtract old net assets from old opening balance sheet = (15,000);

2. Add the buyer’s new equity contribution from the sources and uses table = + 25,000;

3. Remove the old debt refinanced = (65,000), replace it with the new acquisition debt shown in the sources and uses table = + 75,000;

4. Add goodwill = difference between net assets and price paid by the buyer for the shares = (15,000) + 35,000 = 20,000 goodwill.

These adjustments are shown below.

Adjusted post deal balance sheet for XYZ:

Goodwill = (15,000) + 35,000 = _ _20,000 (adj 4)

Assets = no adjustment = _ _ _ _ 150,000

Debt = (65,000) + 75,000 = _ _ _ _75,000 (adj 3)

Other liabs = no adjustment = _ _ 70,000

Net assets = (15,000) + 25,000 = _25,000 (adj 1&2)

The adjusted sheet has drawn on the sources and uses table in the model, adjusting for new debt and equity raised and goodwill created as part of the purchase, so that we now have a new post deal balance sheet.

Yes goodwill is only a non-cash accounting adjustment but, at the end of the process, the adjusted post deal balance sheet is clearly showing the purchaser’s equity commitment of 25,000. This is the same as the 25,000 shown in the sources and uses table and represents the difference between the 100,000 DFCF value and the post deal 75,000 new debt liabilities.

About the company: Financial Training Associates Ltd

Financial Training Associates Ltd. is a company that specialises in providing finance courses for banking, accounting, law and financial services executives. Please see our website for more information on our financial modelling course training.

Valuation modelling

Financial Training Associates Ltd answers the question: “In valuation modelling, where does the terminal value formula come from?” This question was recently posed by a delegate on an FTA Ltd financial modelling course.

1. The terminal value formula. In a discounted cash flow (DCF) valuation model, we can’t forecast forever. Even an Excel spreadsheet has a limit to the number of columns it contains! The terminal value solves this problem by answering the question: “What’s the business worth at the end of the forecast period?” In the final year of an Excel financial model you will usually see a big lump of cash (the terminal value). What we are doing in the model is trying to work out what the company we are valuing is worth at the end of the forecast period, or what it might be able to be sold for.

Many analysts are used to seeing the following formula used to calculate terminal valuation in financial modelling (see the blue box in the slide below, which comes from one of FTA Ltd’s training courses). In the formula, on the top we have next year’s expected free cash flow, divided by [discount rate less long term growth rate]. But where does the terminal valuation formula itself come from?

2. The formula for terminal value is an application of an old valuation formula. The formula is an application of an old valuation methodology called “the dividend discount model” or the “Gordon growth model”, where a business is valued as a stream of its dividends. This model pre-dates discounted cash flow valuation, and the capital asset pricing model on which DCF is based. What we are doing at the back end of our financial model is applying a very old methodology to determine the valuation of the company at the end of the cash flow forecast period.

3. The dividend growth model or the Gordon growth model. The formula for the Gordon growth model is shown below. You can see how the terms match up with the same terms for calculating terminal value.

4. Derivation of the Gordon growth model. The Gordon growth model holds that a company’s valuation is the sum of that company’s discounted forecast dividend payments. For more detail see any good corporate finance textbook or Gordon, M. (1959) “Dividends, earnings and stock prices”, Review of Economics and Statistics, Vol. 41, pp. 99-105.

This long formula is known in maths as a “Geometric series”, where the next term in the series is calculated by multiplying the previous term by a constant. In this case the constant is (1+g)/(1+r).

5. A bit of algebra that you can skip if you wish. If we multiply both sides of the equation by the constant (1+g)/(1+r) we get two different versions of the same long formula.

This second new series is the same as the original, except that the first term is missing from the left hand side. Subtracting the new series from the original, cancels every term in the original but the first.

With a bit of simplification…

Voila – at the bottom we have it: the Gordon growth model! You can see how the terms match up to the same terms used in the terminal value formula.

6. Big sensitivities in financial modelling for valuation. Any analyst who has spent a little bit of time modelling will be able to tell you that big sensitivities on terminal value and hence valuation are the final year cash flow, the long term growth rate, and the discount rate. Making small changes to any of these can result in a very different terminal value. And terminal value can end up accounting for a large proportion of total valuation in a financial model.

To summarise, the terminal value formula used in DCF valuation modelling is an application of a very old (and otherwise now regarded as outdated) valuation methodology, predating the DCF methodology itself. We can put an awful lot of work into modelling intermediate cash flows as accurately as we can, and then try to be very precise about how we discount those cash flows in valuation. But when terminal values (where we are essentially just dividing one quite rough number by another rough number) account for a large proportion of overall valuation, perhaps we should be answering another question. Instead of discounted cash flows, should DCF stand for “Deceit by Computer Fraud”?

About the author: training company financialtrainingassociates.com

Financial Training Associates Ltd is a provider of financial modelling course training and other finance-related courses for accountancy, banking, law and other professionals.

Modelling for a merger or LBO (leveraged buyout)

Excel modelling training company FTA Ltd considers the answer to the question: “How can I model more complicated transactions such as refinancing, acquisitions or buy outs?”

This post relates to material covered on a financial modelling course run by Financial Training Associates Ltd.

1. The starting point – structuring your model: the starting point is to insert a new tab in your financial model that includes the new deal structure – and contains both sources and uses of funds. See the diagram below for guidance.



The new deal structure will result in some significant changes for the business model (e.g. debt will increase). This means that the balance sheet in the financial model needs to be ‘re-wired’ so it picks up key adjustments arising from the deal structure.



If, having made the adjustments, your balance sheet still balances then you are likely to be on the right track with your adjustments!

Note: the detail of how to make the adjustments above is covered on our financial modelling courses. See financial modelling course for more information.

For each transaction that you model, the impact on deal structure, balance sheet and other financial statements will differ. Summary guidance is provided below, starting with how you might model a leveraged buy out (LBO).

2. Modelling a buy out

- Sources of funds in your deal structure tab = new debt and equity

- Uses of funds in the deal structure tab = refinance of old debt, purchase of 100% of the shares of the target, plus any other needs (e.g. extra working capital, extra capex, extra restructuring costs that can't be met through short-term cash flow) and fees.

- Balance sheet effect - debt goes up post deal, goodwill goes up, net assets going forward match the new equity contribution made for the buy out. We sandwich that new deal structure together with the balance sheet of the company we are buying

- P&L effect - extra debt means forecast interest costs are higher. If the accounts are IFRS accounts, fees are usually expensed in the first year of the deal.

3. Modelling a refinancing: this is the simplest transaction to model.

- Sources of funds in the deal structure tab = new debt.

- Uses of funds = refinance of old debt and fees.

- Balance sheet effect - debt goes up post deal. Items 1,2&5 in the diagram above disappear (you’re not usually raising equity or buying anything in a refinancing). All you’re doing is raising some extra debt and perhaps using that to pay off old debt. To the extent that total debt increases post deal, cash on the balance sheet will also increase by the extra total debt raised (until, for example, the extra cash raised is paid out as a dividend).

- P&L effect – as per the buy out.

4. 100% Merger/ acquisition: this one is a bit more complicated.

- Sources and uses of funds = just the same as the buy out.

- Balance sheet effect = the same as the buy out, except this time we are sandwiching together the deal structure, the balance sheet of the buyer and the balance sheet of the target. So we have three things to add together: deal structure + balance sheets of two operating companies.

- P&L effect - the post-deal P&L is an amalgamation of the 2 operating companies together with the flow through costs of the new deal structure (fees plus increased debt means higher interest cost).

4a. Special case: acquisition of a very small stake in another company (= investment: no control)

- Sources and uses of funds = as above (i.e. sources = any new debt or equity raised, uses = purchase of stake plus anything else plus fees)

- Balance sheet effect = different from the above. Because we are acquiring a small stake in another business, we don't consolidate the full balance sheet of the associate company. So the post deal balance sheet is going to = deal structure + a new line item "value of investments".

- P&L effect. There will be some flow through effect e.g. where we have raised extra debt to buy the investment, leading to higher interest costs. Then, on the P&L, we might see a new line item "other income - income from investments".

- See the diagram below ("Group accounting") for summary and guidance as to what is likely to count as an investment.



4b: Acquisition of a non-controlling stake e.g. 40% in another company (= associate)

- Sources and uses of funds = as above (i.e. sources = any new debt or equity raised, uses = purchase of stake plus anything else plus fees).

- Balance sheet effect = as per investment. See the example below.



- P&L effect. Here we "equity account" - on the P&L, what we would do is consolidate 40% of the associate's P&L into the acquirer's P&L. In addition there will be some flow through effect e.g. where we have raised extra debt to buy the stake, leading to higher interest costs.

4c: Acquisition of a majority stake (e.g 75%) in another company

- The same as 100% merger or acquisition (i.e. the post deal balance sheet = deal structure + 100% of the balance sheet of the two operating companies, post deal P&L = flow throughs from deal structure + 100% of the P&L of the two operating companies). But we need to somehow reflect the fact that 25% of the business belongs to another party.

- What you will see is a line item at the bottom of the balance sheet (used to be called "minority interest", now called "non-controlling interest") under liabilities - reflecting the fact that 25% of the value of the acquired business belongs to someone else, reducing the value of equity attributable to ordinary shareholders. See the example below.



- You will also see a line item at the bottom of the P&L ("minority interest" or "non-controlling interest") deducting or splitting net income and making it clear that 25% of the income from the acquired business belongs to outside shareholders, with the remainder net income attributable to ordinary shareholders.

5. Valuation impact of cases a-c above.

Where it is difficult to forecast future income/ cash flow from investments or associates, then the valuation approach is to exclude income from investments and associates from core income, valuing investments/ associates separately (e.g. based on their last balance sheet value) and 'topping up' our valuation of the underlying core business for the value of investments & associates.



6. De-merger/ sale of part of the business

Exactly the opposite of cases a-c above.

About the author: training company Financial Training Associates Ltd

FTA Ltd is a provider of finance programs, including financial modelling course training and other related finance training for law, accountancy, banking and financial services professionals.