Can you thoroughly explain how to do each step?

Exercise 1: Return on investment (ROI) for an IT investment Suppose you are working for a start-up retailer which plans to use phone and fax to receive customer orders. It expects to have 141,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 self- service 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 upfront cost $4,000,000 (in Year 0) and ongoing cost of $1,000,000 in each year that follows (i.e. in Year 1, Year 2, Year 3). 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 E10 to cell K20. Step 2 Calculate the cash flows (revenues - costs) 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 ut the table from cell E23 to cell K38. 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 E40 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. Step 5 Last, what is your conclusion? What would you suggest the manger to do? Type your answer into cell F46. Hints: 1) Please take a careful look at all assumptions listed from cell Al to C30 on the sheet. Use absolute reference when needed. 2) The yearly tax should be equal to "the yearly net income X tax rate. 3) For simplicity, assume the retailer start to run business (i.e. generate revenue) from Year 1. 4) The net incremental cash flows included in your NPV function should not include cash flow in Year 0. 051 v fr D E F G H 1 General Assumptions Transactions in Year 1 Average order size in Year 1 COGS as a % of the sales price Average order size annual growth rate Tax rate Discount rate to calculate NPV) 141000 $258 70% 3% 35% 12% Base case (No Web portal) 10 Base case (No Web portal) assumptions Number of transactions annual growth rate Average processing cost per order Year 0 Year 1 Year 2 Year 3 $30 141000 258 S Number of transactions Average order size (US $) Baseline Revenue (US $) COGS (US $) Order Processing Cost Net Income TAX Cash Flows With the Web portal Year 0 Year 1 Year 2 Year 3 With the Web portal assumptions Initial implementation cost Ongoing cost Total transaction increase in Year 1 Number of transactions annual growth rate Average processing cost of a Web transact Average processing cost per order % total transaction with the Web portal $4,000,000 $1,000,000 20000 10% $18.50 50% Number of transactions Average order size (US $) Baseline Revenue (US $) COGS (US $) Order Processing Cost Gross profit Costs of the Web portal Initial implementation cost Ongoing cost Net Income Tax Cash Flows Incremental Cash Flows Year 1 Year 2 Year 3 Net incremental cash flows Net present value Conclusion Your conclusion