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
