PLease work out steps in excel for practice

Answer Key Problem #3 (10 points) A company has a project under consideration that will produce cash flows of $850,000 per year for 17 years. The project will cost $4.25 million today to begin production. In one year, it is possible that the project will be a runaway success. If this is true, the company can spend S1,500,000 at that time to expand production. After expansion, the annual cash flows would be S1,280,000 per year. There is a 40 percent likelihood that the company will expand. Assume the project will still end 17 years from today. What is the value of the option to expand assuming a required retun of 13 percent? Expected annual cash flow Years Project cost Cost of expansion Expansion CF Probability of expanding Required return S 850,000 17 S 4,250,000 S 1,500,000 S 1,280,000 40% 13% NPV without expansion S 1,469,729.03 PV at Year 1 of expanding S 6,952,960.08 PV at Year 1 of not expanding Expected value in Year 1 minus Yr1 CF NPV w/option to expand Value of option to expand 5,613,293.81 6,149,160.32 1,943,947.18 474,218.15 Answer Key Problem #3 (10 points) A company has a project under consideration that will produce cash flows of $850,000 per year for 17 years. The project will cost $4.25 million today to begin production. In one year, it is possible that the project will be a runaway success. If this is true, the company can spend S1,500,000 at that time to expand production. After expansion, the annual cash flows would be S1,280,000 per year. There is a 40 percent likelihood that the company will expand. Assume the project will still end 17 years from today. What is the value of the option to expand assuming a required retun of 13 percent? Expected annual cash flow Years Project cost Cost of expansion Expansion CF Probability of expanding Required return S 850,000 17 S 4,250,000 S 1,500,000 S 1,280,000 40% 13% NPV without expansion S 1,469,729.03 PV at Year 1 of expanding S 6,952,960.08 PV at Year 1 of not expanding Expected value in Year 1 minus Yr1 CF NPV w/option to expand Value of option to expand 5,613,293.81 6,149,160.32 1,943,947.18 474,218.15