Using average and st.dev to do a prognosis

Originally Posted by MaxMW

I got an amount of sold units of a product for each week of this year. For example, week 1 - 3 units - week 2 - 5 units etc. [....] Now I would like to use these values to do a prognosis of the sales of the resting weeks of 2015.

Originally Posted by MaxMW

I have a thought of using the average + or - the standard deviation randomly. Does anyone know how to achieve this?

Forget about the std dev for now.

What you probably have in mind works well with a normal distribution. But first, you have no reason to expect that weekly sales are normally distributed. Second, I am sure you hope they are not.

The change in weekly sales might be normally distributed. But again, you have no reason to assume that yet.

So let's start over....

If you want us to help you, it would be best if you attached an example Excel file with representative data. But generally....

The first step is to generate an XY scatter chart (markers only) of weekly sales over time. Then add an appropriate trendline.

The big question is: what's "appropriate"? You will have to use your judgment. Usually, it is linear.

Caveat: Avoid the temptation to use a polynomial trendline. Even it is the best fit of existing data, it is probably a poor estimator of future data.

If a linear trendline is best, you can use the FORECAST function to estimate future numbers in the worksheet.

-----

If your weekly sales do not follow a simple trend, you might want to look at the distribution of the change in weekly sales. In your example, the change in week2 is about 66.67% = 5/3 - 1.

Ideally, generate a histogram of the week-to-week changes and generate a column chart of the histogram. If it looks "almost" normally distributed, you can use the average and std dev of the changes to simulate a time series of weekly sales. Generally, the formula to use is:

=NORMINV(RAND(),mean,sd)

But remember: that is the mean and std dev of the week-to-week change in sales, not of the raw sales data.

(Even if the histogram does not look "almost" normally distributed, you might assume that a large enough sample would be normally distributed. But off-hand, we have no reason to make that assumption.)

The problem with such simulations is: they can vary widely, especially when the sample size is relatively small. So the intelligent use of simulation is as much an art as it is a science. Much more needs to be said about that.

-----

Part of your original question might be: how to adapt the statistics as you add actual data over time?

That is difficult for us to answer without an example Excel file that reflects your worksheet design.

But generally, keep in mind that the AVERAGE and STDEV functions ignore empty cells. So you might simply use formulas like =AVERAGE(B2:B261) and =STDEV(B2:B261), which allow for up to 5 years of weekly data.

PS....

Originally Posted by MaxMW

I have a thought of using the average + or - the standard deviation randomly.

Originally Posted by joeu2004

Forget about the std dev for now.

There is one use of the mean and std dev of the raw weekly sales data (not the change in weekly sales). But it probably is not useful to you.

Even if the weekly data is not normally distributed (and we hope it is not), the mean of random samples of the data approaches a normal distribution as the sample size increases.

However, when the data itself is not normally distributed, we usually need relative large samples to approximate the mean.

That theory allows us to construct a confidence interval around the mean. For example, we can say that we are 95% confident that the mean is x +/- delta. For this example, "delta" would be:

=TINV(1-95%,COUNT(B2:B261)-1) * STDEV(B2:B261) / SQRT(COUNT(B2:B261))

where the actual weekly sales data is in B2:B261, some of which might be empty (space for future actual sales data).

Of course, the mean (x) is simply =AVERAGE(B2:B261).

Nitpick: We are treating the past actual data as a "random sample" of all data, including future data. Of course, the historical data might not be "random". This is especially true when the historical data is start-up data; that is, the first weeks of sales. Or when sales are "seasonal" (driven by external cyclical factors), and the historical data covers only one "season".

IMHO, this is not worth pursuing further. I simply mention it in the interest of "full disclosure".

Normal random numbers sounds like as good of an approach as any other. You can use a combination of the RAND() and NORMINV() functions to do this:
=NORMINV(RAND(),mean,stdev).

You might review this (http://www.mathwave.com/articles/ran...sheets-p2.html ) for some of the statistics behind these kind of random numbers.