So far we have decided our optimal order quantity and safety stock. Safety stock is one way to satisfy variable demand but could we lead inventory from the value of the demand? Safety stock is based on service level but now we will calculate stock amount from products sale and ordering price.

Formula is based on matching the demand of one season. Example Christmas. Factory produces some Christmas item that is know to be needed about 10 000 pieces each year and standard deviation of 1 000 pieces. Now the question is shuold we produce 9 000, 10 000 or 11 000 pieces? What is the value of these options?

We will assume that item costs 5 €/piece including delivery. It’s sold 10 €. After the season all excess items are sold with 2,50 €/piece and stock is not held for next year. If me make more than we sell we will have 2,50 € cost per item for every excess items. On the other hand if we produce too little we will loose 5 € profit per every item not sold.

Values for the formula:

  • Overage C0 = 5 – 2,5 = 2,5 €/pcs
  • shortage Cs = 10 – 5 = 5 €/pcs
  • Mean demand µ = 10 000 pcs
  • Standard deviation σ = 1 000 pcs
  • z defines the percentage of the demand that we should fulfill
  • Q = Amount of production/order


Overage or shortage?

Overage or shortage?

From the first formula we will have Φ(z) = 0,67, which means that we should produce enough to fulfill 67 % from demand or take 33 % risk of shortage. This Φ-formula is called CDF or cumulative distribution formula and it can be solved in Excel with NORM.DIST-function and goal seek.

Excel tip, Goal seek

Excel tip, Goal seek

From this screenshot we can see what kind of formula we have in cell A5. We must take z-value from another cell in order to make goal seek. We must set A5 to 0,67 and change z-value. Excel will tell that z is 0,44. When we place this in Q = µ + zσ we will have our production size which is 10 440 pieces. It means that over production from the mean demand is 44 % of the standard deviation. If our standard deviation would be 2 000, our production would be 10 880 pieces.

Formula in practice

In this example we assume that we would have had whole 5 € margin to ourselves and it is therefore seen as cost. In reality this can be little trickier and we have to carefully estimate our costs. This model is as good in purchasing orders. Shop orders magazines daily or weekly and then comes next number which will turn our old stock value to 0. How many magazines should we by in order to optimize our profit?

How much ice cream should I order?

There is an example of ice cream vendor in Factory Physics by Wallace Hopp. Merchant sells Ice Cream at 15 € per can. It costs 10 € to purchase, is delivered weekly and can be ordered any mount merchant wants.

Weekly demand even when 0 stock has been 25. Now we should know it’s standard deviation. Because we don’t have enough data we will assume it follows Poisson distribution and it is square root of the mean demand which is 5 pcs. (see ordering in practice 1).  Merchant uses 25 % interest to calculate capital costs so holding costs for 1 jar is 10 € * (0,25/52) = 0,048 €/week. And shortage costs for the company is 5 € lost in sales.

So we will have that Φ(z) = 5 / (0,048+ 5) = 0,99 and then z is 2,326. Then the optimal weekly stock is 25 + 2,326 * 5 = 37 pieces. Merchant should order enough ice cream to have 37 cans in stock in beginning of the week. Then our costs are optimized.


This model is based on some assumptions too:

  • Products are separable and have no interactions or shared resources.
  • Planning is done for single period, stock is not held for next season.
  • Demand is random
  • Product is made and delivered in advance of demand
  • Costs of overage and underage are linear, producing 1 or 1000 products costs same per piece.

Model works in uncertain demand and inventory is depended bot lost sales and overage caused costs. and standard deviation. If Cs / ( C0 + Cs) is more than 0,5 our lot sizes increases from mean demand. If it’s less than 0,5 then produce/order will be less than mean demand.

Jesse Uitto

Written by Jesse Uitto

Entrepreneur, Purchasing Professional

Leave a Reply

Your email address will not be published. Required fields are marked *