Std. Deviation calculation how to

[Do_Not_Panic]

Guys,

I need help. I have basically no knowledge of statistics, but I really need to solve this question. A famous high-end department store must decide on the quantity of a high-priced women’s handbag to procure in Italy for the upcoming holiday season. The unit cost of the handbag to the store is $68.50 and the handbag will sell for $150. Any handbags not sold at the end of the season are purchased by a discount firm for $20. In addition, the store accountants estimate that there is a cost of $0.40 for each dollar tied up in inventory, as this dollar invested elsewhere could have yielded a gross profit. Assume that this cost is attached to unsold bags only.

a) Suppose that the sales of bags are equally likely to be anywhere from 100 to 300 handbags during the season. Based on this, how many bags should the store purchase? (Hint: this means that the correct distribution of demand is uniform. You can use either a discrete or a continuous uniform distribution).

b) A detailed analysis of past data shows that the number of bags sold is better described by a normal distribution, with mean 200 and standard deviation 25. Now what is the optimal number of bags to be purchased?

c) The expected demand was the same in parts (a) and (b), but the optimal order quantities differed. What accounted for this difference?

For the rest of the question, assume that the demand is normally distributed with mean 200 and standard deviation 25.

d) The Italian bag supplier approaches the department store with the following deal: they will charge $60 per bag instead of $68.50 and buy any bags left unsold at the end of the season for $30. Should the store accept this deal? Why? What are the profits of the supplier and the retail store?

e) What if the supplier offers to sell each bag for $25 to the store but wants a share of 35% of the revenue generated from bags sold at the end of the season? Is this offer acceptable to the store? What are the profits of the supplier and the retail store?

f) If you know that the Italian supplier produces each bag for $13, what is the centralized solution?
I'm not sure what to do, where to start. Is it possible to do this in Excel? If yes, how?

 

[Ray Vickson]

Guys,

I need help. I have basically no knowledge of statistics, but I really need to solve this question.


A famous high-end department store must decide on the quantity of a high-priced women’s handbag to procure in Italy for the upcoming holiday season. The unit cost of the handbag to the store is $68.50 and the handbag will sell for $150. Any handbags not sold at the end of the season are purchased by a discount firm for $20. In addition, the store accountants estimate that there is a cost of $0.40 for each dollar tied up in inventory, as this dollar invested elsewhere could have yielded a gross profit. Assume that this cost is attached to unsold bags only.

a) Suppose that the sales of bags are equally likely to be anywhere from 100 to 300 handbags during the season. Based on this, how many bags should the store purchase? (Hint: this means that the correct distribution of demand is uniform. You can use either a discrete or a continuous uniform distribution).

b) A detailed analysis of past data shows that the number of bags sold is better described by a normal distribution, with mean 200 and standard deviation 25. Now what is the optimal number of bags to be purchased?

c) The expected demand was the same in parts (a) and (b), but the optimal order quantities differed. What accounted for this difference?

For the rest of the question, assume that the demand is normally distributed with mean 200 and standard deviation 25.

d) The Italian bag supplier approaches the department store with the following deal: they will charge $60 per bag instead of $68.50 and buy any bags left unsold at the end of the season for $30. Should the store accept this deal? Why? What are the profits of the supplier and the retail store?

e) What if the supplier offers to sell each bag for $25 to the store but wants a share of 35% of the revenue generated from bags sold at the end of the season? Is this offer acceptable to the store? What are the profits of the supplier and the retail store?

f) If you know that the Italian supplier produces each bag for $13, what is the centralized solution?



I'm not sure what to do, where to start. Is it possible to do this in Excel? If yes, how?

Such problems are standard in introductory Operations Research textbooks; they are known as "Newsboy problems" or "Newsvendor problems" or sometimes as "single-period inventory problems". You can find them discussed in any introductory Operations Reseach textbook, as well as in numerous on-line sources, such as http://www.columbia.edu/~gmg2/4000/pdf/lect_07.pdf (free download).
Just Google 'newsboy problem' or 'newsvendor problem'. Check out the sources first, then come back here if you still need assistance.

RGV