A Guide to Using Excel for Handling Data

Predicting the future using Excel

Excel offers you the function of predicting a trend from some existing data. Once you are happy with the reliability of the data you have sourced, you can find out what would be the outcome for a dependent variable in the event of the independent variable (usually time) moving in steps.

We shall use this Excel function to examine the behaviour of consumer expenditure in the UK on durable goods in a moment. But first, a word of warning!

Most of the methods of forecasting involve projecting an existing trend forward. They rely on the important assumption that the past trend will continue into the future. There are more sophisticated methods of forecasting that tend to be model based. These models take into account certain external factors, which our Excel Trend function cannot.

OK, with that out of the way, we can go ahead enthusiastically, but with caution over interpreting our results.

The accompanying Excel spreadsheet (right click on the link and choose 'save as' if you want to save your own copy of the spreadsheet) contains data on consumer durable spending in the UK between 1985 and 1993. What we are going to do is go through the steps that are needed to arrive at a prediction for durables spending for 1994 to 1999.

Firstly, here's a screenshot of the starting point for the example:

Next, we need to highlight the range which we will use to hold the predicted values. In this case these are B12:B17.

Then click on Insert from the menu bar, select Statistical and then Trend. This will produce a dialogue box as follows:

Now we need to enter the data values into the box, either by typing them or by clicking each box in turn and highlighting the range of cells in question. You may need to 'collapse' the dialogue box by clicking on the button in red to the right of the value.

The following screen should result:

For the purposes of this example leave the Const parameter blank.

To enter the function do not click on OK, but hold down the Ctrl and Alt keys while pressing the Enter key. The set of numbers that appears in the range B12:B17 are the predicted levels of spending on durable goods between 1994 and 1999.

The screen should appear as follows:

Now all that remains to be done is to tidy up the appearance of the table prior to building it into your Word document or presentation. A note explaining the trended data and a source for the data is the minimum you will need.

The finished table may result:

OK that's all there is to it!

For some more exercises and information on these Excel functions why not try the worksheet on fantastic plastic.

[Top]

The next part in this series.