Multiple Regression

One lever at a time is rarely enough

Simple regression (Lesson 2) forces every co-movement between X and Y through a single slope. Real operations violate that simplification constantly. Price drops coincide with holiday promotions. Tenure rises while contract size grows. Ad spend tracks brand campaigns. If you regress sales on price alone, you may conclude price cuts help sales when the true story is "we cut price during Christmas." Multiple regression adds several predictors simultaneously and estimates each coefficient holding the others constant (ceteris paribus, all else equal in the model).

The managerial payoff is partial attribution: "What happens to churn if tickets rise, given tenure and account size?" The risk is multicollinearity (predictors correlate strongly, making individual coefficients unstable) and omitted variable bias (leaving out a driver that belongs in the model distorts slopes). Lesson 1 warned that correlated KPIs are not levers; Lesson 3 shows how to disentangle them mathematically while reminding you that math does not remove unmeasured confounders (Lesson 5).

From Unit 4, remember that statistical significance differs from business significance. A churn model with n = 1,200 may flag support_tickets as significant while NPS fades after controls. That reframes budget: fix incidents before surveying harder.

The multiple regression model

Y = β₀ + β₁X₁ + β₂X₂ + … + βₖXₖ + ε

Each βⱼ is the expected change in Y for a one-unit increase in Xⱼ, holding all other X values in the equation fixed.

OLS still minimizes squared residuals, now in multiple dimensions. You should not hand-compute β vectors except in class examples; spreadsheets and software solve linear algebra internally.

Spreadsheet regression steps (multiple)

Excel Data Analysis Toolpak

1. Arrange data in columns: Y in first column (or dedicated), each X in its own column, one row per observation, no blank rows.
2. Data → Data Analysis → Regression
3. Input Y Range: outcome column including label
4. Input X Range: all predictor columns adjacent, including labels
5. Check Labels
6. Set confidence level (default 95%)
7. Select Residuals and Residual plots if diagnosing fit
8. Click OK

Read output blocks:

• Coefficients table: intercept and each βⱼ with SE, t, p
• R Square and Adjusted R Square
• F statistic for overall model significance
• Observations: n

Google Sheets: no native Toolpak equivalent in all environments; options include LINEST with multiple X columns (array formula), add-ons, or export to Excel/R/Python. Conceptually identical: supply Y vector and X matrix.

Prediction row: once coefficients exist, Ŷ = β₀ + β₁X₁ + … + βₖXₖ in a formula row for each customer or month.

Check: for in-sample predictions, R² = 1 − SS_res/SS_tot reported by Toolpak matches 1 − Σe²/Σ(Y−Ȳ)² ✓

"Holding constant" and partial effects

Consider SaaS churn. Simple regression of churn on tenure shows negative slope: longer tenure, lower churn. Add log(MRR) (log of monthly recurring revenue) and the tenure slope shrinks but may remain negative. Interpretation shifts: "Among accounts of similar revenue size, each extra month associates with X lower churn," not "tenure alone explains everything."

Partial effects discipline strategy. If support_tickets stays positive after tenure and revenue controls, tickets signal distress not fully captured elsewhere. That is actionable in product and service ops even if NPS is insignificant after tickets enter (NPS may mediate through service pain).

Multicollinearity and VIF

When Xⱼ correlates strongly with other predictors, OLS still finds a best fit, but SE(βⱼ) inflates: the model cannot separate which predictor moved the outcome.

Variance Inflation Factor for predictor j:

VIFⱼ = 1 / (1 − R²ⱼ)

where R²ⱼ comes from regressing Xⱼ on all other Xs.

Fixes: drop redundant metric, combine into index, collect more data with independent variation, or use regularization (ridge/lasso, beyond this course). Do not treat VIF as religion; business context matters. Two collinear marketing variables may still help prediction even if causal interpretation of each is hopeless.

Sign flip example: Regress churn on ad_spend only → negative slope (more ads, less churn). Add brand_awareness_index → ad slope turns positive, VIF_ad > 10. Ads correlate with awareness; simple model confounded. Do not ship policy from Model A.

Adjusted R² and comparing models

R² always rises (or stays flat) when you add predictors, even noise columns. Adjusted R²:

R²_adj = 1 − [(1 − R²)(n − 1) / (n − k − 1)]

Penalizes extra k. Prefer R²_adj when comparing models with different predictor counts on the same Y and n.

R² limits (multiple regression): High R²_adj still does not imply causal levers, good forecasts on new data, or correct functional form. Pair with train/test validation (Lesson 4), residual checks, and design for intervention claims.

