Question

Must use Microsoft Excel

1. Analysis of cost for various pipe sizes is as follows:

       Select the best alternative below using Present Worth analysis. (Use Excel)

	A	B	C	D
Installed Cost of Pipeline and Pump	22,000	23,000	25,000	30,000
Cost per hour pumping	1.2	.65	.5	.4
The pump will operate 2000 hours per year
Interest is 7%/year and Life is infinite

Answer 1

Workings

	Interest Rate	7%
		A	B	C	D
Initial Cost of Pipeline & Pump		22,000	23,000	25,000	30,000
Cost/hour Pumping		1.2	0.65	0.5	0.4
No. of Hours		2000	2000	2000	2000
Total Cost per year=No.of hours per year* Cost/hour		2400	1300	1000	800
PW of Cost=Sum of Discounted Future Cost/ year
Discounted Future Cost		Future Cost at time "t" /(1+interest rate)^t
We are looking for period that is infinite hence t is very large
PV=FC1/(1+r)+FC2/(1+r)^2+FC3/(1+r)^3+FC4/(1+r)^4+…infinite times & in our case FC1=FC2=FC3=… for each Pipeline
but 0<(1/1+r)<1 therefore sum of discounted future cash flow is [FC/(1+r)]*(1/r)
		A	B	C	D
Present worth of Future Cost		32042.72363	17356.4753	13351.13485	10680.90788
Net Present Worth of Cost=Present Worth of Future Cost+ Initial Cost		54,043	40,356	38,351	40,681
						As we can see Net Present worth of cost is lowest for Pipeline C therefore Best alternative to choose is Pipeline C

Formulas used:

Column A	Column B	Column C	Column D	Column E	Column F	Column G
5	Interest Rate		0.07
6	Pumping		A	B	C	D
7	Initial Cost of Pipeline & Pump		22000	23000	25000	30000
8	Cost/hour Pumping		1.2	0.65	0.5	0.4
9	No. of Hours		2000	2000	2000	2000
11	Total Cost per year=No.of hours per year* Cost/hour		=D9*D8	=E9*E8	=F9*F8	=G9*G8
12
13
14	PW of Cost=Sum of Discounted Future Cost/ year
15	Discounted Future Cost		Future Cost at time "t" /(1+interest rate)^t; FCt= Future Cost at Time t in future.
16	We are looking for period that is infinite hence t is very large
17	PV=FC1/(1+r)+FC2/(1+r)^2+FC3/(1+r)^3+FC4/(1+r)^4+…infinite times & in our case FC1=FC2=FC3=… for each Pipeline
18	but 0<(1/1+r)<1 therefore sum of discounted future cash flow is [FC/(1+r)]*(1/r)
19			A	B	C	D
20	Present worth of Future Cost		=D10/(1+$D$5)*(1/$D$5)	=E10/(1+$D$5)*(1/$D$5)	=F10/(1+$D$5)*(1/$D$5)	=G10/(1+$D$5)*(1/$D$5)
21	Net Present Worth of Cost=Present Worth of Future Cost+ Initial Cost		=D19+D7	=E19+E7	=F19+F7	=G19+G7
22
23	As we can see Net Present worth of cost is lowest for Pipeline C therefore Best alternative to choose is Pipeline C
24