Lesson 5: Stochastic Inventory Control

Finding An Optimal (Q, R) Policy Using Excel Solver

This lesson illustrates the procedure of finding an optimal (Q,R) policy using Excel Solver. Download the file Lecture_05_w08_431_stochastic_inventory.xls from the course website and follow the instruction given below.

(A) Exercise on Normal Distribution

  1. Find area fromz
  • Area on the left ofz = NORMSDIST(z)
  1. Findzfrom area
  • z = NORMSINV(Area on the left of z)
  1. Find area fromx
  • Area on the left ofx = NORMDIST(x,,,TRUE)
  1. Findxfrom area
  • x = NORMINV(Area on the left ofx,,)
  1. FindL(z) fromz
  • L(z) =NORMDIST(z,0,1,FALSE)-z*(1-NORMDIST(z,0,1,TRUE))

Question A Find

  1. area on the left ofz = 2.5
  2. zcorresponding to the area on the left = 0.4
  3. the area on the left ifx=600, =300 and =120

  4. xif the area on the left =0.4, =300 and =120
  5. L(z) if z=2.5

(B) Exercise on (Q,R) policy for a given pair of Q and R

Inputs:  = mean of the lead-time demand and  = standard deviation of the lead-time demand

  1. Find probability of stock-out
  • Probability of no stock-out = Area on the left ofR

= NORMDIST(R,,,TRUE)

  1. Find proportion of shortages and fill rate
  • z= (R-)/
  • L(z)=NORMDIST(z,0,1,FALSE)-z*(1-NORMDIST(z,0,1,TRUE))
  • Number of units of demand not met in each cycle,n= *L(z)
  • Proportion of shortages =n/Qand fill rate = 1-n/Q
  1. Find costs
  • Further inputs required: , h, K, p
  • Holding cost, regular inventory =h*Q/2
  • Holding cost, safety stock =h*(R-)
  • Ordering cost =K*/Q
  • Stock-out cost = (proportion of shortages)**p
  • Total cost = Holding cost (regular & safety) + ordering cost + stock-out cost


For Parts B and C: Annual demand for number 2 pencils at the campus store is normally distributed with mean 2,000 and standard deviation 300. The store purchases the pencils for 10 cents and sells them for 35 cents each. There is a two-month lead time from the initiation to the receipt of an order. The store accountant estimates that the cost in employee time for performing the necessary paper work to initiate and receive an order is $20, and recommends a 25 percent annual interest rate for determining holding cost. The cost of a stock-out is the cost of lost profit plus an additional 20 cents per pencil, which represents the cost of loss of goodwill.

Question B For Q=1500 and R=500 find

  1. probability of stock-out
  2. fill rate
  3. total inventory costs


(C) Exercise on finding an optimal (Q,R) policy using Excel Solver

  • Minimize total cost
  • Decision variables: Q and R
  • Constraints
  • Case 1: (Section 5.4) no constraint
  • Case 2: (Section 5.5, Type 1 service) probability of no stock-out = 
  • Case 3: (Section 5.5, Type 2 service) fill rate =  i.e., proportion of shortages = 1-
  • Both Q and R are non-negative

Question C Find an optimal (Q,R) policy (using Excel Solver) with

  1. no constraint on ,
  2. =0.95 and  unrestricted

  3. =0.96 and  unrestricted

Excel Solver

  1. Add-in Solver, if needed: Click on the “Tools” menu. If the “Solver” is not one of the items of the “Tools” menu, choose the “Add-Ins.” Click on the box adjacent to “Solver Add-In” (a check mark will appear) and click “OK.”
  1. Define objective: From the “Tools” menu, choose “Solver.” You get the “Solver Parameters” window. Click on the little arrow adjacent to the box “Set Target Cell.” Click on the Cell containing total cost and click again on the arrow. Click on the circle adjacent to “Min.”
  1. Decision variables: The cell addresses for the decision variables are entered in the box below “By Changing Cells:.” The decision variables are the cells containing Q and R.
  1. Constraints: The constraints are entered in the box below “Subject to the Constraints.” Click on “Add” and you get the “Add Constraint” window. Provide the information on constraint (LHS, inequality, RHS) and click on “OK.”
  1. Non-negativity: From the “Solver Parameters” window click on “Options.” You get the “Solver Options” window. Click on a box adjacent to “Assume Non-Negativity.” Click on “OK.”
  1. Solve: Finally, from the “Solver Parameters” window click on “Solve.” A “Solver Results” window pops up. Click on “OK.”

1