Question

A company is interested in forecasting sales in the final quarter of the year based on the first three quarters by fitting a linear regression model.

Sales	170	243	297
Quarter	1	2	3

• Firstly, what proportion of the variability in sales can be explained by the model?
• Secondly, predict the sales for the fourth quarter.

Answer 1

Using excel formula correlation between quarter and sales is r=CORREL(H8:H10,I8:I10)=0.996

So r^2=0.996^2=0.992

Hence 0.992 proportion of the variability in sales can be explained by the model.

Let us find our regression equation

Sum of X = 6
Sum of Y = 710
Mean X = 2
Mean Y = 236.6667
Sum of squares (SS_X) = 2
Sum of products (SP) = 127

Regression Equation = ŷ = bX + a

b = SP/SS_X = 127/2 = 63.5

a = M_Y - bM_X = 236.67 - (63.5*2) = 109.667

ŷ = 63.5X + 109.667

So for x=4, ŷ = (63.5*4) + 109.667=363.667