Fit a specified sales response curve in Excel by minimizing squared errors. Work through the formulas, residual checks and a separate validation row before comparing supplied predictions with actual sales.
Excel nonlinear regression fits a specified curve to observations by minimizing squared prediction errors. Build the prediction formula first, let Solver change its parameters, and inspect the errors before comparing segments or making a spending decision.
The example below uses a small, synthetic US distributor's campaign report. It illustrates the workbook mechanics; four fitting observations cannot establish a dependable business relationship.
Suppose the business expects campaign-attributed sales to increase with spending, with progressively smaller gains. A candidate saturation curve is:
Predicted sales = a × x / (b + x)
Squared error = (Actual sales − Predicted sales)²
Fitting objective = Sum of squared errors
Here, x is campaign spending in thousands of USD, a is the sales ceiling in USD, and b is the spending level, also in thousands of USD, at which the prediction reaches half that ceiling. These interpretations follow from substituting a very large x or setting x equal to b.
The equation is nonlinear in b because that unknown sits in the denominator. NIST describes nonlinear least squares as fitting functions that are nonlinear in their unknown parameters by least squares. For a straight-line relationship, use the separate regression in Excel guide.
This equation assumes zero attributed sales at zero spending. That may be inappropriate for total company revenue, which often includes an existing customer base. Do not select a convenient curve without checking what its assumptions mean for the sales measure.
Use one row per comparable segment and reporting period. Retain the segment ID, reporting dates, campaign spending, attributed sales and the attribution policy used. Resolve duplicate campaign rows before fitting. The spending and sales windows must cover the same business activity, including the agreed conversion lag.
Put segment names in A, spending in B and actual sales in C. All four rows below use the same product scope and attribution definition. B contains thousands of USD; C and D contain USD.
| Excel row | Segment | Spending (thousand USD) | Actual sales (USD) | Predicted sales (USD) | Residual (USD) | Squared residual (USD²) |
|---|---|---|---|---|---|---|
| 2 | East small accounts | 10 | 29,400 | 30,000 | -600 | 360,000 |
| 3 | Central small accounts | 20 | 45,800 | 45,000 | 800 | 640,000 |
| 4 | West small accounts | 40 | 59,700 | 60,000 | -300 | 90,000 |
| 5 | South small accounts | 80 | 72,000 | 72,000 | 0 | 0 |
| Total | 206,900 | 207,000 | -100 | 1,090,000 |
The displayed predictions use fitted parameters a = 90,000 and b = 20. Keep those parameter cells separate: J2 for a and J3 for b. Enter these formulas in row 2, then fill through row 5:
D2: =$J$2*B2/($J$3+B2)
E2: =C2-D2
F2: =E2^2
G2: =ABS(E2)
Place the fitting objective in J5:
=SUM(F2:F5)
This layout keeps the observed values separate from the equation and makes every residual inspectable. A total error alone would conceal which segment disagrees with the curve.
Start J2 at 80,000 and J3 at 30 as initial guesses. With Solver enabled, set the objective to J5, choose Min, and select J2:J3 as the changing cells. Add constraints J2 >= 0 and J3 >= 0.001 so the denominator remains positive for nonnegative spending.
Choose GRG Nonlinear and solve. Microsoft's Solver instructions describe the objective, changing cells and constraints, and identify GRG Nonlinear for smooth nonlinear problems. Covirage is not affiliated with Microsoft.
For this constructed table, the least-squares fit is approximately a = 90,000 and b = 20. Repeat from other reasonable starting guesses and compare the objective values. A solver stopping message does not by itself prove that the chosen curve is useful or that the best possible minimum was found.
Keep full parameter precision in the calculation cells. The displayed parameters and predictions here are rounded to whole numbers; currency error summaries below are rounded to cents.
The residual column must reconcile actual and predicted sales:
=SUM(C2:C5)-SUM(D2:D5)-SUM(E2:E5)
It returns zero: 206,900 − 207,000 − (−100) = 0. Squaring the four residuals gives 360,000 + 640,000 + 90,000 + 0 = 1,090,000 USD², which must equal J5.
Compute descriptive error measures with:
Root mean squared error: =SQRT(J5/COUNT(C2:C5))
Mean absolute error: =SUM(G2:G5)/COUNT(C2:C5)
The results are $522.02 and $425.00. The first divides squared error by the observation count; it is a descriptive RMSE, not a degrees-of-freedom-adjusted estimate of residual variability.
Plot residuals against spending and against reporting order. NIST's validation guidance explains why residual patterns matter and why a high R-squared alone is insufficient. Large errors concentrated in one segment can indicate different customer mix rather than an incorrect spending coefficient.
Reserve an observation before fitting and keep it out of J5. With parameters frozen, calculate its prediction using the same equation:
| Validation segment | Spending (thousand USD) | Actual sales (USD) | Prediction (USD) | Actual minus prediction (USD) |
|---|---|---|---|---|
| Northeast small accounts | 60 | 66,000 | 67,500 | -1,500 |
The prediction is 90,000 × 60 / (20 + 60) = $67,500. This validation error is larger than any fitting residual. One held-out observation does not settle accuracy, but it exposes a weakness that the fitting objective misses.
For subsequent periods, use a consistent data comparison in Excel method. Keep business assumptions separate from statistical fit; the scenario analysis workbook addresses changes to those assumptions.
Bring your completed workbook with supplied fitted predictions, actual sales, spending, segment IDs and reporting dates. Covirage maps the columns, its tools compute and check the comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, set up your business and bring a sample of your own data.
See revenue driver analysis for the supporting business context.
No. A curve can still be linear in its coefficients, as with a polynomial, but a saturation curve with an unknown parameter in the denominator requires a different fitting setup. Choose the equation first, then choose the fitting method.
Starting values, constraints and the shape of the objective can affect the result. Try several reasonable starting points and inspect the resulting errors. Similar errors with very different parameters can mean the observations do not identify the curve reliably.
No. A fitted curve describes the supplied observations under their measurement and attribution rules. Prices, customer mix and seasonality may explain the relationship. A spending decision needs business judgment and stronger evidence than a good fit alone.