Sign in or start a free trial to avail of this feature.
7. Defining our Transaction Assumptions
Our transaction assumptions in the merger model focus primarily on the sources of funding and the fees incurred in completing the deal.
To explore more Kubicle data literacy subjects, please refer to our full library.
Lesson Goal (00:04)
The goal of this lesson is to create the assumptions relating to the structure of the transaction.
Setting up the Assumptions Sheet (00:16)
The transaction assumptions for our M&A model contain a mixture of assumptions we must make, such as the percentage of the deal to be funded with cash, and values obtained from other sheets, such as the equity value and enterprise value of the target company.
The first assumption we must make is whether to refinance the existing debt of the target company. In our example, we choose to do so. Our model also uses a custom cell format that displays either Yes or No, but stores the value 1 for yes, and 0 for no. This allows us to use this cell in formulas.
Financing the Transaction (01:25)
The transaction will be financed by a combination of cash, debt, and stock. We need to identify what proportion of each should be used to complete the transaction. In our example, we assume no cash is used, as ShirtShop has a low cash balance. We assume that the deal is 40% funded by debt and 60% by stock.
When we fund a deal with stock, it means shareholders in the target company will receive shares in the combined entity after the deal closes. We need to identify the price at which these new shares will be issued. In our model, we assume that this share price is the current share price of the acquirer.
After completing these assumptions, we can calculate the amount of cash, debt, and stock used in the transaction. For cash and debt, we multiply the relevant percentage by the total equity value. To calculate the number of shares issued, we multiply the total equity value by the percentage of the transaction funded by stock, then divide by the price of the new shares.
Gross Margin on Revenue Synergies (03:47)
The final assumption we consider is the gross margin on revenue synergies. The increased revenue from synergies does not all turn into additional profit. It will incur a cost of goods sold expense as well. Therefore, we need to estimate the gross margin to understand how much profit can be generated from revenue synergies.
The appropriate gross margin depends on the nature of the synergies. In our case, the revenue synergies will most likely come from ShirtShop selling its products in TrouserTown stores, so it makes sense to use ShirtShop’s most recent gross margin as the gross margin on revenue synergies. If in doubt, it’s best to take the more conservative option.
In the previous lesson we calculated the synergies from the expected deal. With our synergies now calculated, we can begin to build our merger model on a new tab beginning with our transaction assumptions. The transaction assumptions for our merger model with be a combination of assumptions, which are hard coded in the blue cells, and figures that have been pulled from the other Excel tabs, for example, the offer price per share for the target and the basic number of shares outstanding for the target along with the target's equity value and enterprise value. The first assumption that we need to make is whether the target company's debt is going to be refinanced and to do this, I use a clever trick. If I press control one on my keyboard, it brings up the Format Cells dialog box and as you can see, in this custom format, yes will appear if the cell is positive and no will appear if the answer in the cell is zero. And as you can see in this current example, the cells says yes, but the value is one. This allows me to use this cell in formulas but also to have the word yes appear rather than one which is preferable from a user experience perspective. Once we've made a decision on refinancing the target company's debt, we have the equity value and the enterprise value which are both pretty straightforward. Now let's get onto the more difficult part which is financing the deal and here we have a choice of cash, debt or stock. As we saw earlier, Shirt Shop has very little cash on the balance sheet and probably near to its minimum cash balance, so I'm going to assume 0% for the amount cash that's going to fund the deal. Instead, the deal is going to be funded by 40% debt and 60% stock but I'll use a formula to do this which will subtract the debt and the cash from one. If I'm going to fund the deal with stock, it means that the shareholders in Trouser Town are going to receive shares in the combined entity of Shirt Shop after the deal closes.
And these shares are going to be issued at a particular price and I'm going to assume that this price is the current share price of the acquirer.
I can find that at the very top.
With these assumptions now complete, I can calculate the amount of cash used in the transaction, the amount of debt issued and the number of new shares issued. To do this, I'll simply link the percentage to the total amount which will be the equity value.
And I'll do the same for debt.
Management at Shirt Shop have told us that the cost of this debt is going to be 5.2% and it will require us to pay back 10% of the principal each year. To calculate the number of new shares issued is actually quite easy. I calculate the total amount of equity value that's going to be issued in stock and simply divide by the price. So, I'll take my 60%, multiply it by the equity value and then divide by the price per share.
And this tells me the number of new shares issued to the Trouser Town shareholders. So, if the sale does go through, Trouser Town shareholders will receive cash of 513.4 million and 6.25 million of new shares of the combined entity.
The last assumption I'm going to cover in this lesson is the gross margin on revenue synergies. Obviously the greater revenue due to combining the companies will not fall directly to profits. It will incur a cost of goods sold expense as well. As we know, the gross margin in Shirt Shop is around 50% but the gross margin in Trouser Town is closer to 60%. Which gross margin should we use on revenue synergies? Well, when you're in doubt, it's always best to go with the more conservative option. However, I also know that the revenue synergies are more likely to be from Shirt Shop selling its products in Trouser Town stores, so it's more likely that the gross margin on revenue synergies is going to be the 50.3% value that we currently have in 2016 for Shirt Shop, so I'll enter 50.3% here. Needles to say, we can run a sensitivity on this particular assumption later in our model.
In our next lesson, we'll complete our transaction assumptions and create a sources and uses table.