Problem
A 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 P15_36.xlsx. Use simulation to determine how many T-shirts the store should order. Model the problem so that the store first orders some quantity of T-shirts, then discounts deeper and deeper, as necessary, to sell all of the shirts.
You should first build a simulation model using @RISK. Then, try running the model with several order quantities from 2,000 to 8,000, at 500 increments using a RiskSimtable function. Which order quantity maximizes the store's profit?
|
Order quantity
|
Unit cost
|
|
0 to 1499
|
$15.00
|
|
1500 to 3499
|
$14.25
|
|
3500 to 5999
|
$13.54
|
|
6000 or more
|
$12.86
|