Loztrailia said:
I have a set of numbers, such as the ones below:
Date Hours worked
3 Dec -----18
10 Dec-----9
17 Dec ----24
24 Dec-----36
31 Dec-----7
7 Jan-------0
14 Jan-----19
My data goes back over the past 52 weeks and I need to
predict what the data is likely to be over the coming 52 weeks.
Predicting the future based on the past is tricky business. At the very
least, it is not reliable. That said, we all do try.
It would be helpful if you posted all 52 weeks of past data.
Based on the 7 weeks above, your data is essentially random, which makes it
more difficult to predict.
Absent any clear trends, I would assume that the next 52 weeks are similar
to the first 52 weeks, week by week. That would account for seasonal
variation, if any.
Beyond that, I would start by using an XY Scatter chart to plot your data.
Then you might see how the various Excel trendlines fit the data (or not).
Be sure to select the options to display the trendline formula and R-squared
(R2) statistic.
Many people mistaken by using the TREND or FORECAST functions. They assume
a linear trendline. If you select the linear trendline in the chart, it is
indeed declining. But the R2 is near zero. That indicates a poor fit,
which is we can confirm visually.
Ideally, we are looking for a trendline with an R2 close to 1 -- very close.
But resist the temptation to choose a polynomial trendline with a high
order. With only 7 data points, there is a polynomial of order 6 that fits
the data exactly (R2 = 1). And sometimes, that is appropriate for
__interpolating__ data points. But if you __extrapolate__ forward by 52
(another trendline option), you will see that the polynomial formula is very
poor at predicting the future (hopefully ;->).
On the other hand, the order-6 polynomial -- or simply connecting the data
points with a smooth curve -- does reveal what appears to be a sinusoidal
trend. Someone has posted a pointer to a webpage of his that explains how
to plot a sinusoidal trendline. I will look for it.
Alternatively, the historical 52-week data might show that the weekly change
in hours or the hours themselves follows a "normal" distribution. In that
case, a Monte Carlo simulation might be appropriate for estimating future
52-week behavior.
But you might dispense with the full simulation and simply set up the model
to generate one or a few sample predictions. For example, if your data are
in A2:A8, set up the following:
D2: =AVERAGE(A2:A8)
D3: =STDEV(A2:A8)
C2:C53: =ROUND(MIN(40,MAX(0,NORMINV(RAND(),$D$2,$D$3))),0)
For some fun, add a series to chart C2:C53. Then press F9 (recalculate)
repeatedly until the first 7 data points "closely follow" the trend of the 7
historical data points.
Obviously, this is an overview and highly speculative. There is no basis
for assuming that number of hours follows a "normal" distribution or any
particular distribution at this point.
As I noted above, we need to see all of this historical data in order to
give you proper guidance.
