Ivestment Analysis with best case, worst case, medium on excel

An Investment Analysis Case Study: Costco 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\": 1. You estimate that it will cost Costco $110 million to purchase the hardware and $73 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. 2. You estimate that it will cost Costco $18 million to install the new system and $32.5 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. 3. The expected sale price of the old hardware will be $35 million at year current + 1. 4. The expected life of the new warehouse system is 5 years. Since the system comes into production status in Year C+2, the salvage value of the new system will be $14.5 million (at year C+6). 5. The deployment of the new warehouse management system will boost sales. You estimate the expected additional gross profit will be $26 million, starting at year current + 2, and grow at 7% per year. 6. 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 2.5%. Old System: $40 million (current year) New System: $22.5 million (beginning in year current + 2) 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\