The assignment is to do the LBO Pricing case called “Bidding to Win—The LBO of Wellhead Technology”. The case and related spreadsheet are also posted. The case requires that a purchase price and capital structure for an LBO be developed subject to some covenant and pricing constraints. You will need to read the case for some of the discussion below to make sense. The spreadsheet includes a mostly complete 3S model. You will need to complete the $F Statement by linking to appropriate cells in the model. If you do the links properly, the model will balance. The debt section is driven by the inputs in the Purchase Analysis section (see below). You’ll need to input the debt amounts for each tranche and the rate (for the Loan, input the spread). The model is setup to use all free cash flow to pay down the Loan so no dividends will be paid. To simplify the Base v Stress case analysis, I have set up the assumptions with a “toggle” in M193. “1” triggers the Base Case assumptions, “2” the Stress Case. To complete the model all that needs to be done is to: 1. Complete the $F Statement in the projection (which drives the debt reduction) 2. Develop the return analysis calculations for M202 & M203, and 3. Develop an optimal transaction structure (subject to the constraints outlined in the case) in the Purchase Analysis section at line 130. To do this you will experiment with how high an offer can be made (and the case context is that you are in a competitive auction) and then how it can be financed. The goal should be to find the highest bid that allows the firm’s minimum IRR hurdle to be satisfied. The deliverable is the completed model on Blackboard and a hard copy (or hand-written version) of the Bid Summary.