1 / 244

Spreadsheet Simulation Models | Chapter 11 Introduction

This chapter introduces the concepts of spreadsheet simulation models and explores their application in operations, finance, marketing, and games of chance. It demonstrates how simulation can be used to determine optimal bids for contracts, using the example of Miller Construction Company.

judithv
Download Presentation

Spreadsheet Simulation Models | Chapter 11 Introduction

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. Chapter 11 Simulation Models

  2. Introduction • In the previous chapter, we introduced most of the important concepts for developing and analyzing spreadsheet simulation models. • We also discussed many of the features available in the powerful simulation add-in, @RISK, that you receive with this book. • Now we apply the tools to a wide variety of problems that can be analyzed with simulation.

  3. Introduction continued • For convenience, we group the applications into four general areas: • operations models, • financial models, • marketing models, • games of chance. • The only overriding theme in this chapter is that simulation models can yield important insights in all of these areas. • You do not need to cover all of the models in this chapter or cover them in any particular order. • You can cover the ones of most interest to you in practically any order.

  4. Operations models • Whether we are discussing the operations of a manufacturing or a service company, there is likely to be uncertainty that can be modeled with simulation. • In this section we look at examples of bidding for a government contract (uncertainty in the bids by competitors), warranty costs (uncertainty in the time until failure of an appliance), and drug production (uncertainty in the yield and timing).

  5. Bidding for contracts • In situations where a company must bid against competitors, simulation can often be used to determine the company’s optimal bid. • Usually the company does not know what its competitors will bid, but it might have an idea about the range of the bids its competitors will choose. • In this section we show how to use simulation to determine a bid that maximizes the company’s expected profit.

  6. Example 11.1:Background information • The Miller Construction Company is trying to decide whether to make a bid on a construction project. • Miller believes the cost to complete the project to the company has a triangular distribution with minimum, maximum, and most likely values of $9,000, $10,000, and $15,000. • The cost to prepare the bid has a triangular distribution with parameters of $300, $350, and $500. • 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 will bid, independently of others, with probability 0.5, and that each competitor’s bid will be a multiple of Miller’s most likely cost to complete the project, with a triangular distribution and maximum values 0.9, 1.3, and 1.8, respectively.

  7. Example 11.1:Solution • The logic is straightforward. • We first simulate the competitors’ bids. Then for any bid Miller makes, we see whether Miller wins the contract, and if so, what its profit is. • Objective: To simulate the profit to Miller from any particular bid, and to see which bid amount is best.

  8. Example 11.1 continued:Contract Bidding.xlsx • The spreadsheet model appears below. • This file contains the model.

  9. Example 11.1 continued:Developing the simulation model • The model can be developed with the following steps: • Inputs. Enter the inputs in the blue 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(D16:M16). • Miller’s costs. Generate Miller’s cost to prepare a bid in cell B19 with the formula =RISKTRIANG(B5,C5,D5), then copy this to cell B20 to generate Miller’s cost to complete the project.

  10. Example 11.1 continued:Developing the simulation model • Competitor’s bids. First, generate the random number of competitors who bid. This has a binomial distribution with 4 trials and probability of “success” equal to 0.5 for each trial, so enter the formula =RISKBINOMIAL(B8,B9) in cell B21. Then generate random bids for the competitors who bid in row 23 by entering the formula =IF(B22>=$B$20,RISKTRIANG($B$12,$B$13,$B$14)*$C$6,“”) in cell B23 and copying across. This generates a random bid for all competitors who bid, and it enters a blank for those who don’t. (Remember that the random value is the multiple of Miller’s cost to complete the project.) Calculate the smallest of these (if there are any) in cell B24 with the formula =IF(B21>=1,MIN(B23:E23),“”) Of course, Miller will not see these other bids until it has submitted its own bid.

  11. Example 11.1 continued:Developing the simulation model • Win contract? See whether Miller wins the bid by entering the formula =IF(OR(B16<B24,B21=0),1,0) in cell B26. 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 =IF(B26=1,B16-B20,0)-B19 in cell B27. Then designate this cell as an additional @RISK output cell.

  12. Example 11.1 continued:The simulation model

  13. Example 11.1 continued: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.

  14. Example 11.1 continued:Discussion of simulation results • For example, if Miller bids $12,000, the probability of winning the bid is estimated to be 0.578. • 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.

  15. Example 11.1 continued:Histogram of profit

  16. Example 11.1 continued:Results • If Miller is an EMV maximizer, as we discussed in Chapter 10, then the $13,000 bid should be chosen because it has the highest mean profit. • However, potential cost overruns and the corresponding losses are certainly a concern. • Depending on Miller’s degree of risk aversion, the company might decide to • Not bid at all • Bid higher than $13,000 to minimize its worse loss.

  17. Warranty costs • When you buy a new product, it usually carries a warranty. A typical warranty might state that if the product fails within a certain period such as one year, you will receive a new product at no cost, and it will carry the same warranty. • However, if the product fails after the warranty period, you have to bear the cost of replacing the product. • Due to random lifetimes of products, we need a way to estimate the warranty costs (to the manufacturer) of a product. • The next example illustrates how this can be accomplished with simulation.

  18. Example 11.2:Background information • The Yakkon Company sells a popular camera for $400. • This camera carries a warranty such that if the camera fails within 1.5 years, the company gives the customer a new camera for free. • If the camera fails after 1.5 years, the warranty is no longer in effect.

  19. Example 11.2 continued:Background information • Every replacement camera carries exactly the same warranty as the original camera, and the cost to the company of supplying a new camera is always $225. • Use simulation to estimate, for a given sale, the number of replacements under warranty and the NPV of profit from the sale, using a discount rate of 8%. • Objective: To use simulation to estimate the number of replacements under warranty and the total NPV of profit from a given sale.

  20. Example 11.2 continued:Solution • The only randomness in this problem concerns the time until failure of a new camera. Yakkon could estimate the distribution of time until failure from historical data. • This would probably indicate a right-skewed distribution. • If you look through the list of distributions available in @RISK under Define Distributions, you will see several with this same basic shape. The one shown in on the next slide is a commonly used distribution called the gamma distribution.

  21. Example 11.2 continued:Right-skewed gamma distribution

  22. Example 11.2 continued:Warranty Costs.xlsx • The simulation model appears here and in this file.

  23. Example 11.2 continued:Developing the simulation model • The particular random numbers in this figure indicate an example where there are two failures within warranty. • However, because the lifetime of the second replacement (cell D17) is greater than 1.5, the company incurs only two replacement costs, as shown in cells B19 and C19. • The model can be developed with the following steps.

  24. Example 11.2 continued:Developing the simulation model • Inputs. Enter the inputs in the blue cells. • Parameters of gamma distribution. As we discussed previously, if we enter a desired mean and standard deviation (in cells B5 and B6), we have to calculate the parameters of the gamma distribution. Do this by entering the formulas =B5^2/B6^2 and =B6^2/B5 in cells B7 and B8. • Lifetimes and times of failures. We generate at most 5 lifetimes and corresponding times of failures. Why only 5? We could generate more, but it is extremely unlikely that this same customer will experience more than 5 failures within warranty, so 5 suffices. As soon as a lifetime is greater than 1.5, the warranty period, we do not generate any further lifetimes, since “the game is over”; instead, we record “NA” in row 17. With this in mind, enter the formulas =RISKGAMMA(B7,B8)

  25. Example 11.2 continued:Developing the simulation model =IF(B17<B10,RISKGAMMA(B7,B8),“NA”) and =IF(C17=“NA”,“NA”,IF(C17<$B$10,RISKGAMMA($B$7,$B$8),“NA”)) in cells B17, C17, and D17, and copy the latter formula to cells E17 and F17.These formulas guarantee that once “NA” is recorded in a cell, all cells to its right also contain “NA.” To get the actual times of failures, relative to time 0 when the customer originally purchases the camera, enter the formulas =B17 and =IF(C17=“NA”,“NA”,B18+C17) in cells B18 and C18, and copy the latter across row 18. These values are used for the NPV calculation because we need to know exactly when cash flows occur.

  26. Example 11.2 continued:Developing the simulation model • Costs and discounted costs. In row 19, we enter the replacement cost ($185) or 0, depending on whether a failure occurs within warranty, and in row 20, we discount these costs back to time 0, using the failure times in row 18. To do this, enter the formulas =IF(B17<B10,B12,0) and =IF(C17=“NA”,0,IF(C17<$B$10,$B$12,0)) in cells B19 and C19, and copy this latter formula across row 19. Then enter the formula =IF(B19>0,B19/(1+$B$13)^B18,0)in cell B20 and copy it across row 20. This formula uses that well-known fact that the present value of a cash flow at time t is the cash flow multiplied by 1(1 + r)t, where r is the discount rate.

  27. Example 11.2 continued:Developing the simulation model • Outputs. Calculate two outputs, the number of failures within warranty and the NPV of profit, with the formulas =COUNTIF(B19:F19,“>0”) and =B11-B12-SUM(B20:F20) in cells B22 and B23. Then designate these two cells as @RISK output cells. Note that the NPV is the margin from the sale (undiscounted) minus the sum of the discounted costs from replacements under warranty.

  28. Example 11.2 continued:Developing the simulation model • The @RISK setup is typical. We run 1000 iterations of a single simulation (because there is no RISKSIMTABLE function). • The @RISK summary statistics and histograms for the two outputs appear on the next two slides. • They show a pretty clear picture. About 85% of the time, there are no failures within the warranty period and the company makes a profit of $175, the margin from the camera sale. However, there is about a 12.9% chance of exactly 1 failure under warranty, in which case, the company’s NPV of profit is an approximate $50 loss.

  29. Example 11.2 continued:Simulation results

  30. Example 11.2 continued:Simulation results • Additionally, there is about a 2.1% chance that there are even more failures under warranty, in which case the loss is even greater. On average, the NPV of profit was $37.62. • These results indicate that Yakkon is not suffering terribly from warranty costs. • However, the company could decrease the effects of warranty costs in several ways. • First, it could increase the price of the camera. • Second, it could decrease the warranty period, say, from 1.5 years to 1 year. • Third, it could change the terms of the warranty. • Finally, it could try to sell the customer an extended warranty— at a hefty price.

  31. Drug production with uncertain demand • In many manufacturing settings, products are produced in batches, and the usable yields from these batches are uncertain. • This is particularly true in the drug industry. • The following example illustrates how a drug manufacturer can take this uncertainty into account when planning production.

  32. Example 11.3: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. • There are three sources of uncertainty that make planning difficult.

  33. Example 11.3 continued:Background information • 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.

  34. Example 11.3 continued:Background information • 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.

  35. Example 11.3 continued:Background information • Wozac wants to use simulation to help decide how many days prior to the due date it should begin production. • Objective: To use simulation to determine when Wozac should begin production for this order so that there is a high probability of completing it by the due date.

  36. Example 11.3 continued: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.

  37. Example 11.3 continued:Drug Production.xlsx • The completed model appears on the next slide. It can be developed as follows. • Inputs. Enter all inputs in the blue 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 B25:F49 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.

  38. Example 11.3 continued:Drug production simulation model

  39. Example 11.3 continued:Developing the simulation model • Days for batches. Simulate the days required for batches in column B. First, enter the formula =RISKDISCRETE(B9:B15,C9:C15)in cell B25. Then enter the general formula =IF(OR(F25=“Yes”,F25=“”),””,RISKDISCRETE($B$9:$B$15,$C$9:$C$15)) in cell B26 and copy it down to cell B49. • Batch yields. Simulate the batch yield in column C. First, enter the formula =RISKTRIANG(B19,C19,D19) and =IF(OR(F25=“Yes”,F25=“”),””,RISKTRIANG($B$19,$C$19,$D$19)) in cell C25 and C26 and copy it down to C49.

  40. Example 11.3 continued:Developing the simulation model • Pass inspection? Check whether each batch passes inspection with the formulas =IF(RAND()<B21,”Yes”,”No”) and IF(OR(F25=“Yes”, F25=“”),””,IF(RAND()<$B$21,”Yes”,”No”)) in cells D25 and D26 and copy the latter down to cell D49. • 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(D25=“Yes”,C25,0) and =IF(E25>=B4,”Yes”,”Not yet”) in cells E25 and F25 for batch 1.

  41. Example 11.3 continued:Developing the simulation model Then enter the general formulas =IF(OR(F25=“Yes”,F25=“”),””,IF(D26=“Yes”,C26+E25,E29)) and =IF(OR(F25=“Yes”,F25=“”),””,IF(E26>=$B$4,“Yes”,”Not yet”)) in cells E26 and F26, and copy them down to row 49.Note that the entry in column F is “Not yet” 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.

  42. Example 11.3 continued:Developing the simulation model • Summary measures. Calculate the batch and days required in cell I24 and I25 with the formulas =COUNT(B25:B49) and =SUM(B25:B49) 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 I26 with the formula =B5-I25 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.

  43. Example 11.3 continued: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.

  44. Example 11.3 continued:Histograms

  45. Example 11.3 continued:Discussion of results • For the first question, we use the formula =RISKMAX(I24) in cell I29. 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 I31:J35. The formulas in column I are =INT(RISKMEAN(I25)), =RISKMIN(I25), =RISKMAX(I25), =RISKPERCENTILE(I25,0.05) and =RISKPERCENTILE(I25,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.

  46. Example 11.3 continued:Using @RISK • 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 H38:H42. We enter the formula =RISKTARGET($I$25,$B$4-H38) in cell I38 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.

  47. Deming’s funnel experiment • Edwards Deming was an American statistician whose views on quality management revolutionized the way companies do business across the world. • Deming has been given much of the credit for Japan’s spectacular post–World War II economic recovery. • He traveled around the United States giving a famous four-day seminar on quality management. • An important component of Deming’s seminar was his famous funnel experiment. • The funnel experiment is designed to show how businesses often greatly overadjust “stable” processes. • We illustrate how it works in the following example.

  48. Example 11.4: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.

  49. Example 11.4 continued:Background information • 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.

  50. Example 11.4 continued: Background information • 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!

More Related