Scenario 1

You are part of the operational budgeting team at Global Bike U.S. (GBI). 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. 

Asset Attributes and Additional Information

• 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. 

Assume that all values within the estimated range of values for non-discrete (estimated) variables are equally likely to occur unless otherwise stated.

Instructions

Step 1:

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.

Some guidance as to how to create your models is provided in the following sections. It is assumed that you are familiar with most Excel basic functions. If you are unfamiliar with some Excel functions, there are plenty of good tutorials on YouTube, in Excel Help, and various online resources. Here is the link to the official Microsoft Excel help page: https://support.office.com/en-us/article/Excel-training-9bc05390-e94c-46af-a5b3-d7c22f6990bb. 

Step 2:

You will use the random number generator in Excel to simulate possible outcomes to your budgeting problem, (a Monte Carlo simulation). The random number generator is available as a function within the Analysis Toolpak, or as a formula, RAND() or RANDBETWEEN. Settings in the Analysis function can be changed to accommodate various input and output distributions, the number of iterations, standard deviation and so on. You may wish to explore the random generation options in the Analysis dropdown menu.

Alternatively, you could use the parameters you have set out in Step 1 to identify ranges that will modify the results of the random numbers generated via the RAND() formula. A list of random numbers generated in Analysis is static; that is, once they are generated they do not change. A list of random numbers generated by the RAND() function can be refreshed using the F9 function key or by saving your workbook. The instructions for scenario 1 use the Analysis Toolpak.

Remember, random number generation is a sampling technique. It does not generate all possible values of a variable. The larger the number of random numbers generated, the smaller the variance between repeated simulations of a model. We will use a sample size of 2,000 as an illustration of the technique, but feel free to increase that number.

stochastic-modeling-Scenario 1