Question: Use @RISK to analyze the sweatshirt situation

> A company is about to develop and then market a new product. It wants to build a simulation model for the entire process, and one key uncertain input is the development cost. For each of the following scenarios, choose an appropriate distribution togethe

> Modify the Pigskin spreadsheet model so that demand in any of the first five months must be met no later than a month late, whereas demand in month 6 must be met on time. For example, the demand in month 3 can be met partly in month 3 and partly in month

> We all hate to keep track of small change. By using random numbers, it is possible to eliminate the need for change and give the store and the customer a fair deal. This problem indicates how it could be done. a. Suppose that you buy something for $0.20.

> Use @RISK to draw a triangular distribution with parameters 200, 300, and 600. Then superimpose a normal distribution on this drawing, choosing the mean and standard deviation to match those from the triangular distribution. (Click the Add Overlay button

> Use @RISK to draw a binomial distribution that results from 50 trials with probability of success 0.3 on each trial, and use it to answer the following questions. a. What are the mean and standard deviation of this distribution? b. You have to be more ca

> Consider a situation where there is a cost that is either incurred or not. It is incurred only if the value of some random input is less than a specified cutoff value. Why might a simulation of this situation give a very different average value of the co

> When you use a RISKSIMTABLE function for a decision variable, such as the order quantity in the Walton model, explain how this provides a “fair” comparison across the different values tested.

> It is very possible that when you use a correlation matrix as input to the RISKCORRMAT function in an @RISK model, the program will inform you that this is an invalid correlation matrix. Provide an example of an obviously invalid correlation matrix invol

> Consider the claim that normally distributed inputs in a simulation model are bound to lead to normally distributed outputs. Do you agree or disagree with this claim? Defend your answer.

> Why is the RISKCORRMAT function necessary? How does @RISK generate random inputs by default, that is, when RISKCORRMAT is not used?

> A building contains 1000 lightbulbs. Each bulb lasts at most five months. The company maintaining the building is trying to decide whether it is worthwhile to practice a “group replacement” policy. Under a group replacement policy, all bulbs are replaced

> Many people who are involved in a small auto accident do not file a claim because they are afraid their insurance premiums will be raised. Suppose that City Farm Insurance has three rates. If you file a claim, you are moved to the next higher rate. How m

> Modify the Pigskin spreadsheet model so that except for month 6, demand need not be met on time. The only requirement is that all demand be met eventually by the end of month 6. How does this change the optimal production schedule? How does it change the

> Big Hit Video must determine how many copies of a new video to purchase. Assume that the company’s goal is to purchase a number of copies that maximizes its expected profit from the video during the next year. Describe how you would use simulation to she

> Use @RISK to draw a triangular distribution with parameters 300, 500, and 900. Then answer the following questions. a. What are the mean and standard deviation of this distribution? b. What are the 5th and 95th percentiles of this distribution? c. What i

> You plan to simulate a portfolio of investments over a multiyear period, so for each investment (which could be a particular stock or bond, for example), you need to simulate the change in its value for each of the years. How would you simulate these cha

> Suppose you simulate a gambling situation where you place many bets. On each bet, the distribution of your net winnings (loss if negative) is highly skewed to the left because there are some possibilities of really large losses but not much upside potent

> If you want to replicate the results of a simulation model with Excel functions only, not @RISK, you can build a data table and let the column input cell be any blank cell. Explain why this works.

> You are making several runs of a simulation model, each with a different value of some decision variable (such as the order quantity in the Walton calendar model), to see which decision value achieves the largest mean profit. Is it possible that one valu

> We are continually hearing reports on the nightly news about natural disasters—droughts in Texas, hurricanes in Florida, floods in California, and so on. We often hear that one of these was the “worst in over 30 years,” or some such statement. Are natura

> A technical note in the discussion of @RISK indicated that Latin Hypercube sampling is more efficient than Monte Carlo sampling. This problem allows you to see what this means. The file P10_44.xlsx gets you started. There is a single output cell, B5. You

> In statistics we often use observed data to test a hypothesis about a population or populations. The basic method uses the observed data to calculate a test statistic (a single number). If the magnitude of this test statistic is sufficiently large, the n

> Simulation can be used to illustrate a number of results from statistics that are difficult to understand with non-simulation arguments. One is the famous central limit theorem, which says that if you sample enough values from any population distribution

> In one modification of the Pigskin problem, the maximum storage constraint and the holding cost are based on the average inventory (not ending inventory) for a given month, where the average inventory is defined as the sum of beginning inventory and endi

> At the beginning of each week, a machine is in one of four conditions: 1 = excellent; 2 = good; 3 = average; 4 = bad. The weekly revenue earned by a machine in state 1, 2, 3, or 4 is $100, $90, $50, or $10, respectively. After observing the condition of

> A Flexible Savings Account (FSA) plan allows you to put money into an account at the beginning of the calendar year that can be used for medical expenses. This amount is not subject to federal tax. As you pay medical expenses during the year, you are rei

> Use @RISK to draw a normal distribution with mean 500 and standard deviation 100. Then answer the following questions. a. What is the probability that a random number from this distribution is less than 450? b. What is the probability that a random numbe

> United Electric (UE) sells refrigerators for $400 with a one-year warranty. The warranty works as follows. If any part of the refrigerator fails during the first year after purchase, UE replaces the refrigerator for an average cost of $100. As soon as a

> It is surprising (but true) that if 23 people are in the same room, there is about a 50% chance that at least two people will have the same birthday. Suppose you want to estimate the probability that if 30 people are in the same room, at least two of the

> The annual return on each of four stocks for each of the next five years is assumed to follow a normal distribution, with the mean and standard deviation for each stock, as well as the correlations between stocks, listed in the file P10_37.xlsx. You beli

> Dilbert’s Department Store is trying to determine how many Hanson T-shirts to order. Currently the shirts are sold for $21, but at later dates the shirts will be offered at a 10% discount, then a 20% discount, then a 40% discount, then a 50% discount, an

> Lemington’s is trying to determine how many Jean Hudson dresses to order for the spring season. Demand for the dresses is assumed to follow a normal distribution with mean 400 and standard deviation 100. The contract between Jean Hudson and Lemington’s w

> Assume that all of a company’s job applicants must take a test, and that the scores on this test are normally distributed. The selection ratio is the cutoff point used by the company in its hiring process. For example, a selection ratio of 25% means that

> W. L. Brown, a direct marketer of women’s clothing, must determine how many telephone operators to schedule during each part of the day. W. L. Brown estimates that the number of phone calls received each hour of a typical eight-hour shift can be describe

> As indicated by the algebraic formulation of the Pigskin model, there is no real need to calculate inventory on hand after production and constrain it to be greater than or equal to demand. An alternative is to calculate ending inventory directly and con

> A hardware company sells a lot of low-cost, high-volume products. For one such product, it is equally likely that annual unit sales will be low or high. If sales are low (30,000), the company can sell the product for $20 per unit. If sales are high (70,0

> A new edition of a very popular textbook will be published a year from now. The publisher currently has 1000 copies on hand and is deciding whether to do another printing before the new edition comes out. The publisher estimates that demand for the book

> You have made it to the final round of the show Let’s Make a Deal. You know that there is a $1 million prize behind either door 1, door 2, or door 3. It is equally likely that the prize is behind any of the three doors. The two doors without a prize have

> Use @RISK to draw a uniform distribution from 400 to 750. Then answer the following questions. a. What are the mean and standard deviation of this distribution? b. What are the 5th and 95th percentiles of this distribution? c. What is the probability tha

> Six months before its annual convention, the American Medical Association must determine how many rooms to reserve. At this time, the AMA can reserve rooms at a cost of $150 per room. The AMA believes the number of doctors attending the convention will b

> The Business School at State University currently has three parking lots, each containing 155 spaces. Two hundred faculty members have been assigned to each lot. On a peak day, an average of 70% of all lot 1 parking sticker holders show up, an average of

> The effect of the shapes of input distributions on the distribution of an output can depend on the output function. For this problem, assume there are 10 input variables. The goal is to compare the case where these 10 inputs each have a normal distributi

> Suppose you are going to invest equal amounts in three stocks. The annual return from each stock is normally distributed with mean 0.01 (1%) and standard deviation 0.06. The annual return on your portfolio, the output variable of interest, is the average

> Repeat Problem 23, but now make the second input variable triangularly distributed with parameters 50, 100, and 500. This time, verify not only that the correlation between the two inputs is approximately 0.7, but also that the shapes of the two input di

> Repeat problem 23, but make the correlation between the two inputs equal to –0.7. Explain how the results change. Data from Problem 23: When you use @RISK’s correlation feature to generate correlated random numbers, how can you verify that they are corr

> In PC Tech’s product mix problem, assume there is another PC model, the VXP, that the company can produce in addition to Basics and XPs. Each VXP requires eight hours for assembling, three hours for testing, $275 for component parts, and sells for $560.

> When you use @RISK’s correlation feature to generate correlated random numbers, how can you verify that they are correlated? Try the following. Use the RISKCORRMAT function to generate two normally distributed random numbers, each with mean 100 and stand

> The Fizzy Company produces six-packs of soda cans. Each can is supposed to contain at least 12 ounces of soda. If the total weight in a six-pack is less than 72 ounces, Fizzy is fined $100 and receives no sales revenue for the six-pack. Each six-pack sel

> Although the normal distribution is a reasonable input distribution in many situations, it does have two potential drawbacks: (1) it allows negative values, even though they may be extremely improbable, and (2) it is a symmetric distribution. Many situat

> Use Excel’s functions (not @RISK) to generate 1000 random numbers from a normal distribution with mean 100 and standard deviation 10. Then freeze these random numbers. a. Calculate the mean and standard deviation of these random numbers. Are they approxi

> In Problem 12 of the previous section, suppose that the demand for cars is normally distributed with mean 100 and standard deviation 15. Use @RISK to determine the “best” order quantity—in this case, the one with the largest mean profit. Using the statis

> Continuing the previous problem, assume, as in Problem 11, that the damage amount is normally distributed with mean $3000 and standard deviation $750. Run @RISK with 5000 iterations to simulate the amount you pay for damage. Compare your results with tho

> We stated that the damage amount is normally distributed. Suppose instead that the damage amount is triangularly distributed with parameters 500, 1500, and 7000. That is, the damage in an accident can be as low as $500 or as high as $7000, the most likel

> If you add several normally distributed random numbers, the result is normally distributed, where the mean of the sum is the sum of the individual means, and the variance of the sum is the sum of the individual variances. (Remember that variance is the s

> In the Walton Bookstore example with a discrete demand distribution, explain why an order quantity other than one of the possible demands cannot maximize the expected profit.

> Modify the Pigskin model so that there are eight months in the planning horizon. You can make up reasonable values for any extra required data. Don’t forget to modify range names. Then modify the model again so that there are only four months in the plan

> A sweatshirt supplier is trying to decide how many sweatshirts to print for the upcoming NCAA basketball championships. The final four teams have emerged from the quarterfinal round, and there is now a week left until the semifinals, which are then follo

> In the Walton Bookstore example, suppose that Walton receives no money for the first 50 excess calendars returned but receives $2.50 for every calendar after the first 50 returned. Does this change the optimal order quantity?

> In August of the current year, a car dealer is trying to determine how many cars of the next model year to order. Each car ordered in August costs $20,000. The demand for the dealer’s next year models has the probability distribution shown in the file P1

> Suppose you own an expensive car and purchase auto insurance. This insurance has a $1000 deductible, so that if you have an accident and the damage is less than $1000, you pay for it out of your pocket. However, if the damage is greater than $1000, you p

> Continuing the preceding problem, suppose that another key uncertain input is the development time, which is measured in an integer number of months. For each of the following scenarios, choose an appropriate distribution together with its parameters, ju

> Use the RAND function and the Copy command to generate 100 random numbers. a. What fraction of the random numbers are smaller than 0.5? b. What fraction of the time is a random number less than 0.5 followed by a random number greater than 0.5? c. What fr

> Use PrecisionTree’s Sensitivity Analysis tools to perform the sensitivity analysis requested in problem 5 of the previous section. Data from Problem 5: Perform a sensitivity analysis on the probability of a great market. To do this, enter formulas in ce

> Explain in some detail how the PrecisionTree calculations for the Acme problem are exactly the same as those for the hand-drawn decision tree. In other words, explain exactly how PrecisionTree gets the monetary values in the colored cells.

> Can you ever use the material in this chapter to help you make your own real-life decisions? Consider the following. You are about to take an important and difficult exam in one of your MBA courses, and you see an opportunity to cheat. Obviously, from an

> You often hear about the trade-off between risk and reward. Is this trade-off part of decision making under uncertainty when the decision maker uses the EMV criterion? For example, how does this work in investment decisions?

> Can you guess the results of a sensitivity analysis on the initial inventory in the Pigskin model? See if your guess is correct by using SolverTable and allowing the initial inventory to vary from 0 to 10,000 in increments of 1000. Keep track of the valu

> Insurance companies wouldn’t exist unless customers were willing to pay the price of the insurance and the insurance companies were making a profit. So explain how insurance is a win-win proposition for customers and the company.

> It seems obvious that if you can purchase information before making an ultimate decision, this information should generally be worth something, but explain exactly why (and when) it is sometimes worth nothing.

> A potentially huge hurricane is forming in the Caribbean, and there is some chance that it might make a direct hit on Hilton Head Island, South Carolina, where you are in charge of emergency preparedness. You have made plans for evacuating everyone from

> You must make one of two decisions, each with possible gains and possible losses. One of these decisions is much riskier than the other, having much larger possible gains but also much larger possible losses, and it has a larger EMV than the safer decisi

> Your company has signed a contract with a good customer to ship the customer an order no later than 20 days from now. The contract indicates that the customer will accept the order even if it is late, but instead of paying the full price of $10,000, it w

> In a classic oil-drilling example, you are trying to decide whether to drill for oil on a field that might or might not contain any oil. Before making this decision, you have the option of hiring a geologist to perform some seismic tests and then predict

> Sometimes a “single-stage” decision can be broken down into a sequence of decisions, with no uncertainty resolved between these decisions. Similarly, uncertainty can sometimes be broken down into a sequence of uncertain outcomes. Here is a typical exampl

> In the previous question, suppose you have the option of receiving a check for $2700 instead of making the risky decision described. Would you make the risky decision, where you could lose $5000, or would you take the sure $2700? What would influence you

> If your company makes a particular decision in the face of uncertainty, you estimate that it will either gain $10,000, gain $1000, or lose $5000, with probabilities 0.40, 0.30, and 0.30, respectively. You (correctly) calculate the EMV as $2800. However,

> Your company needs to make an important decision that involves large monetary consequences. You have listed all of the possible outcomes and the monetary payoffs and costs from all outcomes and all potential decisions. You want to use the EMV criterion,

> In Solver’s sensitivity report for the product mix model, the allowable decrease for available assembling hours is 2375. This means that something happens when assembling hours fall to 20,000 2 2375 5 17,625. See what this means by first running Solver w

> The following situation actually occurred in a 2009 college football game between Washington and Notre Dame. With about 3.5 minutes left in the game, Washington had fourth down and one yard to go for a touchdown, already leading by two points. Notre Dame

> One controversial topic in basketball (college or any other level) is whether to foul a player deliberately with only a few seconds left in the game. Consider the following scenario. With about 10 seconds left in the game, team A is ahead of team B by th

> George Lindsey (1959) looked at box scores of more than 1000 baseball games and found the expected number of runs scored in an inning for each on-base and out situation to be as listed in the file P09_64.xlsx. For example, if a team has a man on first ba

> The ending of the game between the Indianapolis Colts and the New England Patriots (NFL teams) in Fall 2009 was quite controversial. With about two minutes left in the game, the Patriots were ahead 34 to 28 and had the ball on their own 28-yard line with

> Suppose an investor has the opportunity to buy the following contract, a stock call option, on March 1. The contract allows him to buy 100 shares of ABC stock at the end of March, April, or May at a guaranteed price of $50 per share. He can exercise this

> A homeowner wants to decide whether he should install an electronic heat pump in his home. Given that the cost of installing a new heat pump is fairly large, the homeowner wants to do so only if he can count on being able to recover the initial expense o

> Sharp Outfits is trying to decide whether to ship some customer orders now via UPS or wait until after the threat of another UPS strike is over. If Sharp Outfits decides to ship the requested merchandise now and the UPS strike takes place, the company wi

> Sometimes it is possible for a company to influence the uncertain outcomes in a favorable direction. Suppose Acme could, by an early marketing blitz, change the probabilities of “great,” “fair,” and “awful” from their current values to 0.75, 0.15, and 0.

> A city in Ohio is considering replacing its fleet of gasoline-powered automobiles with electric cars. The manufacturer of the electric cars claims that this municipality will experience significant cost savings over the life of the fleet if it chooses to

> A retired partner from a large brokerage firm has one million dollars available to invest in particular stocks or bonds. Each investment’s annual rate of return depends on the state of the economy in the coming year. The file contains the distribution of

> Some analysts complain that spreadsheet models are difficult to resize. You can be the judge of this. Suppose the current product mix problem is changed so that there is an extra resource, packaging labor hours, and two additional PC models, 9 and 10. Wh

> The spreadsheet model for Sam’s Bookstore contains a two-way data table for profit versus order quantity and demand. Experiment with Excel’s chart types to create a chart that shows this information graphically in an intuitive format.

> A grapefruit farmer in central Florida is trying to decide whether to take protective action to limit damage to his crop in the event that the overnight temperature falls to a level well below freezing. He is concerned that if the temperature falls suffi

> A home appliance company is interested in marketing an innovative new product. The company must decide whether to manufacture this product in house or employ a subcontractor to manufacture it. The file P09_56.xlsx contains the estimated probability distr

> The purchasing agent for a PC manufacturer is currently negotiating a purchase agreement for a particular electronic component with a given supplier. This component is produced in lots of 1000, and the cost of purchasing a lot is $30,000. Unfortunately,

> Mr. Maloy has just bought a new $30,000 sport utility vehicle. As a reasonably safe driver, he believes there is only about a 5% chance of being in an accident in the coming year. If he is involved in an accident, the damage to his new vehicle depends on

> The Indiana University basketball team trails by two points with eight seconds to go and has the ball. Should it attempt a two-point shot or a three-point shot? Assume that the Indiana shot will end the game and that no foul will occur on the shot. Assum

> An electric utility company is trying to decide whether to replace its PCB transformer in a generating station with a new and safer transformer. To evaluate this decision, the utility needs information about the likelihood of an incident, such as a fire,

> A television network earns an average of $65 million each season from a hit program and loses an average of $25 million each season on a program that turns out to be a flop. Of all programs picked up by this network in recent years, 30% turn out to be hi

> Referring to the bank’s customer loan decision problem in Problem 41, suppose now that the bank’s utility function of profit x (in dollars) is U(x) = 1 - e-x/150000. Find the strategy that maximizes the bank’s expected utility. How does this optimal stra

> Perform a sensitivity analysis on the probability of a great market. To do this, enter formulas in cells B9 and B10 to ensure that the probabilities of “fair” and “awful” remain in the same ratio, 35 to 20, and that all three probabilities continue to su

> Referring to Techware’s decision problem in Problem 33, suppose now that Techware’s utility function of net revenue x (measured in dollars) is U(x) = 1 - e-x/350000. a. Find the decision that maximizes Techware’s expected utility. How does this optimal d