Question

Mishawaka Sales Company (MSC) is a direct marketing company involved in sales and distribution of a range of products in the Mid-West. MSC wants to provide better operational support to its sales representatives (SR) by providing each of its 800 SRs a laptop computer. The lifespan of each laptop is assumed to be five years with zero residual value and the cost of each laptop is $1,200. Each laptop needs operational software worth $300 in the beginning. Second year onwards, each laptop will need software upgrades costing $100 per laptop each year. Each SR needs to enroll in an initial training program costing $200 per SR. From the second year onwards, each SR will need to get annual training costing $50 per year. Based on historical data, MSC knows that 2% of laptops will need repair every year and the cost of a laptop repair is $700 per incident.

Directions:

• Determine the total cost of ownership (TCO) for this operational automation project at MSC over the lifespan of laptops.
• To find the TCO, setup and format an Excel spreadsheet where one can easily see the data/assumptions above, calculations of totals of the various cost components, and final result.
• Your spreadsheet must allow us to easily investigate changes in assumptions on TCO. For example, we should be able to know how TCO changes if we have 750 SRs instead of 800, by changing a single value in the spreadsheet.
• Submit your spreadsheet on canvas by the end of class.

Important components of IS model include hardware, software, networking, people, and data. The total cost of information systems in an organization is the sum of the cost of each component of this IS model.

Answer 1

	A	B	Formula
1	Number of Laptop needed	800
2	Cost of each laptop	1200
3	Total cost of Laptop	960000	=B1*B2
4	Year1 -Cost of operational Software for each Laptop	300
5	Year 1-Total Cost of operational Software	240000	=B1*B4
6	Year2 -Cost of operational Software Upgrade for each Laptop	100
7	Year 2-Total Cost of operational Software Upgrade	80000	=B1*B6
8	Year3 -Cost of operational Software Upgrade for each Laptop	100
9	Year 3-Total Cost of operational Software Upgrade	80000	=B1*B8
10	Year4 -Cost of operational Software Upgrade for each Laptop	100
11	Year 4-Total Cost of operational Software Upgrade	80000	=B1*B10
12	Year5 -Cost of operational Software Upgrade for each Laptop	100
13	Year 5-Total Cost of operational Software Upgrade	80000	=B1*B12
14	Each SR software training Cost from 2nd year to 5th year	200
15	Total SR software training cost	160000	=B1*B14
16	Based on historical data Number of laptop Needed to repair each year	16
17	Total number of laptop needed to be repaired in 5 years	80
18	Cost to repair each laptop	700
19	Total cost of Laptop repair for 5 years	56000	=B17*B18
20	Total Cost of Ownership for the project	1736000	=B3+B5+B7+B9+B11+B13+B15+B19
21

Note : Make sure the number is same as in the column one and row ,Use the formula given in last Column in column 3.