Multiple Acquisitions or Projects. There are many situations, most notably roll-up campaigns, where it is necessary to model multiple acquisitions of what is essentially the same business from an economic standpoint. Projects (an oil well, factory, branch office, etc.) can be conceptually modeled as an acquisition. They have an acquisition (building) cost, and return a certain set of financials. Although this example is focused on an acquisition, a project can be modeled in this manner with a few changes to the particulars of the target company. A scaling mechanism and a few tailored formulas will allow you to quickly model multiple scaled acquisitions or projects along with a base business. Please download the free Multiple Acquisition Project Example Excel Spreadsheet: Multiple Acquisition.
- Underlying models. This spreadsheet aggregates a variable number of targets into a base business. The target model is on the “Target” tab and the base model is on the “Base” tab. The base business is the acquiring entity. The target is a prototype of the acquisitions or projects. The target in this case is meant to be generic. This target model can be scaled by multiples using inputs elsewhere in the model. Its time periods are generic because the target company can start in any year when aggregated (this is explained further below). The particulars of these models are not the focus of this example. Please refer to the simple model section of this site to learn more about the construction of these models.
- Adding up Targets. The Acq Input sheet contains the mechanics to add-up (but not to integrate in to the base company) the variously acquired target companies. The spreadsheet works by layering in the target company over multiple years, always starting with the first year of the target model. For example, selecting an acquisition in the year 2014, will add year 1 of the target company model to 2014 and year 2 of the target model to year 2015.
Inputs. Between columns H-M, rows 2-12 is the core driver of this sheet. Row 3 contains the number or scale of acquisitions. The target company in the first year of its model has revenue of 11,000. If you wanted to show the effect of a project or company with 25,000 in revenue in model year one, simply enter the ratio, or 2.27 into row 3 in the appropriate year. In the same manner, you could model the acquisition of multiple companies of the same size. This model assumes through its construction that each company is acquired at the beginning of the first year of the target model. If you wanted to show a company acquired in the middle of the year, you could enter 0.5 in 2011 and 0.5 in 2012, thus centering the total cash flow around the year 2011. The next input is not used to calculate on this sheet. It is the EBITDA multiple used to price the acquisition. You could use any method here to assign a value to the acquisition or project. The feature is that the price could change over time or based on the particular projects that you are pursuing, rather than being fixed for all acquisitions. The number of acquisitions is fed into a wedge below the input lines. This wedge should be followed diagonally for each beginning year. It can be interpreted by reading “how many of each year of the target model” for each year (column) of the aggregated target. Looking at 2013 (column K), there are 5x year 1, plus 3x of year 2, plus 1x of year 3 of the target model. The sum of these is the aggregated 2010 amount. There cannot be a year 4 or 5 of the target model, because those years could not have occurred by 2013.
Formulas. Click on cell B7. Select F2, or click the formula bar to view the formula. While this appears complicated, the construction is simple and allows this formula to be used throughout the income statement, balance sheet, and cash flow by just copying and pasting. The formula is made by recognizing that the multiplier row is constant for each year of the target model and that the target model column is constant for each multiplier row. For example, the year 1 column of the target is always multiplied by the year 1 row in the wedge, the year 2 column in the model is always multiplied by the year 2 row, etc. Therefore, the formula can be built quickly by multiplying the columns of the target by the row of the multiple wedge and locking (by adding the $ before the letter) the column of the target and row of the multiplier. After doing this for the first cell (5 multiplications), you can fill and copy the rest. There are two exceptions to this undertaking. They are end of year cash and the starting cash. As this sheet simply adds up the acquisitions, there is no starting cash (cell B76), only cash which is acquired by purchasing a company. The end of year cash must be modified by the cash held by the companies you acquire. This shows up in the cash flow statement and also on the balance sheet highlighted in blue. Recall that this model assumes the targets are bought at the beginning of the year. Therefore, you will receive all of year 1 cash flow. You also bought the opening cash at the beginning of year one. This cash must be added to the cash balance to generate the correct cash balance at the end of the year. While at this stage, you could use the multiplying formula to do this directly on the balance sheet, your cash flow statement would not tie to the balance sheet without the addition of row 77. Also, integrating into the base business model would be very difficult without a cash flow statement that ties and an explicit recognition of acquired cash (beyond the fact that it is useful to see it separated from an analysis standpoint).
- 3. Integrating with the Base Business. Please refer to tab “Combined”. Integration with the base business is basically adding the aggregated targets to the base business, except for some important points: 1) You must pay for your acquisitions, 2) You need to have funds to pay for the acquisitions, 3) you have bought the assets and liabilities, not the retained earnings & equity of the targets (your accountants are concerned with your equity, not theirs).
Paying for the acquisition. Refer to rows 86-95. Here is an acquisitions cost calculation. This simply uses the enterprise value principal applied to the base year of the target company to determine the cost of the acquisition. As typical, the calculation is shown broadly for de-bugging and clarity. This cash / equity price is used in the cash flow statement on row 69. Since row 69 (as others have been) is added to the sheet, the formula adding the base to the aggregate targets must change as it is copied down, or it will not refer to the correct cells on the other sheets.
Rolling cash forward. Cash for the combined companies is equal to: (i) starting cash of the base business, (ii) +/- cash flow of the combined businesses, (iii) plus acquired cash. Because you are sending money out the door to buy these businesses and potentially raising new capital to do so, you cannot just add up the base business and the aggregated targets, you must calculate a new cash flow. Therefore, row 80 uses the actual cash flow from the combined businesses, and acquired cash to roll total cash forward.
Paying for the acquisition II. It is never good to run out of money. Many significant project building or acquisition endeavors require additional capital. In a model such as this, the amount needed is a significant result of the model. Here, on row 75, a simple equity contribution was calculated. You could use the revolver techniques or other borrowings with or without equity to fund the acquisitions. Note that the formula avoids being circular by referring to elements and not totals that include itself. It simply adds money when the company runs out (this obviously would not work in the real world - credit ratios and other factors will have to be taken into account to determine how much capital a company could raise).
Goodwill. Goodwill is the accountant’s way of balancing your balance sheet. If you buy a dollar of assets for $1 (also a dollar of assets), your balance sheet has no trouble. This rarely happens. Suppose you buy $1 of assets for $5 (hopefully because of your valuation work and not because “it’s strategic”). Now what? Your assets go down by $5 of cash spent and up by $1 of stuff that you bought. You are out of balance by $4! Accountants to the rescue. The difference is a “fake” asset called “Goodwill” (the accounts will say it is not fake because you paid for it, but it is still nothing but a mathematical exercise for the sake of your balance sheet).
A side note on goodwill. It has variously been expensed, amortized on a fixed schedule in aggregate, not amortized at all, and is currently (though this will likely change) divvied up into a multitude of intangible assets (such as brand, customer relationships, employees, etc.) with various lives, with the “unknown” portion called goodwill. As this is a modeling exercise about acquisitions, and the total mechanics are the same, the dividing up of goodwill into its components and amortizing the various components is not covered here. Conceptually however, you can use a technique similar to that in the Depreciation and Taxation sections of this site to divide up and track the elements from each acquisition. Amortization of goodwill can have a large impact on Net Income and EPS, so it should not be ignored. Goodwill only affects taxation in asset acquisitions (opposed to stock acquisitions) and then only to the extent the IRS (or appropriate authority) allows you to deduct the depreciation of any particular intangible. You will need tax advisors to wade through these issues.
Goodwill (refer to sidebar above) is the difference between what you pay and the net assets that you are purchasing. A sub-calculation is on rows 97-104 for goodwill. Again, the companies are assumed to be purchased at the beginning of the year, so the balance sheet you bought is the opening one (closing of base year). The total equity of the balance sheet is the net assets purchased (row 99). What you paid is what you paid (no matter how you pay for it). Goodwill, is of course, an asset, so it will accumulate with goodwill added each year as on rows 102-104.
The Balance Sheet. Now that you know cash and you know goodwill, the rest of the balance sheet (other than equity) is just the addition of the base business and the aggregated acquisitions. For the equity account changes because stock was sold to pay for the acquisitions. So row 48 starts with the base business equity (your equity) and adds only the stock sold (not the equity of the bought businesses). Retained earnings are the historic retained earnings of the base business plus the accumulated earnings of the combined businesses. Note: If you had used debt to pay for acquisitions, then the net income of the combined businesses (because of interest expense) would be different from the sum of the parts. Also, any synergies or integration expenses will change the sum of income. In these cases, just make those portions of the income statement “live” and the rest of the model will follow along. Cash (row 27) is, of course, the ending cash from the cash flow statement. It is also important to note that the relationships between the balance sheet and cash flow statement that are present in simple models are not present here. For example, you cannot derive the change in cash due to accounts receivable by subtracting between years on the balance sheet. You must use the sum of the individual cash statements because you are buying opening positions in assets and liabilities each time you acquire. If you do not use a layered approach as presented here, your models will not work.