BUSINESS PROCESS MANAGEMENT
Jiaccc
SimulationHomework.docxBA 270 Business Process Management
Simulation - Homework
In this homework, you'll work with a simple, discrete simulation model of a fictitious business called “Last-Minute Gifts”.
To successfully complete this homework, we strongly advise that you follow the following procedure:
· The homework is based on Microsoft Excel 2003, 2007 or 2010 and so on (your choice) running on Microsoft Windows.
· For these spreadsheets to work an “add-in” called Analysis Toolpak must be installed on Microsoft Excel. All COB computers already have it. If you are using your own computer and do not have the Analysis Toolpak added in, you can add that by going to Excel's Help (F1) and typing "Analysis Toolpak".
· Although this spreadsheet will run on Microsoft's Mac version of Excel, it is a little harder to make it work nicely. Hence, we advise you to run this homework either on a Windows machine or use a Austin Hall computer remotely.
· Carefully read the assignment and make sure that you understand its logic.
· Study the simulation model associated with the problem. Make sure you understand what the spreadsheet shows and how you can use it BEFORE (!!) you use it to answer the questions.
· If reading the problem and studying the spreadsheet causes you problems or if you lose track of things, first review your notes from class and see if your classmates can help you. If you remain stuck, see your instructor.
Assignment Background
One of the costs of doing business is work-in-progress (WIP) inventory; i.e, work that is in progress but not yet ready to be sold. The value of this 'inventory' is often known as holding cost. The holding cost for a business is the cost for storing and keeping track of the inventory and any finance costs incurred in borrowing the money to pay for the inventory until it can eventually be sold and the expenses recovered.
The holding cost increases as the inventory moves forward through the process chain, with inventory at the next step worth more than at earlier steps. This is because every step adds some more value to the inventory. The total holding cost associated with WIP is, therefore, a function of the holding cost at each step and the time duration for which inventory is held at each step before it is moved to the next step.
To compute the holding cost at each process step, one typically multiplies the WIP quantity at that process step by it’s per unit holding cost at that step.
Your simulation model simulates a five-step process employed by the “Last-Minute Gifts” company for preparing gift assortments for delivery to holiday customers. Each gift that customers order goes through these five steps in the following order:
1. Checking the order against available items in stock.
2. Selecting the right box and packing materials.
3. Retrieving the set of items from the warehouse stock.
4. Assembling the gift in the box.
5. Closing the box and completing the documentation for delivery and sending the completed invoice to order processing.
We will staff each step with its own server (one server for each step).
Please note that since this business is a service business, the WIP inventory refers to the gifts that are not yet ready for delivery and are held overnight at a particular step in the process.
Let us assume that process capacity; i.e., the availability of the people working in the business is variable. Let's also assume that both your capacity and the demand for gift processing are described by simple, uniform probability distributions. Under that assumption we can ask ourselves whether or not increasing the maximum capacity of our process affects WIP levels and hence, holding costs. For example, we can increase the maximum capacity of our process by hiring more workers or allowing existing workers to work longer hours (process more gifts). Increased maximum capacity at each step costs more, but if it lowers the holding cost more than the extra capacity costs, it might be worth it.
To keep the numbers simple, we will assume the holding cost per gift to be $1, $2, $3, $5, and $7 for process steps 1, 2, 3, 4, and 5, respectively.
Now study the Simulation1.xls spreadsheet that models the process of this company and pay attention to the following:
Notice how the Model sheet tracks for each of the five production steps the various variables for a 20-day simulation (20 demands, 20 capacity fluctuations). These demand and capacity data are computed by randomly selecting a value from between the minimum and maximum demand and capacity values listed in the Decision variables box at the top of the Model sheet. Notice how each step in the process has its own maximum capacity (all set at 6). Minimum capacity is 1 for all steps. Maximum demand (6) is the maximum number of gifts requested by customers on any given day. Minimum demand is set at 1.
By pushing the F9 key you can resample the values and run another 20-day simulation.
The Multiple Runs sheet collects 100 of these 20-day runs; i.e., every time you press F9, 100 20-day model runs are made; i.e., this is a Monte Carlo simulation where 100 different futures are simulated. Since the values for the runs are sampled from probability distributions, individual runs come out differently. Make sure you understand this before you go on.
The Summary sheet contains the summary statistics of the Multiple Runs sheet. This is the sheet that you study when evaluating the results of the simulation.
Your assignment:
Using Simulation1.xls, we're going to evaluate the effects of changing capacities on holding cost. To do this, you will need to run several simulations: one to estimate the holding cost with the current capacities and several others to estimate the holding cost with other capacity values. You can change the capacities by changing the Maximum capacity values in the Decision variables box at the top of the Model sheet.
CAUTION: Since pressing the F9 key changes all of the data in the spreadsheet, you will need to copy the Summary sheet for each of the simulation runs to a separate worksheet where they remain unchanged regardless of how many times you push F9. That way you can analyze your result data without it having been changed all the time. Use Copy --> Paste Special... --> Values to do this.
Answer the following questions on a MS Word file:
1. Would you expect holding cost to go down with increases in maximum capacity? Explain your answer.
2. Could the actual pattern be different from the expected one? Explain your answer.
3. Run your model to test your hypothesis formulated under question 1. What happens to the holding costs when you increase the maximum capacity for all five process steps from 6 to 7? Do the holding costs follow the expected pattern? If not, why not? In your answer consider both the mean of and variation in holding costs. Also consider the percentage of shipped gifts?
4. Would you expect the capacity increase to have an effect on slack (idle capacity)? Would you include slack effects in your decision to increase or not increase capacity? What, according to your model, will be the effect of the capacity increase on slack?
5. What happens with holding cost and slack when instead of increasing the maximum capacity for all steps from 6 to 7, we increase the minimum capacity for all steps from 1 to 2? Why is the effect on holding cost of changing the minimum capacities by 1 so much bigger than the effect of increasing the maximum capacities by 1?
6. Next, instead of setting the maximum capacity of all steps in the process from 6 to 7, set only the maximum capacities of steps 4 and step 5 to 7 while leaving all others at 6. Also, at the same time, set the minimum capacity back to 1. What do you observe when you run the model?
7. Make a recommendation as to whether or not and how to increase capacity for this business. In your analysis, carefully consider averages, minimum values, maximum values and variation.
Hint: You may want to try some model runs with capacity configurations other than the ones we have recommended above. Keep in mind that a hold at step 5 ($7) is seven times as expensive as a hold at step 1 ($1). Also keep in mind that as you hold fewer gifts, you have to tell fewer customers that their gift orders are not yet ready and that they have to come back the next day - meaning more satisfied customers.
1. All minimum capacities at 1 and all maximum capacities at 6.
2. All minimum capacities at 1 and all maximum capacities at 7.
3. All minimum capacities at 2 and all maximum capacities at 6.
4. All minimum capacities at 1, the maximum capacity of only steps 4 and 5 at 7, and the other steps at a maximum capacity of 6.
5. Your preferred capacity settings.
In your answers/analysis, specify the numbers from your models on which you base your conclusions:
Bad example: "Average holding cost of the first model is less than that of the second model."
Good example: "Average holding cost of the first model ($114.17) is less than that of the second model ($687)."
Make sure that the numbers mentioned in your answers/analysis correspond with the numbers on the Summary sheets.
