Statistical
INTERCEPT function: estimate fixed overhead cost where X equals zero
Separate fixed baseline overhead from variable unit cost with INTERCEPT, and pair it with SLOPE for transparent forecasts.
Find the Y-axis starting point of a best-fit line
A simple linear cost model assumes Y = Intercept + Slope * X. INTERCEPT(known_y's, known_x's) calculates mean(Y) - SLOPE(Y, X) * mean(X), estimating baseline overhead at X = 0.
Just like SLOPE, pass known_y's (TotalCost) first and known_x's (Units) second.
| Units (A) | TotalCost (B) | TargetUnits (D) |
|---|---|---|
| 10 | 650 | 50 |
| 20 | 800 | |
| 30 | 950 | |
| 40 | 1100 |
Combine INTERCEPT + SLOPE * X to project future volume
With 10 units costing $650 and each unit adding $15, fixed overhead at 0 units is 650 - 150 = $500. Projecting 50 units in D2 gives 500 + 15 * 50 = $1,250.
The fitted intercept is an extrapolation to zero output, not proof of actual fixed overhead. Use it only when the linear relationship is appropriate over the quantities being modeled.
=INTERCEPT(B2:B5, A2:A5)Constant X values return #DIV/0!
When all X values are equal, no unique regression line exists and INTERCEPT returns #DIV/0!.
=INTERCEPT(B2:B5, A2:A5) + SLOPE(B2:B5, A2:A5) * D2Try the example
Use the original values or change them to test your understanding.