Hi Guys,

I'm just a student and perhaps I do not quite fit in here with this thread, but I'm hoping someone can help me with my problem.

I'm quite new to the standard deviation and all that is related to it. I have been trying to do a question that relates to inventory management, but uses std. deviation and I'm not sure how to set it up in excel, so that I can calculate the std. deviation.

To be honest, I've absolutely no idea where to even start. I've never had to do a std deviation question in all my years of education, so now that I need it, I'm absolutely dumbfounded.

The lecturer just talked about different case studies and the theory of inventory management, and then gave us this question to solve, even though he never actually explained the basic principles of any of the calculations.

If anyone knows how to do this, and is willing to explain what I actually need to do, I would be ever so grateful.


Here's the 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).

(here i do not know how to set it up in excel? how do i organise data so that i can try and use the std deviation formula available in excel?

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.
(is there a formula in excel that allows me to assume std deviation and mean and so that I can input the rest of data? if so, how do i go about that?)

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?