I am making a calculator in Excel spreadsheet to calculate safety inventory.
The base formula I am using is:
Safety Stock = `z * Square Root(Lead Time Days / Time Period) * Standard Deviation of Sales Units Per Week`
SL = Desired Service Level Z = Z-Score (SL) LT = Lead Time Days T = Time Period SD = Standard Deviation of Sales Units Per Week
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:
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.
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?