1 / 16

Example 12.3

Example 12.3. Operations Models. Background Information. The Wozac Company is a drug manufacturing company.

mspillman
Download Presentation

Example 12.3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Example 12.3 Operations Models

  2. Background Information • The Wozac Company is a drug manufacturing company. • Wozac has recently accepted an order from its best customer for 8000 ounces of a new miracle drug, and Wozac wants to plan its production schedule to meet the customer’s promised delivery date of December 1, 2000. • There are three sources of uncertainty that make planning difficult.

  3. Background Information -- continued • First, the drug must be produced in batches, and there is uncertainty in the time required to produce a batch, which could be anywhere from 5 to 11 days. This uncertainty is described by the discrete distribution of this table.

  4. Background Information -- continued • Second, the yield (usable quantity) from any batch is uncertain. Based on historical data, Wozac believes the yield can be modeled by a triangular shaped distribution with minimum, most likely, and maximum values equal to 600, 1000, 1100. • Third, all batches must go through a rigorous inspection once they are completed. The probability that a typical batch passes this inspection is only 0.8. With probability 0.2, the batch fails inspection, and none of it can be used to help fill the order.

  5. Background Information -- continued • Wozac wants to use simulation to help decide how many days prior to the due date it should begin production.

  6. Solution • The idea is to simulate successive batches – their days to complete, their yield, and whether they pass inspection – and keep a running total of the usable ounces obtained so far. • We then use IF functions to see whether the order is complete or another batch is required. • We simulate only as many batches as are required to meet the order, and we keep track of the days required to produce all of these batches. • In this way we can “back up” to see when production must begin to meet the due date.

  7. Developing the Simulation Model • The completed model appears on the next slide. It can be developed as follows. • Inputs. Enter all inputs in the shaded cells. • Batch indexes. We do not know ahead of time how many batches will be required to fill the order. We want to have enough rows in the simulation to cover the worst case that is likely to occur. After some experimentation we found that 25 batches are almost surely enough. Therefore, enter the batch indexes 1-25 in column A of the simulation section. The idea, then, is to fill the entire range B29:F53 with formulas. However, we will IF functions in these formulas so that if enough has already been produced to fill the order, blanks are inserted into the remaining cells.

  8. Developing the Simulation Model -- continued • Days for batches. Simulate the days required for batches in column B. First, enter the formula =RISKDISCRETE(Days<Probs) in cell B29. Then enter the general formula =IF(OR(F29=“Yes”,F29=“”),””,RISKDISCRETE(Days,Probs)) in cell B30 and copy it down to cell B53. Note how the IF function enters a blank in this cell if either of two conditions is true; the order was just completed in the previous batch or it has been completed for some time. Similar logic will appear in later formulas. • Batch yields. Simulate the batch yield in column C. First, enter the formula =RISKTRIANG(B23,C23,D23) in cell C29. Then enter the general formula =IF(OR(F29=“Yes”,F29=“”),””,RISKTRIANG($B$23,$C$23,$D$23)) in cell C30 and copy it down to C53.

  9. Developing the Simulation Model -- continued • Pass inspection? Check whether each batch passes inspection with the formulas =IF(RAND()<PrPass,”Yes”,”No”) and IF(OR(F29=“”),””,IF(RAND()<PrPass,”Yes”,”No”)) in cells D29 and D30 and copy the latter down to cell D53. Note that we could use @Risk’s RISKUNIFORM(0,1) function instead of RAND(), but there is no advantage to doing so. • Order filled? We keep track of the cumulative usable production and whether the order has been filled in column E and F. First, enter the formulas =IF(D29=“Yes”,C29,0) and =IF(E29>=AmtReqd,”Yes”,”Not yet”) in cells E29 and F29 for batch 1. Then enter the general formulas =IF(OR(F29=“Yes”,F29=“”),””,IF(D30=“Yes”,C30+E29,E29)) and =IF(OR(F29=“Yes”,F29=“”),””,IF(E30>=AmtReqd,“Yes”,”Not yet”)) in cells E30 and F30, and copy them down to row 53.

  10. Developing the Simulation Model -- continued • Note that the entry in column F is “Not enough” if the order is not yet complete. In the row that completes, the order, it changes to “Yes”, and then it is blank in succeeding rows. • Summary measures. Calculate the batch and days required in cell I28 and I29 with the formulas =RISKOUTPUT() + COUNT(B29:B53) and =RISKOUTPUT()+SUM(B29:B53) These are the two cells we will use as output cells for @Risk. Also, calculate the day the order should be started to just meet the due dates in cell I30 with the formula =DueDate-I29 This formula uses date subtraction to find an elapsed time. Of course, it assumes that production occurs every day of the week, which we will assume. • This completes the simulation model development. The other entries in columns H-J will explained shortly.

  11. Using @RISK • We set the number of iterations to 1000 and the number of simulations to 1. • After running @Risk, we obtain the histograms of the number of batches required and the number of days required on the next two slides. • How should Wozac use this information? The key question are how many batches will be required and when to start production. • We have entered several of @Risk’s statistical functions directly in the spreadsheet to help answer these questions.

  12. Using @RISK -- continued • For the first question, we use the formula =RISKMAX(I28) in cell I33. It shows that the worst case from the iterations, in terms of batches required is 20 batches. • We can answer the second question in two ways. • First, we can calculate summary measures for days required and then back up from the due date. We do this in the range I35:J39. The formulas in column I are =INT(RISKMEAN(DaysReqd)), =RISKMIN(DaysReqd), =RISKMAX(DaysReqd), =RISKPERCENTILE(DaysReqd,0.05) and =RISKPERCENTILE(DaysReqd,0.95)We then subtract each of these from the due date to obtain the potential starting dates in column J. Wozac should realize the pros and cons of these starting dates.

  13. Using @RISK -- continued • Alternatively, we can use @Risk’s RISKTARGET function to find the probability of meeting the due date for any starting date, such as those in the range H42:H46. We enter the formula =RISKTARGET(DaysReqd,DueDate-H42) in cell I42 and copy it down. This function returns the fraction of iterations where the value in the first argument is less than or equal to the value in the second argument. • What is our recommendations to Wozac? • We suggest going with the 95th percentile – begin production on August 2. Then there is only a 5% chance of failing to meet the due date.

More Related