How to estimating purchase amount to achieve servicelevel

[einar.naess]

I'm writing some code to do stockitem analysis and provide estimates to purchaser. My source data is actual sold items per valid salesday grouped by week. Let's say week 1-10 have the following sales numbers per valid sales day: 10,11,15,13,7,6,19,8,12,13 (actual numbers are decimal). *) Out of this its easy to calculate average, variance and standard deviation. For now, let's say that stock level today is 100 items. My purchase will arrive in 3 salesdays from today. How many items do I have to purchase to be 98% sure that my stock will last at least 40 salesdays from now?

The solution is going to be written in SQL. I don't need the SQL code, but the algorithm for the calculation would do.

*)
Usually every week has 5 salesdays, but a few weeks every year has less. Due to transportation reasons special days every week peeks on sales. This would cause unneccessary variaton, so therefore i make this "average" per week.

Best regards
Einar Naess

 

[lalbatros]

The number of items sold over 40 days is a random variable (the S variable).
This random variable is the sum of 40 random variables (the v variables).
You have data allowing you to evaluate the average and variance for the v variables.
From this you can calculate the average and variance for the S variable,
as well as its standard deviation.

You can assume that S is a gaussian variable, see textbooks for explanations.
Using a statistical table you can decide to buy X item for the 40 days in order to avoid a shortage with a probability level of at least 98% (use cumulated probabilties for the normal distribution). If you have Excel you can read about the "Normdist" function or the "Norminv" function to get the number you need.

If Sav is the average of S and Sstdev is the standard deviation of S, then you need to buy at least X = Sav + 2 Sstdev item (approximatively). For the decimal, check the tables or excel.

I hope I understood your question exactly, but I hope that it gives you at least a good direction.

 

[einar.naess]

Thanks for your answer. Still have not solved my problem.

The percentage is a parameter, just like days. It can be any decent percentage, usually between 50% and just below 100%. I need to write the algorithm where percentage, days, average, variance and stddev are parameters (if they are all needed).

Tried help in Excel but found no good answer to what I'm looking for.

Best regards
Einar Naess