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.