show work in excel with formulas, use 14% discount rate as required return

5. Bubba Golf is considering manufacturing a new super-sized' golf club to compete with the successful debut of the Holywood Smasher, produced by Koepka Industries. The initial invest- ment for this project would include $3.0 million in new machinery and an additional $240,000 in setup costs. (The amount to be capitalized and then depreciated is the sum of the machinery and setup costs.) The project life would be 4 years; however, in accordance with the IRS, the depre- ciation method would be 5-year MACRS. (See page 4 for a MACRS schedule.) The relevant re- quired return is 14% and the applicable tax rate is 34%. For simplicity, assume that a $600,000 investment in NWC is required immediately (to be recovered at the project's end) and the assets involved would have a salvage value of $108,000 (before any tax-expense implications) at the end of 4 years. At the end of this problem statement, you'll find year-1 projections that reflect the best estimates from the marketing and engineering teams. (a) (5 pts.) Conduct a scenario analysis to calculate net present value (NPV) for each of the three scenarios specified. Please create (and submit) only one worksheet that can be generalized across all cases, as opposed to creating three different worksheets for all scenarios. Make a note of all three NPVs to be used in part b. Then, leave your single worksheet with the Most Likely Sce- nario values as the inputs. (b) (3 pts.) What is the expected (i.e. probability-weighted) NPV? For part b, please create a separate worksheet that tabulates all three scenario-specific NPVs and also shows the overarch- ing expected NPV calculation. This worksheet should not contain your entire cash flow work- sheet; it should be very succinct. (c)(3 pts.) Now, return to your master spreadsheet and conduct a sensitivity analysis on sales (# units), price per unit, variable costs, and fixed production costs. Specifically, calculate the dollar change in NPV, given a 10% increase in the input variable from its base-case (most likely) value. For example, hold all input variables constant, increase sales to 44,000 units, calculate the new NPV, and report the dollar change in NPV and percent change in NPV for a 10% increase in sales. Next, again hold all input variables constant, increase price per unit to $363.00, calculate the new NPV, and report the dollar change and percent change in NPV for a 10% increase in selling price per unit. Do the same for the other two input variables and create a brief table that summarizes the work. This analysis will show NPV's sensitivity to changes in the various input variables. Finally, in order from most to least critical, indicate which inputs should perhaps be revisited to ensure that it's been forecasted accurately. Pessimistic Most Likely Optimistic Sales (in units) 35.000 40,000 45.000 Price per unit $290 $330 $370 Variable costs (per unit) $206 $198 $190 Fixed Production costs $2,100,000 $1,800,000 $1,500,000 Probability of outcome 30% 50% 20% *For simplicity, assume that the state which is realized at t=1 will be in effect for the project's duration. 5. Bubba Golf is considering manufacturing a new super-sized' golf club to compete with the successful debut of the Holywood Smasher, produced by Koepka Industries. The initial invest- ment for this project would include $3.0 million in new machinery and an additional $240,000 in setup costs. (The amount to be capitalized and then depreciated is the sum of the machinery and setup costs.) The project life would be 4 years; however, in accordance with the IRS, the depre- ciation method would be 5-year MACRS. (See page 4 for a MACRS schedule.) The relevant re- quired return is 14% and the applicable tax rate is 34%. For simplicity, assume that a $600,000 investment in NWC is required immediately (to be recovered at the project's end) and the assets involved would have a salvage value of $108,000 (before any tax-expense implications) at the end of 4 years. At the end of this problem statement, you'll find year-1 projections that reflect the best estimates from the marketing and engineering teams. (a) (5 pts.) Conduct a scenario analysis to calculate net present value (NPV) for each of the three scenarios specified. Please create (and submit) only one worksheet that can be generalized across all cases, as opposed to creating three different worksheets for all scenarios. Make a note of all three NPVs to be used in part b. Then, leave your single worksheet with the Most Likely Sce- nario values as the inputs. (b) (3 pts.) What is the expected (i.e. probability-weighted) NPV? For part b, please create a separate worksheet that tabulates all three scenario-specific NPVs and also shows the overarch- ing expected NPV calculation. This worksheet should not contain your entire cash flow work- sheet; it should be very succinct. (c)(3 pts.) Now, return to your master spreadsheet and conduct a sensitivity analysis on sales (# units), price per unit, variable costs, and fixed production costs. Specifically, calculate the dollar change in NPV, given a 10% increase in the input variable from its base-case (most likely) value. For example, hold all input variables constant, increase sales to 44,000 units, calculate the new NPV, and report the dollar change in NPV and percent change in NPV for a 10% increase in sales. Next, again hold all input variables constant, increase price per unit to $363.00, calculate the new NPV, and report the dollar change and percent change in NPV for a 10% increase in selling price per unit. Do the same for the other two input variables and create a brief table that summarizes the work. This analysis will show NPV's sensitivity to changes in the various input variables. Finally, in order from most to least critical, indicate which inputs should perhaps be revisited to ensure that it's been forecasted accurately. Pessimistic Most Likely Optimistic Sales (in units) 35.000 40,000 45.000 Price per unit $290 $330 $370 Variable costs (per unit) $206 $198 $190 Fixed Production costs $2,100,000 $1,800,000 $1,500,000 Probability of outcome 30% 50% 20% *For simplicity, assume that the state which is realized at t=1 will be in effect for the project's duration