Chapter 11 — Homework: Simple Linear Regression (Excel)

Use one of these two routes on the same dataset (Study Hours → Exam Score):

Prefer the one-click Excel summary? 👉 Use the ToolPak version | Want to see the functions behind it? 👉 Use the Functions version

Dataset — 23 students (X = Study hours, Y = Exam score)

#	Hours (X)	Score (Y)

Paste tip: Click cell A1 (not in edit mode), then Ctrl+V. If a column sticks together: Data → Text to Columns → Delimited → Tab/Comma.

Option A — Excel Data Analysis → Regression (ToolPak)

Excel: Data tab → Data Analysis (enable “Analysis ToolPak” if you don’t see it).
Choose Regression → OK.
Input Y Range: B1:B24 (include header), Input X Range: A1:A24 (include header); check Labels.
Output → New Worksheet Ply; optionally tick Residuals & Standardized Residuals.
Click OK. You’ll get: Regression Statistics, ANOVA, and Coefficients.

Reading the ANOVA box: F tests if slope = 0; small “Significance F” ⇒ the line explains Y. Glossary: SST=Total SS, SSR=Regression/Explained SS, SSE=Error SS, MS=SS/df, and with one X, F = t(b₁)².

Option B — Excel Functions (+ scatter & trendline)

Place X in A2:A24 and Y in B2:B24 (headers in A1, B1).
Compute:
=SLOPE(B2:B24, A2:A24) → b₁ | =INTERCEPT(B2:B24, A2:A24) → b₀ | =RSQ(B2:B24, A2:A24) → R² | =CORREL(A2:A24, B2:B24) → r | =STEYX(B2:B24, A2:A24) → typical error
Insert → Scatter; add Trendline with “Display Equation” and “Display R-squared”.
Add residuals column (if b₀ in E2, b₁ in E3): =B2 - ($E$2 + $E$3*A2); inspect residuals for curve/outliers.

What to submit (single PDF or DOCX)

Results: Either paste the ToolPak “Summary Output” tables or show the function outputs + scatter with trendline (equation + R²).
Forecast: Use x₀ = 10 hours. Report ŷ and a rough range ŷ ± 2·STEYX. Say whether 10 is inside your X-range.
Interpretation (4 bullets):
- Slope meaning (units): “+1 hour ⇒ about ___ points.”
- Intercept meaning (is X=0 in range? if not, say it’s algebra only).
- R² strength (weak / moderate / strong / very strong) and what it means.
- Typical error size (≈ STEYX) in points.
Model check: one sentence about residuals (random, any curve/outliers?).

Reminder: R² does not prove causation. Avoid extrapolating far outside the observed X-range.

Rubric (10 points)

Item	Pts
ToolPak tables pasted OR Functions + chart shown	2
Numbers consistent across method used (b₀, b₁, R², r, STEYX)	2
Interpretation bullets (slope, intercept, R², error size)	3
Forecast at x₀=10 with range (ŷ ± 2·STEYX) & in-range comment	2
Residuals note (curve/outliers?)	1

Instructor Key (auto-computed from this dataset) — collapse before posting

Line & Fit

ŷ = – + –·X
r = – | R² = –
STEYX ≈ – (points)

ANOVA / Significance

SSR = –, SSE = –, SST = –
df = (1, –, –)
MSR = –, MSE = –
F = –, p(F) = –
t(b₁) = –, p = – (and F ≈ t²)

Forecast @ X = 10 hours

ŷ = – | Rough range: – to –