Equipment Replacement

Kelly Jones recently took a new role as the chief executive officer of a manufacturing company in the Midwest. The company operates various machines that requires regular maintenance and needs to be replaced eventually. This is a significant portion of the company's expenses and any reduction in this cost will greatly benefit the company profitability. Kelly thought that replacing the machines earlier to save on the repair cost is the best approach to minimize repair and replacement costs. Kelly identified this as a shortest path problem and wanted to find the optimal schedule to replace the machines to minimize the total cost of ownership. For this, she identified a particular machine that was just bought (Beginning of quarter 1), and which was costing the company significant expenses in repair and replacement. To model this problem Kelly needed to gather data. She found that the purchase price of this machine was USD 4 Million and there was no expectation that this price would go up in the next 5 years. The maintenance cost for the machine was USD 110,000 for the first quarter and increased by USD 75,000 each quarter after that. Kelly found the salvage value of the machine was USD 1.55 Million at the end of the first quarter which was expected to decrease by USD 120,000 every quarter.

Kelly wants to find the optimal solution to replace the machines for the next 5 years (20 quarters); however, as a general practice the company never sells any machine that is less than four quarter old. Similarly, the company always replaces machines that has been operational for more than 3 years. Kelly adheres to the 5-year planning horizon for this analysis.

Question:

Solve the problem as a shortest path problem. State the optimal cost as well as the quarters the equipment will be replaced.

Info to help you

Model

1. This can be modeled as a shortest path problem.
2. Each quarter can be considered as a node.
3. The arc or cost to travel between the one node and another node needs to be calculated based on the information provided above i.e. repair cost, salvage value and replacement cost.
4. There are four quarters in a year.

The Optimal Cost: $16,015,000

PLEASE DO IT IN EXCEL AND KEEP PICTURES OF IT WITH FORMUALS