We will use an example data set from *Regression Analysis by Example*
(4th ed.) by Chatterjee and Hadi (Wiley, New York, 2006).We will use the computer repair data.
In this study a random sample of service call records for a computer repair operation were examined and the
length of each call (in minutes) and the number of components repaired or
replaced were recorded. The data is given below.

Minutes Units 23 1 29 2 49 3 64 4 74 4 87 5 96 6 97 6 109 7 119 8 149 9 145 9 154 10 166 10

To draw a Scatter plot select **Insert** then **Chart wizard** and in **Standard Types** select **XY(Scatter)**
then click on ** first scatter plot ** and press ** Next**, then click the **Series** then click **Add**,after that in x-values select a unit data values
and in y-values select a Minutes data values. Click **next** to write chart title and lable x and y axis, then **Finish** We will get a Scatter plot.

In Excel we can directly find Correlation matrix. To find Correlation matrix select **Tools** then **Data analysis**, in that select **Correlation** and click on **ok**.
Next in **range** select two columns with labels, then click on the **Labels in First Row** finally click **Ok** We get Correlation Matrix as below:

Minutes Units Minutes 1 Units 0.993698746 1

Form the above matrix we can observe that the correlation coefficent of Minutes and Units is **0.993698746**, which is positive and close to 1.
This implies that there is strong positive correlation between the length of each call (in minutes) and the number of components repaired or replaced were recorded.

We can find directly Variance-Covariance matrix in Excel. To find Variance-Covariance matrix select **Tools** then **Data analysis**, in that select **Covariance **and click on **ok**.
Next in **range** select two columns with labels, then click on the **Labels in First Row** finally click **Ok**. We get Variance Covariance Matrix as below:

Minutes Units Minutes 1983.454082 Units 126.2857143 8.142857143

From the above matrix we can observe that the covariance between Minute and Units is **126.2857143**. We also get the variances of Minute and units as **1983.454082** and
**8.142857143** respectively.

In excel 2003 select **Tools** then **Data analysis**, in that select **Regression**and click on **ok**. Next in
**Input Y- Range** select variable Minute with label and in **Input X-range** select variable Unit with label,
then click on **Labels** and on the **Line Fit Plots**. Finally Click **Ok**. A brief regression output
should appear. In 2007 select **Data** then **Data analysis** then follow the same procedure as in 2003.

SUMMARY OUTPUTRegression Statistics Multiple R 0.993698746 R Square 0.987437198 Adjusted R Square 0.986390298 Standard Error 5.391724917 Observations 14ANOVAdf SS MS F Significance F Regression 1 27419.50877 27419.50877 943.2009225 8.91625E-13 Residual 12 348.8483709 29.07069758 Total 13 27768.35714 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Intercept 4.161654135 3.355100399 1.240396304 0.238534368 -3.148481533 11.4717898 Units 15.50877193 0.504981306 30.71157636 8.91625E-13 14.4085122 16.60903166RESIDUAL OUTPUTObservation Predicted Minutes Residuals 1 19.67042607 3.329573935 2 35.17919799 -6.179197995 3 50.68796992 -1.687969925 4 66.19674185 -2.196741855 5 66.19674185 7.803258145 6 81.70551378 5.294486216 7 97.21428571 -1.214285714 8 97.21428571 -0.214285714 9 112.7230576 -3.723057644 10 128.2318296 -9.231829574 11 143.7406015 5.259398496 12 143.7406015 1.259398496 13 159.2493734 -5.249373434 14 159.2493734 6.750626566

From the above output we get the regression equation is as follows

Minutes = 4.16 + 15.5 Units

The t-values (here "t stat") test the hypotheses that the corresponding population parameters are 0. If you wish to test a nonzero value, subtract it from the coefficient in the regression output window and divide by the coefficient's s.e. (Use a calculator for this.) Similarly, if you want confidence intervals, use the coefficient plus or minus the product of its s.e. with a t-value for the desired confidence level and 12 degrees of freedom. (Use a calculator for this.).

To plot the regression line on the scatterplot we need to format the output graph. First click on **fitted line points** then right click and select
a **chart type**. Next select **third chart type** (Scatter with data points connected by the smoothed line without Markers) and then click **Ok**.

We get Scatter plot with fitted regression line as follows:

To fit a regression line through the origin (i.e., intercept=0) redo the
regression but this time select option **Constant is Zero**.

Excel output looks like below:

SUMMARY OUTPUTRegression Statistics Multiple R 0.992887937 R Square 0.985826455 Adjusted R Square 0.908903378 Standard Error 5.50227961 Observations 14ANOVAdf SS MS F Significance F Regression 1 27374.78109 27374.78109 904.2017485 1.14526E-12 Residual 13 393.5760518 30.27508091 Total 14 27768.35714 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 0 #N/A #N/A #N/A #N/A #N/A #N/A #N/A Units 16.07443366 0.221334146 72.62518671 2.38032E-18 15.5962704 16.55259692 15.5962704 16.55259692RESIDUAL OUTPUTObservation Predicted Minutes Residuals 1 16.07443366 6.925566343 2 32.14886731 -3.148867314 3 48.22330097 0.776699029 4 64.29773463 -0.297734628 5 64.29773463 9.702265372 6 80.37216828 6.627831715 7 96.44660194 -0.446601942 8 96.44660194 0.553398058 9 112.5210356 -3.521035599 10 128.5954693 -9.595469256 11 144.6699029 4.330097087 12 144.6699029 0.330097087 13 160.7443366 -6.74433657 14 160.7443366 5.25566343

Form the above output we get the regression equation as follows:

Minutes = 16.1 Units

To find the predicted value of Y for given value of x (Units)= 4.
Plug Unit = 4 in the regression equation *Minutes = 4.16 + 15.5 Units*
We get, Minute = 4.16 + 15.5 * 4
This implies Minute = 66.16.