An excel spreadsheet application for the calculation of reorder point of an ARMA lead-time demand with discrete stochastic lead time
Kal Namit
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
[[sigma].sup.2.sub.t] = Var([S.sub.t]) = U[Z.sub.t][Z.sup.T.sub.t] [U.sup.T] = [[sigma].sup.2.sub.a] [[summation].sup.l.sub.i=1] [[summation].sup.l.sub.j=1][g.sub.ij]
To calculate the reorder point (r) and order quantity (Q) for a given service level, follow the following steps:
1. Calculate the [[psi].sub.j] weights using the following equations:
[[psi].sub.1] = [[phi].sub.1] – [[theta].sub.1]
[[psi].sub.2] = [[phi].sub.1][[psi].sub.j] + [[phi].sub.2] – [[theta].sub.2]
[[psi].sub.j] = [[phi].sub.1][[psi].sub.j-1] + …….. + [[phi].sub.p+d]][[psi].sub.j-p-d] – [[theta].sub.j]
where [[psi].sub.0] = 1, [[psi].sub.j] = 0 for j q. [[phi].sub.j] and [[theta].sub.j] are the coefficients of the autoregressive and moving average in ARMA of order (p, q)
2. Calculate [g.sub.ij] and [g.sub.ii].
3. Compute [[??].sub.t](l), for l = 1, …, L, the forecast values using difference equation forms and then compute E([S.sub.t]) = [[??].sub.t](l) + [[??].sub.t](l – 1) + …….. + [[??].sub.t](1)
4. Compute [[sigma].sup.2.sub.l] = [[sigma].sup.2.sub.a] [[summation].sup.l.sub.i=1][[summation].sup.l.sub.j=1][g.sub.ij], [[sigma].sup.2.sub.a] is the variance of the white noise process which can be estimated from the forecast errors data.
5. Then find the optimal values of both r and Q by solving
Minimize
D/Q A + IC[Q/2] + r – [mu]]
subject to
E(b)/Q [less than or equal to] 1 – s
where
D = Expected annual demand,
A = Cost of placing an order,
I = Inventory holding charge in dollars per dollar per year,
C = Unit cost of the inventory,
[mu] = Expected lead-time demand,
DA/Q = Expected annual ordering cost, and
IC(Q/2+r-[mu]) = Expected annual holding cost.
[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII]
When taking into consideration of the stochastic discrete lead times, the expected back order amount per cycle for a given lead time t (conditional expected value of the back-order) is
E(b,l) = {[[sigma].sub.t]h([[mu].sub.t],[[sigma].sub.t]) – (r – [[mu].sub.t])H(r,[[mu].sub.t],[[sigma].sub.t])}
Then the expected value of back order per cycle is
E(b) = [L.summation over (t=1)] {[[sigma].sub.t]h([[mu].sub.t], [[sigma].sub.t]) – (r – [mu].sub.t])H(r,[[mu].sub.t],[[sigma].sub.t])} [P.sub.t],
where h([[mu].sub.l][[sigma].sub.l]) is the marginal distribution of lead time demand and H([[mu].sub.l][[sigma].sub.l]) is the complementary cumulative of h([[mu].sub.l][[sigma].sub.l]). Note that the means and standard deviations of the distributions from different lead time values are different.
2.1 Forecasting Time Series Algorithm
Box and Jenkins have shown that a forecasting value can be derived from three different forms, difference equation form, integrated form, and forecast as a weighted average of previous observations. For the practical computation of the forecasts, the difference equation form is that the simplest and most elegant. The difference equation form is obtained from the minimum mean square error. According to Box-Jenkins’ derivation, the minimum mean square error forecast is defined in terms of the conditional expectation
[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII], this is the forecast value
for j periods from the origin t.
[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII] the present and the past data values.
[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII], the one period forecast error.
[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII]
The Algorithm for finding the forecast [[??].sub.t](l), and E([S.sub.t]) = [[??].sub.t](l) + [[??].sub.t](l – 1) + …….. + [[??].sub.t](1).
1. Write down the ARMA equation
[Z.sub.t+l] = [[phi].sub.1][Z.sub.t+l-1] + ………. [[phi].sub.p+d] [Z.sub.t+l-d] – [[theta].sub.1][a.sub.t+l-1] …….. – [[theta].sub.q][a.sub.t+l-q] + [a.sub.t]
and then treat the terms on the right according to the following rules:
2. [Z.sub.t+j] = [Z.sub.t+j] for j = 0, 1, 2 … which have already happened at origin t, are left unchanged. These are the data values occurred at the forecast origin and the ones before the origin.
3. [Z.sub.t+j] for j = 1, 2, … is equal to it’s forecast value [[??].sub.t] (j) at the origin t.
4. The past [a.sub.t+j] for j = 0. 1,2, computed from [MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII], the one period forecast error.
5. The [a.sub.t+j] (j = 1, 2,), which have not yet happened, are replaced by zeros.
In this paper, we used the following ARIMA time series model
(1 – 0.62B)(1 – B)[Z.sub.t] = (1 – 0.83B + 0.42[B.sup.2])[a.sub.t] where B is backshift operator, i.e.,
[Z.sub.t] = 1.62[Z.sub.t+1] – 0.62[Z.sub.t+2] + [a.sub.t] – 0.83[a.sub.t-1] + 0.42[a.sub.t+2]
With observations [Z.sub.t] for t = 41, …, 60 to illustrate the development of the Excel Forecasting Template.
2.2 The Excel Forecasting Model
The entire Excel forecasting model is too large to be printed on a single page. Therefore it is divided into three figures which taken together make up the entire spreadsheet. (Copies of the spreadsheet are available from the authors.)
Figure 1 Illustrates the forecast of the lead-time demand.
Data. The data [Z.sub.t] for t = 41, …, 60 are listed in B7:B26.
Forecast Error. [a.sub.t] = [Z.sub.t] – 1.62[Z.sub.t-1] + 0.62[Z.sub.t-2] + 0.83[a.sub.t-1]_- 0.42[a.sub.t+2]
Enter the starting values [a.sub.41] = [a.sub.42] = 0 in cells C7 and C8, since there are no data values of [Z.sub.39], and
[Z.sub.40] available. Compute the forecast error in cell C9 with = B9-1.62*B8+0.62*B7+0.83*C8-0.42*C7 and then copy the formula to the range C11:C40.
Calculating and Updating Forecasts. Enter [Z.sub.59], [Z.sub.60] as the starting values in cells E7 and E8; enter the forecasting formula = 1.62*E8 – 0.62*E7 + C27 – 0.83*C26 + 0.42*C25 in cell E9, and copy it to the range E11:E28.
The What–If capacity of the Excel will allow us to update the forecasts by simply replacing the forecast formula cells with the data values.
[FIGURE 1 OMITTED]
Figure 2 illustrates the calculation of the G value.
Enter index J = -1, 0, 1, 2, … into A2:A10
Enter [[theta].sub.1], [[theta].sub.2] in cells B4, and B5.
Enter [[psi].sub.-1] = 0, [[psi].sub.0] = 1 in cell I0, and J9 as the two starting values
Enter the formula for [[psi].sub.1], = 1.62*J9 – 0.62*J8 – I10, in cell J10
Then use the copy command to generate the values of [[psi].sub.2], …… [[psi].sub.2] in the range J11:[J.sub.s+9]
Name the vector [[psi].sub.0], [[psi].sub.1], ……….. [[psi].sub.2] in the range J9:[J.sub.s+9] as psi.
Enter the formula = SUMPRODUCT(OFFSET(psi,0,0,$19,1), OFFSET(psi,M$8,0,$L9,1)) in cell M9, and copy it to the range M10:M13
Copy the formula in cells M10, M11, and M12 to the range N10:P10, N11:O11, and N12, respectively.
[FIGURE 2 OMITTED]
Figure 3 illustrates the calculation of the optimal Q and r.
Enter = M9, =SUM(M9:M10) + 2*N9, =SUM(M9:M11)+2*SUM(N9:N10)+2*O9, =SUM(M9:M12)+2*SUM(N9:N11)+2*SUM(O9:O10)+2*P9, and =SUM(M9:M13)+2*SUM(N9:N12)+2*SUM(O9:O11)+2*SUM(P9:P10)+2*Q9 in cells L20 through L24, respectively.
Assume [[sigma].sub.a] = 15.5, enter =15.5*SQRT(L20) in cell M20 and copy it to the range M21:M24.
Enter =(Rp-F9)/(M20), =1 – NORMSDIST(O20), =NORMDIST(O20,0,1,0), =M20*Q20-M20*O20*P20 in cells 020, P20, Q20, and R20, respectively and copy O20:R20 to O21:R24.
Enter =SUMPRODUCT(N20:N24,F9:F13), =SUMPRODUCT(N20:N24,R20:R24), =1-EBO/Q, and =(D/Q)*A + IC*(Q/2 + Rp – MU) in J28, J29, J30, and J31, respectively.
Use the Solver to find the optimal values of Q and r by selecting cell J31 to minimize, selecting cells J26 and J27 as the changing cells and constrains them to be positive, constrainting J30 to be greater than or equal to Sv, the required fill rate, and click on Solve.
[FIGURE 3 OMITTED]
3. CONCLUSION
In this paper, we discarded the constant lead time assumption of the inventory model; and instead allowed it to function as a discrete random variable. We also presented an Excel based methodology for identifying the optimal solution of the
inventory system under uncertainty when (a) the demand can be represented by an ARMA process, and (b) the lead-time period is a discrete random variable. Developing an Excel template does not require high-level programming knowledge and skills. In addition, the build-in probability density functions, distribution functions, and the Solver program tremendously simplify the iterative computations by eliminating the need to look for values from the statistical tables. Moreover, the updating equations of Box-Jenkins together with the what-if capability of Excel make it possible to update the reorder point and safety stock periodically. The user friendliness and built-in capabilities of Excel makes a spreadsheet inventory-control application a low-cost tool and model simulator which is easy for whatever modification necessary to better adapt to needs and environments of the market.
REFERENCES:
Box, George E. P. and Jenkins, Gwilym M., Time Series Forecasting and Control., Holden-Day, San Francisco., 1976.
Hadley, G., and Whitin, T., M., Analysis of Inventory Systems, Prentice-Hall ,Englewood Cliffs, N. J., 1963.
Horowitz, A. D., and Daganzo, C. F., ‘A Graphical Method for Optimizing a Continuous Review Inventory System’, Production and Inventory Management Journal, Vol. 27 (4), 1986, pp. 30-46.
Stevenson, William J. Production Operations Management. Irwin , McGraw-Hill Publishing Company, sixth Edition, 1999.
Winston, Wayne, L. and Albright, Christian, S., Practical Management Science–Spreadsheet Modeling and Applications, Duxbury Press, An International Thomson Publishing Company, 1997.
Kal Namit, Winston-Salem State University, North Carolina, USA
Jim Chen, Norfolk State University, Virginia, USA
Dr. Kal Namit earned his Ph.D. at the University of Wisconsin. Currently he is an associate professor of business administration at Winston-Salem State University, North Carolina.
Dr. Jim Chen earned his Ph.D. at the University of North Texas in 1982. Currently he is a professor and the department head for Accounting, Finance, and Information Management at Norfolk State University, Virginia.
COPYRIGHT 2005 International Academy of Business and Economics
COPYRIGHT 2006 Gale Group
