An excel spreadsheet application for the calculation of reorder point of an ARMA lead-time demand with discrete stochastic lead time

Journal of the Academy of Business and Economics, March, 2005 by Kal Namit, Jim Chen

ABSTRACT

In this research paper, we will look into the calculation of reorder point, safety stock and order quantity of an inventory based on the assumption that the process generating demand data can be forecasted by ARMA Box-Jenkins model The distribution of forecast errors from the calculation process in Box-Jenkins' ARMA analysis will be used as the measurement of the accuracy with which the reorder point and safety stock are determined. We also discard the constant lead time assumption and allowed it to function as a discrete random variable. An Excel based methodology is provided at the end.

Keywords: Inventory Model, Stochastic Lead Time, Box Jenkins, ARMA, Excel

1. INTRODUCTION

Two fundamental questions that must be answered in controlling the inventory of any physical goods are when to replenish the inventory and how much to order for replenishment. EOQ models answers the question of how much to order, but not the question of when to order. The latter is the function of models that identify the reorder point in terms of a quantity: the reorder point occurs when the quantity on hand drops to a predetermined amount. The amounts generally includes expected demand during lead time and perhaps an extra cushion of stock, which serve to reduce the risk of experience a stock-out during lead time especially in the environment when variability is present in demand or lead time or both. The following four factors are being used in determining the reorder point quantity (Stevenson, 1999): (1) the rate of demand (usually based on a forecast value), (2) the length of lead- time, (3) the variability of demand and/or lead time, and (4) the degree of acceptable stock-out risk. Taking into consideration of these four factors, Hadley and Whittin (1963) suggested the model with backorder which attempts to answer both two fundamental questions mentioned above. Their expected costs included in the model are the expected annual setup, holding, and the shortage costs. Winston et al., (1997) observed that inventory control and management literature has treated costs (expenses) attributed to shortages in four different ways. One approach assumes that shortage cost is independent of the quantity short and depends on whether there is a shortage or not. Horowitz and Daganzo (1966) used this model to characterize the expedited shipment model (the name for their proposed framework). An alternative approach assumes that there is a shortage of G dollars per unit short. The third and fourth approaches specify service levels to avoid addressing the difficult problem of assessing shortage costs. The third approach specifies a fraction of order cycles that should not experience stockouts. The fourth approach specifies a fraction of demands that must be met on time. In most of the practical cases, it is very difficult to assign numerical values to the stockout costs, therefore the management alternatively would resort to either the third or the fourth approach. We utilize the fourth approach to illustrate the calculation of the reorder point is this paper. In other words, the service constraint of the inventory model in this paper is s = 1 - E(b)/Q where Q is the order size and E(b) is the expected shortage each cycle.

2. DETERMINATION OF REORDER POINT AND ORDER QUANTITY

In order to compute the reorder point with a safety stock that will meet a specific service level, we have to know the probability density of the lead time demand, the total demand during the lead time, and the variance of the total lead time demand.

When the demand can be represented by an ARMA process (Box and Jenkins, 1976), the conditional probability distribution p([z.sub.t-l], | [z.sub.t], [z.sub.t-l], ..., [z.sub.l]) of the future value [z.sub.t l] will be normal with mean [[??].sub.t] (l), the forecast of the future value [z.sub.t l] from the origin t, and variance {1 [[summation].sup.l- 1.sub.j=1][[psi].sup.2.sub.j]}[[sigma].sup.2.sub.a] where [[sigma].sup.2.sub.a] is the variance of the white noise process which can be estimated from the forecast errors data, and then p([z.sub.t l], [z.sub.t l-1], ..., [z.sub.t 1] | [z.sub.t], [z.sub.t-1], ..., [z.sub.1]) is a multivariate normal distribution with mean

[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII]

where [[??].sub.t](l) is the expected value of [z.sub.t l] provided that [z.sub.t], [z.sub.t-1], ..., [z.sub.1] values are available, and covariance matrix

[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII]

where

[g.sub.jj] = {1 [[summation].sup.l-1.sub.j=1][[psi].sup.2.sub.j]}

[g.sub.t,t j] = [[summation].sup.l-1.sub.i=0][[psi].sub.i][[psi].sub.j i] where [[psi].sub.0] = 1.

The total lead-time demand is

[S.sub.t] = [z.sub.t l] [z.sub.t l-1] ... [z.sub.t 1] = U[Z.sub.t]

where

U = [1,1,....,1,1], and

[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII]

The expected total lead-time demand is then

E([S.sub.t]) = U[[??].sub.t] = [[??].sub.t](l) [[??].sub.t](l - 1) ........ [[??].sub.t](1), and

the variance of total lead-time demand is