Problem Joe Fox is the manager of the Gear division and plans to submit a proposal for an expanded production area. Joe has projected various conditions for revenues and expense for the expansion, including different sizes and quality components. Knowing that the effect will likely differ under each, Joe plans to submit a conservative estimate for his proposed capital budgeting project this year to avoid a repeat of an unfavorable post-audit evaluation as occurred two years ago. Joe has provided the following data to be used in the analysis of selecting the proposal to be submitted to the capital budget committee. Required rate of return 6.0% Student Work Area Years of Project Scenario Useful Life Net Initial Investment Operating Cash Flows Year 1 Table of useful lives and cash flows for different capital budgeting scenarios (Dollar amounts in thousands) Operating Operating Cash Flows Year 2 Operating Operating Cash Flows Year 4 a. Calculation of IRRS Cash Flows Year 3 A 6 $ (43,869) $ 9,006 $ 9,564 $ 8,831 $ 9,557 $ B 6 (51,967) 11,372 12,042 11,259 11,517 Cash Flows Year 5 9,143 $ 11,880 Operating Cash Flows Year 6 Project Scenario IRR 8,906 10,758 A B C 4 (46,516) 12,414 12,970 12,586 11,730 C 4 (51,118) 14,982 12,853 13,817 14,031 D E 5 (71,715) 15,237 17,496 17,826 15,962 17,682 E F 4 (49,709) 13,054 12,078 13,866 13,686 F G 4 (55,519) 15,361 13,898 15,514 15,491 G H 6 (46,336) 10,422 10,050 9,228 10,221 10,725 9,079 H I 5 (53,417) 12,843 13,283 12,562 11,670 12,677 J 5 (60,032) 12,845 12,328 10,734 10,113 10,960 J K L M N 6545 (51,321) 10,630 8,576 9,787 8,273 9,265 10,909 K (67,073) 13,627 12,263 13,931 13,080 12,984 L (43,994) 9,537 8,490 8,690 9,395 M (39,793) 9,526 9,079 9,779 10,746 8,277 N Instructions: There are three parts to this problem. Use Excel to perform the following: a. Use Excel's IRR function to calculate IRR for each project scenario. b. Create a column chart that illustrates the IRR for each project. Include a descriptive chart title and axes labels in the chart. c. Examine the chart. Which project scenarios are acceptable? Which project scenario would you recommend that Joe submit to the capital budget committee? Justify your response. b. Chart for part b c. Response to part c C Problem Joe Fox is the manager of the Gear division and plans to submit a proposal for an expanded production area. Joe has projected various conditions for revenues and expense for the expansion, including different sizes and quality components. Knowing that the effect will likely differ under each, Joe plans to submit a conservative estimate for his proposed capital budgeting project this year to avoid a repeat of an unfavorable post-audit evaluation as occurred two years ago. Joe has provided the following data to be used in the analysis of selecting the proposal to be submitted to the capital budget committee. Required rate of return 6.0% Student Work Area Years of Project Scenario Useful Life Net Initial Investment Operating Cash Flows Year 1 Table of useful lives and cash flows for different capital budgeting scenarios (Dollar amounts in thousands) Operating Operating Cash Flows Year 2 Operating Operating Cash Flows Year 4 a. Calculation of IRRS Cash Flows Year 3 A 6 $ (43,869) $ 9,006 $ 9,564 $ 8,831 $ 9,557 $ B 6 (51,967) 11,372 12,042 11,259 11,517 Cash Flows Year 5 9,143 $ 11,880 Operating Cash Flows Year 6 Project Scenario IRR 8,906 10,758 A B C 4 (46,516) 12,414 12,970 12,586 11,730 C 4 (51,118) 14,982 12,853 13,817 14,031 D E 5 (71,715) 15,237 17,496 17,826 15,962 17,682 E F 4 (49,709) 13,054 12,078 13,866 13,686 F G 4 (55,519) 15,361 13,898 15,514 15,491 G H 6 (46,336) 10,422 10,050 9,228 10,221 10,725 9,079 H I 5 (53,417) 12,843 13,283 12,562 11,670 12,677 J 5 (60,032) 12,845 12,328 10,734 10,113 10,960 J K L M N 6545 (51,321) 10,630 8,576 9,787 8,273 9,265 10,909 K (67,073) 13,627 12,263 13,931 13,080 12,984 L (43,994) 9,537 8,490 8,690 9,395 M (39,793) 9,526 9,079 9,779 10,746 8,277 N Instructions: There are three parts to this problem. Use Excel to perform the following: a. Use Excel's IRR function to calculate IRR for each project scenario. b. Create a column chart that illustrates the IRR for each project. Include a descriptive chart title and axes labels in the chart. c. Examine the chart. Which project scenarios are acceptable? Which project scenario would you recommend that Joe submit to the capital budget committee? Justify your response. b. Chart for part b c. Response to part c C