Question

Demand for a new product is estimated as 50,000 units next year (year 2) and is expected to grow at 5% per year for the next 10 years (years 2 to 11). The management needs to decide the capacity of a new factory to be built for this product. Following data have been collected:

Size	Annual capacity (units)	Building cost	Annual operating cost
Small	50,000	$800,000	$20,000
Medium	60,000	$900,000	$25,000
Large	70,000	$1,000,000	$30,000

The factory with take 1 year to build, so it will be ready for production in year 2 (now is the beginning of year 1). The selling price of product will be $5 per unit and variable cost will be $2 per unit. Any excess demand will be lost (assume at no cost). If demand is less than capacity, the factory will produce enough to meet the demand. Assume all cash flows occur at the end of the year, including the building cost (will occur at the end of year 1).  

a. Create below a spreadsheet model of this problem. Assuming a small size (50,000 annual capacity) factory is built but make your model flexible so it can also work for other capacities. Use vlookup to pickup the building cost and annual operating cost associated with the annual capacity. Calculate the NPV of this project using discount rate of 7%.  

b. Use one-way data table to determine which capacity has the highest NPV.

Answer 1

a) NPV at Capacity 50000 units is $325821

b) NPV at Capacity would be highest at $555223.

WORKING NOTE:
Capacity : 50000 units
Year	Capacity	cash flow	Discount 7%	PV
0		0	1	0
1		-800000	0.935	-748000
2	50000	130000	0.873	113490	50000*3 - 20000
3(growth 5%)	52500	137500	0.816	112200
4	55125	145375	0.763	110921
5	57881	153644	0.713	109548
6	60775	162326	0.666	108109
7	63814	171442	0.623	106809
8	67005	181014	0.582	105350
9	70355	191065	0.544	103939
10	73873	201618	0.508	102422
11	77566	212699	0.475	101032
NPV				325821
Capacity : 60000 units
Year	Capacity	cash flow	Discount 7%	PV
0		0	1	0
1		-900000	0.935	-841500
2	60000	155000	0.873	135315	60000*3 - 25000
3(growth 5%)	63000	164000	0.816	133824
4	66150	173450	0.763	132342
5	69458	183373	0.713	130745
6	72930	193791	0.666	129065
7	76577	204731	0.623	127547
8	80406	216217	0.582	125838
9	84426	228278	0.544	124183
10	88647	240942	0.508	122399
11	93080	254239	0.475	120764
NPV				440522
Capacity : 70000 units
Year	Capacity	cash flow	Discount 7%	PV
0		0	1	0
1		-1000000	0.935	-935000
2	70000	180000	0.873	157140	70000*3 - 30000
3(growth 5%)	73500	190500	0.816	155448
4	77175	201525	0.763	153764
5	81034	213101	0.713	151941
6	85085	225256	0.666	150021
7	89340	238019	0.623	148286
8	93807	251420	0.582	146326
9	98497	265491	0.544	144427
10	103422	280266	0.508	142375
11	108593	295779	0.475	140495
NPV				555223