80.2K

Verified Solution

Link Copied!

This is for my MIS (Management Information System) class, and we recently learned how to input data for the ROI analysis. However, we were not taught on WHICH DATA to use, just on HOW to put the data in Excel for the ROI analysis. Below are 3 images showing, respectively: Context, Question, and ROI Analysis. The question asks to calculate the base case revenue and the future cash flows if the retailer would like to only use the phone and fax to receive customer orders. And the data must be inputted in the table from cell E10 to cell K20. To reiterate my point earlier, I have an idea on how to put the data (e.g. what to put in the fx bar in Excel), but I don't know in general how to calculate base case revenue and the future cash flows with the Excel data I am given (e.g. which cells do I use for my data to be inputted in the fx bar). I tried to self-teach myself on how to do this, but I am so clueless and lost on what to do. Please, can you help me on how to do this? CONTEXT: QUESTION: ROI ANALYSIS:

Learning Objectives: Become familiar with using Excel to calculate return on investment (ROI) for technology investment Become familiar with setting up an Excel model and how to separate assumptions from calculations " " Exercise 1: Return on investment (ROI) for 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 you 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 transaction 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 transaction cost to be 50%*30+50%*3-$16.5 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 transactions in Year 1 will have an increase of 20,000. Moreover, after Year 1, the annual growth rate of the number of transactions is expected to become 10% However, the project is assumed to have upfront cost $4 M (in Year 0) and ongoing cost of $1 M in each year that follows (i.e. in Year 1, Year 2, Year 3) The manager asks you to use a ROI analysis to evaluate this new project. Based on the worksheet "ROI_analysis", please do the following: First, calculate the base case revenue, costs, and cash flows expected in the future if the retailer would like to only use phone and fax to receive customer order (without introducing the new E- business project). Fill out the table from cell E10 to cell K20. (10 points) General Assumptions Transactions inYear 1 Average order size in Year COGS as a % of the Average order size annual growth rate Taxrate Discount rate [to calculate NPV) 141000 $258 70% sales price Base case (No Web portal) assumptions Base case (No Web portal) Year 1 Number of transactions annual growth Av Year 0 Year 2 Year 3 erage processing cost per order $30 Number of transactions Average order size (US$) Baseline Revenue (US $) COGS (US $) Order Processing Cost Net Income 141000 258 Cash Flovs With the Web portal assumptions With the Web portal Initial implementation cost Ongoing cost Total transaction increase inYear 1 Number of transactions annual arowth Average processing cost of a Web trar Av $4,000,000 $1,000,000 20000 10% $3 $16.50 Year 0 Year 1 Year 2 Year 3 Number of transactions Average order size(US $) Baseline Revenue (US$) COGS (US $) Order Processing Cost Gross profit erage processing cost per order Costs of the Web portal Initial implementation cost cost Net Income Cash Flovs Incremental Cash Flows Year 0 Year 1 Year 2 Year 3 Net incremental cash flows Net present value Learning Objectives: Become familiar with using Excel to calculate return on investment (ROI) for technology investment Become familiar with setting up an Excel model and how to separate assumptions from calculations " " Exercise 1: Return on investment (ROI) for 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 you 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 transaction 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 transaction cost to be 50%*30+50%*3-$16.5 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 transactions in Year 1 will have an increase of 20,000. Moreover, after Year 1, the annual growth rate of the number of transactions is expected to become 10% However, the project is assumed to have upfront cost $4 M (in Year 0) and ongoing cost of $1 M in each year that follows (i.e. in Year 1, Year 2, Year 3) The manager asks you to use a ROI analysis to evaluate this new project. Based on the worksheet "ROI_analysis", please do the following: First, calculate the base case revenue, costs, and cash flows expected in the future if the retailer would like to only use phone and fax to receive customer order (without introducing the new E- business project). Fill out the table from cell E10 to cell K20. (10 points) General Assumptions Transactions inYear 1 Average order size in Year COGS as a % of the Average order size annual growth rate Taxrate Discount rate [to calculate NPV) 141000 $258 70% sales price Base case (No Web portal) assumptions Base case (No Web portal) Year 1 Number of transactions annual growth Av Year 0 Year 2 Year 3 erage processing cost per order $30 Number of transactions Average order size (US$) Baseline Revenue (US $) COGS (US $) Order Processing Cost Net Income 141000 258 Cash Flovs With the Web portal assumptions With the Web portal Initial implementation cost Ongoing cost Total transaction increase inYear 1 Number of transactions annual arowth Average processing cost of a Web trar Av $4,000,000 $1,000,000 20000 10% $3 $16.50 Year 0 Year 1 Year 2 Year 3 Number of transactions Average order size(US $) Baseline Revenue (US$) COGS (US $) Order Processing Cost Gross profit erage processing cost per order Costs of the Web portal Initial implementation cost cost Net Income Cash Flovs Incremental Cash Flows Year 0 Year 1 Year 2 Year 3 Net incremental cash flows Net present value

Answer & Explanation Solved by verified expert

See Answer