Question

A recent article in BusinessWeek listed the “Best Small Companies.” We are interested in the relationship between the companies’ sales and earnings. A random sample of 12 companies wasselected and the sales and earnings, in millions of dollars, are reported below:

Company
Papa John’s InternationalApplied Innovation Integracare
Wall Data
Davidson & AssociatesChico’s FAS
Checkmate Electronics Royal Grip
M-Wave Serving-N-Slide
Daig
Cobra Golf

Sales ($ millions) $29.2

18.6 18.2 71.7 58.6 46.8 17.5 11.9 19.6 51.2 28.6 69.2

Earnings ($ millions) $4.9

4.4 1.3 8.0 6.6 4.1 2.6 1.7 3.5 8.2 6.0

12.8

I. Use the following directions for Microsoft Excel 2013 (or newer) to create a scatter diagram with the best-fit line, determine the equation of the line, and find the coefficient of determination for the data given above.

1. Open an Excel workbook.

2. Enter the x values (Sales) in the first column. Do not type a title for the data.

3. Enter the y values (Earnings) in the second column. Do not type a title for the data.

4. Place the cursor in cell B13 (just below the y values). Click on “Insert” in the main

   menu bar.

5. Click on the drop down menu next to the icon of the scatter diagram in Charts and

   choose the first type.

6. Click on the words “Chart Title”. In the formula bar, type an appropriate title for the

   data and hit Enter.

7. Click on the “+” sign located to the right of the scatter diagram and check the box for

   axis titles. The vertical “Axis Title” will be in a textbox. Click in the formula bar and type an appropriate title for the vertical axis and hit Enter. Click on the words “Axis Title” located below the horizontal axis of the scatter diagram. Click in the formula bar and type an appropriate title for the horizontal axis and hit Enter.

8. Click on the “+” sign again and check the box for Trendline. Click on the arrow located to the right of the word “Trendline” and click on “More Options”. Check the last two boxes for “Display equation on chart” and “Display r-squared value on chart”. Close the “Format Trendline” window.

9. Click and drag the equation and r-squared value on the chart to the top of the chart and place them to the right of the chart title (to make it easier to read the equation and r-squared value).

II. Find r, the correlation coefficient, using Excel for the data above:

1.

2. 3.

5. 6. 8.

On the same worksheet, place the cursor in a free cell and type “r =”. Move the cursorto the next cell to the right.
On the formula bar, click on fx (insert function).
In the “Or select a category” window, choose “Statistical”. 4. In the “Select a function” window, choose “correl”. Click OK.

With the cursor in the “Array 1” dialogue box, highlight the x-values (in column A).
Click in the “Array 2” dialogue box, highlight the y-values (in column B). 7.Click on “OK”.

In the cell highlighted, you now have the value of r, the correlation coefficient.

III. Based
below. Type the answer, using a complete sentence, in the cell below the cell containing the correlation coefficient.

on the equation found on your scatter diagram, use a calculator to answer the question 4. If a company has sales of $31.2 million dollars, what does the least-squares equation

forecast for the earnings?

Answer 1

From the steps given, we can insert the data in excel as follows and make a scatterplot of the given data points. The trendline equation is shown and the R-square value is also shown in the scatterplot.

29.2 49 Scatterplot 18.6 14 1.3 3 18.2 71.7 0.849634 y = 0.1276x + 0.6501 R3 0.7219 71- 17.5 2.6 11.9 1.7 19.63.5 10 51.28.2 11 28.6 12| 69.2| 12.8 0 10 20 30 4050 6070 80 Sales

The best fit line equation: y = 0.1276x + 0.6501

Coefficient of determination = R^2 = 0.7219

In a free cell, type the formula: =CORREL(A1:A12,B1:B12)

The value of coefficient of correlation between sales and earnings is 0.850. This shows that there is a strong relationship.

4. Let's forecast the earnings when Sales = 31.2

Equation: y = 0.1276x + 0.6501

y = 0.1276*31.2 + 0.6501

Earnings = y = $4.63 millions