Simple Linear Regression

From association to a line you can use

Lesson 1 showed that correlation summarizes linear co-movement but does not give you a rule for prediction or budgeting. Simple linear regression draws the best-fitting straight line through an X–Y scatter and expresses it as an equation managers can plug numbers into: expected Y given X. That sounds modest. In practice it is how teams translate "each extra $1k of ad spend associates with roughly $X of revenue" into forecasts, hurdle rates, and scenario tables.

The failure mode is familiar. A regional retailer regressed monthly revenue on foot traffic, saw R² = 0.88, and extrapolated traffic from a new mall anchor tenant to justify a lease. Traffic rose; revenue did not, because the new anchor sold discount apparel that drew a different shopper than the retailer's premium SKUs. The line fit history within the old mall mix. Extrapolation (predicting outside the range of X values used to fit the model) broke the story. Regression is powerful when you respect its assumptions, report uncertainty, and treat coefficients as associations until causal design (Lesson 5) supports intervention claims.

From Unit 4: Inference and Uncertainty, recall confidence intervals and p-values (probability of seeing a result at least this extreme if the null hypothesis were true). Regression output wraps those ideas around slope and intercept estimates. From Lesson 1, r² in correlation becomes R² (coefficient of determination in regression, same explained-variance idea for the fitted line). This lesson teaches mechanics in spreadsheets, hand calculation for small n, and the managerial habit of reading output as a bundle: coefficient, standard error, R², residuals, and sample size.

The regression model and what OLS does

Simple linear regression models each observation as:

Yᵢ = β₀ + β₁Xᵢ + εᵢ

Ordinary Least Squares (OLS, the standard method that minimizes squared vertical distance from points to the line) chooses β₀ and β₁ to minimize the sum of squared residuals (prediction errors):

Σ(Yᵢ − Ŷᵢ)², where Ŷᵢ = β₀ + β₁Xᵢ

Why squares? Absolute errors are harder to optimize cleanly; squaring penalizes large misses heavily, which matches many forecast cost functions where big errors are disproportionately painful. OLS also has desirable statistical properties when assumptions are roughly met (linear relationship, independent errors, homoscedasticity (roughly constant error spread), errors centered on zero).

Managers rarely derive OLS proofs. They must interpret β₁ correctly: "one additional unit of X is associated with β₁ units of Y in this sample, holding nothing else constant" (because simple regression has only one X). Lesson 3 adds "holding other predictors constant."

Estimating the slope and intercept

For sample size n, with means X̄ and Ȳ:

β₁ = Σ[(Xᵢ − X̄)(Yᵢ − Ȳ)] / Σ(Xᵢ − X̄)²

β₀ = Ȳ − β₁X̄

Equivalent computational form when you have summary totals:

β₁ = [nΣXY − ΣXΣY] / [nΣX² − (ΣX)²]

β₀ = Ȳ − β₁X̄

The slope equals sample covariance of X and Y divided by sample variance of X. That links Lesson 1's r to regression: with standardized variables, slope relates to correlation; with raw units, slope is in business units (dollars per dollar, percentage points per month).

Intercept warning: β₀ is the predicted Y at X = 0. If your data never includes X near zero, the intercept is a mathematical anchor, not a scenario you should quote to the CEO (chief executive officer). Report slopes within the observed X range.

Spreadsheet regression: step-by-step

You can fit simple linear regression in Excel or Google Sheets without add-ins using built-in functions, or with the Data Analysis Toolpak (Excel add-in for statistical routines) for full output tables.

Method A: SLOPE, INTERCEPT, RSQ (fast)

1. Put predictor X in one column (e.g., column A), outcome Y in the next (column B), one row per observation.
2. Slope: =SLOPE(B2:B25, A2:A25) → β₁
3. Intercept: =INTERCEPT(B2:B25, A2:A25) → β₀
4. R²: =RSQ(B2:B25, A2:A25)
5. Predictions: in column C, =INTERCEPT(...)+SLOPE(...)*A2, copy down for each row.
6. Residuals: column D =B2-C2

Check: mean of residuals should be approximately 0 (rounding aside): =AVERAGE(D2:D25) ≈ 0 ✓