F-test for overall model tests H₀: β₁ = β₂ = … = βₖ = 0. Rejection means joint explanatory power; individual t-tests still required per variable.

Dummy variables for categories

Categorical fields (region, industry, plan tier) enter as dummy variables (0/1 indicators). Choose a reference category (baseline); omit one dummy to avoid dummy trap (perfect collinearity with intercept).

If baseline is Tech and industry_retail = 1 for retail:

β_retail = mean churn difference retail vs tech, holding continuous X constant.

Interpretation must name the baseline: "Retail is +3 pp churn vs tech, ceteris paribus."

Standardized coefficients (beta weights)

Compare relative strength when units differ (tenure months vs ticket counts):

β*ⱼ = βⱼ × (s_Xⱼ / s_Y)

β* are unitless; larger magnitude suggests stronger association on a common scale, but collinearity still distorts ranking. Do not confuse with causal importance.

Model-building workflow managers should enforce

Analytics teams ship fewer bad models when a written workflow precedes Toolpak clicks.

Step 1: Write the decision. Example: "Rank accounts for save offers" vs "Estimate causal effect of price." Prediction tolerates correlated X; causation demands design (Lesson 5).

Step 2: List candidate predictors from theory, not from correlation mining alone. Each X should have a mechanism story.

Step 3: Inspect pairwise correlations among X (Lesson 1 matrix). Flag |r| > 0.8 pairs.

Step 4: Fit nested models (simple → full). Watch coefficient stability and R²_adj lift.

Step 5: Check VIF table. Investigate signs that flip between nested specs.

Step 6: Residual diagnostics on training data (patterns by segment, time).

Step 7: Validate on holdout (Lesson 4). Report test MAE, not only R²_adj.

**Step 8: Document who acts on ŷ or β and what happens if wrong.

This workflow keeps multiple regression from becoming a kitchen-sink contest.

Interpreting the F-test and overall model significance

F-statistic tests whether all slopes are zero jointly. Rejecting H₀ means the bundle of X carries signal. It does not mean every individual X matters.

CloudLedger example: F = 82, p < 0.001 with k = 5 predictors confirms the model beats an intercept-only benchmark in sample. Yet nps_score is individually insignificant. Joint significance plus individual failure is normal when predictors overlap.

Managers should ask: "Which variables survive both statistical and operational scrutiny?" Joint F is a gate, not a budget allocator.

R² and adjusted R² limits in multivariate settings

Even R²_adj = 0.40 (CloudLedger) can be valuable for routing save offers if test error beats baseline and key drivers are actionable (support_tickets). Conversely, R²_adj = 0.85 on training data with test collapse (Lesson 4) is dangerous.

R² never answers:

• Whether unmeasured confounders drive β
• Whether policy changing X moves Y
• Whether relationships persist after competitor response

Pair R²_adj with VIF, holdout error, and causal design when stakes are high.

Spreadsheet detail: dummy variables and prediction row

Suppose plan_tier has values Basic, Pro, Enterprise. Create dummy_pro (1 if Pro, else 0) and dummy_ent (1 if Enterprise, else 0). Basic is reference (both dummies 0).

Toolpak X range includes dummy_pro, dummy_ent plus continuous variables. Coefficients β_pro, β_ent are differences vs Basic, holding other X fixed.

Prediction example: Basic customer: add 0 for dummies. Pro customer: add β_pro. Check: predicted ŷ matches manual sum ✓

LINEST note: Google Sheets LINEST accepts multiple X columns as a block; read documentation for intercept handling. Excel Toolpak remains the most readable output for MBA workflows.

When to drop, combine, or segment instead of adding columns

Judgment beats mechanical rules. Document tradeoffs in the model card.

Prediction vs explanation in multivariate models

The same fitted model can rank customers by ŷ (prediction goal) while individual βⱼ are unstable (explanation goal) under collinearity. CloudLedger may safely route saves using ŷ while debating whether β_nps is truly zero. Be explicit about goal: ranking tolerates correlated X more than causal coefficient stories.

Spreadsheet quality checks after Toolpak Regression

After every run, verify:

1. Observations count matches filtered dataset (n).
2. Predicted = actual on average for in-sample (residual mean ≈ 0).
3. One manual ŷ row matches Toolpak prediction row.
4. Signs match business priors or trigger investigation (negative price coefficient).
5. Save coefficient table with date, author, filter SQL description.

These checks catch transposed X columns, a surprisingly common executive-deck error.

Reading CloudLedger output as a cross-functional team

