College Students Weekly Earnings in Dollars

College Students Weekly Earnings in Dollars (n = 5) (a) Make an Excel worksheet to calculate SSxx, SSyy, and SSxy (the same worksheet you used in exercises 12.2 and 12.3). (b) Use the formulas to calculate the slope and intercept. (c) Use your estimated slope and intercept to make a worksheet to calculate SSE, SSR, and SST. (d) Use these sums to calculate the R2. (e) To check your answers, make an Excel scatter plot of X and Y, select the data points, right-click, select Add Trendline, select the Options tab, and choose Display equation on chart and Display R-squared value on chart.

The Correct Answer and Explanation is:

To analyze the relationship between two variables (such as hours worked and weekly earnings for college students), we can follow the outlined steps in Microsoft Excel. Let’s assume you have the following data for 5 students:

(a) Create Excel Worksheet for SSxx, SSyy, SSxy

1. In Excel, create columns for X, Y, X², Y², and XY.
2. Use formulas like =A2^2 for X², =B2^2 for Y², and =A2*B2 for XY.
3. At the bottom, use the =SUM() function to total each column.

Then compute:

• SSxx = Σ(X²) − (ΣX)² / n
• SSyy = Σ(Y²) − (ΣY)² / n
• SSxy = Σ(XY) − (ΣX)(ΣY) / n

(b) Calculate the Slope and Intercept

• Slope (b) = SSxy / SSxx
• Intercept (a) = Ȳ − bX̄
  Where Ȳ and X̄ are the means of Y and X respectively.

(c) Use Slope and Intercept to Compute SSE, SSR, and SST

Create a new column for predicted Y:

• Ŷ = a + bX

Then:

• SST = SSyy
• SSR = Σ(Ŷ − Ȳ)²
• SSE = Σ(Y − Ŷ)²

Use formulas in Excel for each squared deviation.

(d) Calculate R²

• R² = SSR / SST

This value explains the proportion of variation in Y explained by X.

(e) Create Scatter Plot and Trendline

1. Select X and Y data.
2. Insert a scatter plot.
3. Right-click on a data point and choose “Add Trendline.”
4. In the trendline options, check “Display Equation on Chart” and “Display R-squared value on chart.”

This graphical display will confirm the slope, intercept, and R² value calculated.

This entire process helps students build foundational knowledge in linear regression using Excel without requiring advanced statistical software.