Reference no: EM132316139
Business Process Management - Simulation - Homework
In this homework, you'll work with a simple, discrete simulation model of a fictitious last-minute gift business.
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 (your choice) running on Microsoft Windows.
For these spreadsheets to work you MUST(!!) add-in the Analysis Toolpak (if installed you see a Data Analysis option in the Analysis group of the Data option on the main menu). If you do not have the Analysis Toolpak added in and you do not know how to add it in, go to Excel's Help (F1) and type "Analysis Toolpak"
Although this homework 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 on a Windows machine.
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 (adapted from Weida, Richardson, Vazsonyi (2000) Operations Analysis Using Microsoft Excel)
One of the costs of doing business is work-in-progress (WIP) inventory; i.e., value-added work that is in progress but not yet ready to be sold. The value of this 'inventory,' often known as holding cost increases as it moves forward through the process chain, with inventory at the end worth more than at earlier steps; hence the term 'value add'. To compute holding costs one typically multiplies the WIP quantity at each process step by its process-added value at that step. Holding cost is thus a function of how long the holding period is, the costs of storing and keeping track of the inventory, any finance costs incurred in borrowing the money to pay for the inventory until it can be eventually sold and the expenses recovered, etc.
Before you go on, make sure you understand this concept of holding cost and the way in which these are computed.
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:
Checking the order against available items in stock.
Selecting the right box and packing materials.
Retrieving the set of items from the warehouse stock.
Assembling the gift in the box.
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!
-Important. You should realize that this business is a service business; it is NOT manufacturing and hence, it has no inventory(!!) other than the gifts that have not yet been processed and are held overnight.
Let us assume that process capacity; i.e., the availability and performance of the people working in the business is variable. This could be the case, for instance, if you would hire workers with various skill levels off the street (not very realistic, but good enough for this exercise).
Let's also assume that both your capacity and the demand for gifts 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; i.e., allowing workers to work longer hours (process more gifts) affects WIP levels and hence, holding costs. Increased peak 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 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 at 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:
We're going to evaluate the effects on holding cost when changing capacities. 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 Max. capacity values in 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 having them change all the time. Use Copy --> Paste Special... --> Values to do this.
Answer the following questions:
1. Would you expect holding cost to go down with increases in peak capacity? Explain your answer. Could the actual pattern to be different from the expected one? Explain your answer.
2. Run your model to test your hypothesis formulated under 1. What do the holding cost do when you increase the max capacity for all five process steps from 6 to 7? Do the holding cost follow the expected pattern? If not, why not? In your answer consider both the mean of and variation in holding cost. Also consider the percentage of completed gifts (customers do not like finding out that their orders are not ready))
3. Would you expect the capacity increase to have an effect on slack? Would you include slack effects in your decision to increase or not increase capacity? What does the model predict will be the effect of the capacity increase on slack?
4. 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 with 1 so much bigger than the effect of increasing the maximum capacities with 1?
5. Now, instead of setting the maximum capacity of all steps in the process from 6 to 7, set only the max capacities of steps 4 and step 5 to 7 while leaving all others at 6. Set the minimum capacity back to 1. What do you observe when you run the model?
6. 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 tried so far. Keep in mind that a hold at step 5 is seven times as expensive as a hold at step 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 coma back the next day.
Along with your answers, turn in the following copies of the Summary sheet:
one copy for the model with all minimum capacities at 1 and all maximum capacities at 6.
one copy for the model with all minimum capacities at 1 and all maximum capacities at 7.
one copy for the model with all minimum capacities at 2 and all maximum capacities at 6.
one copy for the model with 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.
one copy of the model with the preferred capacity settings (if different from the previous four).
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 are less than those of the second model."
Good example: "average holding cost of the first model ($114.17) are less than those of the second model ($687)."
Make sure that the numbers mentioned in your answers/analysis correspond with the numbers on the Summary sheets.
Writing guidelines:
Submit the homework in word-processed hardcopy (printed on paper).
Add a title (cover) page to your work containing:
Title.
Your name.
Course number (BA302), course session, and name of the instructor of record.
Date.
Add your name (format: Last name, First name) to the document header so that it shows on the left upper corner of each page !!!INCLUDING
THE TITLE (COVER) PAGE.
Do not use the back side of the title (cover) page; leave it free for the instructor to make notes.
Use one-and-a-half (1.5) line spacing, 11- or 12-point font and reasonable margins.
Separate your text in logical blocks and sections.
Spell check (both automatically and manually!).
Grammar check
Peer review. Before handing in your work, consider asking one of your colleagues to review it in exchange for you reviewing his/hers. When reviewing, carefully (!!) read what your colleague wrote. Notice ambiguities, note each and every spelling/grammar error, break up long and awkward sentences into shorter, more elegant ones, etc. In other words: show that you care. Peer review is super powerful -- Use it.
Attachment:- Simulation.xls