Percentages need to be entered in decimal format, for instance 3% would be entered as .03.

West Coast Chemical Company (WCCC) is considering two mutually exclusive investments - Project A and Project B. The projects' expected net cash flows for Years 0-5 are shown in the spreadsheet provided.

Based on the information in the spreadsheet, what is the NPV for Project A and Project B based on the required return of 10%? What is each project's IRR?

If the required rate of return for each project is 13%, which project should West Coast select? If the required rate of return is 9%, what would be the proper choice? If the required rate of return is 15%, what would be the proper choice? For each scenario, explain why you chose the particular project.

Refer to the Model-Generated Data portion of the spreadsheet and identify at what rate the NPV profiles for the two projects cross.

The spreadsheet shows that Project A has a large cash flow in Year 5 associated with ending the project. WCCC's management is confident of Project A's cash flows in Years 0 to 4, but is uncertain about what its Year 5 cash flow will be. There is no uncertainty about Project B's cash flows. Under a worst-case scenario, Project A's Year 5 cash flow will be $40,000. Assuming a 13% required rate of return, what are the NPV profiles for Projects A and B? What are each project's IRR? Which project should be selected? Why?

Based on the information provided in Question 4, the best-case scenario estimates Project A's cash flow will be $50,000. Assuming the same 13% required rate of return, what are the NPV profiles for Projects A and B? What are each project's IRR? Which project should be selected? Why?

Transcribed Image Text:CO9 GRAPH INSTRUCTIONS 60,000 40,000 Project B 20,000 - Prject A 0% 2% 4%6% 8% 10% 12% 14% 16% 18%20% 22% 24% 26%28% 30% -20,000 -40,000
Transcribed Image Text:CO9 GRAPH INSTRUCTIONS Chapter 9 Spreadsheet-Related Problem (c09) NPV and IRR Analysis 1. There are a number of instructions with which you should be familiar to use these computerized models. These instructions appear in a separate worksheet labeled INSTRUCTIONS. If you have not already done so, you should read these instructions now. To read these instructions, click on theworksheet labeled INSTRUCTIONS. 2. The IRR formula requires that an initial "guess" value be entered. We have chosen 10 percent which works in most cases and does give the correct IRR for most capital budgeting problems encountered. However in some cases, the cash flow pattern may be such that Excel will not be able to solve for the IRR and the message "NA" will be displayed instead of the IRR value. 3. A graph of the NPV profiles for each project can be displayed if you click the worksheet labeled GRAPH at the bottom of this spreadsheet. To return to this worksheet, click on the worksheet labeled C09 at the bottom of the GRAPH worksheet. INPUT DATA: KEY OUTPUT: Expected Cash Flows Proj. A (45,000) (20,000) 11,000 20,000 30,000 45,000 Proj. B (50,000) 15,000 Proj. B Proj. A 9,367 Year NPV 6,862 1 IRR 14.28% 15.24% 2 15,000 15,000 15,000 15,000 3 Crossover rate 12.81% 4 Required return 10.00% MODEL-GENERATED DATA: NPV profile: Proj. A NPV Proj. B NPV 0.00% 41,000 25,000 3.00% 29,726 20,104 11,853 4,747 -1,398 -5,036 -9,902 18,696 13,185 6.00% 9.00% 8,345 12.00% 4,072 15.00% 282 17.00% -2,010 -5,141 20.00% IRR 14.28% 15.24% Crossover rate calculation: Difference Expected Cash Flows Proj. A (45,000) (20,000) 11,000 20,000 Proj. B (50,000) 15,000 in CFs 5,000 -35,000 -4,000 Year 1 2 15,000 3 15,000 5,000 30,000 15,000 15,000 45,000 15,000 30,000 Crossover rate = IRR of diff. CF 12.81% Data for NPV profile graph: Required Return Proj. A Proj. B NPV NPV 10.00% 9367.250126 6861.801541 0% 41,000 25,000 2% 33,285 20,702 16,777 13,185 9,891 6,862 4,072 1,496 4% 26,350 6% 20,104 14,466 8% 10% 12% 9,367 4,747 14% 554 -3,260 -6,733 16% -886 18% -3,092 -5,141 -7,045 -8,819 -10,474 -12,020 -13,466 20% -9,902 22% -12,797 -15,446 -17,874 -20,102 -22,149 24% 26% 28% 30%

Question

