Statistics using Excel (Excel97)

Hello Statistical Gurus!
Please advise on how I should compare projected performance vs. actual performance. (Ex. A list of 20 stores with Projected annual sales compared to Actual annual sales for each store at the end of the year). Is Descriptive Statistics from the Analysis ToolPak the best way to compare? And then use the variance and standard deviation? I want to know if my forecasting methodology is valid AND if it has any statistical significance.
Thanks! Manny

Re: Statistics using Excel (Excel97)

Hi Manny,

There are many possibilities to deal with your problem. Excel offers some interesting function, like growth (based on an exponential model), trend and forecast (these last two are based on a linear models). Have a look at the on-line help for more details on these functions. You can also plot your data, use the right-click button when you locate the cursor on the data series in the chart, and add a trendline. Excel offers several models. There is also a forecast possibility here, so that you can visualize the forecast.
Extrapolation is a dangerous thing to do, especially for sales figures. We all know what happend the last month and how it influenced stock and sales data, so be careful if you want to forecast from existing data.
Have also a look at <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=33441&page=&view= &sb=&o=&vc=1>this thread</A> for some more information on forecasts.
If you want to compare actual versus projected data, then statistical techniques (e.g. t-test) will tell you if there is a statistical difference between both groups, but this might be not really an important difference. Moreover, be sure that the t-test is the right choice here, because underlying assumptions should be fulfilled to correctly apply this test. Other statistical tests are not available in Excel to compare groups of data. Refer to a real statistical package if you want to do this.

Re: Statistics using Excel (Excel97)

You need to be clear about what you want to know and why you want to know it.
The best way to start, I believe, is always to inspect the data. This means (in no particular order):
- plotting actual and predicted as two y-ranges (say line type)
- plotting actual along x-range and predicted along y range (XY type). Plot on same graph actual vs actual to give a 45degree line and look for the above the line (worse than prediction) and below-the-line (better than prediction).
- Can you group different stores together? eg by size, by location, by type? Plot these.
- Each time you find something out of the ordinary - are the data correct? (probably not, they never are)
- How did you make the prediction? Can you group together stores with the same predictive factors? Were their actual results close (in some sense)?

- What is the difference between actual and predicted? Make a table and plot, both for numerical difference and percentage difference?

- What practical difference does a percentage difference of (say) 10% between actual and predicted mean to you? to whoever you report to? to the company? to next years' program?

- Who are you going to report the results to?

- What will you do when you have decided that the predictions are correct/ incorrect?

-How would/ could you change your predictions for next year?

- When you have inspected the data, then often you will know where to go to see if any further statistical work is warranted.

If you really need a statistical analysis of the information, then you probably need a statistician, because statistical analyses are ONLY VALID IF THE ASSUMPTIONS UNDERLYING A TEST ARE MET BY THE DATA/ CIRCUMSTANCES. You might get a number from an Excel formula that appears to be real and significant, but if it was not the appropriate formula then it might be an unreal answer.

hope this helps. I would be happy to look briefly at your data and offer further questions/ suggestions.
Ruth