0
$\begingroup$

I am making a calculator in Excel spreadsheet to calculate safety inventory.

The base formula I am using is:

Mathematical Formulation

Reference: http://www.apics.org/apics-for-individuals/apics-magazine-home/magazine-detail-page/2011/09/12/crack-the-code

Safety Stock = `z * Square Root(Lead Time Days / Time Period) * Standard Deviation of Sales Units Per Week`

Where:

SL = Desired Service Level    
Z = Z-Score (SL)
LT = Lead Time Days
T = Time Period
SD = Standard Deviation of Sales Units Per Week

Example 1:

SL = 95%
Z = 1.65
LT = 8 days
T = 7 days (a standard week)
SD = 10 units per week

1.64 * sqr(8/7) * 10 = 17.53 units

Using the above formula, my calculator returns 17.53 units (rounded up to 18) which is correct.

However, when I enter the Standard Deviation of Sales Units in Days instead of Weeks, my calculation does not work as expected. Using another example:

Example 2:

SL = 90%
Z = 1.28
LT = 4 days
T = 7 days (a standard week)
SD = 5 units per day

1.28 * sqr(4/7) * 5 = 4.83 units

The correct value of the above sample is 12.8 units (rounded up to 13) which I found by modifying my formula to not divide the Lead Time (LT) by 7. i.e.

1.28 * sqr(4) * 5 = 12.8 units

I then figured that if I convert both the variables to Weekly Values it would work but it does not, it just inflates my expected result. i.e.

Example 3:

SL = 90%
Z = 1.28
LT = 4 days
T = 7 days (a standard week)
SD = 35 units per week (5 units per day * 7 days)

1.28 * sqr(4/7) * 35 = 33.86 units

With this in mind, I want to be able to input Standard Deviation of Sales in either Days or Weeks, but I don't understand how to do it.

Is anyone able to provide a corrected method that will allow me to achieve what I need and explain (in laymans terms) where I am going wrong?

$\endgroup$
  • $\begingroup$ Not sure what is the source of your formula. Check this: katanamrp.com/blog/what-is-safety-stock-formula $\endgroup$ – NoChance Apr 1 '19 at 4:57
  • $\begingroup$ Not sure why my question was down voted by someone. Is there something wrong with it? $\endgroup$ – tamosa Apr 1 '19 at 12:00
  • 1
    $\begingroup$ I'm not the one who voted it down, but perhaps the down vote was because you provided a link to your formula instead of typing it. It would certainly be preferable to be able to see the question and the formula at the same time. $\endgroup$ – saulspatz Apr 1 '19 at 12:28
  • $\begingroup$ @saulspatz - I tried to embed the formulation into my post but since this was my first time posting here, the website would not allow me to do so until I receive higher reputation score. $\endgroup$ – tamosa Apr 1 '19 at 21:56
  • $\begingroup$ Just use MathJax to type it. $\endgroup$ – saulspatz Apr 1 '19 at 22:51
1
$\begingroup$

According to the page you linked, the "time period" in your formula is meant to be the same period over which you measure the standard deviation. So if your standard deviation is measured over one week, you should divide the lead time by one week inside the square root. If your standard deviation is measured over one day, you should divide the lead time by one day inside the square root.

With a standard deviation of $5$ units measured over one day, and a lead time of $4$ days, you should use $T=1,$ and the formula becomes

$$ 1.28 \cdot \sqrt{4/1} \cdot 5 \approx 12.8 $$

which is the result you wanted to get, because this is the correct application of the formula in this case.


Now for the reason why your attempt to "fix" the formula in Example 3 did not work:

The standard deviation of demand over a seven-day period is not simply seven times the standard deviation of a single day. Assuming the demand for each day is random and independent of the demand on other days, the square of the standard deviation of demand over seven days (also known as the variance of the demand) will be seven times the square of the standard deviation over one day.

That is,

$$ (\sigma_{\text{(7 days)}})^2 = 7 (\sigma_{\text{(1 day)}})^2.$$

Taking the square root of both sides (which we can do because standard deviation is always positive),

$$ \sigma_{\text{(7 days)}} = \sqrt{7} \cdot \sigma_{\text{(1 day)}}.$$

In other words, to scale up a standard deviation from a shorter period to a longer one, we multiply by the square root of the number of shorter periods that fit into the longer period. That's exactly what the square root $\sqrt{(PC)/T_1}$ in your formula is doing: it is "scaling up" the standard deviation $\sigma_0,$ which was measured over the time period $T_1,$ into a standard deviation measured over the time period that is $(PC)/T_1$ times as long.

In short, to convert the daily standard deviation to a weekly standard deviation you should have multiplied by $\sqrt7$. Instead, you multiplied by $7$, which is too large by a factor of $\sqrt7$ (because $7 = \sqrt7 \cdot \sqrt7$).

$\endgroup$
  • $\begingroup$ Thanks very much for your thorough explanation. I could not have expected anything better. Now I understand the issue and have adjusted my formulations according to your advice and it is working as expected. Thanks again. $\endgroup$ – tamosa Apr 1 '19 at 21:58

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.