Thursday, 23 April 2015

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.

Thursday, 26 February 2015

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! 

Thursday, 20 March 2014

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.

Excel modelling with the XNPV formula

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

Wednesday, 19 March 2014

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!

Friday, 14 March 2014

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:

Embedded and nested Excel functions

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 (

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!

Wednesday, 4 December 2013

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.


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.

Friday, 15 November 2013

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!

Wednesday, 18 September 2013

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.

Monday, 4 March 2013

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

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

Monday, 25 February 2013

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.

Thursday, 19 July 2012

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 for more information.

Wednesday, 21 December 2011

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.

Tuesday, 8 March 2011

Valuation modelling course

For a course on modelling debt free cash free valuation click here: valuation training course

Monday, 13 September 2010

New financial modelling course from FTA Ltd

Financial Training Associates Ltd has just announced the release of a newly designed financial modelling course. According to Joanna Smith, business development director, the new course is specially designed to help delegates working in finance-related jobs and who are looking for help with buy out (LBO or MBO), valuation or merger modelling training. "This course is run in an interactive, participative format, where participants learn by doing." The course has been developed in response to recent demand. Much of the course work involves Excel modelling and analysis, equipping participants with the tools to analyse leveraged acquisitions, building up from partially-complete models, working with integrated financial statements, developing an acquisition structure and modelling instruments, running scenarios, iterating and optimising. "As part of their work on this course delegates model transactions based on real-life companies and scenarios." For further details, please see FTA Ltd's online course calendar.

Sunday, 8 August 2010

Tuesday, 20 July 2010

Free CPD from ACCA and FTA Ltd

FTA Ltd and ACCA have teamed up to make free financial training material available at no cost to interested professionals.

If you work within law or finance this is a chance to "look over the fence" at some of the big issues your clients worry about. If you work in accountancy or along side accountants, this is a chance to reflect on matters that you might have to deal with just once in a while. The training material is especially relevant for professionals who work in financial services or advise on company sales and acquisitions.

Click here for details: free financial training

Bookmark this page if you think you could be running short of CPD points at the end of your CPD year.

FTA Ltd is a specialist provider of CPD courses. ACCA (the Association of Chartered Certified Accountants) is the world's largest international professional membership body for accountants.

FTA Ltd and ACCA have teamed up to make CPD material available free of charge to members and other interested professionals.

The material can be found on ACCA's website at and on FTA's website at "free cpd for law and accountancy".

The material consists of a short self study article together with an interactive multi-choice quiz and should take approximately one hour of your time.

Monday, 19 July 2010

CPD update for law, accountancy and banking: issues when buying, selling or financing a business

Free CPD for law and accountancy: this CPD training update looks at how consideration should be determined when buying, selling or raising finance for a business.

The material consists of a short article and a 10 question online multi-choice quiz. It should enable you to claim one CPD hour.

If you think you may be running short of CPD points at the end of your CPD year. Please bookmark this link: valuation training.

Thursday, 8 July 2010

CPD training courses from FTA Ltd

Training provider FTA Ltd has announced the development of a new range of CPD courses.

FTA specialises in CPD training courses for lawyers, accountants, bankers, finance and other professionals.

According to Jo Smith, business development director, the new grouping of courses has been developed in response to recent customer demand.

“We have a large range of courses, but we find that law and accountancy customers regularly enquire about some of those more than others.”

We thought it made sense to group some of our most popular courses in one place on a special CPD website, so that’s what we’ve done now”

Details of FTA’s new courses are available at

FTA Ltd is a specialist training company providing CPD courses for law, accountancy, finance, banking and other professionals.

Thursday, 25 February 2010

Free CPD for law and accountancy from ACCA and FTA

FTA Ltd and ACCA have teamed up to make free CPD training material available at no cost to interested professionals.

If you work within law or finance this is a chance to "look over the fence" at some of the big issues your clients worry about. If you work in accountancy or along side accountants, this is a chance to reflect on matters that you might have to deal with just once in a while. The training material is especially relevant for professionals who work in financial services or advise on company sales and acquisitions.

Click here for details: free cpd for law and accountancy

Bookmark this page if you think you could be running short of CPD points at the end of your CPD year.

FTA Ltd is a specialist provider of CPD courses. ACCA (the Association of Chartered Certified Accountants) is the world's largest international professional membership body for accountants.

FTA Ltd and ACCA have teamed up to make CPD material available free of charge to members and other interested professionals.

The material can be found on ACCA's website at and on FTA's website at "free cpd for law and accountancy".

The material consists of a short self study article together with an interactive multi-choice quiz and should take approximately one hour of your time.

Wednesday, 9 December 2009

Solicitors Journal: A Smart Move

It may not officially be the solicitor's job to take into account working capital when acting for a client selling their business, but for the smart practitioner who wants to keep their client happy it is worth considering from the outset, says Joanna Smith.

You may have been fortunate enough to have spent time advising a client selling a business. That client may have spent years building their company. A large proportion of their personal wealth will probably be tied up in its sale. It is likely they will be finding the whole process stressful and very quickly you will have learned that there are plenty of ways your client can become a little grumpy. And it’s probably the grumpy clients who most resent paying their legal fees. You don’t want a grumpy client.

A quick master class: winding up a business owner

One of the quickest ways that a business owner can become upset is if he suffers a last-minute price ‘chip’. That is, a price reduction late on in the process.

So, what is working capital and what is its role in a last-minute price chip? What’s the possible impact on a deal? No one working on a transaction is thinking that working capital is the solicitor’s responsibility. But is there anything the streetwise lawyer could look out for? When might working capital become an issue? What kind of things could be done during the process to avoid the last-minute price chip?

Even if it’s outside your responsibilities, a few well-chosen words in your client’s ear and you could suddenly find you have become endearing. This of course is no bad thing, given you are hoping your client will only be too happy to write out a cheque for your services later on in the process.

Working capital: a plain-English guide

Working capital is the funding needed to operate a business over the short term. If customers are paying more slowly, less cash is flowing into the business. More short-term funding is required to keep the business operating. Working capital requirements have increased.

Alternatively, if a business is able to delay paying its suppliers, short-term cash outflows and short-term funding requirements drop. A delay in supplier payments results in a reduction in working capital requirements.

Business change is going to drive working capital requirements. Working capital and short-term funding requirements are going to increase for the business whose suppliers suddenly demand to be paid more promptly, or the business that has to wait for more cash from customers. Any increase in the lag between suppliers being paid and customers paying is going to drive working capital and short-term funding requirements upwards.

So, a business that is growing quickly might have high working capital requirements. Doing more work for more customers means more cash is required to fund activities.

A company that is developing more business with a longer delay between doing work and getting paid will see its working capital requirements increasing. Think about a company diversifying into oil exploration or drug development. A business that is becoming more seasonal, manufacturing in one part of the year and selling in another, might also see working capital requirements increasing.

For some businesses, working capital requirements are going to be a bigger issue than others. Contrast the rapidly expanding Christmas tree producer (scaling up quickly, long production cycle, seasonal business) against the well-established greyhound race track operator. In one of these businesses we might expect working capital to be more of an issue than the other!

Solicitors and other advisers: what’s the impact on deals?

When purchasing a business we can almost predict that the buyer’s accountants will try to use financial data to argue that the target business has higher than anticipated working capital requirements.

What’s the impact? Put simply, identifying higher than expected working capital requirements gives the buyer all the ammunition they need to try and reduce their asking price.

The buyer may argue that the seller has mis-represented the working capital requirements for the business. The buyer might argue that it is raising all the debt it possibly can as part of the purchase. The buyer has to keep some debt facilities in reserve to fund the unexpectedly high forecast working capital requirements.

With the new information, it appears that not all of the debt raised can be paid out to the seller. The seller is presented with a last minute price reduction: a last minute price ‘chip’. If the seller has been dealing exclusively with one buyer, and other potential buyers have left the process, the seller may feel they have little option but to accept.

Preparing for the debate

Think about the rapidly expanding Christmas tree producer. The owner of that business knows a lot about selling Christmas trees and even a little about managing working capital. Unfortunately, having never sold a business before, they may be blissfully ignorant regarding last-minute price chips. Fortunately, that same client has had the presence of mind to retain a commercially-focused streetwise solicitor who has invested carefully in their own CPD (and studied this course material carefully).

What could the seller of the rapidly expanding Christmas tree producer do to prepare for a debate about working capital requirements?

The ‘do nothing’ strategy

‘Do nothing’ is always a possibility, and it’s not completely without logic. The argument here is that the buyer might not raise working capital as an issue at any point in the process. The seller bargains on buyer ignorance or stupidity or the seller’s own absolute confidence that any detailed investigation will show that the business is being sold with a robust working capital position.

However, ‘do nothing’ could be a very risky strategy. The buyer’s accountants expect to be rewarded handsomely for the work they are doing to investigate the finances of the target acquisition. They know added value equals a happy client. You can probably almost bet that one of the ways they are going to generate value is by doing everything they can to find data that points to working capital requirements that are higher than expected. They are highly motivated to provider the seller with all the ammunition they need to justify the last-minute price chip.

Do nothing means the owner of the rapidly expanding Christmas tree seller could find themselves suffering a price reduction late on in the process. For you the last-minute price reduction could mean a grumpy client who is not 100 per cent happy about writing out that cheque for your services.

We should be able to do better than ‘do nothing’.

What's the alternative strategy?

The alternative for the seller is to work to prepare themselves in advance of a potential argument. The seller could present a picture of ‘normalised working capital’ for the business and argue that any extraordinary fluctuations, e.g. two years ago, were one-off. This strategy sees the seller trying to get on the front foot and looking for an opportunity to present their own view of working capital.

Opportunities for the seller to present their own picture of working capital include:

1. The information memorandum (early in the process). This is a bit like a business plan, released early on to all bidders in the process. It is designed to contain all the information a potential buyer should need to bid for the business. The information could contain a broad overview of working capital requirements, but in practice release of detailed working capital requirements this early on is very rare.

2. Vendor due diligence. Here, the seller commissions their own accountants to provide a detailed picture of the business’ finances and releases this to short listed bidders. But accountants, like lawyers, are by nature thorough. Talk to any about conducting vendor due diligence and you will be amazed at what they feel they need to look at. And they don’t come cheap. And all of this work has to be done before the seller can even be sure they have a committed buyer for the business. And what the accountants may not tell you is that some buyers may discount the information contained in the vendor due diligence report anyway, given that it was prepared by the seller early on in the process.

3. A focused piece of work around working capital requirements. Alternatively, without commissioning a large piece of vendor due diligence, the seller could just ask his advisers or accountants to provide some supplementary information relating to the business’ working capital position, once the identity of short listed bidders is known.

4. Dataroom (late in the process). The seller could provide some information on working capital in the dataroom. This is relatively late on in the process when the buyer has his own accountants trawling through files of information on the business’ contracts and finances. Success here assumes that a number of buyers are proceeding through to this phase of the process (or at least waiting in the wings, eager to jump back into the process) and the seller is not already stuck with one bidder who is looking for any excuse to chip away at the price.

So, quite a few options. Alternatively, if there are some fly-by-night advisers reading this, and all of 1-4 sounds like too much trouble and work, there’s always the ‘do nothing’ option. The later it is left and the fewer buyers remaining in the process, the more likely it is that the seller could be forced to accept a price reduction from a buyer concerned about working capital. ‘Do nothing’ really does seem like a recipe for a last-minute price chip and a grumpy client!

What should the streetwise solicitor do?

Just a very few well chosen words could make all of the difference to how much your client appreciates your input.

Imagine you were the one person on the deal who was smart enough to check something with the client. Imagine early on you were sitting down with the client to map out the process and agree the scope of your work. Imagine the seller told you he had asked his accountants to amalgamate some information for the buyer’s accountants. Imagine you were smart enough to ask this question: “And what are you expecting the buyer’s accountants to discover about the working capital position for this rapidly expanding Christmas tree producer?” Think what an opportunity you could have to talk to them further and impress them.

Even if the conversation led nowhere, maybe working capital could, by some amazing fluke, become an issue in the sale of the rapidly expanding Christmas tree producer. Maybe your client vaguely remembers your thinly veiled warning about working capital. Maybe as the deal starts to drift south, suddenly you’re the one person your client is relying on (given that you perhaps were the only one to be smart enough to mention the issue early on). If your client is destined to become grumpy about the deal, surely it would be nice if they were least grumpy with you?

Hang on, isn’t working capital someone else’s job?

‘Yes’ and ‘no’. Worrying about working capital is far outside the formal job description for any solicitor. In an ideal world there would probably be a savvy accountant who had raised the same issue. But clients are sometimes slow to involve their accountants (after all, they’re almost as expensive as solicitors) and sometimes they’re brought in late working to a very tight budget, so are not that closely involved. In any case, accountants, like solicitors, are not all savvy.

So, it really depends how you see your job. Are you limiting yourself to the role of nit-picking drafter of documents (which, of course, does have value for your client)? Or are you the commercially focused streetwise solicitor, in touch with your client, speaking to them from the start, involved in the strategy for the process, making sure your position is absolutely cemented as ‘trusted adviser’ in your client’s eyes?

It’s your choice. You decide. At some point, if you find yourself advising the seller of a rapidly expanding Christmas tree producer, it could just help to check your client appreciates the potential impact of working capital.

Joanna Smith is a business development director at Financial Training Associates

Reprinted from Solicitors Journal