Blog · Forecast and pipeline · Sales teams
Build a monthly sales forecast in Google Sheets from baseline revenue, growth assumptions and incremental pipeline. Compare the completed forecast with actual revenue by month and region, then reconcile the differences.
A Google Sheets sales forecast template should separate monthly assumptions, calculated revenue and actual results by region. Build the forecast from baseline revenue plus incremental pipeline, then compare actual revenue with a preserved version of that plan. The layout below gives each input a visible owner and each variance a check.
Create two tabs named Forecast and Actuals. The forecast contains one row for each month and region; actuals contains individual revenue entries from finance. Keep all amounts in USD and use the same definition of revenue on both tabs.
This example is a US business services company planning January and February 2026. Baseline revenue covers expected business from existing customers before the growth adjustment. Incremental pipeline covers additional work that is absent from that baseline.
Use this structure on Forecast. Enter the headers in row 1 and the assumptions in rows 2 through 5.
| Column | Header | Entry or row 2 formula |
|---|---|---|
| A | Month | Real first-of-month date |
| B | Region | East or West |
| C | Baseline revenue (USD) | Input |
| D | Baseline growth | Input percentage |
| E | Incremental pipeline (USD) | Input |
| F | Pipeline probability | Input percentage |
| G | Adjusted baseline (USD) | =C2*(1+D2) |
| H | Weighted pipeline (USD) | =E2*F2 |
| I | Forecast revenue (USD) | =G2+H2 |
| J | Actual revenue (USD) | Monthly lookup below |
| K | Variance (USD) | =J2-I2 |
| L | Variance percentage | =IF(I2=0,"",K2/I2) |
For an Excel workbook with its own worked layout, use the existing sales forecast template. Keep this Sheets workbook organized around the monthly comparisons your team needs.
The example inputs are planning assumptions, not recommended probabilities or growth rates. Use historical results and the sales team's evidence to choose your own.
| Row | Month | Region | Baseline revenue (USD) | Growth | Incremental pipeline (USD) | Probability |
|---|---|---|---|---|---|---|
| 2 | January 2026 | East | 100,000 | 4% | 20,000 | 50% |
| 3 | January 2026 | West | 80,000 | 5% | 40,000 | 25% |
| 4 | February 2026 | East | 110,000 | 2% | 30,000 | 40% |
| 5 | February 2026 | West | 90,000 | 4% | 40,000 | 30% |
In A2 enter =DATE(2026,1,1); set A3 to =A2, A4 to =EDATE(A2,1) and A5 to =A4. Format these cells as month and year. Google's EDATE documentation confirms that it advances a real date by a specified number of months.
Create an East/West dropdown for column B through Data > Data validation > Add rule. Use the same allowed values on Actuals; Google's dropdown instructions explain the rule setup. Keep it single selection because each revenue entry belongs to one region.
Enter the G2:I2 formulas from the layout and fill them down. For East in January, the calculation is $100,000 × 1.04 + $20,000 × 50% = $114,000. Preserve the approved inputs before replacing any assumptions. Your definitions of commit, best case and pipeline should accompany the workbook so reviewers understand what entered the plan.
Paste the revenue export into Actuals, with these headers in A1:D1. The references below are illustrative source identifiers, retained so each amount has a row to inspect.
| Recognition date | Region | Revenue reference | Revenue (USD) |
|---|---|---|---|
| January 10, 2026 | East | REV-01 | 70,000 |
| January 24, 2026 | East | REV-02 | 42,000 |
| January 12, 2026 | West | REV-03 | 60,000 |
| January 26, 2026 | West | REV-04 | 30,000 |
| February 10, 2026 | East | REV-05 | 75,000 |
| February 24, 2026 | East | REV-06 | 48,000 |
| February 12, 2026 | West | REV-07 | 65,000 |
| February 26, 2026 | West | REV-08 | 36,000 |
On Forecast, put this formula in J2 and fill down:
=SUMIFS(Actuals!$D$2:$D$10000,Actuals!$B$2:$B$10000,B2,Actuals!$A$2:$A$10000,">="&A2,Actuals!$A$2:$A$10000,"<"&EDATE(A2,1))
The lower boundary includes the first day; the upper boundary excludes the next month. SUMIFS sums values meeting multiple criteria, so this matches region and both date boundaries. Extend every range together if your export exceeds the stated capacity. Confirm imported dates are date values rather than text.
Fill K2:L2 down using the layout formulas. Negative variance means actual revenue is below forecast. Display percentages to two decimal places while retaining the underlying precision.
| Month | Region | Adjusted baseline (USD) | Weighted pipeline (USD) | Forecast (USD) | Actual (USD) | Variance (USD) | Variance percentage |
|---|---|---|---|---|---|---|---|
| January | East | 104,000 | 10,000 | 114,000 | 112,000 | -2,000 | -1.75% |
| January | West | 84,000 | 10,000 | 94,000 | 90,000 | -4,000 | -4.26% |
| February | East | 112,200 | 12,000 | 124,200 | 123,000 | -1,200 | -0.97% |
| February | West | 93,600 | 12,000 | 105,600 | 101,000 | -4,600 | -4.36% |
| Total | 393,800 | 44,000 | 437,800 | 426,000 | -11,800 | -2.70% |
The first check is structural: each month-region pair appears once in Forecast. In a spare review cell, =COUNTIFS(A$2:A$5,A2,B$2:B$5,B2) should return one for each row. Repeated keys would count the same actual revenue twice.
The revenue check is =SUM(J2:J5)-SUM(Actuals!D2:D9). It returns zero here because every actual entry belongs to a listed month-region pair. Reconcile the source total of $426,000 with finance's report before relying on the variance.
The forecast check is $393,800 adjusted baseline + $44,000 weighted pipeline = $437,800. The variance check is $426,000 − $437,800 = −$11,800, also the sum of K2:K5. Calculate the total percentage as =SUM(K2:K5)/SUM(I2:I5), never as an average of the row percentages.
Once these checks pass, a Google Sheets sales dashboard can present the result. Investigate the underlying movement with a forecast bridge, rather than changing assumptions until the forecast resembles actuals.
Covirage is not affiliated with Google or Microsoft.
Bring your completed Google Sheet or forecast file and the actual revenue export. Covirage maps the columns, and its tools compute and check the month and region comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, and bring a sample of your own data after setting up your business.
See forecast analysis for the supporting business use case.
Yes. Separate your assumptions from imported actual revenue, and calculate the forecast with ordinary cell formulas. Use real month-start dates and consistent region names. Keep an approved version so updating an assumption does not rewrite the forecast you are measuring against.
Include only incremental pipeline expected to produce revenue in that month under your company's revenue policy. Exclude deals already represented in baseline revenue. A deal's expected signing date and its expected revenue period can differ, so agree the timing with finance.
Sum actual revenue for the forecast row's month and region, then subtract forecast revenue. Divide that dollar variance by forecast revenue for the percentage variance. Use the same currency, revenue definition and reporting period on both sides.
This page provides a layout and formulas to build in your own Google Sheets workbook. The linked Excel sales forecast template is a separate resource. Preserve your completed Sheets forecast and actual revenue export when you want to analyze their differences.