Step 1: Read the Full Case.

On the “CapitalBudget” Worksheet:

Step 2: Calculate the weights of Equity and Weights of Debt for the firm. Use the stock and bond data provided in the case.

Step 3: Calculate the Cost of Equity for the firm. Use the CAPM and the Market data provided on on the Worksheet “MarketData”.

Step 4: Calculate the Cost of Debt for the firm. Use the information provided about the firms bonds to calculate the YTM.

Step 5: Calculate the after-tax cost of debt. Use the given tax rate for the firm.

Step 6: Use the results from steps 2-5 to calculate the WACC (Weighted Average Cost of Capital) for the firm.

Step 7: Input the appropriate Initial Cash Outlays

IMPORTANT: All cash inflows need to be POSITIVE and all cash outflows need to be NEGATIVE.

Step 8: Input the appropriate Cash flows from Operations

Step 9: Input the appropriate Terminal Cash flows.

Step 10: Compute the Net Cash flows for Years 0 – 4.

Step 11: Compute the PV of Net Cash flows for Years 0 – 4. (You can either use the EXCEL formula for PV() or use the mathematical formula for PV of a lump sum.)

Step 12: Compute the NPV of the Net cash flows – This can be done as the sum of the PV’s in Step 11 or using Excels NPV formula.

Note 2: Excel’s NPV formula needs to be adjusted by a factor of (1+ I) – Refer to the module 14 notes on CANVAS for details.

Step 13: Indicate the Accept/Reject decision for the most likely scenario.

Note 3: Copy and paste the NPV values in cells C48 – C50 as you will need to input the NPV’s for the 3 scenarios here – DO NOT REFERENCE values.

Step 14: Compute the NPV for the Best Case scenario by changing to the Best Case growth rate in cell B11 and indicate the Accept/Reject Decision for this scenario.

See Note 1 above – This is where it will be helpful.

Step 15: Compute the NPV for the Worst Case scenario by changing to the Worst Case growth rate in cell B11 and indicate the Accept/Reject Decision for this scenario.

On the “NPVProfile” Worksheet:

Step 16: Complete the table to generate a NPV profile for the Most-Likely Scenario. The graph will be automatically generated for you.

Note 4: A sample calculation for generating a NPV profile is shown on the NPVProfile worksheet