|Everything HP200LX: Knowledge, Products, Service|
Statistical "Fortune Telling" with Lotus 1-2-3
Ed gets out his statistical "crystal ball" and shows us how to use "moving averages" in a Lotus spreadsheet to spot trends and forecast.
By Ed KeefeTrying to predict the future is not entirely the domain of those who belong to the Psychic Network. Marketing Directors want to know sales trends. Account Executives at Merril-Lynch want to know the price trends of stocks and bonds. wholesalers and retailers would like to be able to see trends in competitors pricing. And, even though some of them may turn to psychics, most of them turn to the equally arcane knowledge of statistics.
Looking for Trends
Statistics uses full of different methods for predicting a probable future looking at past performance.
Suppose I've collected monthly sales figures for an entire year. The data shows that there are lots of ups and downs, perhaps due to seasonal fluctuations. I could take the average of all 12 sales figures, but that would only give me a single static number that tells me nothing about a sales trend, if there is one.
Of course, if I compared the average of this year's sales with that of the previous year, I would start to notice trends -- whether the average sales were up, or down, or remained the same from one year to the next.
However, it's not advisable in the real world to wait a whole year to look for trends. You might be out of business by then.
A Moving Average Shows Trends
One of the easiest ways to track trends is to use a moving average. Statistics uses moving averages to generate a smoother curve showing the long-term movements of a series. Moving averages are sometimes used to estimate trends.
In the above example, we could look at the average sales more frequently. We could take the average of months 1, 2, and 3 and plot that number. Then take the average of months 2, 3, and 4 and plot that number. Continue with the average of months 3, 4, and 5, and so on. The graph of such a chart over 12 months would look something like this:
Each point on the graph now represents sales over a period of time (three months). This tends to average out seasonal fluctuations in the graph. For example, on a normal graph of sales you might see December sales at 150 units and January sales at 75. All you know is that sales dropped, as might well be expected for the month after Christmas. This is a seasonal phenomenon which tells you nothing about long-term sales trends.
If, however, the first point represents the average of October through December and the next point the average of November through January, any difference detected between the two points on the graph will relate to sales over a longer periods of time. The changes reflected are more meaningful in terms of long term trends. Just eye-balling the graph should reveal if the business is heading in the right direction.
However, a three month moving average may still be too sensitive to seasonal fluctuations. A moving average made up of more data points will dampen the fluctuations even more.
Let me illustrate the moving average technique with the following Lotus 1-2-3 spreadsheet.
Lotus Spreadsheet for Moving Averages
Pictured on the next page is what the finished spreadsheet will look like.
SAMPLE MOVING AVERAGE SPREADSHEET
The spreadsheet is available on this month's Palmtop Paper on Disk (MOVAVE.WK1 in TLG13.ZIP (ON DISK ICON)), but if you want to see how it's put together, let me show you how to construct it from scratch.
If you're a new-comer to Lotus 1-2-3, or any spreadsheet, you'll be introduced to a couple of built-in functions and see how to use a "logical operator" (OR) and a "relational operator" (=).
To construct the spreadsheet, begin by keying in the column headers: that is, the information in columns A, B, C, and D, rows 1 and 2.
Next key in the names of the months in column A, starting at row 4 and continuing down to row 15. (See the sidebar for instructions on creating a system macro that will input months into a spreadsheet.)
Once you have the names of the months entered, go ahead and key in the seven sales figures for the months of January through July. These numbers go in cells B4 through B10. Don't enter any of the other numbers. Save that for later. Instead press (MENU) Worksheet Global Format Fixed 2 and press (ENTER). This will make all the cells in the worksheet appear as decimal numbers with two digits after the decimal point.
Now, move the cursor to cell C6 and key in the formula @AVG(B4..B6) . Press (ENTER) to see if you get the 411.00 figure. If so, all is well. If not, check your data entry.
Press (MENU) Copy (ENTER), key in a period (.) and use the down arrow key to highlight the range C6 through C15 and press (ENTER). The cells C11 through C15 will contain the word "ERR". ERR means that you're trying to find the average of nonexistent data. That's OK for now.
Good spreadsheet design implies that you get rid of such error messages. They could be disconcerting to a novice (or even to you, two weeks from now). Getting rid of the error messages involves a couple of steps.
We want to modify the @AVG() formula so it will put a blank in the cells that now contain ERR, but still put the average value in cells that currently show a number.
First, we'll create some range names. Giving a cell, or a range of cells, a name makes the cell appear more like a computer variable, that is "a location in the memory of a computer that can be named and can hold data."
To do this, move the cursor to cell A4 and press (MENU) Range Name Labels Right. Press (END) (<DownArrow>) to highlight the range A4 to A15. Then press (ENTER). This associates cells B4 through B15 the corresponding name of the month. From here on we can refer to the cells by using the month name (JAN, FEB, etc.) instead of B4, B5, and so on.
Return the cursor to cell C6 and press (F2) (EDIT). Edit the formula so that it looks like this.
Now with the cursor on C6, press (MENU) Copy (ENTER), key in C7..C15 and press (ENTER). The numbers appearing C6 -- C10 should remain the same as above. The ERRs in C11 C15 should disappear.
To create the moving averages for a six month interval, move the cursor to cell D9 and enter the following formula (on one line -- wrapped here because of formatting constraints):
Copy the formula to D10-D15 by pressing (MENU) Copy D9..D9, press (ENTER), key in D10..D15 and press (ENTER). Finally, go to cell B11 and key in the sales figure for Aug (560) and watch the moving averages (493.33 and 482.67) appear in the next two columns.
Plotting the Forecast
Just looking at the numbers in the spreadsheet may be enough for some people. However, I like to get a picture of what the data shows. We can use the graph function of 1-2-3 to plot the monthly sales, and the two moving averages, over an entire year.
To make this happen, press (MENU) Graph Type Line to tell the 1-2-3 program that you want a Line graph.
In Lotus, we can plot up to six different charts on the same graph. In our case, we'll only use three charts.
Press X to select the X-axis, on which we'll plot the months of the year. Move the cursor to cell A4 and press the period key (.) to anchor the range. Then press (<DownArrow>) to highlight the names of the months through Dec and press (ENTER).
Next, we'll enter the first set of values on the Y-axis. In Lotus, the first set of Y values on the Y-axis is referred to by the letter "A" in the menu. Press A, use the arrow keys to highlight B4, press (.) (period) to anchor the range, move to B15 and press (ENTER).
If you want, you can press the View to take a look at the graph at this point. You'll see the seasonal ups and downs for this particular business. Press any key to return to the spreadsheet and press B to enter the second set of Y-axis values. This time highlight the cells C4..C15 and press (ENTER). Finally, press C to enter the third set of Y-axis values, highlight the cells D4..D15 and press (ENTER). Press View to take a look at the final graph.
The resulting graph may appear different depending on whether you're using an HP 95LX or 100LX. However, it should be apparent how the technique of moving averages smoothes out the fluctuations and gives flatter lines from which to extrapolate what will happen in the future.
The technique of moving averages is used as a first approximation in business forecasting. It provides enough information to tell whether more sophisticated forecasting tools should be used to analyze the data.
Other Uses for Moving Averages
It is very common to use moving averages to track the prices of stocks, bonds or mutual funds. It can also be used in meteorology to track temperature, rainfall, and other weather-related variables. It can be used in manufacturing to help track productivity. It can even be used for more mundane purposes.
If you've ever used a calculator to try to figure out an estimated time of arrival during a long car trip, you'll recall how tricky it is to find an "average" speed.
If you're not traveling on interstate highways, your speed can fluctuate widely. At times you can zip along at the maximum speed. At other times you are slowed down by speed traps, getting caught behind tractors and combines, or you may have to come to a full stop for the kids in the back seat.
You could use the technique of moving averages to smooth out these fluctuations and give you a better estimate of your average speed. From that number, you should be able to compute a more realistic estimate of the time it will take to cover the remaining miles.
However, as they say in many text books, this problem is left as an exercise for the interested student. (Which usually means that the author found it to be a real challenge.) Until next time, Happy Porting.
Copyright © 2010 Thaddeus Computing Inc