Method B: LINEST array (compact output)

=LINEST(Y_range, X_range, TRUE, TRUE) entered as an array returns slope, intercept, R², standard errors, and more in a 5×2 block. Read Excel/Sheets documentation for row order; label cells before presenting to executives.

Method C: Data Analysis Toolpak (Excel)

1. Data → Data Analysis → Regression
2. Input Y Range: outcome column
3. Input X Range: predictor column (include label if "Labels" checked)
4. Output to new worksheet

You receive coefficients, t-statistics, p-values, R², and residual columns. This mirrors what statistical software reports and prepares you for multiple regression (Lesson 3).

Correlation check: =CORREL(X,Y)^2 should match RSQ for simple regression with intercept ✓

Reading regression output like a manager

Typical software table:

95% confidence interval (CI, range plausibly containing the true slope*) for β₁:

β₁ ± t × SE(β₁)*

Use t* from t-distribution with n − 2 degrees of freedom (df, remaining independent pieces of information*) when n is modest; 1.96 approximates when n is large.

Unit 4's distinction applies: a statistically significant slope can be economically tiny. If each $1M ad spend associates with +$50k revenue, significance does not justify spend if margin and CAC (customer acquisition cost, spend to win one customer) fail.

R² and its limits (regression edition)

R² answers: "How much of the swing in Y does this line capture in the sample?" Limits managers must internalize:

In-sample vs out-of-sample: High R² on data used to fit the line does not guarantee forecast accuracy on future months (Lesson 4 covers train/test validation).

Omitted variables: Simple regression attributes all linear co-movement to one X. If seasonality drives both ad spend and revenue, R² may be high while the slope misstates incremental ad effect.

Range restriction: R² computed only on a flat spend band can collapse when you expand into new channels.

Spurious time trends: Two metrics that trend upward over years can show high R² even if unrelated; use time-aware validation and controls.

R² is not "model quality" for decision purposes. Pair it with RMSE (root mean squared error, √(average squared residual) in Y units), residual plots, and business judgment.

Residuals, RMSE, and diagnostic plots

Residual for observation i: eᵢ = Yᵢ − Ŷᵢ

Plot residuals vs X or vs Ŷ:

• Random cloud around zero → linear model plausible
• Curve pattern → consider transformation or nonlinear model
• Funnel shape → heteroscedasticity; predictions less reliable at some X levels
• Single huge residual → outlier to investigate (data error or real shock)

RMSE = √(Σeᵢ² / n) (some software uses n − k − 1 in denominator). If Y is revenue in $000s, RMSE is in $000s typical error.

Example read: "Revenue averages $110k; RMSE ≈ $4k" gives forecast precision context R² hides.

Log transformations and elasticity

When outcomes grow multiplicatively, log transforms linearize relationships:

Spreadsheet: =LN(Y) column, regress log(Y) on X or log(X). Report coefficients with transform language in footnotes so finance is not surprised.

OLS assumptions in plain manager language

OLS coefficients are best in a technical sense when four conditions hold reasonably well. You do not need proofs; you need red flags.

Linearity: The true relationship between X and Y is roughly straight over the operating range. Red flag: U-shaped scatter (price too high and too low both hurt).

Independence: Observations do not mechanically duplicate each other. Red flag: weekly rows from the same store treated as independent when store fixed effects matter (Lesson 3).

Homoscedasticity: Spread of residuals is similar across X levels. Red flag: funnel widening as X grows (heteroscedasticity).

Errors centered at zero: No systematic under/over prediction. Red flag: residuals mostly negative for high X.

Violations do not always invalidate OLS for exploration, but they weaken CI (confidence interval) coverage and forecast claims. Residual plots are the manager-friendly diagnostic.

Testing whether slope differs from zero

Software t-test for β₁ tests H₀: β₁ = 0 vs H₁: β₁ ≠ 0. t = β₁ / SE(β₁) compared to t-distribution with n − 2 df.

Unit 4 taught p-values: small p means a slope this far from zero is unlikely if true slope were zero. Combine with economic magnitude: β₁ = 0.0001 per dollar may be significant with n = 1,000,000 but useless for budgeting.

