Instruction for exercise 3

1. Question 1 deals with the issue of the relationship between two samples of data. For example, suppose you have a company's monthly data on market share and advertising expenditures and you want to determine if advertising expenditures affect (or are related to) market share in the current or the following months.
1. Scatter plot is a useful tool to display a possible relationship between two variables. To create a scatter plot, first select (highlight) the two columns of data which include the paired data of the two variables, then click the chart wizard, and choose chart type of XY (Scatter).
2. After you finish the scatter plot, you can add a best-fit line (trendline) on the chart. Click on the chart (to select it) and then from the pull-down menu, choose Chart - Add Trendline..., then from the Trendline dialogue box, choose Trend/Regression type: Linear to plot a linear relationship. A relationship between two variables is linear if as the value of the explanatory variable, x, increases, y change by a constant amount. We normally describe a linear relationship as y = a + bx.
3. Scatter diagrams are useful in identifying possible relationship between two variables. However, scatter diagrams and their accompanying best-fitting lines do not indicate quantitatively the strength of the linear relationship between two variables. Correlation coefficients provide a numerical index that measures the degree of the linear association between two variables.
4. To calculate correlation coefficient, when sheet (not the chart) is active (e.g., you have just clicked on the data sheet), from the pull-down menu, choose Tools - Data Analysis... - Correlation.
5. Question 2b
1. Note the data in Question 2 are time ordered. Therefore, not only can we examine the relationship between the pairs of number of two variables in the same month, it is also possible to examine whether sale of a particular month is really affected by the quota of the month before. It is reasonable to expect that the effect of quote often takes time to impact on the actual sale.
2. To determine if the sale is affected by the quote of the previous month, you might want to develop a new column of data for Quote (t-1) by copying the original quote data and paste it onto a new column (possibly just beside the column for "sale volume") and aim into the raw position which is one raw below the original position. In this way, the sale volume of a particular month is aligned with the quote one month before that.
3. Create a scatter plot and calculate correlation coefficient from the data in two columns for sale volume and quote(t-1).
2. Question 2 asks you to compare the mean of two samples of data
1. To perform a t-test, from the pull-down menu, choose Tools - Data Analysis... - T-test: paired two sample for means. This t-test is sometime also called within subject t-test.
2. To plot mean and standard error (SE), you have to calculate standard error (t-test only gives you mean values for each of the two groups). From the pull-down menu, choose Tools - Data Analysis... - Descriptive Statistics to perform this test on both samples of data.
3. To create a bar chart for mean and SE, you might want to create a new table to include data for means and SEs for both groups.
4. After you create the bar chart for mean, double click on the bar to open a dialogue box which includes the function for Y error bars. From "Y error bars" menu, choose "custom" to input the data address range for SE.