Need this problem solved in Excel. More info is attached in an Excel file:

Capital Budgeting Analysis: You have been asked to evaluate a potential acquisition of a smaller privately owned competitor. The acquisition candidate produces an EBITDA of 10% of your current EBITDA and is offered to your firm at a price of multiple of 8 times EBITDA. Assume the following:

Current debt costs you 8% and you can raise additional debt at this rate today. The loan is to be amortized over 7 years.

Current return on equity is 15% Current WACC is 10% Tax rate is 30% (constant)

80% of the purchase price is considered depreciable assets - to be depreciated over ten years on a straight-line basis with no residual values.

Residual value for this operation is to be 2x current EBITDA in year ten.

Create an after-tax cash flow analysis to answer the following:

Economic analysis: is this a fundamentally sound investment?

Using the tax cash flows and no debt (pure equity), is the prospect a positive NPV using ROE as the hurdle rate?

Using the after tax cash flows and the firm?s WACC, is this project desirable? Explain how you came to this conclusion.

WACC Pre-tax cost of debt Tax Rate Post-tax cost of debt 10.00% 8.00% 30.00% 5.60% Cost of Equity 15.00% Weight of Equity Weight of Debt Raytheon's Current EBITDA $ 3,509.00 Acquisition's EBITDA (10% of current EBITDA) Purchase Price (8 x Acquisition's EBITDA) Loan Portion Equity Portion Annual Instalments 10% $ 350.90 8 $ 2,807.20 To be amortized in 7 years Depreciable Assets (80% of Purchase Price) No. of Years Yearly Depreciation Residual Value Yr 10 (2 x Current EBITDA) 80% $ 2,245.76 10 $ 224.58 2 $ 701.80 Your Firm's Current EBITDA: $ 3,509 Acquisition Target's EBITDA: 10% $ 350.90 Step 1: NOTE: Problem gives you cost of debt and equity and WACC You will be required to figure out what % is equity vs debt (back into these numbers!). Step 2: % Component $ Amounts Equity in deal Debt for deal Step 3: Calculate Loan Payment schedule (you will need "P" and "I" for each yr); Step 4: Calculate depreciation Calculate amortization (of goodwill....if goodwill was created in the transactio). Goodwill is created if your purchase price for the entity is higher than the value of the assets purchas For this analysis (*), amortize your goodwill on a straight-line basis for 25 yrs (*) This step is not the current "law in the land"...but worthwile exercise Step 5: Assume the entity survives in perpetuity; however, you are to assume the values of yrs 10 to infinity are incorpo Step 6: Create an Income statement....then extend this to an After Tax Cash flow statement Yr 0 1 2 All numbers below are made up as an example only) Purchase Price (4,580) EBITDA (no Growth) 572.5 572.5 Int Exp Dep Exp Amort Exp Gross Profit Taxes Net Income After Tax Analysis: Net Income: Add: Dep Add: Amort Less: Principal Less: CapEx (assume $0) After Tax Cash flow= 3 572.5 4 5 6 572.5 572.5 572.5 Financial Analysis: Use purchase price, and after tax cash flows with your required rate of return (which rate? You need to select correct one) Do you buy? Yes or no? Perform the above analysis again...this time assume 100% equity (no debt; remove all cash flows due to debt). Calculate NPV and IRR here; does this answer differ from the above? That is, if leverage is the only reason to buy something, is that a good decision? alue of the assets purchased. s 10 to infinity are incorporated in a 2 X EBITDA value in yr 10 7 8 9 572.5 572.5 572.5 10 572.5 eed to select correct one) to get NPV and IRR answers. due to debt)