Report for executives: "We estimate +$5.9k revenue per +$1k spend (95% CI +$4.1k to +$7.7k); p < 0.01." That bundles magnitude, uncertainty, and significance without jargon overload.

R² limits: a manager checklist before trusting a regression

Before acting on R², walk this checklist:

1. Sample size: Is n large enough that one season does not dominate?
2. Range: Are we predicting inside the X range we fit?
3. Holdout: What is test MAE vs naive (Lesson 4)?
4. Omitted drivers: Are seasonality and competitor moves absent from the model?
5. Causation: Is this an association slide or an intervention claim (Lesson 5)?
6. Units: Does RMSE in Y units matter more than R² percentage language?

A model with R² = 0.94 and n = 6 fails items 1, 3, and often 4. A model with R² = 0.35 and n = 500 may still rank churn risk usefully if RMSE beats baseline and variables are actionable.

Extended spreadsheet regression walkthrough (24 months)

Setup: Column A month, B ad_spend_k, C revenue_k, rows 2–25 (n = 24).

Summary block (optional check): =AVERAGE(B2:B25), =AVERAGE(C2:C25), =COUNT(B2:B25).

Fit: =SLOPE(C2:C25,B2:B25) → β₁; =INTERCEPT(C2:C25,B2:B25) → β₀; =RSQ(C2:C25,B2:B25) → R².

Predictions column D: =$B$30 + $B$31*B2 if β₀ in B30, β₁ in B31.

Residuals column E: =C2-D2. =AVERAGE(E2:E25) should ≈ 0 ✓

Toolpak path: Regression output adds SE, t, p per coefficient. Copy into a "model card" tab used in governance reviews.

Train/test preview: Hide rows 20–25 from regression input; refit on rows 2–19 only; score test months with frozen β. Lesson 4 formalizes this; doing it early prevents R² overconfidence.

Stakeholder read on regression output

Regression is a shared language between finance and analytics only when units, range, and causal humility are explicit.

Second business pattern: funnel metrics and redundancy

Growth teams often present a funnel: impressions → clicks → signups → paid. Pairwise correlations among stages are frequently r > 0.7 because all respond to macro demand and spend. That is useful for monitoring (one metric may signal failure early) but dangerous for attribution (each stage is not an independent lever).

When building a regression with multiple funnel variables (Lesson 3), expect VIF warnings. Often one stage per conceptual block (top, mid, bottom) suffices. Correlation matrix review before regression prevents unstable coefficients and executive confusion when signs flip.

Worked micro-example: computing r for three points (sanity check)

Perfect line Y = 2X, so r = 1, r² = 1. Change Y at X=3 to 10 (outlier): r drops materially; one point dominates. Micro examples build intuition before 24-month spreadsheets. ### The managerial cost of skipping residual review

Residual review sounds technical. It is how operators catch models that "fit" but systematically under-forecast peak weeks. If residuals are mostly negative every December, the line misses holiday lift and inventory will understock. If residuals widen as X grows, finance should widen forecast bands even when R² looks fine.

Schedule residual review alongside monthly close for any regression used in S&OP (sales and operations planning, the cross-functional forecast and supply process). One plot saves more than another decimal of R².

Connecting simple regression to correlation numerically

When you center both variables, OLS slope equals r × (s_Y / s_X). That is why Lesson 1's r and Lesson 2's β₁ tell a coherent story: correlation is standardized slope. Reporting both on the same slide is redundant unless units matter for budgeting. Executives need β₁ in dollars; analysts need r to compare across pairs with different units.

Full numerical walkthrough: NovaHome residuals and RMSE

Using NovaHome coefficients β₀ ≈ 37.29, β₁ ≈ 5.857:

Σe² ≈ 5.14 → RMSE = √(5.14/6) ≈ 0.93 in $000s → $930 typical error scale (small in this tight sample).

Check: mean residual (−0.86+0.43−1.29+0.28−0.15+1.57)/6 ≈ 0 ✓

