BUSINESS PROCESS MANAGEMENT

BA 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 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 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 and/or minimum 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 by typing them in a word document:

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 for the optimum capacity at each step 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.)

Along with your answers, also turn in the revised excel workbook that has the following summary sheets with:

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.

Upload two files on Canvas (Assignments->Homework->Simulation Homework):

1. The MS-Word file with your answers (Save it as your Firstname_Assignment 3.docx) 2. The MS-Excel workbook (Save it as your Firstname_Simulation.xlsx)

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.