Question

Use Excel.

BACKGROUND: ABC is a manufacturer of medical devices. They buy components from others and complete the final assembly. Because the components are specialized, they have to buy them in large quantities and they are stored in an off-site warehouse that is 2 hours away.
They are evaluating if building a new warehouse will be worth the investment. They will save money by eliminating costs from their current state, but will incur new expenses from the future state. Details of each state are below.

CURRENT STATE (Savings): Every day, the company has a truck that brings them to the manufacturing facility at a cost of $1100 per day. The factory runs 5 days per week for 50 weeks a year. Additionally, the company pays the warehousing facility $420,000 per year for holding their goods. Both the weekly trucking and warehouse storage are expected to increase annually at 1.76% which is the inflation rate

FUTURE STATE (Costs): A promising young engineer is looking at building a warehouse on property the company already owns next to the factory and eliminate the need for the remote contracted warehouse. . It will cost $3,000,000 to build this warehouse, $125,000 for annual utilities and operational costs which will go up at the same inflation rate as the other costs.

Details: If the company MARR is 18% and the life of the project is 16 years, should they build the new warehouse? What is the NPV?

NPV BACKGROUND: ABC is a manufacturer of medical devices. They buy components from others and complete the final assembly. Because the components are specialized, they have to buy them in large quantities and they are stored in an off- site warehouse that is 2 hours away. They are evaluating if building a new warehouse will be worth the investment. They will save money by eliminating costs from their current state, but will incur new expenses from the future state. Details of each state are below. CURRENT STATE (Savings): Every day, the company has a truck that brings them to the manufacturing facility at a cost of $1100 per day. The factory runs 5 days per week for 50 weeks a year. Additionally, the company pays the warehousing facility $420,000 per year for holding their goods. Both the weekly trucking and warehouse storage are expected to increase annually at 1.76% which is the inflation rate FUTURE STATE (Costs):A promising young engineer is looking at building a warehouse on property the company already owns next to the factory and eliminate the need for the remote contracted warehouse.. It will cost $3,000,000 to build this warehouse, $125,000 for annual utilities and operational costs which will go up at the same inflation rate as the other costs. Details: If the company MARR is 18% and the life of the project is 16 years, should they build the new warehouse? What is the NPV? 2b What is the Project NPV? Should they build the new Warehouse? Yes/No NPV

Answer 1

Year	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	NPV
Cost of Building warehouse	-$30,00,000	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0
Savings in Current cost	$0	$6,95,000	$7,07,232	$7,19,679	$7,32,346	$7,45,235	$7,58,351	$7,71,698	$7,85,280	$7,99,101	$8,13,165	$8,27,477	$8,42,040	$8,56,860	$8,71,941	$8,87,287	$9,02,903
-Cost in Future State	$0	-$1,25,000	-$1,27,200	-$1,29,439	-$1,31,717	-$1,34,035	-$1,36,394	-$1,38,795	-$1,41,237	-$1,43,723	-$1,46,253	-$1,48,827	-$1,51,446	-$1,54,112	-$1,56,824	-$1,59,584	-$1,62,393
Net Cashflow	-$30,00,000	$5,70,000	$5,80,032	$5,90,241	$6,00,629	$6,11,200	$6,21,957	$6,32,903	$6,44,043	$6,55,378	$6,66,912	$6,78,650	$6,90,594	$7,02,749	$7,15,117	$7,27,703	$7,40,511
PV Factor @ 18% (1/1.18^n)	1.0000	0.8475	0.7182	0.6086	0.5158	0.4371	0.3704	0.3139	0.2660	0.2255	0.1911	0.1619	0.1372	0.1163	0.0985	0.0835	0.0708
PV of Cashflow	-$30,00,000	$4,83,051	$4,16,570	$3,59,239	$3,09,798	$2,67,161	$2,30,392	$1,98,684	$1,71,340	$1,47,759	$1,27,423	$1,09,886	$94,763	$81,721	$70,474	$60,775	$52,411	$1,81,447

2a	Project NPV	$1,81,447
2b	Build Warehouse ?	Yes	(As NPV is Positive)