NORMSINV Excel Formula function in Safety stock calculations
There was a question on Linked in about the use of the NORMSINV function in Excel. Initially the poster mentioned the NORMSDIST function.
But for safety stock purposes, you already know the required service level percentage. You need to derive the Service Level Multiple or the Z-factor.
So NORMSINV does the job.
So the Z-factor = NORMSINV ( Service level percentage).
For example, NORMSINV(98%) = 2.05 etc.
So the safety stock = NORMSINV (Service Level%) * RMSE * SQRT(Lead Time).
You need to calculate the Root Mean Squared Error from the Forecast at the item level and use the Lead Time based on the supply information.
Happy Forecasting!
Leave a Reply