Poll: what advanced Excel modelling function would you like help with next?
Click on the poll below (opens in a new window) to tell us which advanced Excel function you'd like to see included in our advanced financial modelling course material next:
Wednesday, 25 January 2012
Advanced Excel modelling functions
Click on the poll below (opens in a new window) to tell us which advanced Excel function you'd like to see included in our advanced financial modelling course material next:
Wednesday, 21 December 2011
Advanced financial modelling course tips
Training company FTA Ltd answers a recent question raised on its advanced 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 advanced financial modelling course and other related training for accountancy, law and banking and finance professionals.
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 advanced financial modelling course and other related training for accountancy, law and banking and finance professionals.
Tuesday, 8 March 2011
Friday, 4 March 2011
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 one of Financial Training Associates’ Excel modelling courses.
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?
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 valuation modelling and other finance-related courses for accountancy, banking, law and other professionals.
Friday, 25 February 2011
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 merger modelling and LBO 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 http://www.financialtrainingassociates.com/modelling-course-subjects/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 LBO modelling course and merger modelling training, for law, accountancy, banking and financial services professionals.
This post relates to material covered on a merger modelling and LBO 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 http://www.financialtrainingassociates.com/modelling-course-subjects/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 LBO modelling course and merger modelling training, for law, accountancy, banking and financial services professionals.
Thursday, 30 September 2010
Monday, 13 September 2010
New modelling course from FTA Ltd
Financial Training Associates Ltd has just announced the release of a newly designed Excel 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. "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
free cpd course.mp4
See http://www.cpd-courses.org/CPD-course-subjects/free-CPD.htm for a free CPD course from Financial Training Associates Ltd
Tuesday, 20 July 2010
Free CPD from ACCA and FTA Ltd
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 http://www.accaglobal.com/members/publications/accounting_business/CPD/debtfree 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 course.
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.
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 http://www.cpd-courses.org.
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 http://www.accaglobal.com/members/publications/accounting_business/CPD/debtfree 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
Friday, 4 December 2009
Excel modelling training: modelling goodwill and intangibles in a merger
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.
Friday, 25 September 2009
Law CPD: a new course for lawyers, accountants, solicitors and finance executives
Qualified solicitors and legal professionals are required to undertake CPD (continuous professional development). CPD requirements are determined by the SRA (Solicitors’ Regulation Authority), with the CPD year running to 31 October. Solicitors are encouraged to assume responsibility for their own development by choosing from a wide range activities relevant to their professional responsibilities and personal development. All solicitors who in legal practice or employment, or who work 32 hours or more per week, are required to complete a minimum of 16 hours’ CPD per year. At least 25 per cent of the requirement must be met by participating in courses that are offered by providers authorised by the SRA and which require attendance for one hour or more.
For more on law CPD points requirements click here:
· Law CPD
For more on CPD hours requirements for other professions, click here:
· CPD points.
Legal CPD course provider accredited for the legal profession
Financial Training Associates is accredited with the SRA as a CPD training provider for the legal profession. The company offers an extensive range of introductory, intermediate and specialist courses which can be delivered at a location to suit. Financial Training Associates' courses are regularly reviewed and revised to ensure they take account of latest developments, utilising experienced professional trainers who are experts in their field.
CPD training courses for solicitors
Financial Training Associates has developed a new CPD accredited course for lawyers titled: “negotiating the big issues in transaction agreements”. The course examines the commercial impact of key negotiations attached to company sales and acquisitions. The programme is designed as a forum where solicitors can comfortably practice debating the main value items in transactions.
Click here for more information regarding FTA's CPD training courses:
Friday, 18 September 2009
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.
Subscribe to:
Posts (Atom)
Posts
