966.00 76.00 $1,000 I. Given the following data on proposed capital budgeting project. (Numerical Inputs Expected from you are highlighted in yellow and Formula/Function Inputs are highlighted in blue) Parameters Economic life of project in years. 4 Market Value of Equity # of Shares Outstanding Price of New Equipment 214.630.00 Market Value of Debt # of Bonds Outstanding Change in NWC Total Market Value S Market Price of Bonds S Fixed Costs Weight of Equity Market Price of Stock S Variable Costs (% of Revenue) ( Weight of Debt Salvage value of New Equipment E(R.) Marginal Tax Rate RE Bond Info Provided for your convenience First Year Unit Sales 4700 B Years to Maturity Sales Growth Rate 22.00% Cost of Equity (r.) | PMT Unit Sale Price S 93.00 Cost of Debt (ra) FV First Year Revenue After-Tax Cost of Debt Before tax YTM WACC Before tax YTM 2 2 Note Cells C21 and C22 include the initial (today's) cash flows. . ColumnD through G are the operating cash flows. Spreadsheet for determining Cash Flows Cells G38-G40 contain the terminal cash flows. Timeline: Year 0 1 2 3 II. Net Investment Outlay = Initial CFS Price of Equipment S (214,630,00) Change in NWC III. Cash Flows from Operations Revenue Generation Unit Sales 4700 Unit Sale Price $ 93.00 Revenues s 437,100.00 Costs Variable Costs Fixed Costs Depreciation Earnings Before Taxes Taxes Net Income Depreciation Net operating CFS IV. Terminal Cash Flows Salvage Value Tax on Salvage Value Return of NWC V. Final Cash Flow Cash Flows Present Value of CFS NPV of Project Summarize Answers for NPV under three cases in area below Sales Growth Rate NPV Accept? Best Case 32.0% 22.00% Most Likely Worst Case 2.2% Note 1: Please use appropriate Cell referencing in Excel so that your numerical values update when you change any input(s). This will be helpful when you analyze the Best and Worst Case growth rate scenarios. Numerical Inputs expected from you are highlighted in yellow and Formula/Function Inputs are highlighted in blue. 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 PVO 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+1) - 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 "NPV Profile" 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 NPV Profile worksheet Step 17: Optional - Complete the Worksheet "Answer Sheet" as needed. (see instructions on the sheet) Hint: Explain/Clarify any assumptions or methods used. Note: Use this sheet to convey any comments to the instructor. Step 18: Double check your work Step 19: Save with Solutions for Most Likely Case and Upload your final Excel project by the due date. 966.00 76.00 $1,000 I. Given the following data on proposed capital budgeting project. (Numerical Inputs Expected from you are highlighted in yellow and Formula/Function Inputs are highlighted in blue) Parameters Economic life of project in years. 4 Market Value of Equity # of Shares Outstanding Price of New Equipment 214.630.00 Market Value of Debt # of Bonds Outstanding Change in NWC Total Market Value S Market Price of Bonds S Fixed Costs Weight of Equity Market Price of Stock S Variable Costs (% of Revenue) ( Weight of Debt Salvage value of New Equipment E(R.) Marginal Tax Rate RE Bond Info Provided for your convenience First Year Unit Sales 4700 B Years to Maturity Sales Growth Rate 22.00% Cost of Equity (r.) | PMT Unit Sale Price S 93.00 Cost of Debt (ra) FV First Year Revenue After-Tax Cost of Debt Before tax YTM WACC Before tax YTM 2 2 Note Cells C21 and C22 include the initial (today's) cash flows. . ColumnD through G are the operating cash flows. Spreadsheet for determining Cash Flows Cells G38-G40 contain the terminal cash flows. Timeline: Year 0 1 2 3 II. Net Investment Outlay = Initial CFS Price of Equipment S (214,630,00) Change in NWC III. Cash Flows from Operations Revenue Generation Unit Sales 4700 Unit Sale Price $ 93.00 Revenues s 437,100.00 Costs Variable Costs Fixed Costs Depreciation Earnings Before Taxes Taxes Net Income Depreciation Net operating CFS IV. Terminal Cash Flows Salvage Value Tax on Salvage Value Return of NWC V. Final Cash Flow Cash Flows Present Value of CFS NPV of Project Summarize Answers for NPV under three cases in area below Sales Growth Rate NPV Accept? Best Case 32.0% 22.00% Most Likely Worst Case 2.2% Note 1: Please use appropriate Cell referencing in Excel so that your numerical values update when you change any input(s). This will be helpful when you analyze the Best and Worst Case growth rate scenarios. Numerical Inputs expected from you are highlighted in yellow and Formula/Function Inputs are highlighted in blue. 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 PVO 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+1) - 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 "NPV Profile" 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 NPV Profile worksheet Step 17: Optional - Complete the Worksheet "Answer Sheet" as needed. (see instructions on the sheet) Hint: Explain/Clarify any assumptions or methods used. Note: Use this sheet to convey any comments to the instructor. Step 18: Double check your work Step 19: Save with Solutions for Most Likely Case and Upload your final Excel project by the due date