Question

There are two investment projects. Both projects will provide benefits to the city for the next 30 years. The annual rate is 7%. Below are the initial cash outlays and monthly benefits:

Ice Cream Shop
Alternative	Investment ($K)	Monthly Benefits
1	0	0
2	3000	24.5
3	6000	58.5
4	9000	76.4
5	12000	85.5
Cookie Shop
Alternative	Investment ($K)	Monthly Benefits
1	0	0
2	3000	28.6
3	6000	50.4
4	9000	65.5
5	12000	72.4

a) Find the NPV for each of the alternatives.

b) Based on costs and benefits and considering all possible investments together as one integrated capital program, choose the best alternative under the following total budget constraints. Explain how you arrive at your answers

1) 4 Millon

2) 8 Millon

3) 12 Millon

4) 16 Millon

Answer 1

Ice cream shop
Alternative	Investment ($k)	Monthly Benefits	NPV calculation	NPV	Ranking	Total ranking
1	0	0		0	5
2	3000	24.5	=(PV(7%/12,12*30,-24.5,0))-3000	682.54	4	6
3	6000	58.5	=(PV(7%/12,12*30,-58.5,0))-6000	2,792.99	1	1
4	9000	76.4	=(PV(7%/12,12*30,-76.4,0))-9000	2,483.50	2	2
5	12000	85.5	=(PV(7%/12,12*30,-85.5,0))-12000	851.30	3	5
Cookie shop
Alternative	Investment ($k)	Monthly Benefits	NPV calculation	NPV
1	0	0		0	4
2	3000	28.6	=(PV(7%/12,12*30,-28.6,0))-3000	1,298.80	2	4
3	6000	50.4	=(PV(7%/12,12*30,-50.4,0))-6000	1,575.50	1	3
4	9000	65.5	=(PV(7%/12,12*30,-65.5,0))-9000	845.15	3	7
5	12000	72.4	=(PV(7%/12,12*30,-72.4,0))-12000	-1,117.73	5	8
4 Million - Cookie shop alternative 2 as it is the highest available NPV in exchange of 3 million cash outflow
8 Million - Ice cream shop alternative 3 as it is the highest available NPV in exchange of 6 million cash outflow
12 Million - Ice cream shop alternative 3 as it is the highest available NPV in exchange of 6 million cash outflow, and Ice cream alternative 2 and cookie shop alternative 2
16 Million - Ice cream shop alternative 3 as it is the highest available NPV in exchange of 6 million cash outflow, and cookie shop alternative 2 and alternative 3 as it gives highest cumulative NPV