Slove this problem to create one excel file with cells that contain the formulas used to derive the answers. The produce manager at the local Pig & Whistle grocery store must determine how many pounds of bananas to order weekly. Based upon past experience, the demand for bananas is expected to be 100, 150, 200 or 250 pounds with the following probabilities: 100 lbs 0.20; 150 lbs 0.25, 200 lbs 0.35, 250 lbs 0.20. The bananas cost the store $.45 per pound and are sold for $0.85 per pound. Any unsold bananas at the end of each week are sold to a local zoo for $.30 per pound. Use your knowledge of Decision Analysis to model and solve this problem in order to recommend how many pounds of bananas the manager should order each week.

Slove this problem to create one excel file with cells that contain the formulas used to derive the answers. The produce manager at the local Pig & Whistle grocery store must determine how many pounds of bananas to order weekly. Based upon past experience, the demand for bananas is expected to be 100, 150, 200 or 250 pounds with the following probabilities: 100 lbs 0.20; 150 lbs 0.25, 200 lbs 0.35, 250 lbs 0.20. The bananas cost the store $.45 per pound and are sold for $0.85 per pound. Any unsold bananas at the end of each week are sold to a local zoo for $.30 per pound. Use your knowledge of Decision Analysis to model and solve this problem in order to recommend how many pounds of bananas the manager should order each week.

Practical Management Science

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter10: Introduction To Simulation Modeling

Section10.5: Introduction To @risk

Problem 19P: In Problem 12 of the previous section, suppose that the demand for cars is normally distributed with...

See similar textbooks

