Question

Your company is developing a new product for cleaning up oil spills. This process quickly turns oil spilled from tankers into a biodegradable component, but it is expense to produce and now that you have done some intial development, you are trying to determine if it is feasible to move forward into full scale production. Based on your engineering estimates, it will cost $22,500,000 to build the production facility to make this product. It will cost $1,200,000 to operate it annually. However, you don't know how many oil spills there will be per year and how much of your product will be needed. You look over the last 25 years of history and come up with 4 scenarios and you figure they are all equally likely and your company MARR is 20% due to the risk of this project for a 10 year life. Should you do it? Please Use Excel and show equations

Scenario 1	Few Spills
Sales	$1,500,000 per year
Scenario 2	Starts high, but goes down due to conversion to Renewable Energy
Sales yr 1	8,000,000  goes down by 3% per year
Scenario 3	Starts Low, but increases due to tankers getting old and in bad shape
Sales Yr 1	3,000,000 and increases by 4% per year
Scenario 4	Steady at first, but Declines by 50% per year in years 7-10
Sales Yr 1	6,000,000

Potential Answers: A)-13,264,500 B) -9,395,633 C) -21,242,258 D)310,9099

Answer 1

Here the likely NPV of the project is -9,395,633 (option B). As the figure is negative it should not be accepted.

Calculations: First determine the NPV of all the scenarios:

Year	Cost of production facility/annual operating costs	Sales (scenario 1)	Net cash flow (scenario 1)		Sales (scenario 2)	Net cash flow (scenario 2)		Sales (scenario 3)	Net cash flow (scenario 3)		Sales (scenario 4)	Net cash flow (scenario 4)	1+r	PVIF	PV of 1	PV of 2	PV of 3	PV of 4
0	22,500,000		- 22,500,000			-       22,500,000			-       22,500,000			-       22,500,000	1.2	1.000	-    22,500,000	-     22,500,000	-     22,500,000	-     22,500,000
1	1,200,000	1,500,000	300,000		8,000,000	6,800,000		3,000,000	1,800,000		6,000,000	4,800,000		0.833	250,000	5,666,667	1,500,000	4,000,000
2	1,200,000	1,500,000	300,000		7,760,000	6,560,000		3,120,000	1,920,000		6,000,000	4,800,000		0.694	208,333	4,555,556	1,333,333	3,333,333
3	1,200,000	1,500,000	300,000		7,527,200	6,327,200		3,244,800	2,044,800		6,000,000	4,800,000		0.579	173,611	3,661,574	1,183,333	2,777,778
4	1,200,000	1,500,000	300,000		7,301,384	6,101,384		3,374,592	2,174,592		6,000,000	4,800,000		0.482	144,676	2,942,411	1,048,704	2,314,815
5	1,200,000	1,500,000	300,000		7,082,342	5,882,342		3,509,576	2,309,576		6,000,000	4,800,000		0.402	120,563	2,363,982	928,167	1,929,012
6	1,200,000	1,500,000	300,000		6,869,872	5,669,872		3,649,959	2,449,959		6,000,000	4,800,000		0.335	100,469	1,898,829	820,486	1,607,510
7	1,200,000	1,500,000	300,000		6,663,776	5,463,776		3,795,957	2,595,957		3,000,000	1,800,000		0.279	83,724	1,524,840	724,484	502,347
8	1,200,000	1,500,000	300,000		6,463,863	5,263,863		3,947,795	2,747,795		1,500,000	300,000		0.233	69,770	1,224,206	639,049	69,770
9	1,200,000	1,500,000	300,000		6,269,947	5,069,947		4,105,707	2,905,707		750,000	-             450,000		0.194	58,142	982,590	563,146	-             87,213
10	1,200,000	1,500,000	300,000		6,081,848	4,881,848		4,269,935	3,069,935		375,000	-             825,000		0.162	48,452	788,446	495,812	-           133,242
														Total	-   21,242,258	3,109,099	-    13,263,486	-       6,185,889

Now as the scenarios are equally likely the project NPV = (0.25*-21,242,258.37) + (0.25*3,109,099.12) + (0.25*-13,263,486.18) + (0.25*-6,185,889.18)

= -9,395,633