Percentages need to be entered in decimal format, for instance 3% would be entered as .03.

West Coast Chemical Company (WCCC) is considering two mutually exclusive investments - Project A and Project B. The projects' expected net cash flows for Years 0-5 are shown in the spreadsheet provided.

Based on the information in the spreadsheet, what is the NPV for Project A and Project B based on the required return of 10%? What is each project's IRR?
If the required rate of return for each project is 13%, which project should West Coast select? If the required rate of return is 9%, what would be the proper choice? If the required rate of return is 15%, what would be the proper choice? For each scenario, explain why you chose the particular project.
Refer to the Model-Generated Data portion of the spreadsheet and identify at what rate the NPV profiles for the two projects cross.
The spreadsheet shows that Project A has a large cash flow in Year 5 associated with ending the project. WCCC's management is confident of Project A's cash flows in Years 0 to 4, but is uncertain about what its Year 5 cash flow will be. There is no uncertainty about Project B's cash flows. Under a worst-case scenario, Project A's Year 5 cash flow will be $40,000. Assuming a 13% required rate of return, what are the NPV profiles for Projects A and B? What are each project's IRR? Which project should be selected? Why?
Based on the information provided in Question 4, the best-case scenario estimates Project A's cash flow will be $50,000. Assuming the same 13% required rate of return, what are the NPV profiles for Projects A and B? What are each project's IRR? Which project should be selected? Why?

CO9
GRAPH
INSTRUCTIONS
60,000
40,000
Project B
20,000 -
Prject A
0% 2% 4%6% 8% 10% 12% 14% 16% 18%20% 22% 24% 26%28% 30%
-20,000
-40,000

Transcribed Image Text:CO9 GRAPH INSTRUCTIONS Chapter 9 Spreadsheet-Related Problem (c09) NPV and IRR Analysis 1. There are a number of instructions with which you should be familiar to use these computerized models. These instructions appear in a separate worksheet labeled INSTRUCTIONS. If you have not already done so, you should read these instructions now. To read these instructions, click on theworksheet labeled INSTRUCTIONS. 2. The IRR formula requires that an initial "guess" value be entered. We have chosen 10 percent which works in most cases and does give the correct IRR for most capital budgeting problems encountered. However in some cases, the cash flow pattern may be such that Excel will not be able to solve for the IRR and the message "NA" will be displayed instead of the IRR value. 3. A graph of the NPV profiles for each project can be displayed if you click the worksheet labeled GRAPH at the bottom of this spreadsheet. To return to this worksheet, click on the worksheet labeled C09 at the bottom of the GRAPH worksheet. INPUT DATA: KEY OUTPUT: Expected Cash Flows Proj. A (45,000) (20,000) 11,000 20,000 30,000 45,000 Proj. B (50,000) 15,000 Proj. B Proj. A 9,367 Year NPV 6,862 1 IRR 14.28% 15.24% 2 15,000 15,000 15,000 15,000 3 Crossover rate 12.81% 4 Required return 10.00% MODEL-GENERATED DATA: NPV profile: Proj. A NPV Proj. B NPV 0.00% 41,000 25,000 3.00% 29,726 20,104 11,853 4,747 -1,398 -5,036 -9,902 18,696 13,185 6.00% 9.00% 8,345 12.00% 4,072 15.00% 282 17.00% -2,010 -5,141 20.00% IRR 14.28% 15.24% Crossover rate calculation: Difference Expected Cash Flows Proj. A (45,000) (20,000) 11,000 20,000 Proj. B (50,000) 15,000 in CFs 5,000 -35,000 -4,000 Year 1 2 15,000 3 15,000 5,000 30,000 15,000 15,000 45,000 15,000 30,000 Crossover rate = IRR of diff. CF 12.81% Data for NPV profile graph: Required Return Proj. A Proj. B NPV NPV 10.00% 9367.250126 6861.801541 0% 41,000 25,000 2% 33,285 20,702 16,777 13,185 9,891 6,862 4,072 1,496 4% 26,350 6% 20,104 14,466 8% 10% 12% 9,367 4,747 14% 554 -3,260 -6,733 16% -886 18% -3,092 -5,141 -7,045 -8,819 -10,474 -12,020 -13,466 20% -9,902 22% -12,797 -15,446 -17,874 -20,102 -22,149 24% 26% 28% 30%

Accepted Answer

“Since you have posted a question with multiple sub-parts, we will solve first three sub-parts for…