5
$\begingroup$

I am using Monte-Carlo simulations in Microsoft Excel to determine optimum reorder points and safety stock levels. I have the demand patterns of the last one year of the product. Using that I can construct a cumulative distribution function of the demand to draw random samples from and construct a table of demand on each day for a whole year.

One problem that I found was that the simulation is based on demand patterns alone. That is, if the company did not forecast at all, then the individual runs of the simulation would generate the demand pattern that they can expect in a year. However, if the company is able to forecast demand with 100% accuracy, then there would be no need to keep a safety stock (or very little of it). Forecast accuracy is something I am not sure how to incorporate in my model. There are formulas for calculating safety stock such as using the Mean Absolute Deviation from the forecasted demand but I would like to develop a simulation model that takes into account forecast accuracy. Thanks in advance.

$\endgroup$
6
$\begingroup$

I'm in general agreement with Larry's answer, but with one qualification. If you are generating random demand quantities from the sample CDF for a year, your demands will not conform to any trends or seasonal patterns (or even just short-term autocorrelation) in the historical data. If you then generate forecasts from the randomly sampled observations, the forecasts will also not contain trend or seasonality (and perhaps not autocorrelation, or the wrong autocorrelations), so you will be missing a chance for greater accuracy. If you use rolling forecasts from the historical data, then there will be a mismatch between forecasts and "actual" (meaning sampled) demand.

One way to mitigate this is to first analyze the historical data and see if you can suss out any patterns. Use them to build a model for the demand process. In the simulation, obtain demand observations from this model (using it to predict the current observation, then adding a bit of noise). Simultaneously use forecasts obtained from the simulated data (possibly using the same model, possibly using something similar such as exponential smoothing, perhaps with a seasonal component).

$\endgroup$
5
$\begingroup$

Why not build the forecasting directly into your simulation? So, in each period $t$, you generate a forecast $y_t$ using whatever method you want (moving average, exponential smoothing, etc.), and choose an order quantity based on the forecast and the current estimate of the standard deviation of the forecast error. Then generate the random demand, calculate the forecast error, and update the estimates of the SD of the FE.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.