How to estimating purchase amount to achieve servicelevel


by einar.naess
Tags: achieve, estimating, purchase, servicelevel
einar.naess
einar.naess is offline
#1
Apr18-07, 12:24 PM
P: 2
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
Phys.Org News Partner Science news on Phys.org
Going nuts? Turkey looks to pistachios to heat new eco-city
Space-tested fluid flow concept advances infectious disease diagnoses
SpaceX launches supplies to space station (Update)
lalbatros
lalbatros is offline
#2
Apr18-07, 02:10 PM
P: 1,235
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
einar.naess is offline
#3
Apr18-07, 02:31 PM
P: 2
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 algoritm 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


Register to reply

Related Discussions
Where can i purchase a superconductor? General Physics 2
Capillary Tubes (purchase in Canada) General Discussion 6
Purchase uranium ore. Nuclear Engineering 7
Do you purchase cheapo or the best of the best General Discussion 15