Question

Celtic Inc is considering expanding their existing vegetable processing operations with a new plant in Ireland. The plant is expected to produce 8,472,000 pounds of processed vegetables each year for the next 15 years (1,000 pounds per hour, 24 hours per day, 353 days per year). The land for the plant will cost approximately $500,000. Construction of the physical plant building will cost approximately $18,500,000, and the required investment in equipment will be an additional $22,500,000. The initial investment in net working capital required to have the plant operating at full capacity will include $2,500,000 in cash, $1,800,000 in receivables, and $650,000 in inventory, which will be partially funded by $250,000 of supplier financing.

The vegetables processed in Ireland will sell in year = 1 for $2.25 per pound. This selling price per pound is expected to increase by 0.50% per year throughout the life of the project. Operating expense has two components: 1) a fixed component, and 2) a variable component. The fixed overhead expenses will equal $4,254,000 the first year. These fixed operating expenses will increase each year by 1.00% throughout the life of the project. The variable operating expenses are equal to $0.75 per pound produced. These variable operating expenses are expected to increase by 0.50% per pound per year (for example, year 1 variable operating expenses are $0.75 per pound, and year 2 variable operating expenses are expected to be $0.75 × 1.005 = $0.75375 per pound). The plant building will be depreciated straight-line over 15 years to a value of zero. The equipment within the plant will be depreciated MACRS according to the 7-year schedule (percentages provided within the spreadsheet template). For analysis purposes, the firm uses 28.5% for their marginal tax rate for operating income. They assume a tax rate of 15.0% for capital gains. They use a discount rate of 12.0% for capital budgeting purposes.

Assume that the Ireland plant can be sold at the end of the life of the project. Assume that the land portion of this sale will reflect an annual growth rate in the land value of 1.5%. Assume that the building portion of this sale will reflect an annual growth rate in the building value of -10.0% (negative ten percent per year). Assume that the equipment portion of this sale will be $50,000. Assume that the entire initial investment in net working capital is recaptured at the end of the project.

I need assistance calculating NWC, NINV and NPV!!

Answer 1

NWC
Cash	2500000
Receivables	1800000
Inventory	650000
CA	4950000
Supplier Financing	250000
CL	250000
NWC = CA-CL
4700000
NINV
Land	500000
Plant building	18500000
Equipment	22500000
NWC	4700000
NINV	46200000

NPV
	Sale	Fixed Cost	Variable Cost	Dep on plant blg	Dep on Equip	Total proft	[email protected]%	Net profit	Dep	Cashinflow	PV@12% Cashinflow
1	19062000.00	4254000.00	6354000.00	1233333.33	3215250	4005416.67	1141544	2863872.92	4448583	7312456.25	6528978.79
2	19157310.00	4296540.00	6385770.00	1233333.33	5510250	1731416.67	493453.8	1237962.92	6743583	7981546.25	6362839.80
3	19253096.55	4339505.40	6417698.85	1233333.33	3935250	3327308.97	948283.1	2379025.91	5168583	7547609.24	5372239.18
4	19349362.03	4382900.45	6449787.34	1233333.33	2810250	4473090.90	1274831	3198260.00	4043583	7241843.33	4602322.36
5	19446108.84	4426729.46	6482036.28	1233333.33	2009250	5294759.77	1509007	3785753.24	3242583	7028336.57	3988066.92
6	19543339.39	4470996.75	6514446.46	1233333.33	2007000	5317562.84	1515505	3802057.43	3240333	7042390.76	3567894.33
7	19641056.08	4515706.72	6547018.69	1233333.33	2009250	5335747.34	1520688	3815059.35	3242583	7057642.68	3192519.13
8	19739261.36	4560863.79	6579753.79	1233333.33	1003500	6361810.46	1813116	4548694.48	2236833	6785527.81	2740560.87
9	19837957.67	4606472.43	6612652.56	1233333.33	0	7385499.36	2104867	5280632.04	1233333	6513965.37	2349001.22
10	19937147.46	4652537.15	6645715.82	1233333.33	0	7405561.16	2110585	5294976.23	1233333	6528309.56	2101940.96
11	20036833.20	4699062.52	6678944.40	1233333.33	0	7425492.95	2116265	5309227.46	1233333	6542560.79	1880829.89
12	20137017.36	4746053.15	6712339.12	1233333.33	0	7445291.77	2121908	5323383.61	1233333	6556716.94	1682945.93
13	20237702.45	4793513.68	6745900.82	1233333.33	0	7464954.62	2127512	5337442.56	1233333	6570775.89	1505852.24
14	20338890.96	4841448.81	6779630.32	1233333.33	0	7484478.50	2133076	5351402.12	1233333	6584735.45	1347367.33
15	20440585.42	4889863.30	6813528.47	1233333.33	0	7503860.31	2138600	5365260.12	1233333	6598593.45	1205538.35
										Cashinflow	48428897.3

Value of land at end of 15th year with annual growth of 1.5%					615877.87
Book Value					500000
Net Gain					115877.87
Tax on gain 15%					17381.681
Net proceeds					598496.19
PV of proceeds (Net proceeds*0.182696)					109343.02
Value of building at end of 15th year with annual growth of -10%					4232207
Book Value					0
Net Gain					4232207
Tax on gain 15%					634831.05
Net proceeds					3597376
PV of proceeds					657227.14
Equipment					50000
Book Value					0
Net Gain					50000
Tax on gain 15%					7500
Net proceeds					42500
PV of proceeds					7764.59
PV of NWC					858672.43
NPV = NINV - Cashinflow - PV proceeds of land , building,equipment,NWC
	-3861904.47