Several of the models described in this chapter depend on estimates of average demand and average lead time and on associated measures of variance (σ2) or standard deviation

(σ). The spreadsheet model in Figure 14.14 shows how such values can be quickly estimated from historical data using Microsoft Excel’s built-in functions. The spreadsheet contains historical demand data for 20 weeks, as well as lead time information for 15 prior orders. From this information, the spreadsheet calculates average values and variances and then uses these values to calculate average demand during lead time, safety stock, and the reorder point. The highlighted cells represent the input values. The calculated cells are as follows:

Transcribed Image Text:

Cell C32 (average weekly demand): Cell C33 (variance of weekly demand): Cell G27 (average order lead time): Cell G28 (variance of lead time): Cell F5 (average demand during lead time): Cell F6 (safety stock): Cell F7 (reorder point): = AVERAGE(C12:C31) = VAR(C12:C31) = AVERAGE(G12:G26) = VAR(G12:G26) = C32*G27 = F3*SQRT(G27*C33+C32^2*G28) = F5+F6