Question

You are asked to conduct a five-year economic feasibility study of an e-commerce site for a small office supply company. Due to construction time, the system will only be in operation for 6 months for the current year (Year 1). Once the system is in operation, you expect to reduce staff cost by $37,000 and to reduce printing/postage cost by $12,000 for a full year of operation between year 1 and year 3. The numbers will be $38,000 and $15,000, respectively, for year 4 and year 5.

New computer and software costs $6,000, and the cost of system development is estimated at $87,500. The software license and the cost of hiring/training a part-time operator is totaled $8,000 per full year of operation between year 1 and year 3. The same cost is estimated to be $9,000 for year 4 and 5.

1.) Calculate the NPV for the above project assuming a discount rate of 6%. Is there a break-even point in the first five years? If so, when? What is the ROI for the project? Make sure to prorate the appropriate system costs and benefits for Year 1. Make a spreadsheet showing all years.

2.) What is the impact on NPV if the discount rate is 9% instead? Does the NPV decreases with higher discount rate? Why or why not? Paste another copy of the spreadsheet.

3.) Use Goal Seek feature of Excel to find out the amount of the development cost that would make the NPV zero for the project.

Answer 1

Part 1)

Particulars	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5
Reduction in staff cost		$18,500.00	$37,000.00	$37,000.00	$37,000.00	$37,000.00
Reduced printing/postage		$   6,000.00	$12,000.00	$12,000.00	$38,000.00	$15,000.00
New computer and software costs	$   -6,000.00
cost of system development	$ -87,500.00
cost of hiring/training		$ -4,000.00	$ -8,000.00	$ -8,000.00	$ -8,000.00	$ -8,000.00
Net cash flows	$ -93,500.00	$20,500.00	$41,000.00	$41,000.00	$67,000.00	$44,000.00
Discount rate	6%
Discounting factor	1	0.94339623	0.88999644	0.83961928	0.74725817	0.62741237
Present value of cash flows	$ -93,500.00	$19,339.62	$36,489.85	$34,424.39	$50,066.30	$27,606.14
NPV	$                                                                                                                          74,426.31
Break even is when NPV>0, in year 4
ROI is the IRR	21.38%

2)

Net cash flows	$ -93,500.00	$20,500.00	$41,000.00	$41,000.00	$67,000.00	$44,000.00
Discount rate	9%
Discounting factor	1	0.91743119	0.84167999	0.77218348	0.64993139	0.50186628
Present value of cash flows	$ -93,500.00	$18,807.34	$34,508.88	$31,659.52	$43,545.40	$22,082.12
NPV	$                                                                                                                          57,103.26

NPV decreases with the increase in discount rate, because the NPV is inversely proportional to the discounting rate.

3) considering a 6% discount rate

Particulars	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5
Reduction in staff cost		$18,500.00	$37,000.00	$37,000.00	$37,000.00	$37,000.00
Reduced printing/postage		$   6,000.00	$12,000.00	$12,000.00	$38,000.00	$15,000.00
New computer and software costs	$        -6,000.00
cost of system development	$   -1,61,926.31
cost of hiring/training		$ -4,000.00	$ -8,000.00	$ -8,000.00	$ -8,000.00	$ -8,000.00
Net cash flows	$ -1,67,926.31	$20,500.00	$41,000.00	$41,000.00	$67,000.00	$44,000.00
Discount rate	6%
Discounting factor	1	0.94339623	0.88999644	0.83961928	0.74725817	0.62741237
Present value of cash flows	$   -1,67,926.31	$19,339.62	$36,489.85	$34,424.39	$50,066.30	$27,606.14
NPV	$                                                                                                                                             -