Chapter 4, Problem 98CE

(a) Make an Excel scatter plot of X = weekly closing price of Dolana stock and Y = weekly closing price of ZalParm stock over 52 consecutive weeks. (b) Describe the scatter plot. Hint: You may need to rescale the X and Y axes to see more detail. (c) Calculate the sample correlation using the Excel function =CORREL. What does it tell you? (d) Select both data columns (including column headings) and make a line chart displaying both series. What does the line chart say about the relationship?

To determine

Construct a scatter plot for weekly closing price of Dolona and ZalParm using excel.

Answer to Problem 98CE

The scatter plot for the weekly closing price of Dolona and ZalParm is as follows,

Explanation of Solution

Calculation:

The data represents weekly closing price of Dolona and ZalParm over 52 consecutive weeks.

Scatter plot:

Software procedure:

Step-by-step software procedure to obtain the Scatter plot using EXCEL is as follows:

• Open an EXCEL file.
• In column A, enter the column of Dolona, in column B enter the column of ZalParm.
• Select the data that is to be displayed.
• Click on Insert > select Scatter icon.
• Click on the chart > select Layout from the Chart Tools.
• Select Chart Title > Above Chart.
• Enter Scatter plot for Dolona Vs. ZalParm   in the dialog box.
• Select Axis Title > Primary Horizontal Axis Title > Title Below Axis.
• Enter Dolona in the dialog box.
• Select Axis Title > Primary Vertical Axis Title > Rotated Title.
• Enter ZalParm in the dialog box.
• Thus, the scatter plot for the weekly closing price of Dolona and ZalParm has been obtained using Excel.

To determine

Explain the relationship between weekly closing price of Dolona and ZalParm and identify whether it is Weak or Strong or Negative or Positive or Linear or Nonlinear.

Answer to Problem 98CE

The weekly closing price of Dolona and ZalParm has a strong positive linear relationship.

Explanation of Solution

Weak relationship:

If the data points in the scatter plot are moderately scattered then there is a weak relationship between X and Y.

Strong relationship:

If all the data points in the scatter plot are moving in a particular direction then it is considered as strong relationship between X and Y.

Negative relationship:

If higher values of one variable in the scatterplot are associated with lower values of the other variable, then it is called a negative relationship.

Positive relationship:

If higher values of one variable in the scatterplot are associated with higher values of the other variable, then it is called a positive relationship.

Linear relationship:

If the data points in the scatter plot are in the form of a straight line then there is a linear relationship between X and Y.

Nonlinear relationship:

If the data points in the scatter plot are not in the form of a straight line then there is a non-linear relationship between X and Y.

A careful inspection of the scatterplot reveals that the data are clustered around a straight line, indicating that there exists a linear relationship between the two variables considered, weekly closing price of Dolona and ZalParm.

Thus, there is a strong positive linear relationship between the weekly closing price of Dolona and ZalParm.

To determine

Find the correlation coefficient using Excel.

Answer to Problem 98CE

The correlation coefficient for the weekly closing price of Dolona and ZalParm is 0.861.

Explanation of Solution

Calculation:

Correlation:

The correlation coefficient, r, between ordered pairs of variables, (x, y) having sample means $\overline{x}$ and $\overline{y}$, for a sample of size n is given as $r=\frac{{\displaystyle \sum _{i=1}^n\left(x_i-\overline{x}\right)\left(y_i-\overline{y}\right)}}{\sqrt{{\displaystyle \sum _{i=1}^n{\left(x_i-\overline{x}\right)}^2}}\sqrt{{\displaystyle \sum _{i=1}^n{\left(y_i-\overline{y}\right)}^2}}}$. The correlation coefficient is useful for measuring the strength of the linear relationship between the two variables.

Software procedure:

Step-by-step software procedure to obtain correlation coefficient using EXCEL software is as follows:

• Open an EXCEL file.
• Enter the data in the column I in cells I1 to I52 and column J in cells J1 to J52.
• In cell A1, enter the formula “=CORREL(I1:I52,J1:J52)”.
• Output using EXCEL software is given below:

Thus, the Correlation coefficient for the weekly closing price of Dolona and ZalParm is 0.861.

Interpretation of the correlation coefficient:

The correlation coefficient, r, between two linearly related variables is interpreted as follows:

• A positive value of r indicates a positive linear relationship.
• A negative value of r indicates a negative linear relationship.
• A value of r close to 0 indicates a weak linear relationship.
• A value of r close to 1 indicates a strong positive linear relationship.
• A value of r close to –1 indicates a strong negative linear relationship.
• The value $r=1$ indicates a perfect positive linear relationship.
• The value $r=-1$ indicates a perfect negative linear relationship.

A careful inspection of the correlation coefficient reveals that it is nearer to 1, indicating that there exists a linear relationship between the two variables considered, weekly closing price of Dolona and ZalParm.

Thus, there is a strong positive linear relationship between the weekly closing price of Dolona and ZalParm.

To determine

Construct a line Chart for weekly closing price of Dolona and ZalParm using excel.

Answer to Problem 98CE

The line Chart for the weekly closing price of Dolona and ZalParm is as follows,

Explanation of Solution

Calculation:

Line Chart:

Software procedure:

Step-by-step software procedure to obtain the Scatter plot using EXCEL is as follows:

• Open an EXCEL file.
• In column A, enter the column of Dolona, in column B enter the column of ZalParm.
• Select the data that is to be displayed.
• Click on the Insert tab > select Line icon.
• Select the option Stacked Line with markers
• Click on the chart > select Layout from the Chart Tools.
• Select Chart Title > Above Chart
• Thus, the Line Chart for the weekly closing price of Dolona and ZalParm has been obtained using Excel.
• A careful inspection of the line chart shows that stocks of Dolona and ZalParm moves in the same direction and together, though the stock price of ZalParm has more deviations than that of the Dolona.