4
This Page Intentionally Left Blank
39
Financial Planning using Excel
“One can’t believe impossible things,” said Alice. “I dare say you haven’t had much practice,” said the Queen. “When I was your age, I always did it for half-an-hour a day. Why, sometimes I’ve believed as many as six impossible things before breakfast.”
– Lewis Carroll, Through the Looking Glass, 1982.
Introduction
Many forecasting methods are based on the assumption that the variable being forecast is related to something else. Thus sales might vary according to the amount spent on advertising, or the life of a component might be determined by how long it has been held in the warehouse. Regression analysis is a widely used tool for analysing the relationship between such variables and using these variables for prediction purposes. For example, can variations in the number of marketing emails sent each month be used to help predict the income to the conference business?
To answer this question the first step is to establish whether there is indeed a relationship between the number of marketing emails sent and the income. This is achieved by recording the required information over a period of time and then plotting the data on a scatter diagram.
Figure 4.1 shows a set of data collected monthly for a period of one year. CWL Emails sent monthly vs income
B C D
Figure 4.1 Monthly income and number of emails sent
REGRESSION.XLS
Financial Planning using Excel
40
Before drawing a scatter diagram from the data it should be sorted by the number of emails sent. This is achieved by first selecting the range A2:C14 and then using the Data Sort command to sort on column B in ascending order (Figure 4.2).
Figure 4.2 Data sorted by number of emails sent 1 CWL Emails sent monthly vs income
B C D
Figure 4.3 Scatter diagram showing relationship between the number of emails sent and the monthly income
0 A scatter diagram is produced by selecting the range B3:C14, clicking on the Graph icon on the toolbar and selecting XY-scatter. From the sub-choices of XY-graphs take the option with no lines. Figure 4.3 is the resulting chart.
41
Financial Planning using Excel
By looking at the chart in Figure 4.3 it is clear that there is a strong lin-ear relationship between the two variables. Had this not been the case the points on the graph would be scattered more widely. Figure 4.4 is scatter diagram showing the number of aircraft sold at different rainfall levels and it is clear from this graph that there is no relation-ship between these two variables, as one would of course expect.
Aircraft sales in relation to rainfall
0
Figure 4.4 Scatter diagram showing no discernible relationship between the variables
1The word ‘regression’ originates from the nineteenth century when Galton collected the heights of fathers and their sons and put forward the idea that since very tall fathers tended to have slightly shorter sons and very short fathers tended to have slightly taller sons, there would be what he termed a ‘regression to the mean’
(D. Rees, 1991, Essential Statistics, Chapman and Hall).
Having established a linear relationship between the two variables it is useful to fit a least square line to the data, and this is referred to as simple linear regression1. Simple here implies that only one variable is being used to predict another as opposed to multiple linear regression which is discussed later in this chapter.
The algebraic formula for fitting a straight line is:
Y⫽ MX ⫹ c
where Y⫽ the dependent variable, M ⫽ the slope or gradient of the line (and is sometimes called the regression coefficient), X ⫽ the
Financial Planning using Excel
42
independent variable and c is the constant, or intercept, sometimes referred to as the Y intercept. The c is the value of Y when X is zero. It is the base value of Y before any increase or decrease in X is taken into account. The dependent variable (Y) is the variable to be predicted which in the above example would be the income, and the independent variable (X) is the variable used to do the predict-ing, which in this case refers to the number of emails sent.
In defining the least square line consider the annotated graph in Figure 4.5. For any given value of X there is a difference between Y1 and the value of Y determined by the line. The difference is denoted by D, which is referred to as a deviation, residual or error.
The least square line is defined as the line through a data set that has the property of minimising the sum of any given set of D2s.
Figure 4.5 Least square line 10000
Given that the least square line approximation has the form Y⫽ MX ⫹ c this formula can be broken down as follows:
and
c⫽(兺Y)(兺X)2⫺ (兺X)(兺XY) N兺X2⫺ (兺X)2 m⫽N兺XY ⫺ (兺X)(兺Y)
N兺X2⫺ (兺X)2
43
Financial Planning using Excel
where N⫽ the number of data points available, X ⫽ the sum of the X data points, Y⫽ the sum of the Y data points and XY ⫽ the sum of the product of each set of data.
Having established the equation representing the least square line, an estimate of a value for Y corresponding to a value for X may be obtained. When the least square line is being used in this way it has a regression line of Y on X, since Y is estimated from a value of X.