please help me fill it in and find net present value

Suppose you are working for a start-up retailer which plans to use phone and fax to receive customer orders. It expects to have 130,000 transactions in its first year (Year 1). The average sales revenue per order is $258 and expects a 3% annual growth rate. The average cost of goods sold (COGS) is 70% of each order. The transaction cost using phone and fax averages $30 per transaction. After the next year (Year 1), the company anticipates an annual growth rate of 3% for the number of transactions. From your MIS class, you know it is important for a company to leverage information technology to create value and improve operation efficiency. Thus, you recommend the retailer to consider adopting a Web portal, which provides two major advantages. First, it enables selfservice order entry by customers, thus reducing costs. More specially, the transaction cost using phone and fax averages $30 per order whereas the electronic processing through the Web portal only costs $3 per order. For simplicity, let's assume that after implementing this Web portal, 50\% customers will use the Web portal and the other 50% will still use traditional phone and fax. This leads to average order cost to be 50%30+50%3=$16.50 per order. Second, the Web portal also enables access into a broader market for customers, potentially increasing revenue. If the retailer starts to use the Web portal from Year 1, the number of orders in Year 1 will have an increase of 20,000. Moreover, after Year 1, the annual growth rate of the number of orders is expected to become 10%. However, the project is assumed to have an upfront cost of $4,000,000 (in Year 0 ) and ongoing costs of $1,000,000 in each year that follows (i.e. in Year 1, Year 2, Year 3). The manager asks you to use ROI analysis to evaluate this new project. In the Exercise 1 worksheet, please do the following: Step 1: Calculate the base case revenue (current earnings), costs (for taking orders), and cash flows expected in the future if the retailer would like to only use phone and fax to receive customer orders (without introducing the new E-business project). Fill out the table from cell H13 to cell K21. Step 2: Calculate the same if the retailer implements the proposed Web portal project. You need to include total revenue, potential cost savings, and all costs of the Web portal project. Fill out the table from cell H27 to cell K37. Step 3: Get the net incremental cash flows by subtracting the base case cash flows from the expected cash flows with the new Web portal project. Fill out the table from cell H42 to cell K42. Step 4: Use the NPV (Net Present Value) function embedded in Excel to calculate the net present value (NPV) for this stream of future cash flows and fill out the cell G44. Write-up: What is your conclusion? What would you suggest the manager to do? Play around with the percentage of customers that will use the web portal. Would your suggestion change under a different percentage (compared to 50% )? What percentage of web portal users would make the NPV equal to exactly 0? (Hint: use Goal Seek) Home Insert Draw Page Layout Formulas Data Review View Automate Script Lab @ Tell me Suppose you are working for a start-up retailer which plans to use phone and fax to receive customer orders. It expects to have 130,000 transactions in its first year (Year 1). The average sales revenue per order is $258 and expects a 3% annual growth rate. The average cost of goods sold (COGS) is 70% of each order. The transaction cost using phone and fax averages $30 per transaction. After the next year (Year 1), the company anticipates an annual growth rate of 3% for the number of transactions. From your MIS class, you know it is important for a company to leverage information technology to create value and improve operation efficiency. Thus, you recommend the retailer to consider adopting a Web portal, which provides two major advantages. First, it enables selfservice order entry by customers, thus reducing costs. More specially, the transaction cost using phone and fax averages $30 per order whereas the electronic processing through the Web portal only costs $3 per order. For simplicity, let's assume that after implementing this Web portal, 50\% customers will use the Web portal and the other 50% will still use traditional phone and fax. This leads to average order cost to be 50%30+50%3=$16.50 per order. Second, the Web portal also enables access into a broader market for customers, potentially increasing revenue. If the retailer starts to use the Web portal from Year 1, the number of orders in Year 1 will have an increase of 20,000. Moreover, after Year 1, the annual growth rate of the number of orders is expected to become 10%. However, the project is assumed to have an upfront cost of $4,000,000 (in Year 0 ) and ongoing costs of $1,000,000 in each year that follows (i.e. in Year 1, Year 2, Year 3). The manager asks you to use ROI analysis to evaluate this new project. In the Exercise 1 worksheet, please do the following: Step 1: Calculate the base case revenue (current earnings), costs (for taking orders), and cash flows expected in the future if the retailer would like to only use phone and fax to receive customer orders (without introducing the new E-business project). Fill out the table from cell H13 to cell K21. Step 2: Calculate the same if the retailer implements the proposed Web portal project. You need to include total revenue, potential cost savings, and all costs of the Web portal project. Fill out the table from cell H27 to cell K37. Step 3: Get the net incremental cash flows by subtracting the base case cash flows from the expected cash flows with the new Web portal project. Fill out the table from cell H42 to cell K42. Step 4: Use the NPV (Net Present Value) function embedded in Excel to calculate the net present value (NPV) for this stream of future cash flows and fill out the cell G44. Write-up: What is your conclusion? What would you suggest the manager to do? Play around with the percentage of customers that will use the web portal. Would your suggestion change under a different percentage (compared to 50% )? What percentage of web portal users would make the NPV equal to exactly 0? (Hint: use Goal Seek) Home Insert Draw Page Layout Formulas Data Review View Automate Script Lab @ Tell me