Re: Insurance financial modeling
To those of you who have read the last three posts above, please allow us to clarify since the original row numbers referenced in the posts were not matching the completed file from the videos. Row 37 is actually row 35. Row 40 is actually row 38. That is, in our response, it should read: add one ro... To those of you who have read the last three posts above, please allow us to clarify since the original row numbers referenced in the posts were not matching the completed file from the videos. Row 37 is actually row 35. Row 40 is actually row 38. That is, in our response, it should read: add one row above the existing row 35 ("Mandatory debt (repayment) / borrowing", which now becomes row 36). Now, in the formula for the cells in your new row 39, which should read "Total Cash Available / (Required)", be sure to include the new row 35 in that sum formula. Apologies for any confusion. Thank you. Read More

Re: Carve-out Accounting
If the Parent Company is selling greater than 50% control of the SUB, this is considered a change of control and thus the Parent no longer consolidates and instead, is now minority interest shareholder (assuming equity accounting in which Parent retains 20%-50% stake). If that is the case, there is ... If the Parent Company is selling greater than 50% control of the SUB, this is considered a change of control and thus the Parent no longer consolidates and instead, is now minority interest shareholder (assuming equity accounting in which Parent retains 20%-50% stake). If that is the case, there is a gain on the basis since it is a change of control.

However, from your accounting entries, it seems as if the Parent is maintaining over 50% control, in which case, the new minority shareholder that is buying in, will use equity accounting. And the Parent does consolidation with minority interest. This also incurs a gain as you noted because minority interest is % of book value of the SUB. Under new mark-to-market rules however, now the minority interest (now called NCI - Non-Controlling Interest), will be at market values, which would further distort the entries. Read More

Re: Adv Merger model error
Correct observation!
However, in the 6th and last video of the Super-Advanced M&A course, at 6:27, we go back and fix the error by re-anchoring. If you want to match the (wrong) numbers throughout the video, leave it as incorrect zeroes until you reach that point in the video.

Re: Adv Merger Model - debt sweep
The proper way is to have it in order of priority of liquidation. So: ALL Senior Notes (primarily bank debt) (NEW and OLD, TAR and ACQ) ALL Corporate Bonds All Subordinated etc Our assumption was that all existing debt is corporate bonds and since we definitely raised new Senior Notes, we put Senio... The proper way is to have it in order of priority of liquidation.
So:
ALL Senior Notes (primarily bank debt) (NEW and OLD, TAR and ACQ)
ALL Corporate Bonds
All Subordinated
etc

Our assumption was that all existing debt is corporate bonds and since we definitely raised new Senior Notes, we put Senior Tranches on top.
Revolver in theory goes on top as well since it's the most senior, but our modeling best practices is to put Revolver at the bottom since generally, for a gonig concern, you shouldn't need to draw upon your revolver. Since our Complex LBO and Super-Advanced M&A models require revolver to be paid down before any other pre-payments, one could move the new revolver to the very top. Note, that we treated existing revolvers as Corporate Bonds since most likely they would be refinanced anyway, and certainly, the old revolver facility shut down. Read More

Re: Toggle Reference File Links
Hi, Thank you for your inquiry. Yes, this is doable fairly easily, combining a bunch of various tools in excel. While we generally discourage linking between files, we can understand why you want to in this case. Here’s how we’d go about it. Assume you’re working in a new file, called “Ro... Hi,

Thank you for your inquiry.
Yes, this is doable fairly easily, combining a bunch of various tools in excel. While we generally discourage linking between files, we can understand why you want to in this case.

Here’s how we’d go about it.

Assume you’re working in a new file, called “Roll-Up” and a tab called “Summary” that will pull all this information in. We also assume you’re pulling the same cell reference from each file/tab (else this doesn’t make sense to automate).

Set up your Summary as follows:

In cell A1, put in the FILE NAME as hard coded text:
Product Line by Division 10.13
In cell B1, put in the TAB NAME as hard coded text:
NE Watsontown
In cell C1, put in the CELL ADDRESS as hard coded text:
B4 (random cell, but update with your reference you want to pull in)

Now, if you did a “normal link”, here’s how the formula in say D1 of Summary would look:
=’[Product Line by Division 10.13.xlsx]NE Watsontown’!B4

So you need to concatenate to get that result and wrap an INDIRECT function around it to work. You can look up what INDIRECT does in Excel help. In D1, your answer is:
=INDIRECT("'["&A1&"]"&B1&"'!"&C1)
And viola!

Please note that in order for this to work, all the files must be in the same folder (if new file, it must be saved first). If the files are in different folders, just use the same logic and pre-pend the path/directory in front. Idea is to replicate the formula if you were to have manually linked it, except you are concatenating from different cells. Read More