Question

Objective of this Assignment:    To conduct a regression and correlation analysis

Instructions:

• The term paper involves conducting a regression and correlation analysis on any topic of your choosing. The paper must be based on yearly data for any economic or business variable, for a period of at least 20 years

(Note: the source of the data must be given. Data which is not properly documented as to source is unacceptable and paper will be graded F. Textbook examples are unacceptable).

• The paper can be between 3 to 5 pages (1500 to 2000 words).
• The term paper should distinguish between dependent and independent variables; determine the regression equation by the least squares method; plot the regression line on a scatter diagram; interpret the meaning of regression coefficients; use the regression equation to predict values of the dependent variable for selected values of the independent variable and construct forecast intervals and calculate the standard error of estimate, coefficients of determination (r2) and correlation (r) and interpret the meaning of the coefficients (r2) and (r). Your regression and correlation analysis must:

1. Graph the data (scatter diagram)

2. Use the method of least squares to derive a trend equation and trend values


3. Use check column to verify computations ∑ (Y-Yc)=0


4. Superimpose trend equation on scatter diagram.


5. Use your model to predict the movement of the variable for the next year.


6. Compare your predictions with the actual behavior of the variable during the 21styear .

Answer 1

Solution:

Suppose you have N pairs of data points (X_1, Y_1), (X_2, Y_2), ... (X_N, Y_N) and you would like to know the relationship of the Y's to the X's and, if possible, predict one from the other. The X's and Y's could be whatever you like, but let's say for now that they are heights and weights. You would expect there to be a clear relationship between the two; on average, you would expect taller people to be heavier than shorter people.

A good way to start might be to model the relationship with a linear equation, Y = mX + b, where m is the slope of the line and b is the Y-intercept, as you learned in high school algebra. The question now is to determine the best way to estimate m and be given your pairs of X's and Y's.

It turns out that the best way to do this is to use least squares regression. We call it least squares regression because the line that we choose will be the one for which the sum of the squares of the differences between predicted and observed values is as small as possible.

1. Scatter Plot of Y and X1

Scatter plot of sales and calls shows that there can be a linear trend between the both. The trendline indicates that it looks like higher the number of calls, higher will be sales

2. Best fit line

Using the Regression option in Excel Data analysis menu, we obtaain the following output

From this, bestfit line equation is Sales=Intercept+Coefficient of Calls *Calls

i.e., Sales = 22.52 + 0.1237 * Calls

3. Coefficient of Correlation

It denotes the strength of association between two variables. The sign denotes the direction of association.

In Exel, we calculate Correlation coefficient as Correl(X1array,Yarray)

We get the value as 0.318

This means that calls and sales are slightly positively associated. With increase in one quantiity, the other is also showing an increasing trend. Please note that this does not imply causation, i.e.,we CANNOT say that the rise or fall in one is causing the change in other.

4. Coefficient of Determination

It is more commonly known as R squared value. It gives the measure of how close the data points are to the best fit line. In other words, it gives the proportion of variability in dependent variable that can be explained by the independent variable. Higher the Rsquared value, better the model is.

From Excel regression output, we get R squared value or Coefficient of Determination as 0.101

~10% of variability in sales is explained by calls.

5. Utility of Regression model

F test can be used to test the utility of the model.

Null Hypothesis: Beta coefficient of call = 0; i.e., Calls is NOT linearly associated with sales

Alternate Hypothesis: Beta coefficient of call 0; Calls is linearly associated with sales

Let us choose significance level, = 0.05.

From the regression ANOVA output, we get p value (or significance value) of F test as 0.0012 (<0.05) for the given degrees of freedom (highlighted)

Since p value < , we can reject Null hypothesis, thereby concluding that with the given data it can be said that calls is linearly associated with sales.

6. Based on the above findings, it can be said that calls is a good and important variable in predicting sales volume. It has been proved that calls and sales have a positive linear association between them. From the best fit line (Sales = 22.52 + 0.1237 * Calls), we can say that with every call, sales increases by 0.1237units (interpretation of coefficient of calls).