Finance cares whether log_mrr coefficient implies save offers for small accounts are uneconomic. Support cares that support_tickets stays significant after controls. Product cares that nps_score dropped out. A 30-minute read with the coefficient table projected prevents each function from cherry-picking one row.

When p values are near 0.05, discuss power (Unit 4): maybe tickets matter but sample lacks precision. When p is tiny but coefficient is tiny, discuss business significance: 0.0003 per NPS point may not fund a program.

Document reference industry for dummy interpretation so retail vs tech comparisons are not misread in customer emails.

Nested model comparison table (Meridian-style workflow)

Meridian-style teams log this table in Confluence or SharePoint. R²_adj gain from Model 2→3 justifies promo control; β_price stabilization justifies using Model 3 for scenario pricing. If Model 4 adds weather_index with R²_adj gain only 0.005, reject complexity unless weather is controllable.

VIF scan at Model 3: if all < 5, proceed to Lesson 4 validation on last 13 weeks holdout before national price sync.

Omitted variable bias in managerial terms

When an important Z is left out, β on included X absorbs part of Z's effect. Price coefficients pick up promo timing; tenure coefficients pick up account quality. Multiple regression reduces but does not eliminate bias for unmeasured Z. The fix is better data or experiment, not another dozen correlated KPIs.

Multiple regression in the analytics request template

Analysts receiving "run a regression on churn" should return: hypothesis paragraph, variable dictionary, missing-data rules, Toolpak screenshot or coefficient table, VIF table, R²_adj, one predicted row worked by hand, and recommended next step (validate Lesson 4 or experiment Lesson 5). Managers reject one-line "churn is driven by NPS" emails without partial effects table.

For categorical variables with dozens of levels (SKU, zip code), collapse or regularize before dumping dummies; otherwise k explodes and R²_adj penalizes without insight.

Standardized coefficients example (CloudLedger-style)

Suppose s_tenure = 8 months, s_churn = 0.05 (5 pp), β_tenure = −0.006. β ≈ −0.006 × (8/0.05) = −0.96*. Compare to β*_tickets if β_tickets = 0.004, s_tickets = 3, same s_y: β ≈ 0.004 × (3/0.05) = 0.24*. Tenure ranks stronger on standardized scale in this illustration; tickets still significant and actionable. Standardized weights guide ranking conversations, not budget alone.

Always note: β* unstable under high VIF; compute only after VIF scan.

Hand-checking one CloudLedger prediction (spreadsheet discipline)

Using Part B coefficients: tenure 6, log_mrr = 3.0, tickets 5, NPS = 40, retail 1.

Row formula: **=0.18 + (-0.006)6 + (-0.012)3 + 0.0045 + (-0.0003)40 + 0.03

Stepwise: 0.18 − 0.036 = 0.144; −0.036 → 0.108; +0.02 → 0.128; −0.012 → 0.116; +0.03 → 0.146.

Compare to Toolpak ŷ column for same customer ID. Mismatch flags transposed column or wrong dummy coding. This 60-second check prevents six-figure campaigns targeted with inverted signs.

Partial vs simple regression: when signs disagree

If simple β_tickets on churn is negative (more tickets, less churn) because high-touch success teams serve enterprise accounts that rarely churn, multiple regression with log_mrr and industry may flip tickets positive. The multiple sign matches operator intuition (pain → churn). Always show both models when recommending budget shifts so stakeholders see confounding resolved.

When presenting to the board, show a two-row table: simple β vs partial β with one-sentence confound label. Decisions improve when leaders see what changed, not only the final number.

R²_adj rewards parsimony: prefer the smallest predictor set that still beats naive on Lesson 4 holdout. Extra k that only inflates training fit is noise for operators.

CloudLedger-style churn models often feed CRM (customer relationship management, the system tracking accounts and outreach) workflows: partial coefficients rank levers, ŷ ranks accounts. Separate meetings for "who to call" (prediction) and "what to change" (causation). Mixing them creates policy errors when insignificant NPS and significant tickets coexist in the same table.

Meridian and NorthPeak examples together teach: always show nested simple vs multiple coefficients when stakes exceed a staff meeting. The extra table row prevents million-dollar misreads.

Toolpak Regression output is the contract between analytics and finance: archive each run with filters documented so coefficients can be replayed months later during audit or dispute.

If VIF and R²_adj agree the model is stable but Lesson 4 holdout fails, trust Lesson 4: prediction deployment stops even when coefficients look elegant.

Partial effects are the right language for "holding other KPIs fixed"; they are the wrong language for "we proved causation" without Lesson 5.

