For this question you are asked to thoroughly review the following case study, then draw on your understanding of the textbook and lectures, particularly from Modules 7 and 8, to develop a total cost of ownership model in order to decide on the purchase of equipment. Only those responses submitted in a MS Excel spreadsheet will be accepted. While it's important that you get the right answer (i.e., calculating the right TCO for each piece of equipment), it is even more important that you demonstrate through your spreadsheet work that you understand TCO and its concepts.

Case Study

The sandwich shop owner is growing increasingly frustrated that she can't decide on whether to keep the original panini oven (it's still in the crate at a local warehouse waiting to be delivered) or return the oven and purchase Prince Panini's instead. In her desperation, she offers you a larger ownership position if you will help her develop a 5-year side-by-side TCO comparison of the equipment. She knows that you thoroughly understand and can calculate TCO as you are a student of the world-renowned supply chain guru, David Burt. Because of this fact, her decision on which oven to ultimately purchase hinges entirely on your TCO analysis.

You ask her for all the information that she can provide you about the two ovens as you were not privy to her discussions with them and, in turn, she provides you with a mountain of information that she has collected and researched. While a bit disorganized (though oddly enough she has provided the information in alphabetical order), the information does appear to be reliable. Eager to increase your ownership position, you set right to work!

Here is the information for each supplier that the shop owner provides you:

Information on Original Supplier

• Annual insurance cost increase = $250
• Annual ongoing training for each of the shops 5 employees = $50
• Costs of upgrade accessories, inclusive of taxes, needed to use the new oven = $500
• Dismantling costs of the oven after its five-year useful life = $500
• Initial change management and training costs for each of the shops 5 employees = $150
• Monthly ongoing fuel costs to run the oven = $50
• Net purchase cost of the new oven after incentives and discounts = $10,00
• Quarterly ongoing maintenance and repair costs = $75
• Reconfiguration of the shops space to accommodate the new oven = $1,000
• Salvage value of the ovens parts = $150
• Semi-annual ongoing replacement of ovens air filter, inclusive of taxes = $100
• Transportation and "final mile" installation of the new oven = $300

Information on Prince Panini

• Annual insurance cost increase = $300
• Annual ongoing training for each of the shops 5 employees = $75
• Costs of upgrade accessories, inclusive of taxes, needed to use the new oven = $275
• Dismantling costs of the oven after its five-year useful life = $400
• Initial change management and training costs for each of the shops 5 employees = $100
• Monthly ongoing fuel costs to run the oven = $75
• Net purchase cost of the new oven after incentives and discounts = $9,000
• Quarterly ongoing maintenance and repair costs = $70
• Reconfiguration of the shops space to accommodate the new oven = $800
• Salvage value of the ovens parts = $200
• Semi-annual ongoing replacement of ovens air filter, inclusive of taxes = $125
• Transportation and "final mile" installation of the new oven = $500

Note that in both cases:

• Milwaukee County environmental impact tax (one-time) on all dismantled equipment originally valued at $10,000 = 3% of equipment's purchase price
• Wisconsins initial new equipment tax (one-time) for small businesses = 4% of equipment's purchase price (a discounted rate to encourage and support small business growth)

Could someone solve this case in excel I have an example and it must be similar to it. Use it as a template

TCO of a New Car Year 1 Year 2 Year 3 Year 3 + 1 Total Comments cost of research $ 31,600 Year o Acquisition Costs Purchase Price $ 30,000 Taxes $ 1,500 Planning/Quality $ 100 Subtotal $ 31,600 Ownership Costs Energy and other utilities needed to operate Maintenance & Repairs Ongoing financing Inspection and preventative costs Subtotal Post-Ownership Costs Disposal and environmental costs Warranty costs Product liability costs Subtotal Total Salvage Value $ 2,600 $ 2,678 $ 2,758 $ 250 $ 258 $ 265 $ 1,500 $ 1,500 $ 1,500 $ 1,000 $ 1,000 $ 1,000 $ 5,350 $ 5,436 $ 5,524 taking into account 3% inflation taking into account 3% inflation 5% on $30k loan annual state inspection, license, insurance $ 16,309 150 tow truck from home to CarMax $ $ $ $ 150 $ 150 $ 48,059 $ 12,000 Assumes 40% residual value Total Cost of Ownership $ 36,059 *Compare to other type car, used car of same type or different transportation options 5 TCO of a New Car Year 1 Year 2 Year 3 Year 3 + 1 Total Comments cost of research $ 31,600 Year o Acquisition Costs Purchase Price $ 30,000 Taxes $ 1,500 Planning/Quality $ 100 Subtotal $ 31,600 Ownership Costs Energy and other utilities needed to operate Maintenance & Repairs Ongoing financing Inspection and preventative costs Subtotal Post-Ownership Costs Disposal and environmental costs Warranty costs Product liability costs Subtotal Total Salvage Value $ 2,600 $ 2,678 $ 2,758 $ 250 $ 258 $ 265 $ 1,500 $ 1,500 $ 1,500 $ 1,000 $ 1,000 $ 1,000 $ 5,350 $ 5,436 $ 5,524 taking into account 3% inflation taking into account 3% inflation 5% on $30k loan annual state inspection, license, insurance $ 16,309 150 tow truck from home to CarMax $ $ $ $ 150 $ 150 $ 48,059 $ 12,000 Assumes 40% residual value Total Cost of Ownership $ 36,059 *Compare to other type car, used car of same type or different transportation options 5