Python is the spine for Week 8, but Excel can do this same work too. The same
ordinary-least-squares fit takes only a couple of minutes in a spreadsheet, and it is worth knowing both
routes. This page walks the fast path on the
same Summit Gear customers data and the same target — days_to_pay, how long a
customer takes to pay an invoice. Two ways to fit it (the Data Analysis ToolPak first, then
LINEST as the formula alternative), how to read the coefficients and R², and — the part
that matters most — how to do feature selection and spot multicollinearity in Excel, with a
correlation matrix, before you trust a single number.
Open summit_gear_customers.csv in Excel. Each row is one B2B customer; the column we want to
forecast is days_to_pay. The predictors are the other numeric columns. Here are the first few rows
laid out the way Excel sees them — row 1 is the header, so the data starts on row 2.
Download the data — summit_gear_customers.csv
2,200 rows, one per B2B customer. The final hosted raw-URL is wired at deploy; if the link above does not resolve on the preview host yet, the same file is published with the rest of the course data at deploy time.
F:M (credit_limit through avg_order_value) and the target
days_to_pay sits in column P, with data on rows 2 through 2201 (2,200 customers).
The exact letters depend on your column order — the formulas below use these as a concrete example.Reach for the Data Analysis ToolPak first. It runs the same ordinary-least-squares fit but hands you a labeled, readable output table — coefficients, standard errors, t-stats, p-values, and R², all named — from a four-click dialog:
days_to_pay column, header included (P1:P2201).F1:M2201).LINESTIf you want the same fit live in a cell without opening a dialog, LINEST fits the whole
regression in a single array formula. The arguments are the Y range (the target), the X range
(the predictors), then two TRUE flags that say "yes, fit an intercept" and "yes, give me the full
statistics block."
In modern Excel this spills automatically — click one cell, type it, press Enter, and the output
block fills in. In older Excel you select a block first (rows = number of stat lines, columns = number of
predictors + 1), type the formula, then press Ctrl+Shift+Enter to enter it as an array.
LINEST returns the
coefficients in reverse column order (last predictor first), the intercept is the far-right value on
the top row, and the statistics are unlabeled. That is why the ToolPak (Step 2) is the path to start with for
a readable table. LINEST is the no-dialog way to get the same numbers live in a cell — same
fit either way, pending the instructor's live-Excel check.Both paths produce the same fit. This is the ToolPak-style output for the full "kitchen sink" model on Summit Gear — every numeric predictor thrown in at once. The numbers are the real fit from the dataset.
| Multiple R | 0.919 | R Square | 0.844 |
| Adjusted R Square | 0.843 | Observations | 2,200 |
R² = 0.844 means the model explains about 84% of the variation in how long customers take to pay. Adjusted R² (0.843) is R² with a penalty for the number of predictors — watch it, not plain R², when you add or drop columns.
| Term | Coefficient | Std Error | t Stat | P-value |
|---|---|---|---|---|
| Intercept | 23.432 | 0.930 | 25.19 | 0.000 |
| credit_limit | -0.00000 | 0.00003 | -0.11 | 0.915 |
| avg_balance | 0.00078 | 0.00009 | 9.02 | 0.000 |
| annual_spend | 0.00001 | 0.00000 | 1.76 | 0.079 |
| prior_late_count | 3.339 | 0.111 | 30.04 | 0.000 |
| days_since_last_order | 0.026 | 0.006 | 4.28 | 0.000 |
| orders_per_year | -0.192 | 0.033 | -5.91 | 0.000 |
| discount_rate | -1.696 | 0.077 | -22.06 | 0.000 |
| avg_order_value | 0.00004 | 0.00004 | 1.03 | 0.303 |
| noise_a | 0.003 | 0.010 | 0.30 | 0.767 |
| noise_b | 0.004 | 0.005 | 0.76 | 0.448 |
Read a coefficient as: holding the other predictors fixed, a one-unit rise in this predictor changes
predicted days_to_pay by this many days.
prior_late_count = +3.34: each prior late
payment adds about three and a third days to the predicted time-to-pay. discount_rate =
-1.70: customers on deeper discounts pay a bit faster. Both have tiny p-values — the data is
confident about them. The two noise columns sit near zero with large p-values: the model
correctly finds nothing there.credit_limit,
avg_balance, annual_spend — look weak and jumpy here (one is even slightly
negative). That is not because they do not matter. It is multicollinearity, and Step 5 is how you
catch it.=CORRELBefore trusting any coefficient, check whether your predictors are stepping on each other. The fast Excel
way is a correlation matrix: =CORREL of each predictor pair. Build it by hand for a few suspect
columns, or use Data Analysis → Correlation for the whole block at once.
One pair at a time: this returns the correlation between two columns, a number from -1 to +1. Drag it across a grid of predictor-vs-predictor cells and you have the matrix below. These are the real Summit Gear correlations. The diagonal is always 1.00; the cells that matter are the off-diagonal ones — a hot off-diagonal cell, two predictors correlating near ±1, is the warning sign that they measure the same thing and the regression cannot separate their effects.
| credit_ limit | avg_ balance | annual_ spend | prior_ late | discount_ rate | days_to _pay | |
|---|---|---|---|---|---|---|
| credit_limit | 1.00 | 0.98 | 0.96 | 0.11 | 0.01 | 0.84 |
| avg_balance | 0.98 | 1.00 | 0.99 | 0.12 | 0.01 | 0.86 |
| annual_spend | 0.96 | 0.99 | 1.00 | 0.11 | 0.01 | 0.85 |
| prior_late_count | 0.11 | 0.12 | 0.11 | 1.00 | 0.01 | 0.35 |
| discount_rate | 0.01 | 0.01 | 0.01 | 0.01 | 1.00 | -0.17 |
| days_to_pay | 0.84 | 0.86 | 0.85 | 0.35 | -0.17 | 1.00 |
credit_limit, avg_balance, and annual_spend correlate with
each other at 0.96 to 0.99. They are three measurements of essentially the same thing —
how big a customer is. When predictors are that tightly linked, the regression cannot tell their individual
effects apart, so it hands out unstable, jumpy coefficients (exactly what you saw in Step 4).prior_late_count, discount_rate
— correlate near 0 with everything except the target. Those are the ones the model reads
confidently.The correlation matrix is the whole diagnostic here — no extra metric needed. The hot block in the
top-left corner is the signal to act: pick one member of the cluster to represent customer size, drop
the other two, and re-run the regression. Refitting with just avg_balance (cluster trimmed, noise
columns removed) gives a stable coefficient that recovers the real relationship:
| Term (pruned model) | Coefficient | Std Error |
|---|---|---|
| Intercept | 23.629 | 0.519 |
| avg_balance | 0.00090 | 0.00001 |
| prior_late_count | 3.332 | 0.111 |
| days_since_last_order | 0.026 | 0.006 |
| orders_per_year | -0.212 | 0.022 |
| discount_rate | -1.698 | 0.077 |
avg_balance now reads a clean, stable +0.00090 with a tiny standard error. That is the
payoff of feature selection: fewer predictors, a more honest model, coefficients you can actually defend.=LINEST(Y, X, TRUE, TRUE) returns the same fit live in a cell if you want it
without a dialog. Same numbers either way.days_to_pay per one-unit rise in
that predictor, holding the others fixed. R² is the share of variation explained; watch
adjusted R² when you change the predictor set.=CORREL correlation matrix is the diagnostic — a hot
off-diagonal cell (two predictors correlating near ±1) means the regression cannot separate their
effects, so the individual coefficients are unstable and not trustworthy.This is the same logic as the Python feature-selection visualizer and the OLS trainer, in a spreadsheet. Python stays the spine for the lab and homework; Excel is the fast second lane when you want a fit and a sanity check in a couple of minutes.