Similar questions

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 be normally distributed with a mean of 5000 and a standard deviation of 1000. If the number of people attending the convention exceeds the number of rooms reserved, extra rooms must be reserved at a cost of 250 per room. a. Use simulation with @RISK to determine the number of rooms that should be reserved to minimize the expected cost to the AMA. Try possible values from 4100 to 4900 in increments of 100. b. Redo part a for the case where the number attending has a triangular distribution with minimum value 2000, maximum value 7000, and most likely value 5000. Does this change the substantive results from part a?
It costs a pharmaceutical company 75,000 to produce a 1000-pound batch of a drug. The average yield from a batch is unknown but the best case is 90% yield (that is, 900 pounds of good drug will be produced), the most likely case is 85% yield, and the worst case is 70% yield. The annual demand for the drug is unknown, with the best case being 20,000 pounds, the most likely case 17,500 pounds, and the worst case 10,000 pounds. The drug sells for 125 per pound and leftover amounts of the drug can be sold for 30 per pound. To maximize annual expected profit, how many batches of the drug should the company produce? You can assume that it will produce the batches only once, before demand for the drug is known.
Play Things is developing a new Lady Gaga doll. The company has made the following assumptions: The doll will sell for a random number of years from 1 to 10. Each of these 10 possibilities is equally likely. At the beginning of year 1, the potential market for the doll is two million. The potential market grows by an average of 4% per year. The company is 95% sure that the growth in the potential market during any year will be between 2.5% and 5.5%. It uses a normal distribution to model this. The company believes its share of the potential market during year 1 will be at worst 30%, most likely 50%, and at best 60%. It uses a triangular distribution to model this. The variable cost of producing a doll during year 1 has a triangular distribution with parameters 15, 17, and 20. The current selling price is 45. Each year, the variable cost of producing the doll will increase by an amount that is triangularly distributed with parameters 2.5%, 3%, and 3.5%. You can assume that once this change is generated, it will be the same for each year. You can also assume that the company will change its selling price by the same percentage each year. The fixed cost of developing the doll (which is incurred right away, at time 0) has a triangular distribution with parameters 5 million, 7.5 million, and 12 million. Right now there is one competitor in the market. During each year that begins with four or fewer competitors, there is a 25% chance that a new competitor will enter the market. Year t sales (for t 1) are determined as follows. Suppose that at the end of year t 1, n competitors are present (including Play Things). Then during year t, a fraction 0.9 0.1n of the company's loyal customers (last year's purchasers) will buy a doll from Play Things this year, and a fraction 0.2 0.04n of customers currently in the market ho did not purchase a doll last year will purchase a doll from Play Things this year. Adding these two provides the mean sales for this year. Then the actual sales this year is normally distributed with this mean and standard deviation equal to 7.5% of the mean. a. Use @RISK to estimate the expected NPV of this project. b. Use the percentiles in @ RISKs output to find an interval such that you are 95% certain that the companys actual NPV will be within this interval.
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 quantityin this case, the one with the largest mean profit. Using the statistics and/or graphs from @RISK, discuss whether this order quantity would be considered best by the car dealer. (The point is that a decision maker can use more than just mean profit in making a decision.)
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.
Dilberts 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, and finally a 60% discount. Demand at the full price of 21 is believed to be normally distributed with mean 1800 and standard deviation 360. Demand at various discounts is assumed to be a multiple of full-price demand. These multiples, for discounts of 10%, 20%, 40%, 50%, and 60% are, respectively, 0.4, 0.7, 1.1, 2, and 50. For example, if full-price demand is 2500, then at a 10% discount customers would be willing to buy 1000 T-shirts. The unit cost of purchasing T-shirts depends on the number of T-shirts ordered, as shown in the file P10_36.xlsx. Use simulation to determine how many T-shirts the company should order. Model the problem so that the company first orders some quantity of T-shirts, then discounts deeper and deeper, as necessary, to sell all of the shirts.
The annual demand for Prizdol, a prescription drug manufactured and marketed by the NuFeel Company, is normally distributed with mean 50,000 and standard deviation 12,000. Assume that demand during each of the next 10 years is an independent random number from this distribution. NuFeel needs to determine how large a Prizdol plant to build to maximize its expected profit over the next 10 years. If the company builds a plant that can produce x units of Prizdol per year, it will cost 16 for each of these x units. NuFeel will produce only the amount demanded each year, and each unit of Prizdol produced will sell for 3.70. Each unit of Prizdol produced incurs a variable production cost of 0.20. It costs 0.40 per year to operate a unit of capacity. a. Among the capacity levels of 30,000, 35,000, 40,000, 45,000, 50,000, 55,000, and 60,000 units per year, which level maximizes expected profit? Use simulation to answer this question. b. Using the capacity from your answer to part a, NuFeel can be 95% certain that actual profit for the 10-year period will be between what two values?
Lemingtons 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 Lemingtons works as follows. At the beginning of the season, Lemingtons reserves x units of capacity. Lemingtons must take delivery for at least 0.8x dresses and can, if desired, take delivery on up to x dresses. Each dress sells for 160 and Hudson charges 50 per dress. If Lemingtons does not take delivery on all x dresses, it owes Hudson a 5 penalty for each unit of reserved capacity that is unused. For example, if Lemingtons orders 450 dresses and demand is for 400 dresses, Lemingtons will receive 400 dresses and owe Jean 400(50) + 50(5). How many units of capacity should Lemingtons reserve to maximize its expected profit?
Assume that all of a companys 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 the company will accept applicants for jobs who rank in the top 25% of all applicants. If the company chooses a selection ratio of 25%, the average test score of those selected will be 1.27 standard deviations above average. Use simulation to verify this fact, proceeding as follows. a. Show that if the company wants to accept only the top 25% of all applicants, it should accept applicants whose test scores are at least 0.674 standard deviation above average. (No simulation is required here. Just use the appropriate Excel normal function.) b. Now generate 1000 test scores from a normal distribution with mean 0 and standard deviation 1. The average test score of those selected is the average of the scores that are at least 0.674. To determine this, use Excels DAVERAGE function. To do so, put the heading Score in cell A3, generate the 1000 test scores in the range A4:A1003, and name the range A3:A1003 Data. In cells C3 and C4, enter the labels Score and 0.674. (The range C3:C4 is called the criterion range.) Then calculate the average of all applicants who will be hired by entering the formula =DAVERAGE(Data, "Score", C3:C4) in any cell. This average should be close to the theoretical average, 1.27. This formula works as follows. Excel finds all observations in the Data range that satisfy the criterion described in the range C3:C4 (Score0.674). Then it averages the values in the Score column (the second argument of DAVERAGE) corresponding to these entries. See online help for more about Excels database D functions. c. What information would the company need to determine an optimal selection ratio? How could it determine the optimal selection ratio?
A common decision is whether a company should buy equipment and produce a product in house or outsource production to another company. If sales volume is high enough, then by producing in house, the savings on unit costs will cover the fixed cost of the equipment. Suppose a company must make such a decision for a four-year time horizon, given the following data. Use simulation to estimate the probability that producing in house is better than outsourcing. If the company outsources production, it will have to purchase the product from the manufacturer for 25 per unit. This unit cost will remain constant for the next four years. The company will sell the product for 42 per unit. This price will remain constant for the next four years. If the company produces the product in house, it must buy a 500,000 machine that is depreciated on a straight-line basis over four years, and its cost of production will be 9 per unit. This unit cost will remain constant for the next four years. The demand in year 1 has a worst case of 10,000 units, a most likely case of 14,000 units, and a best case of 16,000 units. The average annual growth in demand for years 2-4 has a worst case of 7%, a most likely case of 15%, and a best case of 20%. Whatever this annual growth is, it will be the same in each of the years. The tax rate is 35%. Cash flows are discounted at 8% per year.
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 during the next year is governed by the probability distribution in the file P10_31.xlsx. A production run incurs a fixed cost of 15,000 plus a variable cost of 20 per book printed. Books are sold for 190 per book. Any demand that cannot be met incurs a penalty cost of 30 per book, due to loss of goodwill. Up to 1000 of any leftover books can be sold to Barnes and Noble for 45 per book. The publisher is interested in maximizing expected profit. The following print-run sizes are under consideration: 0 (no production run) to 16,000 in increments of 2000. What decision would you recommend? Use simulation with 1000 replications. For your optimal decision, the publisher can be 90% certain that the actual profit associated with remaining sales of the current edition will be between what two values?
Franz Beckmann is selling sausages at the local soccergames in Pullach. History has shown that in average 190 are sold,with a standard deviation of 34. He purchases the sausages for$0.25 and sells them for $2 each. Sausages not sold will be given away for free to the players. How many sausages should he pur-chase in order to maximize profit?