sales forecasting in Google Sheets: complete tutorial
most solopreneurs forecast sales by feeling. they look at last month, add 10%, call it the forecast, and miss by 30% in either direction. when investors or partners ask “what is next quarter looking like,” they make up a number that matches their hopes. when the actuals come in, they update the running narrative to fit. nothing is learned, nothing improves.
a real forecast in Google Sheets takes 30 minutes. you need 8+ weeks of historical sales, a FORECAST formula, an adjustment for known seasonality, and a pipeline-weighted overlay for the current month. that produces three numbers: trend forecast, pipeline forecast, and blended forecast. when the forecast is wrong (it will be), you learn which input was off and you adjust.
this tutorial walks the entire build with a realistic 16-row sample dataset. by the end, you will have a working monthly forecast, a confidence interval, and a clear answer about what to expect next month. no Pipedrive forecast module, no $50/month tool, just Sheets.
the sample dataset
below is the dataset we will use. paste into Google Sheets, save as sales-forecast-2026, and follow along.
| month_index | month_label | actual_revenue | new_customers | open_pipeline_value | weighted_pipeline |
|---|---|---|---|---|---|
| 1 | 2025-01 | 12500 | 18 | ||
| 2 | 2025-02 | 11800 | 17 | ||
| 3 | 2025-03 | 14200 | 21 | ||
| 4 | 2025-04 | 15600 | 22 | ||
| 5 | 2025-05 | 14900 | 20 | ||
| 6 | 2025-06 | 13200 | 19 | ||
| 7 | 2025-07 | 13800 | 19 | ||
| 8 | 2025-08 | 16400 | 23 | ||
| 9 | 2025-09 | 18200 | 26 | ||
| 10 | 2025-10 | 19500 | 28 | ||
| 11 | 2025-11 | 21300 | 30 | ||
| 12 | 2025-12 | 23800 | 33 | ||
| 13 | 2026-01 | 22100 | 31 | ||
| 14 | 2026-02 | 21400 | 30 | ||
| 15 | 2026-03 | 24800 | 35 | ||
| 16 | 2026-04 | 26500 | 37 | 18000 | 7200 |
sales forecasting in Google Sheets uses three layers: a trend forecast from FORECAST.LINEAR or FORECAST.ETS, a seasonal adjustment from prior-year monthly indices, and a pipeline-weighted overlay for the current month. the standard solopreneur build takes 30 minutes, requires only monthly historical revenue and current pipeline, and consistently lands within 10% of actuals after 2-3 cycles of calibration. the goal is not perfection; it is a forecast you can defend, and that improves with each new month.
step 1: build the simple linear forecast
the simplest forecast is a straight-line projection of past revenue.
formula in column G (linear_forecast) for May 2026 (month_index 17):
=FORECAST.LINEAR(17, C2:C17, A2:A17)
expected output: approximately $25,800. (depends on exact data; this projects the linear trend forward by one month.)
the FORECAST.LINEAR function fits a least-squares regression line through your historical revenue and projects it forward.
check the fit
calculate R-squared to see how well the line fits.
=RSQ(C2:C17, A2:A17)
expected output: approximately 0.85. an R² above 0.7 means the linear trend explains most of the variation. above 0.9 means the trend is very stable.
below 0.5 and your data has too much volatility for a linear forecast; you need seasonal or pipeline-weighted methods instead.
common mistake: most solopreneurs use FORECAST.LINEAR on highly seasonal data and get a forecast that systematically misses. always check R² first; if below 0.7, layer in seasonality.
step 2: add seasonal adjustment
if your business has clear monthly seasonality (ecommerce in november, B2B in september), the linear forecast misses the pattern.
build a seasonal index column.
compute monthly average
| month | avg_revenue |
|---|---|
| jan | $17,300 |
| feb | $16,600 |
| mar | $19,500 |
| apr | $21,050 |
| … | … |
formula:
=AVERAGEIF(month_text_column, "01", revenue_column)
compute overall average
=AVERAGE(all_months)
seasonal index for each month
seasonal_index = month_avg / overall_avg
| month | seasonal_index |
|---|---|
| jan | 0.94 |
| feb | 0.90 |
| mar | 1.06 |
| apr | 1.14 |
| may | 1.07 |
| jun | 0.95 |
| jul | 0.99 |
| aug | 1.18 |
| sep | 1.30 |
| oct | 1.39 |
| nov | 1.53 |
| dec | 1.71 |
note: this dataset shows a strong fall/winter peak. typical for B2B SaaS or holiday-driven ecommerce.
seasonal-adjusted forecast
seasonal_forecast = linear_forecast × seasonal_index_for_target_month
for May 2026:
= $25,800 × 1.07 = $27,600
step 3: use FORECAST.ETS for built-in seasonality
Sheets has a built-in seasonal forecast: FORECAST.ETS.
formula:
=FORECAST.ETS(target_date, historical_revenue, historical_dates, 12)
the 12 parameter tells Sheets you have monthly seasonality (12-month cycle).
=FORECAST.ETS(DATE(2026,5,1), C2:C17, dates_column, 12)
expected output: approximately $27,400 for may 2026.
FORECAST.ETS is more sophisticated than FORECAST.LINEAR + manual seasonality. for most solopreneurs, it is good enough on its own.
confidence interval
FORECAST.ETS.CONFINT gives you the prediction range.
=FORECAST.ETS.CONFINT(target_date, historical_revenue, historical_dates, 0.95, 12)
expected output: approximately ±$3,200. so the forecast becomes $27,400 ± $3,200, a range of $24,200 to $30,600 at 95% confidence.
always present forecasts as ranges. point forecasts give false precision.
step 4: add the pipeline-weighted overlay
the FORECAST methods are pure trend extrapolation. they do not know about your current pipeline. add a pipeline overlay.
formula for current-month pipeline forecast:
pipeline_forecast = (last_month_recurring × renewal_rate) + weighted_pipeline + expected_new_signups × ARPU
with our sample data for may 2026:
| input | value |
|---|---|
| last_month_recurring | $25,000 (estimate) |
| renewal_rate | 95% |
| weighted_pipeline | $7,200 |
| expected_new_signups | 35 |
| ARPU | $50 |
pipeline_forecast = ($25,000 × 0.95) + $7,200 + (35 × $50) = $32,700
this is higher than the trend forecast of $27,400. the gap means either:
– the pipeline is unusually strong and the trend underestimates
– the pipeline is overstating because stage probabilities are wrong
investigate which is more likely before trusting the higher number.
step 5: build the blended forecast
most accurate solopreneur forecasts are a weighted blend of trend and pipeline.
blended = 0.5 × trend_forecast + 0.5 × pipeline_forecast
= 0.5 × $27,400 + 0.5 × $32,700 = $30,050
weights:
– 0.6 trend / 0.4 pipeline if pipeline data is noisy or unreliable
– 0.4 trend / 0.6 pipeline if pipeline tracking is solid and stage probabilities are calibrated
– 0.5 / 0.5 as default
over time, calibrate the weights by comparing prior forecasts to actuals.
step 6: track forecast accuracy
after each month closes, log the forecast vs actual.
| month | forecast | actual | error_pct |
|---|---|---|---|
| 2026-02 | $22,000 | $21,400 | -2.7% |
| 2026-03 | $24,000 | $24,800 | +3.3% |
| 2026-04 | $26,000 | $26,500 | +1.9% |
| 2026-05 | $30,050 | TBD |
after 6+ months you can calculate mean absolute percentage error (MAPE):
MAPE = AVERAGE(ABS(error_pct))
healthy SaaS solopreneur MAPE is 5-10%. above 15% means your forecast method needs revising.
step 7: build the dashboard
create a tab forecast_dashboard. paste:
| month | trend | pipeline | blended | actual | error |
|---|---|---|---|---|---|
| 2026-05 | $27,400 | $32,700 | $30,050 | TBD | |
| 2026-06 | $26,500 | TBD | TBD | TBD | |
| … | … | … | … | … |
chart the historical actuals as a line, with the blended forecast extended forward. add the confidence interval as a shaded band.
this is the chart you show at investor calls, partner meetings, and team check-ins. it is defensible because every input is traceable.
comparing forecasting methods
| method | accuracy | effort | best for |
|---|---|---|---|
| gut feel | low (15-30% MAPE) | zero | brand new businesses with no history |
| FORECAST.LINEAR | medium (10-20% MAPE) | 5 minutes | stable, non-seasonal businesses |
| FORECAST.ETS | medium-high (8-15% MAPE) | 10 minutes | seasonal businesses with 18+ months data |
| pipeline-weighted | high (5-12% MAPE) | 30 minutes | businesses with reliable pipeline tracking |
| blended | highest (4-10% MAPE) | 30 minutes | mature solopreneurs after 6 months calibration |
solopreneurs should start with FORECAST.ETS and layer pipeline once they have 12+ months of history.
our building a sales tracker in Google Sheets tutorial covers the pipeline tracking that feeds this forecast, and our revenue forecasting in Excel and Sheets guide covers the broader monthly forecast process.
frequently asked questions
how many months of history do I need before forecasting?
minimum 8 months for FORECAST.LINEAR, minimum 18 months for FORECAST.ETS to capture seasonality. below 8 months, use pipeline-weighted only.
should I forecast revenue or new customers?
both. revenue is the headline number. new customers is the leading indicator that explains why revenue moved. always present them together.
what if my data is too noisy for any forecast?
three causes: (1) too few customers (CV is high under 50 customers), (2) extreme seasonality, or (3) recent product or pricing change. for (1), wait. for (2), use FORECAST.ETS. for (3), reset the history starting from the change date.
how do I forecast quarterly?
sum monthly forecasts. quarterly forecasts directly are usually less accurate than three monthly forecasts summed.
what about external events (Black Friday, product launch)?
add a manual override. trend forecasts cannot predict singularities. always document the override so you can compare to actuals later.
conclusion: ship a forecast this week
forecasts are not about being right. they are about being calibrated. the value of running a monthly forecast is not the number itself; it is the discipline of comparing forecast to actual every month and learning what you systematically miss.
start this week. paste the last 12 months of monthly revenue into the schema above. run FORECAST.ETS. add the confidence interval. publish a forecast for next month, even if you are unsure. log it. one month later, compare and adjust.
after six months of this, your MAPE will drop below 10% and you will trust your forecast enough to make spend, hiring, and pricing decisions on it. that is when forecasting stops being a guessing game and starts compounding into a real business advantage.
for connected work, our customer churn analysis tutorial provides the retention input that drives recurring revenue, and our pricing analysis tutorial covers the price-elasticity work that often improves forecasts most. measure, project, calibrate.