LBO enhanced model question

[wstguest]

The LBO enhanced model, once I put in 2007 as the last full year, uses
2007 as the year to add the balance sheet deal adjustments--even though
the deal will happen in, say, August 2008. It then gives the 2008P and
2009-2012E balance sheets. This seems counter-intuitive: Shouldn't the
deal adjustments happen in 2008?

[wstexpert]

You would need to build a stub period into your model. Add a new column after 2007 full year and use that to add with adjustments for pro forma. Your financials (IS and CF etc) would need to make sure 2008 column are stub year (say sept to dec 2008).

[wstguest]

To clarfiy: I would need to insert a column next to 2007 (before the
Adjustments column) for, say, the 6/30/08 balance sheet; this would come
under the "Actual" section. Then have the adjustments apply to that new
column, which then gives the Proforma("2008"? or "2007"?) column? WOuld I
still keep the separate "Estimanted 2008" column following (the model
hardwires the column title to be the year following the last year under
Actual)?

[wstexpert]

The pro forma is now proforma 6/30/08.
You would keep estimated 2008 still as you still have 6 months in the year. Hence you need a last 6 months (2H 08 is the label) for IS and CF and Debt. You can make all of 2008 2nd half only, thus ending up with a 4.5 year projection model. Or add a brand new year for a 5.5 year model.

[wstguest]

I am confused on how to modify the model assuming the following deal
characteristics:

1) The target is a private company
2) The company will be bought on 8/30/08, using asset-backed debt
financing via an LBO; that is, the company is bought as an asset deal with
no debt
3) Management gets upfront equity with Sponsor, plus further equity if
EBITDA targets are hit in 2009 and 2010

My confusion comes from the LBO Summary page, the BS, and the IRR page:

Summary Page
a) Should I hardwire in the equity contrib and debt in the Illus. Val.
Summary box, so that the Enterprise Value comes on to the known deal size
(since we set that number as the buyer)?

b)The Equity Sources box is linked to the Sources/Uses of Funds box. I
know what the equity contrib is (fixed at $3.2) and I know what the debt
number will be going into the trans. However, what throws off all the
numbers in these 2 boxes is the fact that the Uses calc reads the
historical debt numbers from the BS page--but the deal will come with zero
debt since it's an asset purchase. This, is turn, gives me an incorrect
Sources of Funds as well as Equity Sources number. Note that I'm assuming
I should use the Refinance All switch. [If I plug in 0 for those numbers,
the Sources/Uses only pick up the new equity contrib. and fees while
ignoring the new deal debt.] How do I handle this?

Balance Sheet

c) How should I handle the Adjustments colum re the new deal debt given
the above?

IRR Page

d) To handle the upfront equity grants, I assume that is subtracted on the
Summary page in the Equity Sources box. But how do I handle the
conditional future grants? SHould I add an IF formula under the EBITDA
line where I say "if EBITDA hits minimum number in this year subtract X%
of total EBITDA", where X=new additional %age equity ownership? [The
resulting EBITDA stream that is discounted for Sponsor would then reflect
their actual ownership percentage--I think....]

[wstexpert]

Summary Page
a) The Illustrative Valuation box is meant for information only. The real key is the Sources & Uses of Funds. The Illustrative Valuation should be a snapshot of the current valuation of the entire company (even if less than 100% purchase).

b) You can hard code the Equity Contribution as well as the Debt. Since you know your specific numbers and deal structure, then yes, definitely overwrite it (and make it blue). If you didn't already know it, the suggested deal terms read off from the BS page as you noted.

The Refinance switch comes into play only if you have existing debt, but you just mentioned that you don't have any debt, so this shouldn't be any issue?

Balance Sheet
c) After hard coding your Sources & Uses (effectively driving your Pro Forma Capital Strucutre), everything should flow through dynamically. The only issue might be if you have existing debt but you aren't taking that along. So an easy fix would be to have your Historical BS (last actual year have no debt on it - you'd have to find the balancer to make BS balance still).

IRR
d) This is commonly known as a waterfall (yours sounds like a simplified one, which is good). In the IRR page, add a new row or rows under EBITDA and do exactly what you just said. Keep the match as simple as possible. If you have EBITDA figures to achieve (vs growth rates) then hard code the EBITDA figures in a new row, and in another row, hard code the additional %ages to be received. These are both hard coded since its already been negotiated. Then if the EBITDA targets are hit, mgmt gets additional %age equity. This flows through to the Implied Financial Sponsor Equity Value (lowering it). and everything else updates. Create a new block of calculations for the mgmt's Implied Equity Value.

In either case, EBITDA should NOT change since this doesn't sound like a bonus paid in cash, but rather, additional equity. SO EBITDA shouldn't change and shouldn't be discounted, etc.

[wstguest]

It's the BS issue that still confuses me. Asset purchase deals (like this one) mean we buy the company without the debt. How do I "find the balancer"? If I simply subtract the existing debt and add the new deal debt in the BS adjustments how can I be sure to have the BS balance? This seems like a recurring issue with asset purch deals.

It is this issue which feeds the SandU issue on the Summary sheet.

[wstexpert]

You have ask yourself, what is the new Balance Sheet that you are buying?
Because the old debt never makes it into the deal, you don't want to
include
it on the actual, historical BS else you won't balance correctly as you
noted.

If you are only buying the assets, you have to recast the balance sheet
pre-deal. Use equity as the balancer.

So based on your file, keep assets the way they are, at $26,867. blow out
Interest Payable, Revolver and Short Term & LTD. Those three add up to
$14,643. add that same amount to Common Stock. Then you balance in the
historical year.

Since this is an Asset Deal, there is NO Goodwill to be created. Thus you
can create an additional switch to force Goodwill to be zero.

[wstguest]

A couple of follow-up questions re the model, now that I have modified it
to fit my LBO via asset purchase deal (no existing debt acquired) closing
8/30/08:

a) Below you assert that such a deal structure creates no GW, but it does
(as per a Partner at a major PE firm). I'm confused as to your contrary
assertion.

b) I've attached a copy of my model to illustrate the issue on the IRR
page re the 2 versions of SPonsor returns w/PIKs and warrants. I keep
getting #NUM errors despite having the same formulas as the clean version
we built in the videos.

c) Debt Sweep: I copied the New Senior Debt--Term Loan module to
incorporate a second tranche of senior debt. FOr discretionary pyts the
first term loan module uses a conditonal formula (row 18) that uses any
spare cash to pay down the term loan. If I copy that formula to senior
tranche #2 do I need to modify the condition to say any spare cash after
paying off mandatory pyt for tranche # + revolver sweep + tranche #1
discretionary? And so on if there's a senior tranche #3, etc.? Or,
alternatively, do I simply copy the same conditional formula for tranche
#2 and the revolver sweep only (which I what I did, in fact)?

[wstexpert]

If its a true asset deal, you are buying an asset and there's no goodwill. If the lawyer says otherwisen go with the lawyer as they presumbaly have viewed deal specific structure. It also depends on if its a 338(h)(10) election and if so, there is goodwill as that is a tax election. In short, too many variables - we've covered the basics in our M&A Deal Structuring video course.

The error may be due to your starting point or your assumptions. Unfortunately we would not be able to troubleshoot your specific model further.

Yes, follow the same logic for the new det as the term loan - excess cash available (if any) is now initial cash + revolver changes + term loan so it gets cumulative.