Question

You are part of the operational budgeting team at Global Bike U.S. (GB). You have been asked to assist the production manager in budgeting for the following fiscal year. One estimate that has been troubling you is that of the cost of equipment maintenance. You have historical data that provide you with estimates of Repair Time (in hours), Time Between Repairs (in days), Cost of Labor (per hour fully burdened), Cost of Parts (per Repair), and Expected Life of Asset (in years) for all assets.Previously, you have used Excel to calculate total cost of maintenance (TCM) and you noticed that frequently the actual cost varies significantly from the estimated TCM. Since budgets are tight, you would like to get a feel for not only the worst case and best case TCM for each piece of equipment in the production facility, but also the most likely TCM. You have decided to create a model for the TCM estimate and test it on an asset with the following attributes:

Repair Time on average runs 8 hours per repair.

•Estimated Time Between Repairs is 180 days, but can range between 120 and 180 days.

•Estimated Cost of Labor is $75 per hour, but can range between $70 and $90 per hour depending on personnel required to make the repair.

•Estimated Cost of Parts is $1,200, but based on prior vendor invoices, can run anywhere from $1,120 to$1,350.

•Expected Life of the Asset is 10 years, but could possibly range from 8 years to 11 years depending on wear and tear.

The first step is to calculate TCM using the estimated values. Set up an Excel spreadsheet and use the cells to calculate TCM. Hint: you should end up with a cost of $36,500. Please explain excel sheet

Answer 1