Regression and trends

01The problem

Two requests land on the analysts' desks the same morning.

Harbourline's sales team wants a quick way to quote: "A customer is asking what 6 containers from Shanghai to Lagos will cost. Can we get a formula instead of looking up old invoices?"

Kolanut's managing director wants a number for the budget: "Revenue's been growing. If the trend continues, what will July 2026 look like?"

Both are regression questions: fit a line through past data and use it to estimate something new. One of them will work very well. The other needs a large health warning. This lesson shows you how to tell which is which.

02The concept

The line of best fit

Simple linear regression finds the straight line y = intercept + slope × x that sits closest to the points on a scatter chart (it minimises the squared vertical distances, hence "least squares").

=SLOPE(known_ys, known_xs)          how much y changes when x rises by 1
=INTERCEPT(known_ys, known_xs)      y when x is 0
=RSQ(known_ys, known_xs)            r²: share of the variation in y the line explains
=FORECAST.LINEAR(x, known_ys, known_xs)   the line's estimate of y for a new x

On a scatter chart, right-click the points → Add Trendline, and tick Display equation and Display R-squared. Google Sheets: Customise → Series → Trendline, with the label set to the equation.

Reading the slope

The slope is in real units: "₦4.2 million per container", "₦730,000 more revenue each month". That's usually the most useful number regression gives you.

How good is the line? r²

r² runs from 0 to 1. Near 1, the line captures almost everything and estimates from it are reliable. Near 0, the line explains little: the points scatter widely around it and any single estimate could be far off. There's no universal "good" value. For pricing, you'd want 0.9 or more; for messy business trends, 0.3 can still show a real direction while warning you not to trust individual months.

Residuals: what the line misses

A residual is actual minus predicted. Plot or list them. A big residual is a point the line doesn't explain: an outlier worth investigating (December's festive spike). A pattern in the residuals (all positive in the middle, negative at the ends) means a straight line is the wrong shape.

When regression misleads

• Extrapolation: a line fitted to 1–8 containers says nothing reliable about 40 containers. A trend fitted to 18 months says little about 3 years from now.
• Seasonality: a straight line through monthly sales ignores December. Compare the same months year on year (lesson 9) or model seasons separately.
• Cause: a slope doesn't prove x causes y (lesson 6). Regression measures association.
• Outliers can drag the line; check the chart.

03Example

Quoting Shanghai → Lagos (Apapa). Harbourline's route 1 shipments, with containers as x and freight_charge as y:

=SLOPE(charge, containers)        4,219,636
=INTERCEPT(charge, containers)    -18,339
=RSQ(charge, containers)          0.98

The line: charge ≈ ₦4.22 million per container (the intercept is effectively zero). r² = 0.98: containers explain 98% of the variation in price. A quote for 6 containers:

=FORECAST.LINEAR(6, charge, containers)     25,299,478

About ₦25.3 million, and with r² this high the sales team can quote with confidence, for loads within the range the route has carried (1 to 8 containers).

Kolanut's revenue trend. Number the months 1 (January 2025) to 18 (June 2026) and fit a line to monthly revenue:

The direction is real: revenue has grown by roughly ₦730,000 a month. But r² is only 0.32. Individual months scatter widely around the line, mostly because December 2025 (₦66.3m) is far above it. Take December out and r² rises to 0.52. The honest budget sentence:

"Revenue has grown by about ₦0.7m a month over 18 months. A straight-line trend puts July 2026 at about ₦53m, but months vary a lot around the trend (r² = 0.32), so plan for a range: recent months have been between ₦43m and ₦55m."

04Walkthrough

1. In the logistics shipments.csv, filter to route_id 1. Insert a scatter chart of containers against freight_charge, add a trendline with its equation and R².
2. Calculate SLOPE, INTERCEPT and RSQ with formulas, and quote 6 containers with FORECAST.LINEAR.
3. In Kolanut's orders.csv, build monthly revenue with a pivot table (order_date grouped by month), then copy it to a sheet with a month number 1–18 next to each month.
4. Fit a trendline and calculate slope, r² and the forecast for month 19.
5. Add a residual column: actual − FORECAST.LINEAR(month). Which month has the largest residual?
6. Write the budget sentence for the managing director, with a range rather than a single number.

05Practice

06Challenge

07Check your understanding