CloudLedger predicts monthly churn_rate (0–1) for n = 1,200 SaaS customers.

Variable              Coef      SE       t       p      VIF
Intercept             0.18    0.02     9.0    0.000
tenure_months        -0.006   0.001    -6.0    0.000    1.4
log_mrr              -0.012   0.004    -3.0    0.003    2.1
support_tickets_30d   0.004   0.001     4.0    0.000    1.8
nps_score            -0.0003  0.0004   -0.75   0.45     2.3
industry_retail       0.03    0.01     3.0    0.003    1.1

R² = 0.41   R²_adj = 0.40   F = 82, p < 0.001   n = 1,200

Part A: Line-by-line interpretation

1. tenure_months (−0.006): +1 month → −0.6 pp churn, holding MRR, tickets, NPS, industry fixed. Significant.
2. log_mrr (−0.012): Larger accounts (log scale) churn less; ~0.012 pp per 1-unit log MRR step (approximate log interpretation).
3. support_tickets (+0.004): Each extra ticket in 30 days → +0.4 pp churn, ceteris paribus. Operator lever: reduce P1/P2 incidents.
4. nps_score (p = 0.45): Not significant after controls. NPS may correlate with tickets; do not fund NPS campaigns as churn fix without testing.
5. industry_retail (+0.03): Retail +3 pp vs reference (tech), holding other X.
6. VIFs < 5: Multicollinearity manageable.

Check: R²_adj slightly below R² with k = 5 ✓

Part B: Prediction walkthrough

Customer: tenure 6, log_mrr = 3.0, tickets 5, NPS = 40, retail dummy 1.

ŷ = 0.18 − 0.006(6) − 0.012(3.0) + 0.004(5) − 0.0003(40) + 0.03

= 0.18 − 0.036 − 0.036 + 0.020 − 0.012 + 0.030 = 0.146

Predicted churn 14.6%. Compare to portfolio average before action thresholds.

Check arithmetic: 0.18 − 0.036 = 0.144; −0.036 → 0.108; +0.020 → 0.128; −0.012 → 0.116; +0.030 → 0.146 ✓

Part C: Spreadsheet replication (described)

Columns: churn_rate, tenure_months, log_mrr, support_tickets_30d, nps_score, industry_retail. Toolpak Regression with Y = churn, X = five columns. Paste coefficients; verify ŷ row formula matches hand calculation for one customer.

Part D: Managerial read

Prioritize incident reduction and retail playbooks over NPS billboards. Use scores for routing, not as proof of causality. Board question: "If we cut tickets 20% via engineering, what churn delta does the model imply, and what experiment confirms it?" (Lesson 5)

Meridian Apparel regresses weekly sales ($000s) on own_price ($), competitor_price ($), and promo_flag (1 if circular featured, 0 otherwise) for 52 weeks.

Part A: Simple vs multiple contrast

Simple: sales on own_price only → β_price = −8.2 (each $1 price cut associates with +$8.2k sales).

Multiple: add competitor_price and promo_flag → β_price = −3.1 (SE 1.0, p = 0.003), β_promo = +12.0 (SE 2.5, p < 0.001), R²_adj = 0.68.

Part B: Interpretation

Price effect shrinks when promotions and competitor moves are held constant. Simple β absorbed holiday promos that lowered effective price and raised sales. Partial β_price is the better input for "what if we change list price holding promos fixed?" still not causal without experiment.

VIF for own_price = 3.2 (acceptable).

Part C: Spreadsheet steps

1. Columns: week, sales, own_price, competitor_price, promo_flag.
2. Toolpak Regression: Y = sales, X = three predictors.
3. Copy R²_adj, coefficients; compute VIF if not printed by regressing each X on others.

Check: n = 52 weeks complete ✓

Part D: Managerial read

Meridian should not use simple price slope for permanent markdown strategy. Use multiple model for scenario tables and run regional price test (Lesson 5) before national roll. Merchandising owns promo_flag accuracy; bad promo coding destroys β_price.

NorthPeak consumer app tests whether paid ads reduce churn.

Model A (simple): churn on ad_spend only, n = 60 weeks.

β_ad = −0.002 (more spend, lower churn), p = 0.01, R² = 0.18

Model B (multiple): add brand_awareness_index (weekly survey composite).

β_ad = +0.001 (sign flip), p = 0.04, VIF_ad = 12, R² = 0.31

Part A: What happened