Interpretation: fit is visually tight, but n = 6 means RMSE is unstable; one month edit moves it sharply. This table belongs in internal model docs, not only headline R².

Building a one-page regression memo for approvers

Every regression used in budgeting should ship with: (1) business question; (2) n and date range; (3) equation with units; (4) R², RMSE, 95% CI on β₁; (5) residual plot thumbnail; (6) explicit "association not causation" line unless Lesson 5 evidence attached; (7) planned validation date (Lesson 4). One page forces discipline and reduces Slack threads asking "where did 8.14 come from?" when coefficients update.

NovaHome sells premium home goods online. Marketing wants a rule linking monthly X = paid search spend ($000s) to Y = attributed revenue ($000s).

Part A: Data (n = 6 months)

X̄ = 12.5, Ȳ = 110.5, n = 6

Check totals: ΣX = 75 ✓, ΣY = 663 ✓

Part B: Hand calculation

Σ(X − X̄)(Y − Ȳ) = 38.75 + 1.25 + 11.25 + 12.75 + 36.25 + 2.25 = 102.5

Σ(X − X̄)² = 6.25 + 0.25 + 2.25 + 2.25 + 6.25 + 0.25 = 17.5

β₁ = 102.5 / 17.5 ≈ 5.857 → each additional $1,000 spend associates with +$5,857 revenue

β₀ = 110.5 − 5.857 × 12.5 ≈ 37.29

Equation: Ŷ = 37.29 + 5.857X

Prediction at X = 14: Ŷ = 37.29 + 5.857(14) ≈ 119.29 ($119.29k)

March actual = 118; residual ≈ −1.29

Part C: Spreadsheet verification

=SLOPE(B2:B7, A2:A7) → 5.857 ✓
=INTERCEPT(B2:B7, A2:A7) → 37.29 ✓
=RSQ(B2:B7, A2:A7) → R² ≈ 0.94

Check: CORREL² ≈ 0.94 ✓

RMSE: residuals squared average → sqrt ≈ $2.8k in this tiny sample.

Part D: Managerial read

The slope is plausible for association but n = 6 is fragile. One promotional month could dominate. Do not extrapolate to X = 0 (intercept $37k is not "organic revenue"). Before budget scaling, extend to 24 months, add seasonality dummies (Lesson 3), and hold out last 6 months for forecast error (Lesson 4). CFO question: "If we add $3k spend, what incremental contribution margin do we need given this slope, not gross revenue alone?"

CloudLedger provides B2B billing software. Analysts regress churn_rate (fraction 0–1) on tenure_months for n = 500 accounts.

Predicting churn_rate from tenure_months

                    Coef    SE      t      p
Intercept           0.42   0.03   14.0   0.000
tenure_months      -0.008  0.001   -8.0   0.000

R² = 0.35    RMSE = 0.06    n = 500

Part A: Slope interpretation

Each additional month of tenure associates with −0.008 lower churn rate (0.8 percentage points), in sample, with only tenure in the model.

At tenure = 0, predicted churn 0.42 is extrapolation if no accounts have zero tenure in data. Quote slopes for observed tenure band (e.g., 3–36 months).

Part B: Uncertainty

95% CI for slope: −0.008 ± 1.96(0.001) ≈ −0.010 to −0.006 per month.

p < 0.001 rejects "slope = 0" under null; tenure matters statistically.

Part C: R² limit

R² = 0.35 → tenure explains 35% of churn variance; 65% remains elsewhere (tickets, price changes, industry). Statistically significant does not mean sufficient for a retention strategy built on tenure alone.

Part D: Managerial read

Use tenure as segmentation, not sole lever. Add support_tickets, MRR (monthly recurring revenue, subscription revenue per month), and industry controls in multiple regression (Lesson 3). Board: "What is incremental churn reduction if we only extend contracts without fixing P1 incidents?"

Eight stores, weekly transactions (Y) vs labor hours (X):

n = 8, X̄ = 487.5, Ȳ = 944.375

Using summary formulas or Toolpak: β₁ ≈ 1.35 transactions per labor hour, β₀ ≈ 286, R² ≈ 0.97.

Part A: Interpretation trap

