Free Excel financial modelling course

We can give you an Excel formulas tune up in around ten minutes a day – for free.

We’ll send you one email a day for a month. Each email should take only around ten minutes a day to work through.

The material draws on our regular Excel financial modelling course training and is well proven with people regularly taking the program.

The program covers all the formulas attendees on our financial modelling course ask about and find the most useful of all. Excel has hundreds of formulas on offer. That can present a bit of a challenge: you need to become fluent in the formulas that are going to be most helpful in financial modelling.

We think that, by sending you one email a day for a month, in just ten minutes a day we can check you’re on top of critical Excel financial modelling formulas. All for free.

Here you can sign up online for the free Excel formulas course.

Sumproduct's superpower

We think Sumproduct is a bit like Bruce Wayne/ Batman. During the day Bruce has a regular job (businessman, playboy, philanthropist) and no-one suspects what he gets up to at night.

Sumproduct has a day job too: taking two sets of numbers, multiplying one by the other and adding up the result.

Of course Bruce Wayne has a secret. At night he squeezes himself into his tights and goes out fighting crime.

Sumproduct’s secret is that you can build conditionality into it. It’s definitely a secret because you don’t get any guidance about that from Excel’s usually comprehensive “F1” help. It’s something advanced practitioners have probably learnt by looking at what’s going on in other modelling work. They just ‘know’ about it. The ability to build conditionality into Sumproduct means it can go head to head with Vlookup or Index as a data picking solution. But Sumproduct does even more…

You can read more here: Excel's Sumproduct formula and its awesome superpower.

Excel's Offset function

Previously we looked at how to use Excel's Sumproduct function to pick data out of a table in Excel. That article was in response to a question we had from a delegate on a recent financial modelling course. Some Excel users are unaware that Sumproduct has a special hidden power which makes the function helpful when picking data out of a spreadsheet.

The role of Offset in amalgamating e.g. quarterly data

Here's the example we developed in that first Sumproduct article. You can click on it and download the example:

