Costco is considering building a new warehouse management system to better manage its inventory and improve its responsiveness to the market. You have been asked to collect the data to make the assessment and have come back with the following information [which we describe as the "Base Assumption":

You estimate that it will cost Costco $122 million to purchase the hardware and $77 million to develop the software. The investment on hardware purchase has to be spent upfront (right now). However, since it takes one year to develop the software, the investment on software development will be made at year current + 1.
You estimate that it will cost Costco $22 million to install the new system and $33 million to train the staff to use the system. Since it takes one year to build the new system, these two investments will be made at year current + 1.
The expected sale price of the old hardware will be $38 million at year current + 1.
The deployment of the new warehouse management system will boost sales. You estimate the expected additional gross profit will be $27.5 million, starting at year current + 2, and grow at 8% per year.
The deployment of the new warehouse management system will save operational cost. You estimate the below operational costs will grow at the inflation rate of 3%.

Old System: $41 million (current year)
 New System: $24 million (beginning in year current + 2)  

6. The expected life of the new warehouse system is 5 years after the system goes into production. Since production status begins in year C+2, then the analysis should be carried into year C+6. The salvage value of the new system will be $17 million.  

Step 1: Create a spreadsheet that clearly indicates the development cost and payback analysis of the Base Assumption scenario (through year Current+6). Be sure to put all growth rates in an assumption box, and link the relevant cells to those assumptions - this will be important in Step 2 - below.

In addition to the Base Assumption - we introduce two more scenarios for analysis:

Step 2: Create two more spreadsheets that indicate the development costs and payback analysis for the "Conservative" and "Optimistic" scenarios. These should be created by revising the assumption box on the "Base Assumption" spreadsheet. Good idea to backup the "Base Assumption" analysis before doing these revisions.

Step 3: Calculate Net Present Value (NPV) of the investment under all three (base, conservative and optimistic) scenarios as in the table above (through year Current+6).

Reminder: The present value factor is:

Where "r" is the interest rate and "t" is the year (e.g., Current +1 would be "1", etc.). The "PVIF" or present value interest factor should be considered to three places beyond the decimal point.


Step 4: Create a summary table of your Payback and NPV results under each scenario. Provide a one-page explanation of your findings (as a coversheet) which includes an appropriate recommendation to Costco management.