Leadership reads: "Every hour adds 1.35 transactions; add hours until revenue maxes." R² looks decisive.

Part B: Residual and range check

Residuals small in sample; relationship nearly linear within 400–580 hours. Extrapolation to 700 hours unsupported.

Part C: Lesson 3 preview

Adding floor_sqft drops β_hours toward zero: hours proxy for store size. Simple regression conflates size with staffing.

Part D: Managerial read

Do not staff nationally using simple slope. Validate holdout weeks (Lesson 4) and add controls (Lesson 3) before ops policy. R² = 0.97 in-sample is not enough.

Check: n = 8 stated; slope direction plausible within range ✓

Given summary statistics from 10 stores:

ΣX = 50, ΣY = 240, ΣXY = 2,510, ΣX² = 550, n = 10

X = square footage (000s), Y = weekly transactions (100s).

Tasks:

1. Compute β₁, β₀, and the regression equation.
2. Predict Ŷ when X = 6.
3. If R² = 0.78 from software, interpret for the store planning team.
4. Explain one reason R² = 0.78 could still produce poor forecasts for a new mega-store at X = 25.

Solution

X̄ = 50/10 = 5, Ȳ = 240/10 = 24

β₁ = [2,510 − 10×5×24] / [550 − 10×25] = (2,510 − 1,200) / (550 − 250) = 1,310 / 300 ≈ 4.367

β₀ = 24 − 4.367×5 ≈ 2.165

Equation: Ŷ = 2.165 + 4.367X

Ŷ(6) = 2.165 + 4.367×6 ≈ 28.37 (2,837 transactions if Y is in 100s → 2,837 units if scaled)

Check: β₁ × (X̄) + β₀ ≈ 24 = Ȳ ✓

R² interpretation: About 78% of cross-store variance in weekly transactions aligns linearly with square footage in this 10-store sample. Footage matters, but one-third of variance still sits in location, staffing, and format.

Forecast failure at X = 25: Extrapolation far above observed X (max likely near 10–12 in sample). Mega-store dynamics (parking, departments) may not follow the same slope. Lesson 4: even in-sample R² does not protect out-of-range predictions.

Marketing regresses log(revenue) on ad_spend and reports β₁ = 0.015 (SE 0.004, p = 0.002, R² = 0.62, n = 36 months).

Tasks:

1. Interpret the slope in plain language for a non-technical CMO (chief marketing officer).
2. Compute approximate 95% CI for the slope.
3. Why is R² = 0.62 insufficient to approve a 40% spend increase?

Solution

1. Interpretation: Each additional $1,000 (or whatever unit X uses) of ad spend associates with roughly 1.5% higher revenue (because log-level coefficient × 100 ≈ percent change per unit X; confirm X units in footnote). Association in 36 historical months, not guaranteed incremental return on the next dollar.

2. CI: 0.015 ± 1.96(0.004) ≈ 0.007 to 0.023 per unit X. Even lower bound is positive, but width shows uncertainty.

3. Why R² is not enough: In-sample fit ignores holdout forecast error; omitted seasonality or competitor entries may inflate R²; margin and CAC may make revenue lift unprofitable; causal effect of +40% spend may differ from historical association (Lesson 5). Require train/test validation (Lesson 4) and contribution-margin math before approval.

Key takeaways

• Simple linear regression fits Y = β₀ + β₁X + ε by OLS, minimizing squared residuals.
• Use spreadsheet SLOPE, INTERCEPT, RSQ, or Toolpak Regression; verify mean residual ≈ 0.
• Report β₁ with SE, CI, p-value, R², and RMSE; interpret slope within observed X range.
• R² measures in-sample linear explanation, not causal leverage or guaranteed forecast accuracy.
• Residual plots and log transforms address nonlinearity and skew; extrapolation outside X range is dangerous on uncaptured segments.

1. Run simple regression on a real or public dataset in Sheets/Excel; paste coefficients and interpret slope and R² in two sentences a executive would understand.
2. Plot residuals for your model. Do you see curvature, funnel shape, or outliers?
3. Continue to Lesson 3: Multiple Regression.