1 / 99

MATH 528

MATH 528. Operations Models. Example 12.1. Bidding for a Government Project. Background Information. The Miller Construction Company is trying to decide whether to make a bid on a construction project.

xylia
Download Presentation

MATH 528

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. MATH 528 Operations Models

  2. Example 12.1 Bidding for a Government Project

  3. Background Information • The Miller Construction Company is trying to decide whether to make a bid on a construction project. • Miller believes it will cost the company $10,000 to complete the project, and it will cost $350 to prepare a bid. • Four potential competitors are going to bid against Miller. The lowest bid will win the contract. • Based on past history, Miller believes that each competitor’s bid will be a multiple of its cost to complete the project, where this multiple has a triangular distribution with minimum, most likely, and maximum values 0.9, 1.3, and 2.5.

  4. Background Information -- continued • These four competitor’s bids are also assumed to be independent of one another. • If Miller decides to prepare a bid, then it has decided that its bid amount will be a multiple of $500 in the range $10,500 to $15,000. • The company wants to use simulation to determine which strategy to use to maximize its expected profit.

  5. Solution • The logic is straightforward. • We first simulate the competitor’s bids. Then for any bid Miller makes, we see whether Miller wins the contract, and if so, what its profit is.

  6. BIDDING.XLS • The spreadsheet model appears on the next slide. • This file contains the model.

  7. The Spreadsheet

  8. Developing the Simulation Model • The model can be developed with the following steps. • Inputs. Enter the inputs in the shaded cells. These include Miller’s costs, Miller’s possible bids, and the parameters of the triangular distribution for the competing bids. • Miller’s bid. We can test all of Miller’s possible bids simultaneously with the RISKSIMTABLE function. Do this in cell B15 with the formula =RISKTABLE(PossibleBids). • Competitor’s bids. Generate random bids for the four competitors in the CompBid range by entering the formula =RISKTRIANG($B$9,$B$10,$B$11)*ProjectCost in cell B15 and copying across. Of course Miller will not see these other bids until it has submitted its own bid.

  9. Developing the Simulation Model -- continued • Win contract?. See whether Miller wins the bid by entering the formula RISKOUTPUT( )+IF(MillerBid<MIN(CompBids),1,0)in cell B23. Here, 1 means that Miller wins the bid, and 0 means a competitor wins the bid. Note that we are designating this cell as an output cell for @RISK. • Miller’s profit. If Miller submits a bid, the bid cost is lost for sure. Beyond that, the profit to Miller is the bid amount minus the cost of completing the project if the bid is won. Otherwise, Miller makes nothing. So enter the formula=RISKOUTPUT( ) +IF(B23=1,MillerBid-ProjectCost,0)-BidCost in cell C23. We also designate this as an output cell.

  10. Using @RISK • We set the number of iterations to 1000 and the number of simulations to 10. • The summary results appear on the next slide. • For each simulation – that is, each bid amount – there are two outputs: 1 or 0 to indicate whether Miller wins the contract and Miller’s profit. • A little thought should convince you that each of these can have only two possible values for any bid amount.

  11. Using @RISK

  12. Using @RISK -- continued • For example, if Miller bids $12,000, it will either win or lose the contract, and its profit will be either $1650 or -$350. • This is reflected in the histogram of profit for this bid amount shown on the next slide, where there are only two bars. • The two possible values of the outputs appear in the Minimum and Maximum columns of the table on the previous slide.

  13. Using @RISK -- continued

  14. Using @RISK -- continued • The Mean column, on the other hand, indicates the average of these values over the 1000 iterations. • For example, the mean of 0.545 for the “Win Bid?” output for simulation #4 indicates that Miller wins the contract on 54.5% of the iterations when bidding $12,000. • The mean profit of $740 for this bid amount is simply a weighted average of the two possible profits, $1650 and -$350.

  15. Using @RISK -- continued • Specifically, you can check that it is 0.545(1650)+0.455(-350) = 740. The other means in the output can be interpreted similarly. • What should Miller bid? • First, it is clear that Miller should bid. Not bidding means no profit, Whereas all of the possible bids except for the last one lead to a positive expected profit with at most a $350 loss.

  16. Using @RISK -- continued • If Miller is an EMV maximizer, as we discussed in Chapter 10, then the $12,000 bid should be chosen because it has the highest mean profit. • However, if Miller is risk averse, a smaller bid amount might be attractive. • As the bid amounts increase, the upside potential is greater, but the chance of not winning the bid and losing $350 increases.

  17. Example 12.2 Tampering with a Stable Process

  18. Background Information • Suppose that you are in the business of drilling a tiny hole in the exact center of a square piece of wood. • In the past, the holes you have drilled were, on average, in the center of the wood, and the x- and y-coordinates each had a standard deviation of 0.1 inch. • Also, the drilling process has been stable – that is, the holes average being in the center of the square, and the deviations from the center of the square follow a normal distribution with mean 0 and standard deviation 0.1 inch.

  19. Background Information -- continued • This mean, for example, that the x-coordinate is within 0.1 inch of the center for 68% of the holes, the x – coordinate is within 0.2 inch of the center for 95% of the holes, and the x-coordinate is with 0.3 inch of the center for 99.7% of the holes. • This describes the inherent variability in the drilling process. • Without changing the hole-drilling process, you must live with this amount of variation. • Now suppose that you drill a hole and its x- and y- coordinates are x=0.1 and y=0.

  20. Background Information -- continued • A natural reaction is to reduce the x-setting of the drill by 0.1 to correct for the fact that the x-coordinate was too high. • Then if the next hole has coordinates x = -0.2 and y = 0.1, you might try to increase the x-coordinate by 0.2 and decrease the y-coordinate by 0.1. • Deming’s funnel experiment shows that this method of continually readjusting a stable process – he calls it “tampering” – will actually increase the variability of the coordinates of the position where the hole is drilled. In other words, tampering will generally make a process worse!

  21. Background Information -- continued • To illustrate the effects of tampering, Deming placed a funnel above a target on the floor and dropped small balls through the funnel in an attempt to hit the target. • As he demonstrated, many balls did not hit the target. His goal, therefore, was to make the balls fall as close to the target as possible. • Deming proposed four rules for adjusting the positioning of the funnel.

  22. Deming’s Rules • Never move the funnel. • After each ball is dropped, move the funnel – relative to its previous position – to compensate for any error. To illustrate, suppose the target has coordinates (0,0) and the funnel begins directly over the target. If the ball lands at (0.5,.1) on the first drop, we compensate by repositioning the funnel at (0-0.5,0-0.1) = (-0.5,-1). If the second drop has coordinates (1,-2), we then reposition the funnel at (-0.5 – 1, -0.1 – (-2)) = (-1.5, 1.9).

  23. Deming’s Rules -- continued • Move the funnel – relative to its original position at (0,0) – to compensate for any error. For example, if the ball lands at (0.5,0.1) on the first drop, we compensate by repositioning the funnel at (0-0.5, 0-0.1) = (-0.5, -1). If the second drop has coordinates (1, -2), we then reposition the funnel at (0, -1, 0 – (-2)) = (-1,2).

  24. Deming’s Rules -- continued • Always reposition the funnel directly over the last drop. Therefore, if the first ball lands at (0.5,1), we position the funnel, (0.5, 1). If the second drop has coordinates (1, 2), we position the funnel at (1, 2). This rule might be followed, for example, by an automobile manufacturer’s painting department. With each new batch of paint, they attempt to match the color of the previous batch – regardless of whether the previous color was “correct”. • Do you believe any of the latter three rules will outperform rule 1, the “leave it alone” rule? If so, read on – you might be surprised.

  25. Solution • To see how these rules work, we assume that the x-coordinate on each drop is normally distributed with mean equal to the x-coordinate of the funnel position and standard deviation of 1. • A similar statement holds for the y-coordinate. Also, we assume that the x- and y- coordinates are selected independently of one another. These assumptions describe the inherent variability in the process of dropping the balls.

  26. Solution -- continued • To see how the rules work, let F0, X0, F1 be respectively, the x-coordinates of the funnel position on the previous drop, the outcome of the previous drop, and the repositioned funnel position for the next drop. • Then rule 1 never repositions, so that F1 = F0. Rule 2 repositions relative to the previous funnel position, so that F1 = F0 – X0. Rule 3 repositions relative to the original position (at 0), so that F1 = 0 – X0 = -X0. Finally rule 4 repositions at the previous drop, so that F1 = X0. Similar questions hold for the y-coordinate.

  27. Solution -- continued • For the simulation model, we simulate 50 consecutive drops of the ball from each of the four rules. • Our single output measure is the (straight-line) distance of the final drop from the target. • A rule is presumably a good one if the mean distance is small and the standard deviation of this distance is also small.

  28. FUNNEL.XLS • Given the repositioning equations for the rules, the simulation model is straightforward. • In fact, we use a RISKSIMTABLE function to test all four rules simultaneously. • The spreadsheet model appears on the next slide. • This file contains the model.

  29. The Spreadsheet

  30. Developing the Simulation Model • The model can be developed with the following steps. • Rule. Enter the formula =RISKSIMTABLE({1,2,3,4}) in cell B3 to indicate that we want to simulate all four rules. Note that if individual values are listed in RISKSIMTABLE, they must be enclosed in curly brackets. No curly brackets should be used if the list is referenced by a range. • Position funnel. Enter 0 in cells B7 and C7 to indicate that the original funnel position is above the target at (0,0). Then implement the positioning equations by entering the formula =IF(Rule=1,B7,IF(Rule=2,B7-D7,IF(Rule=3,-D7,D7))) in cell B8 and copying it to the range B8:C56. Note how this formula references the location of the previous drop. The IF function captures the logic for all four rules.

  31. Developing the Simulation Model -- continued • Simulate drops Simulate the positions of the drops by entering the formula =RISKNORMAL(B7,1) in cell D7 and copying it to the range D7:E56. This says that the ball’s drop position is normally distributed with mean equal to the funnel’s position and standard deviation 1. • Distance. Calculate the final distance from the target in cell C58 with the formula =RISKOUTPUT( ) +SQRT(SUMSQ(D56:E56)). Here we have used SUMSQ function to get the sum of squares for the distance formula. We have also indicated that this is an output cell for @Risk.

  32. Using @RISK • We set the number of iterations to 1000 and the number of simulations to 4. • Selected summary measures for the final distance from the target for all four rules appears in the table shown here. • We also show histograms of this distance for rules 1, 2, 3 on the next three slides.

  33. Using @RISK

  34. Using @RISK

  35. Using @RISK

  36. Using @RISK -- continued • These results prove Deming’s point about tampering. • Rule 2 might not appear to be too much worse than rule 1, but its mean distance and standard deviation of distances are both about 40% higher than rule 1. • Rules 3 and 4 are disastrous. Their mean distances are more than seven times higher than for rule 1, and their standard deviations are also much higher. • The moral of the story, as Deming preached, is that you should not tamper with a stable process. If the process is not behaving as desired, then fundamental changes to the process are required, not a lot of tinkering.

  37. Example 12.3 Order Due Dates at Wozac

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

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

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

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

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

  43. 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 use IF functions in these formulas so that if enough has already been produced to fill the order, blanks are inserted into the remaining cells.

  44. Developing the Simulation Model

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

  46. 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=“Yes”, 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.

  47. Developing the Simulation Model -- continued • 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.

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

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

  50. Using @RISK

More Related