From time series data create a scatter plot and fit a trend line (or ‘line of best fit’)
From the above plot of unemployment & inflation for the UK (from 1950 to 2003) these appears to be periods when unemployment & inflation move in the opposite direction (i.e. when unemployment is high, inflation is low). This is particularly apparent for the periods 1950 to 1966 and from the early 1980s to the early 1990s.
We might therefore want to create a scatter plot to have a closer look at any likely relationships. A scatter plot is simply one variable (X) plotted against the other variable (Y), in the above example we would want to plot unemployment (along the X axis) against inflation (plotted on the Y axis)[1].
To create a scatter plot you will basically follow the steps you took to create the time series plots (i.e. steps 8 to 26). However since we want unemployment to be plotted along the X axis we have to make sure that it is in the first column of the two columns containing the unemployment and inflation data (fortunately this is the case anyway). So, as in step 8 highlight only the unemployment and inflation data (you should not highlight the year column). Use the chart wizard to include a title for the chart and axis and then format the following:
· The alignment of the axis titles (steps 12 & 13)
· The plot area to have no border and no pattern (step15)
· The scale of each axis (steps 16 & 17)
· Remove any grid lines (see step 19)
If you have done all the above for country B the result should look something like that in figure 21
Figure 21
In the above screen save you will note that I have highlighted the chart (i.e. clicked on it) which the shows the two columns of data used in the scatter plot. You noticed also that the formula bar tells us which row and columns the data are to be found (have a look back at the formula bar in figures 11, 13 &14) . For country B there does seem to be a close inverse relationship between unemployment and inflation (when one is high the other is low).
To get an idea of the extent of this relationship we can fit a line of best fit using Excel. So click on your chart and then go to the tool bar a select ‘Chart’, in the drop down menu select ‘Add Trendline…’ the pop-up menu for ‘Add Trendline’ is shown in figure 22. The default option is Linear (shown as the shaded choice in figure 22 – which will suit our purposes here). To get the coefficients for this line of best fit go to the ‘Options’ tab an tick: ‘Display equation on chart’ and tick ‘Display R-squared value on chart’
Figure 22
If you have followed these steps you should end up with your work area looking like that shown in figure 23. In my example I have formatted the equation and R-squared simply to highlight them for you benefit.
Figure 23
For an example of how the equation and R-squared values can be interpreted see page 23 of the module handbook. Note that here the term -0.6575x is referring to the slope of the line and the term + 6.9178 is the intercept – i.e. where the line would strike the inflation axis (Y).
To do the same scatter plot for country A you will have to repeat all the above steps (unfortunately you can not use the country A data in a copy of the country B scatter plot). If you intend to use a scatter plot in your essay, then the procedure to copy and paste is the same as above (steps 27 to 29)
Notwithstanding, the above scatter plot (in figure 23) and the line of best fit is clearly telling us something about the relationship between unemployment and inflation in country B – which is what you will be expected to explain and illustrate in the April essay. To do this you will need to draw the models economics uses when tackling such a task, so the last section shows how you can go about creating your own diagram using the ‘Insert Object’ feature found in Microsoft Word.
1
handout6.doc, David Allen 19/01/07
[1] See page 14 of the module handbook for an example of a scatter plot using seminar attendance (X) against final mark (Y)