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.

Improve this question
$\endgroup$
2
  • 4
    $\begingroup$ Question to editor @SecretAgentMan . How does bumping a 19 month old thread to remove " Thanks in advance." from the question serve to improve the board? All it does is bump the thread, prompting some readers to look at it until they find the only reason it was bumped was to remove "Thanks in advance.". That just wastes the time of the readers, not saving them time. by sparing them from reading " Thanks in advance.". $\endgroup$
    – Mark L. Stone
    Jan 4, 2022 at 16:37
  • 3
    $\begingroup$ I second Mark's comment. For those of us relying on RSS feeds, every minor edit results in a "false positive" that sends us looking to see what changed (new answer, new comment, ...) when in fact there is no reason to be revisiting the post. $\endgroup$
    – prubin ♦
    Jan 4, 2022 at 16:51

2 Answers 2

Reset to default
7
$\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).

Improve this answer
$\endgroup$
6
$\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.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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