The example works. The quarterly data gets amalgamated up successfully using Sumproduct. The very observant among you (some would say 'fussy' or 'a bit picky'; we're fine with that) will have noticed that we've broken one of Excel's little golden rules in putting that example together.

Good practice: set your financial model up so that you can fill your Excel spreadsheet across right

If you download the previous spreadsheet example and click on cells C11 and C12 you'll notice that we've typed those cell entries in manually. What we should have done at a minimum is shade the cells a different colour (the spreadsheet uses blue) to show that they're hard coded inputs. Shading the cells blue is one thing, but imagine the Excel spreadsheet were a lot lot bigger. Imagine it had 10s or even 100s of columns (imagine a 30 year monthly or quarterly financial model). It would get pretty painful typing those Y1, Y2, Y3 entries in manually for 10s or 100s of columns. We could improve the spreadsheet further if we could turn line 11 into a formula that we could fill to the right easily.

Excel's Offset function can help you 'jump' and amalgamate columns

In the example the Offset function could help us 'jump' 4 columns at a time. In the example below Offset, combined with Sumproduct, provides a more complete solution for amalgmating data. You can click on the image to download the example:

Embedding Offset inside Sumproduct

If we wanted to get carried away we could take the example further by embedding the work that Offset is doing (allowing us to 'leap' 4 colulmns at a time) inside the Sumproduct. But would that make the whole example any clearer? We think not. Probably better to have the extra line and the steps (Offset, Sumproduct) broken out separately and a bit more clearly.

Good Excel modelling practice guidelines

We've ended up with quite a sophisticated little example where we're combining Offset and Sumproduct to amalgamate data as efficiently as possible. But at the same time we've been trying to observe some simple good modelling practice rules as we go along. Here's a recap on the rules we've been trying to follow:

• Always colour code your inputs. Those cells where we've manually typed ('hard-coded') an entry are ones that we might want to come back to later and change. Those are our input or assumption (as opposed to our calculation and output) cells. It would be a good idea to highlight those input cells with a different colour. Essentially we're trying to give the user a clear message: "This is an input cell. This is something that you might want to change later."
• Set up your calculations so that you can always fill right. We spend a lot of time filling right in models - particularly in those models that run for a long time and have a lot of columns ("Ctrl" "R" is a great keyboard shortcut for those who regularly like to fill right). What we don't want to do is find ourselves making manual 'tweaks' to formulas as we work across to the right. The last thing we want is to have to make manual entries to extend the model to the right. What we should always be doing is thinking about how we can manufacture a calculation that can be filled all the way across the model. The last thing we want is two formulas sitting next to each other that are slightly different from each other. In such a case a new user could pick up the model and, being in the habit of filling left to right, fill the first formula right across the second. Having two different formulas sitting next to each other will prime a model for disaster. Always try and set your calculations up so they can be filled happily from left to right.
• Avoid nesting or embedding functions inside each other. Remember we had a bit of discussion about whether we should put an Offset function inside a Sumproduct? It might have made us feel clever and it would eliminate a line in our financial model. But getting into the habit of putting functions inside functions has a nasty side effect. Your functions become long, complex and hard for a new user (the one who's not as clever as you) to check and understand. For clarity's sake we'd say that it's much better to pay the price of a few extra lines in your model, perhaps consigned to a dedicated 'workings' section at the very bottom. We'd say that, for clarity's sake, it's much better to step a new user through any complexity line by line. That's going to help someone new use your model or check or modify what you've done.

Excel as an art form

Rules sound very dull and we do expect you to break them from time to time (life wouldn't be any fun if you didn't break the odd rule from time to time now would it?). But even great art seems to follow a few rules most of the time right? Paint generally gets applied to a canvass (except, perhaps, in the case of Tracey Emin's bed). There's usually a paint brush involved. Generally light colours tend to go on top of dark. We're not pretending that you're making great art with Excel but if you can get your models to the point where:

• You are able to do something a bit clever like employ just a few simple formulas to solve some pretty hefty data manipulation problems and, at the same time
• You are able to employ a few good modelling principles (the common language that enables other model users to understand what you've done)

Then we wouldn't be at all surprised if you discovered yourself sitting back and taking a long lingering look at what you've done, perhaps finding a self-satisfied smile creeping across your face. OK it might not be Leonardo da Vinci's Mona Lisa that you're looking at but it's still your very own private and somewhat wonderful creation.

Who says Excel financial modelling can't result in a thing of beauty? We think it can!

Excel tip: using Sumproduct to pick and amalgmate data from a table

What's the best way to pick and amalgamate data from a table in Excel? This question was asked recently by a delegate on a financial modelling course. He'd recently been set an Excel test by a potential future employer. The test looked simple (picking and amalgamating data from a list) but sometimes something that seems that it should be simple in Excel turns out to be just a little tricky. And that trickiness is compounded by the fact that Excel always provides us with multiple solutions to a problem. With Excel sometimes half the battle is navigating your way through all of its potential solutions.

Picking data from a table

What about using Excel to pick the correct piece of data out of a list? That’s simple enough. We could use something like a Vlookup or an Index function.

Click on the picture above to download the example.

Amalgamating data within the table

What if we wanted to amalgamate data within a table? The challenge itself is easy enough to describe, but the trickiness starts when we decide that we'd like to amalgamate data using a short elegant function. You might first think of picking out the pieces that you need using e.g. your Vlookup function and adding those all together, but that could result in some pretty long cumbersome functions.

The conditional Sumproduct function

We’d like to introduce you to a little known Excel fact. Conditionality can be built into the humble Sumproduct function. That makes the function perfect for amalgamating data the way we’re imagining here.

Regular use of the Sumproduct function

In its regular day-to-day use Sumproduct kind of does what it says on the tin. If you’ve got two lists of data, you can multiply one list by the other and sum up those products.

Here’s an example for you to download (click on the picture). Imagine you’re a sales person. Some jobs you get paid a certain % commission. Other jobs you get a different commission. You could use Sumproduct to calculate your total commission.

Building conditionality into Sumproduct

Most Excel users are not aware that you can build conditionality into Sumproduct. You can use Sumproduct to pluck data from a list. As an alternative to Vlookup or Index it’s often a shorter neater solution (and we think that means it's better).

You need to be very careful how you construct the function (Excel’s regular help function doesn’t give you any guidance on this - so it's like an 'Excel secret') and we’ve shown you how to do that in the attached spreadsheet. Click on the picture above to download it.

Using Sumproduct to amalgamate data

Better still, you can use Sumproduct to amalgamate data. For example, imagine your data was presented quarterly and you wanted to calculate the total for the year. The conditional Sumproduct function could help you do that really rather neatly. It’s hard to think of a shorter neater solution to solving the quarterly data amalgamation problem!

Excel secret discovered! You can build conditionality into a regular Sumproduct. You can use that conditionality to amalgmate data really quickly.

About the author: Financial Training Associates Ltd

FTA Ltd is a provider of financial training courses. FTA’s financial modelling course runs regularly and includes coverage of helpful Excel tips and tricks.

Excel VBA change file name utility

How could you use Excel VBA to list out and change file names quickly?

We show you how here: Excel VBA macro change file name.

It's all part of something we did for a company that wanted to get their people up the curve on macros.

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

Project finance modelling

How could you build a very simple project finance model? If you want a few clues, having an example to look at might just be helpful. That's what we've prepared for you right here: simple project finance model.

The specimen model is designed to be simple and, if you just want to get an overview of how project finance models work, it might be a good place to start. It's got many of the core elements you would expect (e.g. construction loan mounting up with interest unpaid while the project is being built, refinanced with long-term loan once the project is operating).

Unfortunately, it does have a few sections missing (like working capital - although we've left space in the model for these to be filled in). If you haven't seen this kind of modelling before, it might be hard to make much sense of it all without a bit of gentle and clear explanation. But that's the point of our project finance course!

Getting better at financial modelling

So you want to improve still further after attending a financial modelling course? Here are some ideas about how to take your own work forward.

Learning modelling is probably a bit like learning to surf. You can look at it and talk about it forever, but at some point you've got to just get out there and "do it".

Build your own model

The quickest way to come up the curve now is to find an opportunity (e.g. as part of a work project) to build your own model. Hopefully, in an environment where you have a few friends you can say "Hey, how can I do this?". That's the quickest way to keep learning now.

Practise using your keyboard shortcuts?

If you want to get faster, we've got some material to help you bone up on your Excel shortcuts.

Buy a book?

Don't rush out and buy a book (they can be helpful, but it's like the difference between reading about surfing - which is always something that's going to be hard to describe - instead of doing it) - unless it's something you decide you can keep on your shelf and use for reference as do your own modelling.

Make use of Excel's own 'built in' resources

Get used to clicking on Excel's fx (insert function) button all the time as you see other modellers using functions you haven't seen before - that will help you work out what they're trying to achieve. Get used to using Excel's help function all the time (it takes a bit of effort, but it's generally very good).

Hone in on the Excel functions most likely to help your modelling

Excel is massively over-engineered (they feel like they have to add something every time they want to try and sell you a new version - and they've been bringing out new versions regularly for more than 20 years) and the very good news is that you can survive a career in finance using hardly any functions at all. People around you may use all kinds of functions to try and make themselves feel clever compared to others:

But maybe they're not quite as clever as the soul-surfing modellers who aren't going to be intimidated by what everyone else does; the modellers who play their own game, constantly challenge themselves to solve the problem in the neatest way possible, work out how to break the problem into clear logical steps, are really careful about how they structure their model, and use just a few neat functions at each stage (https://www.google.co.uk/search?hl=en&q=soul+surfer+definition&meta=&rlz=1I7ADRA_en).

The most helpful Excel modelling functions

Here is some of the material we looked at on the course. It highlights some of the Excel functions you will likely find most helpful for modelling: Excel modelling functions.

If you are determined right now it might be helpful to work through it (questions/ feedback always gratefully received). But you might just want to save it for future reference as you continue with your own modelling.

Get surfing/ start modelling!

Time to paddle out into the waves/ fire up Excel!

Excel modelling training: modelling goodwill and intangibles in a merger

The question: how can I can I use Excel to model goodwill and intangibles? Editor's note: this post was prepared by one of our trainers following a question received from a participant during a financial modelling course.

What is goodwill?

Goodwill is a class of intangible asset which arises when you acquire a business. Goodwill is the surplus of price paid for the target's shares over the net assets of the target (net assets = book value of equity = total assets less total liabilities = shareholders' equity = shareholders' funds).

Writing down goodwill under IFRS

Under IFRS (international financial reporting standards) the value of goodwill is checked each year under an "impairment test" and goodwill is written down if a valuation shows that the acquired target is not worth as much as previously thought. An example is the UK bank RBS's 2007 acquisition of Dutch bank ABN Amro. In 2009 RBS revealed the biggest loss in UK corporate history after it impairment tested ABN Amro and wrote down the value of its investment.

Writing down goodwill under other accounting regimes

Under other accounting regimes e.g. UK and Dutch generally accepted accounting practice, goodwill is amortised or written down a little bit each year, just like depreciation on fixed assets.

Lessons for financial modelling in Excel - the simple solution

If you are trying to model an acquisition by a business that accounts under IFRS, the simplest way to model goodwill is to assume no future forecast change. It's not going to make much sense to forecast an anticipated write down or other revaluation and, in any case, it's a not a cash item so doesn't affect the business's economics.

The more complicated picture

The picture above is slightly simplified. When one business acquires another, goodwill is generated as described above. At the same time, the acquirer gets an opportunity to revalue the existing assets of the target upwards. The acquirer gets the opportunity to review the target's existing assets and also identify separate intangibles sitting within the target (e.g. a brand or publishing title that can be valued as a separate intangible asset). In effect, this means that the price the acquirer pays for the target can be broken down into:

(i) the fair market value of the target's existing assets and liabilities;

(ii) the value attached to separately identifiable intangibles; and

(iii) goodwill (equals the surplus of price paid for the target's shares over the value of the other two types of assets).

Points (i) through (iii) above provide you with a sense of how balance sheet values could change following an acquisition. In the P&L, following acquisition:

(i) revalued tangible assets will be depreciated, increasing depreciation expense;

(ii) intangibles will be amortised, increasing amortisation expense;

(iii) under IFRS goodwill will be impairment tested each year as per the previous RBS example.

In effect the acquisition process gives the acquirer the chance to:

(i) 'find' some extra tangible assets that can be depreciated;

(ii) 'find' some extra intangibles that can be amortised; and

(iii) reduce the amount of goodwill showing on the balance sheet.

Lessons for financial modelling in Excel: the more complicated solution

When modelling a merger in Excel you could, if you wished:

(i) estimate expected revaluations of tangible assets and increases in depreciation;

(ii) estimate separately identifiable intangibles and increases in amortisation.

Conclusion

Without having gone through a valuation exercise ahead of the acquisition it is going to be very hard to forecast expected revaluations and they are non cash anyway - so it may make more sense to model intangibles as per "the simple solution" above. That is, just calculate goodwill as the surplus of price paid for the target's shares over the net assets of the target and forecast no change/ write down going forward. There are always so many big variables when you are trying to model an acquisition that it's hard to imagine that there is much to gain by super-accurate forecasting of non-cash items.

About the author: Financial Training Associates Ltd

FTA Ltd is a company that provides finance-related CPD programs, including Excel financial modelling course training, project and corporate finance, valuation and related courses. Course delegates are drawn from the financial services, accounting, legal and professional services industries.

Course question: how do I build a financial model?

What are the exact steps in a financial modelling build up? This question was asked recently by a delegate on a financial modelling course. Here's a suggestion regarding how to proceed. You can get fuller details here: modelling steps.

Step 1) Create an assumptions tab in your model first. Use the same structure as in this Excel spreadsheet: modelling start point. Enter your operating inputs onto the assumptions tab one at a time (starting with revenue). Just create the base case first. Each time you add a new operating assumption, create a link from the top of the assumptions tab (the scenario picker, using the choose function) to your calculations tab. You can see the example here: modelling start point.

Later on you are going to copy the base case down to create your stress and growth cases (see the final tab in the modelling start point example). But right now you're just going to concentrate on building the base case.

Step 2) Keep adding more operating inputs onto your assumptions tab, wiring them up to the P&L on the calculations tab (the second tab of the modelling start point shows GM% as the next operating input for example).

Step 3) As you move down the P&L you're going to have to model depreciation. And to model depreciation you're going to have to create a fixed assets schedule, with assumptions around depreciation rates and cap-ex rates.

Step 4) Once you have created your fixed asset schedule and modelled depreciation and cap-ex, you can wire depreciation into the P&L. You can also wire fixed asset balances into your BS, and cap-ex into your cash flow. You can also model amortisation.

Step 5) As you continue to move down the P&L, you can leave the interest calculations alone for now - we're gong to come back to those later. You can fill in the rest though, including the tax charge. You can insert a tax rate assumption into your inputs/ assumptions tab (as discussed on the course, use effective rather than corporate), allowing you to model the tax expense in the P&L.

If you think you're going to need to model tax losses, you can do that now, using this model as a guide: modelling tax losses.

Step 6) Model most of the rest of the balance sheet items. This means you are going to need to insert extra inputs around working capital items (e.g. modelling debtors/ receivables based on debtor days). Other items we can link to the overall size of the business. We can project intangibles at this stage (they will reduce by amortisation). Other items will move in line with the overall growth in the business.

Step 7) Wire up the cash flow. Many of the items (EBITDA, cap-ex for example) link to other parts of the financial statements. You'll have to be careful in modelling working capital movements and other balance sheet movements. If you miss one, or wire it up the wrong way, you'll never get your balance sheet to balance, and you'll spend a long time trying to trace the error (the voice of bitter experience that is).

It's really important to be careful at this stage because mistakes in wiring up the cash flow statement are a common source of error in modelling.

Step 8) If you want to construct a model that allocates all surplus cash flow to repaying debt we recommend following this example/ format: cash flow model. It is designed to map out and help you model the critical cash flows. It will enable to model debt repayments in your cash flow statement, as well as debt and cash balances in the balance sheet.

When you've completed this step you will have filled in the debt and cash balances in your balance sheet. The interest payable and receivable in the P&L and CF will still be empty (zero values, that's fine).

IF you've done everything correctly (it never happens first time) then you might have a balance sheet that's balancing. More likely, you've got a fair bit of checking to do and some differences to iron out. That's fine. That's normal - it's part of the process.

Remember step 8 is all about getting the balance sheet balancing. At the moment interest calculations are blank. Once your balance sheet is balancing, it's time to move on.

Step 9). At this stage you can create the interest calculations. You may be tempted to average opening and closing balances. That's going to result in a circular reference (explained here: circular references). Turn iteration on. Sit back and pat yourself on the back. You've got a balancing balance sheet. Sensible interest calculations. And a model that's projecting cash flow for you! Congratulations.

To do your scenario testing, copy the base case down (as per the final tab on modelling start point). Create some dropdown boxes. Now you can instantly switch your scenarios, and see what happens to your modelling outputs.

If you wanted a model that integrates financial statements, and gives you a cash flow projection - if that was your objective for modelling - you've done that: well done - you've just achieved a result that takes training delegates the best part of three days on our financial modelling course!

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.

Private equity training course question

This question was asked on a recent private equity course. The question was: what's the difference between a closed end fund and an open ended fund?

Closed-ended vs. open-ended fund: a closed-ended fund will require a private equity firm to sell all of its shareholdings in portfolio companies, e.g. within 10 years, returning the proceeds to investors. An open-ended fund does not have any set date for return of funds to investors.

Most funds are closed ended with a relatively long life (e.g. the 10 years referred to above), perhaps granting investors the right to extend for a year or two. The long life is designed to give a private equity firm enough time to make investments in portfolio companies and generate returns when selling out of those same companies, hopefully without finding itself under pressure to sell stakes quickly. Any right to extend the life of the fund for 1-2 years is designed to avoid a situation where a private equity firm finds that it has not been able to sell out of all portfolio companies within the 10 years. A 1-2 year extension is designed to give investors the time they would need to facilitate an orderly winding up of portfolio companies and avoid a "fire sale" at the 10 year point.

In both a closed end and an open ended fund an investor would expect the private equity firm to be motivated to sell out of investments. Annual management fees for the private equity firm would reduce over time, and bonus fee arrangements for the private equity firm would only be triggered once investments had been sold at a high enough value. The structure of fee arrangements is designed to motivate private equity firms to sell portfolio companies at high values to generate firm bonuses, and raise fresh funds to replenish declining management fees - irrespective of the life of the fund.

Under both closed ended and open ended funds an investor would expect the private equity firm to be motivated to succeed. In practice most funds are closed-ended funds with a long life, so that the investor has the ability to get its money back e.g. if the private equity firm has failed to make successful investments.

About private equity training courses from FTA Ltd

FTA Ltd is a provider of private equity course training to banking, law, accounting and the financial services industry. Please see www.cpd-courses.org for more information.

Financial modelling course tips

Training company FTA Ltd answers a recent question raised on its financial modelling course. Here's the question, asked by a recent course delegate: "What do you do if you are asked to manipulate a large complex financial model? Here's the answer...

There's plenty of scope for error if you've been asked to manipulate a large complex model that you haven't created yourself - an advanced model that you can not be completely familiar with. Our approach would have you following the CYA financial modelling method. That's the Cover Your AXXX advanced financial modelling method. Here are some further thoughts about how to CYA if you find you're given the horrible task of manipulating a large complex financial model.

1. Dodge the bullet. If you are presented with a complex inflexible model, and asked to run scenarios or make complex modifications, the advanced CYA financial modelling method would have you stopping for a second, and just considering whether there's any scope to push the modifications back to the person who originally built the model. Perhaps the model was originally built by a consultant who might be happy to make the changes for you. Perhaps it was built by someone who has transferred into another department, and can be persuaded to spend a little more time modifying the financial model for its current purpose. There's no shame in that. It's no poor reflection on your modelling skills. Large complex models take a lot of time to understand and modify. If you plough on, perhaps under time pressure, because of the complexity of the model there's a real risk that you disrupt the model - without even realising it. If you've got the opportunity to get input from the person who originally created the model do that. Make the argument that, because they understand the model, it's going be a more efficient use of everyone's time. Because they understand the model, there's less of a chance that a mistake will be made with your name on it. That's CYA financial modelling.

2. Make a note: next time, get it right first time. We admit, maybe it's too late for this one, but wouldn't it have been nice if, when the financial model were built, it had been built with the flexibility to run the scenarios or modifications that are needed right now? Wouldn't it have been better if, when the terms of reference for the original model build were developed, those terms required the financial model to be able to run the business case that is quite obviously needed right now. Yes maybe it's too late for this one, but you could at least ask the (almost rhetorical) question: "What did the original terms of reference say about the model's ability to run this business case?". The answer to that question, although obvious at this late stage, might get people around you admitting that the model should have been designed to run this case from the start, and perhaps agreeing that the person who originally built the model is best placed to make the current set of modifications. Even if none of that does any good, maybe the discussion will at least make people appreciate the importance of good model design, and getting it right from the start. If the model wasn't right from the start last time, perhaps the lesson can be learned for next time. So that at least we don't all find ourselves in this horrendous situation again - being asked to make a complex set of adjustments to a complex financial model.

3. Flex the large horrible model very simply. If you are presented with the large horrible complex inflexible financial model, and asked to run a new case on it, take a second to step back from the detail of the adjustments you are being asked to make. Think about the real purpose behind the adjustments. For example, rather than changing every price and volume that contribute to the business's revenues, think about the real purpose here. Despite what case we run, do we really have a great deal of certainty about exact prices and volumes? Is what we're really interested in the potential for total revenues to change? Instead of modelling detailed prices and volumes, couldn't we just scale total revenues up or down by a set %? If you've stopped to consider the real purpose behind the modifications, then you might be lucky enough to be able to model the modifications relatively simply. Instead of going through the model changing individual prices and volumes, perhaps you can very simply insert an extra line that scales total revenues up and down by a certain %. Making a simple adjustment will save time plus reduce the risk of you making a mistake in how you are flexing the original complex financial model.

4. Start again. It's very hard to manipulate someone else's large complex model. Sometimes it is just easier to start again and start with a new model. Stop and think about the modifications that you're being asked to make right now. Are they very detailed changes you're making, or are they high level changes? If you're being asked to make relatively high level changes, maybe you'd be better creating a simpler model (one less prone to error) designed purely to illustrate the impact of the case you're being asked to run right now. Yes you are bypassing all that previous work, but you'll end up with a model that you actually understand, removing the complexity of the old financial model as a source of error. Just have a think about it before you jump in. Would you be better off starting again?

5. Bite the bullet. OK OK. We're working in real world financial modelling. A world where sometimes we can't dodge the bullet and the model wasn't built right first time. Although you'd rather just start the model again, or flex the existing model very simply, maybe you really haven't got any choice. Maybe, no matter what you've suggested, someone is yelling at you just to make the required changes. There's no getting around it, you're going to have to set aside enough time to make all the tiny little changes needed, and make sure they're tracking through the financial model correctly. And in making the changes, be very careful to document exactly what changes you have made and what your basis for making the changes has been. That's what the CYA method would have you doing but sometimes, no matter how creatively you've tried to approach an advanced financial modelling problem, there's no easy answer!

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