Re: Insurance financial modeling
Hello, we have a solution for you. The original logic that we used to construct the insurance sweep was that if you generated cash that year, i.e. Cash Available/Required in row 8 of Sweep tab was positive, that the minimum cash balance was essentially ignored because you would be building cash b... Hello, we have a solution for you.

The original logic that we used to construct the insurance sweep was that if you generated cash that year, i.e. Cash Available/Required in row 8 of Sweep tab was positive, that the minimum cash balance was essentially ignored because you would be building cash balance in that scenario. Then, if you lost money that year (row 8 is negative), then the min cash balance kicks in because you want to ensure enough for working capital purposes. Any required cash comes from portfolio sweep.

However, you are correct in pointing out that in either case, to build a purely dynamic model, as we always advocate, the minimum cash balance requirement should always be in play and affecting the model regardless of the other figures. As such, here is the fix required, from your completed model, Sweep tab:

1) In cell C16, add this label: Cash Required to hit Min Cash Bal / (Excess above Min)

2) In cell G16, add this formula: =G15-G10 and copy it to column K. We then made this row 16 white cell so it won't show up on printout.
The logic is that you are calculating the amount of money required to "replenish" cash to hit MIN or if negative, it is the excess above MIN. This is an interim subtotal we need to facilitate the next calculation.

3) In cell G17 (Cash Required from Portfolio), put in this formula and copy to column K: =-MAX(0,G16-G8)
The logic is that you are calc'ing either:
a) Cash Req. less Generated => get ? from Port
b) Excess Cash less Shortfall => get ? from Port
c) Max(0) to force positive number & correct calc

OPTIONAL NOTE: You can actually remove the negative sign in front of the MAX function here, but if you do so, in all of the Discretionary Sweep calculations below (rows 22, 29, 35, 41 and 47), remove the negative sign in front of G17 in the MIN(MAX()) function. In addition, in Debt Borrowing in row 80, you need to switch the signs, so it would be: =G17+G22+G29+G35+G41+G47. If you leave the - in front of the MAX in Step 3 above, you do not need to change the signs in the Sweeps or Debt Borrowing.

4) Finally, in cell G11, (Change in Cash), update the formula to: =G8+G17 and copy to column K
The logic is that there are two sources of cash changes: (i) cash generated that year (or shortfall required if row 8 is negative); and (ii) portfolio drawdowns.

Thus, the end result is that we reverse the flow and the logic to calculate the Cash Required from Portfolio first based on any changes from cash beginning balance vs minimum cash balance and cash generated (or shortfall) that year. Then the Change in Cash is easier to calculate.

This is cleaner, more dynamic and more important, gets rid of the ugly IF statement that we initially had used because we got a bit lazy.

To prove this works in all scenarios, we tested the following relevant scenarios, and the model changes we outlined are sufficient to capture all possible outcomes:
Min Cash > Cash Beg: Made Money (more than Required) => Build Cash Balance
Min Cash > Cash Beg: Made Money (less than Required) => get from Portfolio
Min Cash > Cash Beg: Lost Money => Portfolio: Required + Loss
Min Cash > Cash Beg: Lost Money (lost less than Excess) => Cash down, no Portfolio required
Min Cash < Cash Beg: Lost Money (lost more than Excess) => Cash down to Min, get from Portfolio
Min Cash < Cash Beg: Lost Money (equal to Excess) => Cash down to Min, no Portfolio required
Min Cash < Cash Beg: Made Money => Cash Up
Min Cash = Cash Beg: Lost Money => Cash Same, get from Portfolio
Min Cash = Cash Beg: Made Money => Cash Up

Seek and yee shall find. Read More

Re: Merger modeling - Purchase price allocation
What would your other option would have been? We didn't want G37 x F42 per our previously stated response: "No particular reason. We want to say, allocate the book value portion and whatever is left is allocated based on whatever %age we chose." And again, as previously stated, Trx Costs ... What would your other option would have been? We didn't want G37 x F42 per our previously stated response:
"No particular reason. We want to say, allocate the book value portion and whatever is left is allocated based on whatever %age we chose."

And again, as previously stated, Trx Costs is 100% allocated to GW so for sure, you cannot take G39 x F42.

Make sense? Read More

RE: Complex Trading Comps Analysis: Weighs question
The weights for wacc in complex trading comps were estimated based on the target capital structure of the industry that is "normalized". If the weights are roughly the same as the actual, then it doesn't really matter which one to use. However, in a case of Costco, with no debt, one may want to sens... The weights for wacc in complex trading comps were estimated based on the target capital structure of the industry that is "normalized". If the weights are roughly the same as the actual, then it doesn't really matter which one to use. However, in a case of Costco, with no debt, one may want to sensitize the capital structure for actual (no debt) and a industry average.

There are no silly questions - unless you are asking what LTM stands for! Read More

Re: Cash Flow and Balance Sheets for M&A Models
Generally speaking, when pitching, it is not required since its a bit overkill and you don't have all the required info from mgmt.

However, in real deals, it is critical to build the fully blown version. This is covered in our super-advanced m&a class

Re: Bank Modeling
You are not incorrect.
However, the convention is for Recoveries to be offset against GCO. Keeping in mind that GCO don't impact the IS, so hence you don't see it as a net against Provisions.