RE: Complex Trading Comps Analysis: Convertibles of COSTCO
No, upon conversion, the holder does NOT pay $22 per share. The $22 is simply used to determine the INITIAL value of the convertible bond/note when priced, so in short, the $22 is no longer required at this point. Don't confuse convertibles with options and warrants!

Re: Revolver, ST. Debt or LT Debt?
revolver is usually classified as short term debt. truth be told, the company's classification isn't really all that important. the key is to read the revolver credit agreement and ascertain the following: - length of revolver: usually 2 years or so (can be 4-6 years if issued in conjunction with a ... revolver is usually classified as short term debt.
truth be told, the company's classification isn't really all that important.
the key is to read the revolver credit agreement and ascertain the following:
- length of revolver: usually 2 years or so (can be 4-6 years if issued in conjunction with a prior M&A, etc)
- ability to pay down and redraw balance
- undrawn commitment fee (usually about 25 bps)

if these three key parameters are met (in our eyes and avoiding the legalise of the actual covenants), then it's a revolver and clearly short term debt. normally revolvers and commercial paper are used for short-term funding liquidity needs and hence, short-term. however, if somehow the company (or you) deem that is a long term for another reason, then that might impact your final decision. Read More

Re: WST Macro Problem
Step 1: disable capiq shortcuts: Go to capiq menu in excel, select settings, look for general at the bottom then uncheck use capiq shortcuts and click on. If you get an msg about online access ignore it. It will be disabled Step 2: reload WST macros: go to tools add-ins (alt + t+i) and uncheck WST ... Step 1: disable capiq shortcuts: Go to capiq menu in excel, select settings, look for general at the bottom then uncheck use capiq shortcuts and click on. If you get an msg about online access ignore it. It will be disabled

Step 2: reload WST macros: go to tools add-ins (alt + t+i) and uncheck WST macros and hit ok. Then go back and re-check WST macros and hit ok. Done! Read More

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