Excel modelling training: modelling goodwill and intangibles in a merger

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

What is goodwill?

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

Writing down goodwill under IFRS

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

Writing down goodwill under other accounting regimes

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

Lessons for financial modelling in Excel - the simple solution

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

The more complicated picture

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

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

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

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

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

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

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

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

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

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

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

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

Lessons for financial modelling in Excel: the more complicated solution

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

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

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

Conclusion

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

About the author: Financial Training Associates Ltd

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