ADD A TRENDLINE TO A CHART

You have collected a sample set of data that includes the years of service and salaries of 50 teachers from the Banton school system. You want to use this data to create a trendline that can be used for forecasting. You will create a scatter plot chart of the data and add a linear trendline with equation and R-squared. Refer to Figure 8.26 as you complete Step 1.

a. Open e08h2Assessment_LastFirst if you closed it at the end of Hands-On Exercise 2, and save it as e08h3Assessment_LastFirst, changing h2 to h3.

b. Make the Educator Assessment worksheet active and click cell G4. Click the Insert tab, click Insert Scatter (X,Y) or Bubble chart in the Charts group, and select Scatter. This creates a blank scatter plot. You will manually add X,Y data in the next step.

c. Click Select Data in the Data group on the Design tab. Click Add (on a Mac, click the plus sign) in the Legend Entries section of the Select Data Source dialog box. The Edit Series dialog box displays for manual input of X,Y values.

d. Click in the Series X values box and select the range C3:C52. Click the Series Y values box, delete the current value (={1}), and then select the range D3:D52. Click OK and click OK again to complete the chart.

e. Click Chart Elements, click the Trendline arrow, and select More Options. In the Format Trendline task pane, scroll down and click Display Equation on chart and Display R-squared value on chart. Accept all additional default options and close the Format Trendline pane.

By adding these options, you now display the R-squared value and linear equation directly on the chart.

f. Move the Linear equation and R-squared values to the upper-left corner of the chart Plot Area.

g. Add the chart title Salary Analysis and reposition the chart so the upper-left corner is in cell F6.

h. Save the workbook

USE THE INTERCEPT AND SLOPE FUNCTIONS

Excel automatically created the linear equation y = 2967.3x + 16304 however, you can calculate this equation manually using functions in Excel. You will use this method to verify the accuracy of the chart. Next, you will use the INTERCEPT and SLOPE functions to verify the results. Refer to Figure 8.27 as you complete Step 2.

a. Click cell G21 and type Intercept. Click cell G22 and type Slope.

