I've spent about 3 hours on this problem already and I think I have a good solution, but I want to make sure it checks out and see how someone else would solve this problem, as I only get one attempt and I want to make sure I get it right : ) Thanks in advanced!

Option A: Internal Development

The initial cost (Year 0) consists of the cost of new server and networking hardware, which the company estimates at $300,000.

In the first year, the IT team will be developing the e-commerce platform. This will cost an estimated $800,000 in employee wages and related costs. There will be no revenue in the first year, since the development is expected to take over a year

In the second year, the IT team will accrue another $400,000 in cost due to employee wages and related before the new e-commerce website is launched. After that, it is estimated that there will be another $200,000 in maintenance costs in the first six months after launch. The e-commerce site is expected to be in operation during the second half of Year 2 only, and is anticipated to bring in $200,000 in revenue (net of unit/sales costs) during this time.

In the third year, maintenance cost for the website is expected to be $150,000 total, whereas revenue from the website is expected to be about $600,000. In the fourth year, maintenance cost is expected to be $150,000 total and revenue from the website is expected to be about $750,000.

In the fifth year, maintenance cost is expected to be $150,000 in total. In addition, the IT department expects to need to spend another $200,000 to upgrade server and networking hardware. Revenue from the website is expected to be about $850,000.

Option B: Off-the-Shelf Software

The initial cost (Year 0) consists of the cost of new server and networking hardware, which the company estimates at $300,000, as well as the purchase of the software, which costs $975,000.

In the first year, the IT team will be populating content into the purchased e-commerce platform. This will cost an estimated $250,000 in employee wages and related costs. In addition, the company expects another $150,000 in maintenance costs. The company would also need to pay $120,000 in licensing fees to the software manufacturer. The e-commerce site is expected to launch about halfway through Year 1 and generate revenue of $200,000.

In the second year, the company expects $120,000 in maintenance costs and $120,000 in licensing fees. Revenue is expected to be about $600,000.

In the third year, the maintenance cost for the website is expected to be $120,000 with another $120,000 in licensing fees. Revenue from the website is expected to be about $750,000.

In the fourth year, maintenance cost is expected to run $120,000 and licensing fees $120,000. In addition, the IT department expects to spend another $200,000 to upgrade server and networking hardware. Revenue from the website is expected to be about $850,000.

In the fifth year, maintenance is expected to cost $120,000 and licensing fees $120,000. Revenue is expected to be $950,000.

Question:

1. Assuming a five-year time horizon for the analysis (consider the initial cost to occur in Year 0 and calculate through Year 5) and a discount rate of 8%, calculate the NPV for both options. Be sure to include all cash flows (i.e., revenue and cost) for every year in the analysis.

a. Use the details given in the Background section to determine the positive (revenue) and negative (cost) cashflows and calculate the net cashflow for each year in the analysis.

b. In your Word document, provide a screenshot of the spreadsheet(s) you used to calculate the cash flows for each year (Year 0 through Year 5) for each of the two options.

c. When complete, include in your Word document a screenshot of your spreadsheet showing the NPV calculations for both options.