Assignment: IT Investment Cost-Benefit Analysis Using Excel
Task Description
You have been hired by the CIO of a medium-sized retail company, Honolulu Originals Inc., to do an analysis on an IT investment. Honolulu Originals sells men's and women's work and casual attire based on Hawaiian designs. It also sells fashion accessories and gift items targeting a market segment of men and women, ages 18-40. The CIO believes that the Internet is a great channel to extend the sales of Honolulu Originals' products globally and build a community of shoppers. You have been working with directors from the IT and Finance departments to research this project. You have gathered the following facts and you now need to put together a cost-benefit analysis as part of a presentation to the Executive Board, who will make the actual IT investment decision. Here are your estimates thus far:
Initial Investment:
For the first year, the following costs for implementing the online system will apply:
|
Hardware
|
$ 80,000
|
|
Software (DBMS, TPS, etc.)
|
$ 180,000
|
|
Network/Web services
|
$ 90,000
|
|
Other Infrastructure (excluding hardware, software)
|
$ 55,000
|
|
Web Admin. personnel (customer service, technical support)
|
$ 280,000
|
The company needs to 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 interest rate is 5.2% annually. After the first year, the upkeep and maintenance of the online system including Web Admin personnel will cost a total of $200,000 for Year 2, and this is projected to increase by 6% per year over the previous year for Year 3, Year 4 and Year 5.
Honolulu Originals' current gross sales are $6 million with a profit margin of 52%. (Note: all the costs of sales are included for calculating the profit margin and these are assumed to remain constant for the other years.) You estimate that the implementation of the online system and extensive marketing will increase Honolulu Originals' total sales by 5% in the first year as people are still trying out and learning about the system. Based on data gathered from marketing firms that have surveyed and monitored similar e-commerce implementations you have projected that Honolulu Originals' total sales will increase (due to on-line exposure) by an additional 10% the second year, by an additional 12% in the third year, and 18% and 21% increases in the fourth and fifth years. This projection quantifies the "word of mouth" effect and takes into considerations the effect of redistribution of sales in online and in-store sales.
In addition, there will be a reduction of personnel costs in the store due to self-service on the web as a side benefit. You estimate that it will save in-store sales personnel costs of $120,000 in Year 1 with an increase of 30% over the previous year's personnel savings from Year 2 to Year 5. The additional marketing costs for the new online store are budgeted to be $350,000 for the first year, which will decrease: by 10% for Year 2, by 20% over Year 2 for Year 3, by 20% over Year 3 for Year 4 and by 10% over Year 4 for Year 5.
Assignment Deliverables
You will create your analysis using 4 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, other people (such as the CEO and the Executive Board) will be reading this spreadsheet!
1. Cover Page: Use a textbox for specifying 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. Also, indicate what would be the effects on the breakeven point if the marketing costs were $500,000 for the first year, and if this would change your recommendation.
3. Worksheet I (Cost-benefit Analysis):
- an appropriate title for this analysis and, on the second line, type "BUS315, , ". The title should be centered and large.
- the cost-benefit analysis format is similar to but not exactly the same as the one on page 327 of the course text book.
- you should include the interest cost for each year in your calculation. (Hint: your monthly payment is not your cost).
- you must include a break-even analysis chart. In your spreadsheet, you should indicate which year is the breakeven year by using the IF function in Excel. Print "Breakeven year" below that cell only.
- you should provide a conclusion regarding your proposal based on the cost-benefit analysis (included in the Executive Summary).
- documentation (see below)
4. Worksheet II (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.
- documentation (see below)
Worksheet I and II Documentation: 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-else logic in your calculations, etc.
Notes:
1) 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 3 as a business case presentation and use your judgment to make the presentation attractive and easy to understand.
2) 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!
3) Use a green colored background in cells that require manual data entry; use a blue colored background in cells with formulas; use default coloring in cells with text information.