Ads and awareness co-move; simple model attributed churn reduction to ads when awareness (and unmeasured product love) drove both. Adding awareness splits partial effects; ad coefficient reflects incremental ad signal after awareness, unstable due to collinearity.

Part B: Managerial read

Do not cut ads based on Model A story. Options: drop one variable, build composite marketing_intensity, or run geo holdout test (Lesson 5). R² rose but interpretation of β_ad is fragile while VIF > 10.

Check: both models same n = 60 ✓; sign flip documented with VIF ✓

You regress quarterly sales ($M) on own_price ($), marketing ($M), and competitor_price ($) for n = 48 quarters:

β_price = −2.5 (SE 0.8, p = 0.002), β_marketing = 4.1 (SE 1.2, p = 0.001), β_comp_price = 0.9 (SE 0.5, p = 0.07), R² = 0.72, R²_adj = 0.70

Tasks:

1. Interpret β_price holding marketing and competitor price fixed.
2. What do you conclude about competitor price at α = 0.05?
3. List three reasons R² = 0.72 could still yield poor forecasts.
4. If VIF_price = 8 because marketing bundles include temporary price promos, how does that change your confidence in β_price?

Solution

1. Each $1 increase in own price associates with $2.5M lower sales this quarter, holding marketing spend and competitor price constant. Significant at 1%.

2. p = 0.07 exceeds 0.05; fail to reject zero effect at conventional level. Competitor price may matter, but evidence is marginal; gather more quarters or refine measurement before treating as confirmed lever.

3. (a) In-sample fit without train/test overstates future accuracy; (b) structural break (new entrant, COVID-era demand) breaks historical coefficients; (c) extrapolation outside price ranges observed in the 48 quarters.

4. VIF = 8 signals moderate-high collinearity; SE(β_price) may be understated stability. Price effect is directionally plausible but CI is wide; consider separating promo depth from list price or running price experiment in selected regions.

HR regresses output_units on headcount only: β_headcount = 12.5 (positive), R² = 0.55. Adding shift_hours and machine_age yields β_headcount = 2.1 (p = 0.18), R²_adj = 0.71.

Tasks:

1. Explain the headcount coefficient change in plain language.
2. Should leadership hire freely based on the simple model?
3. Write one additional variable you would still worry is omitted.

Solution

1. Simple model attributed all output co-movement with headcount to people. With shift hours and machine age held constant, extra headcount associates with only 2.1 units, statistically indistinguishable from zero. Earlier slope absorbed capital utilization and equipment effects.

2. No. Simple R² and positive slope invite over-hiring. Use multiple model and operational constraints (line speed, training). Causal hiring test needs pilot lines or staggered hiring (Lesson 5).

3. Omitted skill mix or supervisor quality: headcount counts bodies, not capability; unmeasured management drives output and correlates with staffing decisions.

You inherit a churn model with R²_adj = 0.42, all VIF < 4, and support_tickets coefficient +0.005 (p < 0.001). Product wants to "fix NPS first."

Tasks:

1. Using Lesson 3 output logic, argue for or against NPS-first budget in two paragraphs.
2. What validation metric from Lesson 4 should accompany deployment?
3. What causal evidence from Lesson 5 would justify a tickets-reduction OKR?

Solution

1. NPS-first is weak if nps_score is insignificant after controls while support_tickets remains positive and significant. NPS may be a symptom of service pain already captured by tickets. Budget should prioritize incident reduction and measure ticket trend; NPS surveys can monitor but should not lead if partial effect vanishes in multiple regression.

2. Report test MAE on holdout accounts vs naive baseline churn rate. In-sample R²_adj alone fails Lesson 4 gate.

3. RCT or DiD around engineering sprints that cut P1 rates: randomize enablement order across regions, primary metric 90-day retention, pre-specified MDE. Observational ticket coefficient motivates test; does not confirm intervention.

Check: answers reference partial effects, validation, and experiment ✓

Key takeaways

• Multiple regression estimates partial βⱼ: effect of Xⱼ while other predictors stay fixed.
• Use spreadsheet Toolpak Regression with all X columns; verify predictions and R²_adj.
• Check VIF before trusting individual signs; collinearity widens uncertainty and can flip coefficients.
• Adjusted R² compares nested models; high R² still does not prove causation or forecast success.
• Dummy variables need a reference category; insignificant controls reframe where to spend (NPS vs tickets).

1. Run multiple regression on a business dataset; paste the coefficient and VIF table and interpret one partial slope in one sentence.
2. Did any coefficient flip sign when you added controls? What confounder explains the flip?
3. Continue to Lesson 4: Forecast Accuracy and Model Validation.