Question 4 (25 points): Prof. Yunes's daughter, Lavinia, has come up with the brilliant idea of...

Transcribed Image Text:

Question 4 (25 points): Prof. Yunes's daughter, Lavinia, has come up with the brilliant idea of having a GBSA beach luau by the Cape Florida lighthouse, and sweet talked the vice dean into providing transportation (attagirl!). Using her wits, she convinced you to be in charge of buying the wine. To save money, you found a supplier that sells wine in 12-bottle cases, and you are trying to decide whether to buy 12 or 14 cases. Each bottle costs $15 and can be sold at the cookout for $20. Leftover (unsold) wine bottles from the luau can be sold later at an upcoming happy hour on campus for $5 each. You estimate the demand for wine bottles at the luau to be normally distributed with a mean of 135, a standard deviation of 35, and truncated between 90 and 180 bottles (rounded to the nearest integer). Make cell B1 = 15 (unit cost), cell B2 = 20 (selling price), cell B3 = 5 (salvage price), cell B4 = 144 (first order size = 12 cases x 12/case = 144 bottles), and cell C4 - 168 (second order size 14 cases x 12/case = 168 bottles). = (a) Write down the formulas needed to calculate: the demand for wine bottles in B5; the number of bottles sold for each order size in B6 and C6; the number of bottles left over for each order size in B7 and C7; and the profit for each order size in B8 and C8. Run a single simulation to estimate the mean profit for each of the two order sizes. In addition, ask the simulation add-in to monitor cell B5 (the demand) as an information cell. Use 8675309 as your random number seed and perform 1,000 trials. (b) What is the mean profit and mean standard error for each order size? Compute the 90% confidence intervals in both cases. (c) What is the probability of losing money if you order 14 cases of wine? What is the 99% confidence interval around this probability? (d) If you order 12 cases of wine, how likely are you to run out of wine at the luau? (e) In these 1,000 simulated demand scenarios, how often was the 14-case order superior to the 12-case order in terms of profit? Question 4 (25 points): Prof. Yunes's daughter, Lavinia, has come up with the brilliant idea of having a GBSA beach luau by the Cape Florida lighthouse, and sweet talked the vice dean into providing transportation (attagirl!). Using her wits, she convinced you to be in charge of buying the wine. To save money, you found a supplier that sells wine in 12-bottle cases, and you are trying to decide whether to buy 12 or 14 cases. Each bottle costs $15 and can be sold at the cookout for $20. Leftover (unsold) wine bottles from the luau can be sold later at an upcoming happy hour on campus for $5 each. You estimate the demand for wine bottles at the luau to be normally distributed with a mean of 135, a standard deviation of 35, and truncated between 90 and 180 bottles (rounded to the nearest integer). Make cell B1 = 15 (unit cost), cell B2 = 20 (selling price), cell B3 = 5 (salvage price), cell B4 = 144 (first order size = 12 cases x 12/case = 144 bottles), and cell C4 - 168 (second order size 14 cases x 12/case = 168 bottles). = (a) Write down the formulas needed to calculate: the demand for wine bottles in B5; the number of bottles sold for each order size in B6 and C6; the number of bottles left over for each order size in B7 and C7; and the profit for each order size in B8 and C8. Run a single simulation to estimate the mean profit for each of the two order sizes. In addition, ask the simulation add-in to monitor cell B5 (the demand) as an information cell. Use 8675309 as your random number seed and perform 1,000 trials. (b) What is the mean profit and mean standard error for each order size? Compute the 90% confidence intervals in both cases. (c) What is the probability of losing money if you order 14 cases of wine? What is the 99% confidence interval around this probability? (d) If you order 12 cases of wine, how likely are you to run out of wine at the luau? (e) In these 1,000 simulated demand scenarios, how often was the 14-case order superior to the 12-case order in terms of profit?

Answer rating: 100% (QA)

a The formulas needed to calculate the demand for wine bottles B5 the number of bottles sold for eac... View the full answer