PLEASE SOLVE USING EXCEL. THANK YOU!
A large retailer of kitchen appliances has asked you for advice on how much to stock and how to price leftovers of its products. Here's a typical situation: Demand for espresso machines in the northeast region runs about 2,000 per year (with a standard deviation of 500). They cost the retailer $20 and sell for $40. Since the models change yearly, unsold machines can be hard to sell at the end of the year. Steep markdowns are usually offered, but no real effort has gone into determining what price to set for unsold stock, since the relationship between price and sales has not been well understood. Management has estimated the following relationship between the price they set for leftovers and demand. Note: this represents mean demand; actual demand may vary around this mean by a standard deviation of 20% of the mean. Price for leftover stock $0 $10 $15 $20 $25 $30 Mean demand for leftovers 1000 600 450 300 150 10 For intermediate values, we use linear interpolation. For example, demand at a price of $16 is 450-[(16-15)(20-15)]*(450-300)=420. The retailer needs to know how many machines to order originally, as well as how to set prices on leftover stock. Since the number of units left over is unpredictable, it needs to select recommended price points for its various outlets that will apply regardless of the situation. A typical price schedule is as follows: 10 Quantity Recommended Left Price 0 $30 $25 150 $20 300 $18 450 $16 600 $14 1000 $12 Again, to determine the recommended price for intermediate values, use linear interpolation. a) What is the expected profit for an order quantity of 2,000, given that they price all leftovers at a price of $25 regardless of the quantity left? b) Based on the assumptions in a), show how expected profit varies for order quantities between 1,000 and 3,000 (with a step size of 100). Also, determine the probability that profits are negative, and show how this varies across the same range of order quantities. c) Determine the optimal values for the order quantity and the seven recommended price points (as in the second table above). A large retailer of kitchen appliances has asked you for advice on how much to stock and how to price leftovers of its products. Here's a typical situation: Demand for espresso machines in the northeast region runs about 2,000 per year (with a standard deviation of 500). They cost the retailer $20 and sell for $40. Since the models change yearly, unsold machines can be hard to sell at the end of the year. Steep markdowns are usually offered, but no real effort has gone into determining what price to set for unsold stock, since the relationship between price and sales has not been well understood. Management has estimated the following relationship between the price they set for leftovers and demand. Note: this represents mean demand; actual demand may vary around this mean by a standard deviation of 20% of the mean. Price for leftover stock $0 $10 $15 $20 $25 $30 Mean demand for leftovers 1000 600 450 300 150 10 For intermediate values, we use linear interpolation. For example, demand at a price of $16 is 450-[(16-15)(20-15)]*(450-300)=420. The retailer needs to know how many machines to order originally, as well as how to set prices on leftover stock. Since the number of units left over is unpredictable, it needs to select recommended price points for its various outlets that will apply regardless of the situation. A typical price schedule is as follows: 10 Quantity Recommended Left Price 0 $30 $25 150 $20 300 $18 450 $16 600 $14 1000 $12 Again, to determine the recommended price for intermediate values, use linear interpolation. a) What is the expected profit for an order quantity of 2,000, given that they price all leftovers at a price of $25 regardless of the quantity left? b) Based on the assumptions in a), show how expected profit varies for order quantities between 1,000 and 3,000 (with a step size of 100). Also, determine the probability that profits are negative, and show how this varies across the same range of order quantities. c) Determine the optimal values for the order quantity and the seven recommended price points (as in the second table above)