b. Click cell H21, type =INTERCEPT( and click Insert Function on the Formula Bar. Click Known_ys, select range D3:D52, and press Tab so the insertion point is in the Known_xs box. Select range C3:C52 and click OK (on a Mac, click Done). The INTERCEPT function returns the value 16303.55681, which matches the intercept created as part of the linear equation in the prior step. This indicates the intercept was calculated correctly.

c. Click cell H22, type =SLOPE(D3:D52, C3:C52), and press Ctrl+Enter. The SLOPE function returns the value 2967.269728. This matches the slope created as part of the linear equation in the prior step. This indicates the slope was calculated correctly.

d. Save the workbook.

USE THE RSQ AND STEYX FUNCTIONS

Your next step is to test the accuracy of your trendline by using the RSQ and STEYX functions. Refer to Figure 8.28 as you complete Step 3.

a. Click cell G23 and type RSQ. Click cell G24 and type Standard Error.

b. Click cell H23, type =RSQ(D3:D52, C3:C52), and then press Enter. The RSQ function uses the same Known_ys and Known_xs as used to calculate the SLOPE and INTERCEPT. The RSQ function returns the value 0.892822. This indicates that the linear trendline accurately represents 89% of your data observations.

c. Click cell H24, type =STEYX(D3:D52, C3:C52), and then press Ctrl+Enter. The STEYX function returns the standard error 5557.874553. This means that if the trendline or rounded linear equation y = 2967.3x + 16304 is used to forecast a potential salary, it will be accurate plus or minus $5557.87.

d. Save the workbook.

USE THE FORECAST FUNCTION

For your final task, you will use the FORECAST.LINEAR function to predict the salary of a teacher who has worked for 30 years. You will then manually check the results using the linear equation y = mx + b in which m and b represent intercept and slope, respectively. Refer to Figure 8.29 as you complete Step 4.

a. Click cell G3 and type Predicted Salary, press Enter. Type High, press Enter, and type Low.

b. Click cell H3, type =FORECAST.LINEAR(H2,D3:D52,C3:C52), and then press Enter. Cell H2 contains a reference to the years of service you will use as the starting point for the forecast. Based on the historical data, you determine a teacher who has served for 30 years will earn a salary of $105,321.65. Remember, because this is a prediction, you must take into consideration the standard error of plus or minus $5557.87. You add this into your final prediction.

c. Click cell H4, type =H3+H24, and then press Enter.

d. Click cell H5, type =H3-H24, and then press Ctrl+Enter.

e. Select the range H3:H5 and apply Accounting Number Format.

f. Click cell J2 and type Check.

g. Click cell K2, type =H22*H2+H21, and then press Ctrl+Enter. The FORECAST.LINEAR function uses the equation y = mx + b. In this step, you verified the accuracy of the function by manually calculating the forecast in which m equals the intercept and b equals the slope. The manual calculation returns the value $105,321.65, which matches the calculation created by the FORECAST.LINEAR function, verifying your calculations are correct.

h. Ensure that cell K2 is still selected and apply Accounting Number Format.

i. Save and close the file. Based on your instructor’s directions, submit e08h3Assessment_LastFirst.

ADD A TRENDLINE TO A CHART

You have collected a sample set of data that includes the years of service and salaries of 50 teachers from the Banton school system. You want to use this data to create a trendline that can be used for forecasting. You will create a scatter plot chart of the data and add a linear trendline with equation and R-squared. Refer to Figure 8.26 as you complete Step 1.

a. Open e08h2Assessment_LastFirst if you closed it at the end of Hands-On Exercise 2, and save it as e08h3Assessment_LastFirst, changing h2 to h3.

b. Make the Educator Assessment worksheet active and click cell G4. Click the Insert tab, click Insert Scatter (X,Y) or Bubble chart in the Charts group, and select Scatter. This creates a blank scatter plot. You will manually add X,Y data in the next step.

c. Click Select Data in the Data group on the Design tab. Click Add (on a Mac, click the plus sign) in the Legend Entries section of the Select Data Source dialog box. The Edit Series dialog box displays for manual input of X,Y values.

d. Click in the Series X values box and select the range C3:C52. Click the Series Y values box, delete the current value (={1}), and then select the range D3:D52. Click OK and click OK again to complete the chart.

e. Click Chart Elements, click the Trendline arrow, and select More Options. In the Format Trendline task pane, scroll down and click Display Equation on chart and Display R-squared value on chart. Accept all additional default options and close the Format Trendline pane.

By adding these options, you now display the R-squared value and linear equation directly on the chart.

f. Move the Linear equation and R-squared values to the upper-left corner of the chart Plot Area.

g. Add the chart title Salary Analysis and reposition the chart so the upper-left corner is in cell F6.

h. Save the workbook

USE THE INTERCEPT AND SLOPE FUNCTIONS

Excel automatically created the linear equation y = 2967.3x + 16304 however, you can calculate this equation manually using functions in Excel. You will use this method to verify the accuracy of the chart. Next, you will use the INTERCEPT and SLOPE functions to verify the results. Refer to Figure 8.27 as you complete Step 2.

a. Click cell G21 and type Intercept. Click cell G22 and type Slope.

b. Click cell H21, type =INTERCEPT( and click Insert Function on the Formula Bar. Click Known_ys, select range D3:D52, and press Tab so the insertion point is in the Known_xs box. Select range C3:C52 and click OK (on a Mac, click Done). The INTERCEPT function returns the value 16303.55681, which matches the intercept created as part of the linear equation in the prior step. This indicates the intercept was calculated correctly.

c. Click cell H22, type =SLOPE(D3:D52, C3:C52), and press Ctrl+Enter. The SLOPE function returns the value 2967.269728. This matches the slope created as part of the linear equation in the prior step. This indicates the slope was calculated correctly.

d. Save the workbook.

USE THE RSQ AND STEYX FUNCTIONS

Your next step is to test the accuracy of your trendline by using the RSQ and STEYX functions. Refer to Figure 8.28 as you complete Step 3.

a. Click cell G23 and type RSQ. Click cell G24 and type Standard Error.

b. Click cell H23, type =RSQ(D3:D52, C3:C52), and then press Enter. The RSQ function uses the same Known_ys and Known_xs as used to calculate the SLOPE and INTERCEPT. The RSQ function returns the value 0.892822. This indicates that the linear trendline accurately represents 89% of your data observations.

c. Click cell H24, type =STEYX(D3:D52, C3:C52), and then press Ctrl+Enter. The STEYX function returns the standard error 5557.874553. This means that if the trendline or rounded linear equation y = 2967.3x + 16304 is used to forecast a potential salary, it will be accurate plus or minus $5557.87.

d. Save the workbook.

USE THE FORECAST FUNCTION

For your final task, you will use the FORECAST.LINEAR function to predict the salary of a teacher who has worked for 30 years. You will then manually check the results using the linear equation y = mx + b in which m and b represent intercept and slope, respectively. Refer to Figure 8.29 as you complete Step 4.

a. Click cell G3 and type Predicted Salary, press Enter. Type High, press Enter, and type Low.

b. Click cell H3, type =FORECAST.LINEAR(H2,D3:D52,C3:C52), and then press Enter. Cell H2 contains a reference to the years of service you will use as the starting point for the forecast. Based on the historical data, you determine a teacher who has served for 30 years will earn a salary of $105,321.65. Remember, because this is a prediction, you must take into consideration the standard error of plus or minus $5557.87. You add this into your final prediction.

c. Click cell H4, type =H3+H24, and then press Enter.

d. Click cell H5, type =H3-H24, and then press Ctrl+Enter.

e. Select the range H3:H5 and apply Accounting Number Format.

f. Click cell J2 and type Check.

g. Click cell K2, type =H22*H2+H21, and then press Ctrl+Enter. The FORECAST.LINEAR function uses the equation y = mx + b. In this step, you verified the accuracy of the function by manually calculating the forecast in which m equals the intercept and b equals the slope. The manual calculation returns the value $105,321.65, which matches the calculation created by the FORECAST.LINEAR function, verifying your calculations are correct.

h. Ensure that cell K2 is still selected and apply Accounting Number Format.

i. Save and close the file. Based on your instructor’s directions, submit e08h3Assessment_LastFirst.

ADD A TRENDLINE TO A CHART

You have collected a sample set of data that includes the years of service and salaries of 50 teachers from the Banton school system. You want to use this data to create a trendline that can be used for forecasting. You will create a scatter plot chart of the data and add a linear trendline with equation and R-squared. Refer to Figure 8.26 as you complete Step 1.

a. Open e08h2Assessment_LastFirst if you closed it at the end of Hands-On Exercise 2, and save it as e08h3Assessment_LastFirst, changing h2 to h3.

b. Make the Educator Assessment worksheet active and click cell G4. Click the Insert tab, click Insert Scatter (X,Y) or Bubble chart in the Charts group, and select Scatter. This creates a blank scatter plot. You will manually add X,Y data in the next step.

c. Click Select Data in the Data group on the Design tab. Click Add (on a Mac, click the plus sign) in the Legend Entries section of the Select Data Source dialog box. The Edit Series dialog box displays for manual input of X,Y values.

d. Click in the Series X values box and select the range C3:C52. Click the Series Y values box, delete the current value (={1}), and then select the range D3:D52. Click OK and click OK again to complete the chart.

e. Click Chart Elements, click the Trendline arrow, and select More Options. In the Format Trendline task pane, scroll down and click Display Equation on chart and Display R-squared value on chart. Accept all additional default options and close the Format Trendline pane.

By adding these options, you now display the R-squared value and linear equation directly on the chart.

f. Move the Linear equation and R-squared values to the upper-left corner of the chart Plot Area.

g. Add the chart title Salary Analysis and reposition the chart so the upper-left corner is in cell F6.

h. Save the workbook

USE THE INTERCEPT AND SLOPE FUNCTIONS

Excel automatically created the linear equation y = 2967.3x + 16304 however, you can calculate this equation manually using functions in Excel. You will use this method to verify the accuracy of the chart. Next, you will use the INTERCEPT and SLOPE functions to verify the results. Refer to Figure 8.27 as you complete Step 2.

a. Click cell G21 and type Intercept. Click cell G22 and type Slope.

b. Click cell H21, type =INTERCEPT( and click Insert Function on the Formula Bar. Click Known_ys, select range D3:D52, and press Tab so the insertion point is in the Known_xs box. Select range C3:C52 and click OK (on a Mac, click Done). The INTERCEPT function returns the value 16303.55681, which matches the intercept created as part of the linear equation in the prior step. This indicates the intercept was calculated correctly.

c. Click cell H22, type =SLOPE(D3:D52, C3:C52), and press Ctrl+Enter. The SLOPE function returns the value 2967.269728. This matches the slope created as part of the linear equation in the prior step. This indicates the slope was calculated correctly.

d. Save the workbook.

USE THE RSQ AND STEYX FUNCTIONS

Your next step is to test the accuracy of your trendline by using the RSQ and STEYX functions. Refer to Figure 8.28 as you complete Step 3.

a. Click cell G23 and type RSQ. Click cell G24 and type Standard Error.

b. Click cell H23, type =RSQ(D3:D52, C3:C52), and then press Enter. The RSQ function uses the same Known_ys and Known_xs as used to calculate the SLOPE and INTERCEPT. The RSQ function returns the value 0.892822. This indicates that the linear trendline accurately represents 89% of your data observations.

c. Click cell H24, type =STEYX(D3:D52, C3:C52), and then press Ctrl+Enter. The STEYX function returns the standard error 5557.874553. This means that if the trendline or rounded linear equation y = 2967.3x + 16304 is used to forecast a potential salary, it will be accurate plus or minus $5557.87.

d. Save the workbook.

USE THE FORECAST FUNCTION

For your final task, you will use the FORECAST.LINEAR function to predict the salary of a teacher who has worked for 30 years. You will then manually check the results using the linear equation y = mx + b in which m and b represent intercept and slope, respectively. Refer to Figure 8.29 as you complete Step 4.

a. Click cell G3 and type Predicted Salary, press Enter. Type High, press Enter, and type Low.

b. Click cell H3, type =FORECAST.LINEAR(H2,D3:D52,C3:C52), and then press Enter. Cell H2 contains a reference to the years of service you will use as the starting point for the forecast. Based on the historical data, you determine a teacher who has served for 30 years will earn a salary of $105,321.65. Remember, because this is a prediction, you must take into consideration the standard error of plus or minus $5557.87. You add this into your final prediction.

c. Click cell H4, type =H3+H24, and then press Enter.

d. Click cell H5, type =H3-H24, and then press Ctrl+Enter.

e. Select the range H3:H5 and apply Accounting Number Format.

f. Click cell J2 and type Check.

g. Click cell K2, type =H22*H2+H21, and then press Ctrl+Enter. The FORECAST.LINEAR function uses the equation y = mx + b. In this step, you verified the accuracy of the function by manually calculating the forecast in which m equals the intercept and b equals the slope. The manual calculation returns the value $105,321.65, which matches the calculation created by the FORECAST.LINEAR function, verifying your calculations are correct.

h. Ensure that cell K2 is still selected and apply Accounting Number Format.

i. Save and close the file. Based on your instructor’s directions, submit e08h3Assessment_LastFirst.

ADD A TRENDLINE TO A CHART

ADD A TRENDLINE TO A CHART

f. Move the Linear equation and R-squared values to the upper-left corner of the chart Plot Area.

f. Move the Linear equation and R-squared values to the upper-left corner of the chart Plot Area.

h. Save the workbook

h. Save the workbook

USE THE INTERCEPT AND SLOPE FUNCTIONS

USE THE INTERCEPT AND SLOPE FUNCTIONS

a. Click cell G21 and type Intercept. Click cell G22 and type Slope.

a. Click cell G21 and type Intercept. Click cell G22 and type Slope.

d. Save the workbook.

d. Save the workbook.

USE THE RSQ AND STEYX FUNCTIONS

USE THE RSQ AND STEYX FUNCTIONS

a. Click cell G23 and type RSQ. Click cell G24 and type Standard Error.

a. Click cell G23 and type RSQ. Click cell G24 and type Standard Error.

d. Save the workbook.

d. Save the workbook.

USE THE FORECAST FUNCTION

USE THE FORECAST FUNCTION

a. Click cell G3 and type Predicted Salary, press Enter. Type High, press Enter, and type Low.

a. Click cell G3 and type Predicted Salary, press Enter. Type High, press Enter, and type Low.

c. Click cell H4, type =H3+H24, and then press Enter.

c. Click cell H4, type =H3+H24, and then press Enter.

d. Click cell H5, type =H3-H24, and then press Ctrl+Enter.

d. Click cell H5, type =H3-H24, and then press Ctrl+Enter.

e. Select the range H3:H5 and apply Accounting Number Format.

e. Select the range H3:H5 and apply Accounting Number Format.

f. Click cell J2 and type Check.

f. Click cell J2 and type Check.

h. Ensure that cell K2 is still selected and apply Accounting Number Format.

h. Ensure that cell K2 is still selected and apply Accounting Number Format.

i. Save and close the file. Based on your instructor’s directions, submit e08h3Assessment_LastFirst.

i. Save and close the file. Based on your instructor’s directions, submit e08h3Assessment_LastFirst.