Computation Section
Subunit Inventory Analysis
 Functions for Stochastic Systems

Many of the functions used for deterministic systems are also used for stochastic systems. Because of the random nature of the inventory level during a cycle, the meaning of the results are somewhat different for stochastic systems compared to the deterministic system. For example the maximum, minimum and average inventory in a cycle are all random variables for stochastic systems, so the functions for these results compute estimates of the expected values of the respective numbers or long run averages over many cycles. Most of the functions also involve some approximation as it is very difficult to compute the exact expected values. When used for stochastic systems, the functions still have two arguments. The Inv_Level function is as before

Inv_Level(instance As Range, inventory As Range)

Both arguments are ranges. The instance is a range that specifies the variables controlling the inventory. For a stochastic system, the instance still includes the lot size and fill rate, but a third argument is added for efficient calculation, the reorder point. The range inventory holds the parameters of the inventory model appropriate for the type of system.

The figure below shows computations for a model with the data name Inv8. The type shown in cell B3 indicates that this is a stochastic system with instantaneous replenishments and that backorders are allowed. The entire data is in the range B1:B16. We have chosen in this example to analyze the instance which is the optimal solution. The optimal lot size is computed in cell B17 with the function expression:


This function computes the economic order quantity for the stochastic system. In cell B18 we have:


This function computes the optimal fill rate given the lot size computed in B17. In cell B19 we have:


This function computes the reorder point for the lot size in B17 (named Inv8_EOQ) and the fill rate in B18 (named Inv8_OptFR). The range B17:B19 has been named Inv8_Opt for subsequent computations. Note that the three numbers just computed are not independent. The EOQ computation includes a computation of the optimum fill rate since both are determined simultaneously. The fill rate depends depends on the lot size, and the reorder point is function of the lot size and fill rate.

Subsequent calculations compute various measures for the system when the optimum parameters are used. The functions in B20:B29 are the same used for deterministic systems, but now they compute the expected values of the measures. The computations performed by the functions depend on the type of system being analyzed. The results in B30 and B31 are meaningful only for stochastic systems and are computed with new functions. In B30 we see the probability that a cycle experiences a shortage. For deterministic systems this probability is 0 when no shortages are allowed and 1 when shortages are allowed. Here the number is constrained by the limit in B16 and will generally be some small value. In B31 we see the safety stock. This is the extra stock maintained above the expected demand during the lead time to guard against the risk of shortage. For deterministic systems the safety stock is always 0 because there is no risk.

Notice that the data for stochastic systems will always include an indication of the probability distribution of demand. For the current example the demand is Normal with a standard deviation of 10 per week. The expected value of demand for an interval of time is the demand rate multiplied by the length of the interval.

Functions that are unique to stochastic systems are listed below with their arguments.
Inv_StochRP(Q, FR, inventory As Range)

Computes the reorder point given the fill rate and lot size

Inv_ProbShort(instance As Range, inventory As Range)
Computes the probability of a shortage in a single reorder cycle
Inv_ExpShort(instance As Range, inventory As Range)
Computes expected number of shortages in a single cycle
Inv_Safety(instance As Range, inventory As Range)
Computes the inventory required in excess of the expected demand during the lead time. This is called the safety stock.
Inv_Stoch_EOQ(inventory As Range)
Computes the optimum order quantity for a stochastic system
Inv_Stoch_OptFR(Q, inventory As Range)
Computes the optimum fill rate given the lot size
Inv_Opt_RP(Q, inventory As Range)
Computes the optimum reorder point given the lot size
Stochastic models considered by the add-in allow the demand during the lead time to have either a Normal distribution or a Poisson distribution. Depending on the model, various operations regarding these distribution must be performed. The following functions are used by the stochastic functions listed above. The functions may have value to other applications that involve random variables.


Computes the expected value of the shortage when the reorder point is kk and the demand during the lead time is Normally distributed
Computes the value of the standard Normal density function at fz
Computes the value of of x that gives a specified value of G(x).= g_z
Inv_PoissonRP(ES, theta)
Computes the reorder point for an the Poisson distribution when with a mean demand of theta during the lead time and the expected shortage is ES
Inv_Inverse_Poisson(Fx, theta)
Computes the inverse value of the Poisson with mean theta given the value of the cumulative distribution is Fx
Inv_Inverse_Poisson_Dist(Ps, theta)
Computes the inverse value of the Poisson with mean theta given the value of the probability Ps
Return to Top

tree roots

Operations Management / Industrial Engineering
by Paul A. Jensen
Copyright 2004 - All rights reserved