Assignment 1:
IT Investment Cost-Benefit Analysis Using Excel

 
You have been asked by the CIO of a retail company, Terraco, to do an analysis of an IT investment.  The original business model of Terraco utilized a long tail strategy of selling specialized products from different cultures of the world.  They offer a wide range of products that are typically hard to find outside of a particular region, including clothing, personal grooming products, accessories, and gift items including spices and non-perishable food.  Their slogan, representing their value proposition, is: "you don't have to travel to get exotic originals".  Their target customers are people who enjoy exotic items and want them even when they have returned home from their travels.  In addition, Terraco offers the convenience of one-stop shopping for items from many countries, selected based on their extensive marketing research.

They currently have 200 shops all over the U.S.A.  They are profitable but, of course, desire to have more sales and more profit.  The CIO believes that their long-tail strategy will be best served by having an e-commerce site.  This will not only add an additional revenue stream but will also reduce the need for physical stores and the number of sales personnel in each shop. Another major saving will be on the costs for shipping and warehousing as many items can be drop-shipped to a local store.   You have been working with directors from the IT and Finance departments to put together a cost-benefit analysis as part of the e-commerce plan presentation to the Executive Board, who will make the final IT investment decision. 

The e-commerce plan proposal includes detailed analysis of the market, value proposition, competitive environment, technology innovations, and so on as well as a detailed year-to-year marketing plan.  The cost and benefit assumptions in each of these analyses are captured and summarized below as the basis for your cost-benefit analysis.

1. Initial Investment:  For the first year, the following costs for implementing the e-commerce system will apply:




Hardware	$200,000
Software (DBMS, TPS, etc.)	$650,000
Network/Web services	$550,000
CRM & SCM  Apps	$800,000
Other Infrastructure	$275,000
Web Admin & support personnel	$1,200,000




2. Loan:  Terraco, being an established company with documented assets and cashflow, can obtain financing (borrow money) from the bank for all of the first year startup funds (those items listed above).  Assume that a five year loan is obtained and a monthly payment is scheduled for payback.  The negotiated interest rate is 6.5% annually. 
   
3. System Maintenance:  After the first year, the upkeep and maintenance of the online system including Web Admin personnel will cost a total of $1,300,000 for Year 2, and this is projected to increase by 7% per year over the previous year for Year 3, Year 4 and Year 5.
4. Marketing costs:  The cost for marketing for the first year is planned to be $3,500,000 and will increase over the previous year 10% from year 2 to 5.
   

B. Benefits Assumptions:

Based on substantial market analysis and surveys there are three major benefits forecast:

1. Benefits from additional revenue stream: 
   a.    Terraco's current gross sales are $95 million with a profit margin of 19%.   (Note: To simplify the assignment, this profit margin is calculated based on all costs, such as cost of goods, rentals of physical shops, etc. and this is assumed to remain constant for other years.)  With an aggressive marketing effort, and based on your market research, you calculate that the implementation of the e-commerce system will increase Terraco's total sales by 15% in the first year.
   b.    Based on data gathered from marketing firms that have surveyed and monitored similar e-commerce implementations you have projected that Terraco's total sales will increase by an additional 15% the second and third years, and 18% and 20% increases in the fourth and fifth years.  (Note: these percentages are stated as increases over the previous year.)   
   
2. Benefits from Inventory saving for individual stores: As all the orders can be shipped from the warehouses, the inventory for each store is predicted to decrease. The total savings due to decreased inventory is projected to be $500,000 in the second year and this will increase by 20% in year 3 and 10% each year thereafter.
   
3. Benefits from reducing the number of physical stores: As more customers move to buying online, some store locations can be closed. This is expected to save $2,000,000 (year 3), $8,000,000 (year 4), and $12,000,000 (year 5).
    
   

Assignment Deliverables

You will create your analysis using 5 worksheets contained in a single spreadsheet.   You must parameterize  each variable  (e.g., define and document each variable outside of a formula)  in the spreadsheet for easy "what if" analysis and also for readability of your spreadsheet.  Remember, many other people will be reading, using, and possibly modifying this spreadsheet! 

1. Cover Page:  

Use a textbox to specify the title of your report, your name and the date.

2.  Executive Summary:  

Summarize the purpose of the analysis, the content of your worksheets, and your final recommendation to the Terraco board in terms of whether this is a good investment.  Also, describe your additional recommendation regarding the results of your what-if analysis (see below).


3. Cost-benefit Analysis: 
This sheet should include:

• An appropriate title for this analysis, using a textbox.
• Clear documentation of cost and benefit assumptions, using textboxes.    
• The interest cost for each year in your calculation.  (Hint: your monthly payment is not your cost).
• A clearly indicated breakeven year, using if-else logic.  No hand-input values. Print "Breakeven year"" below that cell of the breakeven year only.  
• A break-even analysis chart.  Use the Chart functionality in Excel.
• A professional recommendation to the CIO regarding your proposal based on the cost-benefit analysis (and this should also be included in the Executive Summary). Do you recommend this investment?

4. Loan:  

• the calculation of the monthly payment for the loan
• an amortization table including  the yearly interest cost for the loan.  You can use Excel's PMT function for this purpose.  

 
5. "What if" analysis:  

• Copy the entire Cost-benefit Analysis worksheet, including the breakeven chart, documentation, etc. to a new worksheet.
  
• Make the assumed changes in marketing costs and sales.  Highlight your changes using a bold red font on this worksheet.
• Describe the result of this what-if analysis, here and in your executive summary.  Does this cause you to change your recommendation to the CIO?

Notes:  

1) You must provide documentation within each of the worksheets for readability and communications for executive decision making.  Documentation includes explanations of formulas, definition of terms, cost and benefit assumptions, an explanation of the chart, an explanation of IF logic in your calculations, etc.   

2) Your logic flow must be clear: a reader (e.g., your CIO or CFO) should be able to understand the logic of your formulas without having to read the formulas in the spreadsheet cells.  The parameterization of variables as well as the clear naming of each variable cell will help the readability.  Think of  this Assignment 1 as a business case presentation and use your judgment to make the presentation attractive and easy to understand.   

3) Your logic flow must not be a copy/paste of a financial calculation but instead must utilize Excel functions, such as relative references, absolute references, and cross-worksheet references.  DO NOT copy/paste values from one cell or one sheet to another--use references!

4) Use a light green colored background in cells that require manual data entry; use a light blue colored background in cells with formulas; use default coloring in cells with text information.

Submission:  
Submit your spreadsheet via Laulima in the Assignments area under Assignment 1.  Your  spreadsheet should be named: "LastName_FirstName_ASST1.xlsx"