TL;DR
You can build a working revenue forecast in Google Sheets using nothing but historical sales data, a handful of built-in formulas, and about two hours of focused work. The approach combines a linear trend with a simple seasonality multiplier so your projections actually reflect your business cycles. You need at least 12 months of revenue history and a free Google account to get started.
What You Need Before You Start
- A Google account (free tier is fine)
- At least 12 months of monthly revenue data, ideally 24 months or more
- Revenue broken out by month, not just annual totals
- Access to Google Sheets (browser or the Google Sheets app, any recent version)
- Optional: a CSV export from your accounting tool like QuickBooks, Xero, or Wave so you are not typing numbers by hand
- Optional: the Coefficient add-on if you want live data pulls from Salesforce or HubSpot directly into Sheets
- A clear definition of what “revenue” means for your business: gross revenue, net revenue, or MRR if you are SaaS
If your data lives in separate spreadsheets for each year, consolidate everything into one sheet before you follow these steps. Chasing broken references mid-build wastes time.
Step 1: Set Up Your Historical Data Table
Open a new Google Sheet and name it something you will recognize in six months. “Revenue Forecast 2026” works fine.
In column A, enter your date column header: Month. Fill rows 2 onward with the first day of each month you have data for. Format the column as Date using Format > Number > Date. In column B, add the header Actual Revenue and paste in your monthly numbers.
Your table should look like this:
| Month | Actual Revenue |
|---|---|
| 2024-01-01 | 18400 |
| 2024-02-01 | 17200 |
| 2024-03-01 | 22100 |
Do not include currency symbols in the cells themselves. Store plain numbers and apply currency formatting via Format > Number > Currency so formulas do not break later.
You should now see a clean two-column table with no blank rows, no merged cells, and dates that Sheets recognizes as actual date values (they should align right in the cell by default).
Step 2: Add a Row Index Column
Formulas like FORECAST.LINEAR need a numeric x-axis, not dates. Dates work in theory, but a simple row index is easier to reason about and debug.
In column C, add the header Period Index. In C2 enter 1. In C3 enter =C2+1. Drag that formula down to match all your historical rows.
This gives you a clean sequence: 1, 2, 3, 4… one number per month. When you extend the table into future months, you keep adding 1 each row, which is exactly the input FORECAST.LINEAR expects.
You should now see a sequential number next to each month, starting at 1 and ending at whatever your most recent data row is. If you have 24 months of history, the last index should be 24.
Step 3: Calculate Your Trend Line with FORECAST.LINEAR
Add future month dates in column A below your last historical row. If your history ends at December 2025, add January 2026 through December 2026 in A-rows below. Fill their Period Index values in column C by continuing the sequence.
In column D, add the header Trend Forecast. Skip the historical rows for now and start in the first future row. Use this formula, adjusting row references to match your sheet:
=FORECAST.LINEAR(C26, $B$2:$B$25, $C$2:$C$25)
Here C26 is the Period Index for the month you are forecasting. $B$2:$B$25 is your historical revenue. $C$2:$C$25 is your historical index. Lock those ranges with dollar signs so they do not shift when you drag down.
Drag the formula through all 12 future rows.
You should now see projected revenue numbers in column D for each future month. They should follow a smooth upward or downward trend consistent with your historical direction. A wildly different number suggests a data entry error in your history.
Step 4: Calculate Monthly Seasonality Multipliers
A straight trend line ignores the fact that your December might always spike 40% above your average or that August is always slow. Seasonality multipliers fix that.
In column E, add the header Monthly Avg (Historical). You want the average revenue for each calendar month across all years you have. Use a formula like this for January (assuming your Month column is in A):
=AVERAGEIFS($B$2:$B$25, MONTH($A$2:$A$25), 1)
Change the last argument (1 through 12) for each month. You can do this in a small reference table on a separate tab to keep things tidy.
Then calculate the overall monthly average of your entire history:
=AVERAGE($B$2:$B$25)
Your seasonality multiplier for each month is:
= [Month Average] / [Overall Average]
A multiplier above 1.0 means that month tends to run hot. Below 1.0 means it runs cold. A December multiplier of 1.35 means December historically runs 35% above your typical month.
You should now have 12 multipliers, one per calendar month. They should average out close to 1.0 across the year. If they average well above or below 1.0, check for data gaps in your history.
Step 5: Map the Seasonality Multiplier to Each Forecast Row
Now you need to attach the right multiplier to each future month. The cleanest way is a VLOOKUP or INDEX/MATCH against your 12-row multiplier table.
Assuming your multiplier table is on a tab called Seasonality with month number in column A and multiplier in column B:
=INDEX(Seasonality!$B$1:$B$12, MATCH(MONTH(A26), Seasonality!$A$1:$A$12, 0))
Put this in column F with the header Season Multiplier. Drag it down through all forecast rows.
You should now see a multiplier between roughly 0.6 and 1.5 next to each future month, matching the pattern from your historical data. January 2026 should carry the same multiplier as January 2025.
Step 6: Build the Final Adjusted Forecast
This is the column you will actually use and share. In column G, add the header Adjusted Forecast. The formula is simple:
=D26 * F26
Trend times seasonality. Drag it down through all forecast rows.
This number blends the direction your business is heading (trend) with the natural rhythm of your calendar (seasonality). It is not a perfect crystal ball, but it is far more useful than a flat average or a gut-feel guess.
You should now see a 12-month forecast that rises and falls with your historical seasons while still respecting the overall growth or decline trend in your data.
Step 7: Add a Best-Case and Worst-Case Range
Point forecasts give stakeholders false confidence. A range is more honest and more useful for planning. A practical method is to calculate the standard deviation of your historical forecast errors.
First, back-fill your FORECAST.LINEAR formula over the historical period (column D) and compare it to actual revenue in column B. In column H, calculate the error:
=B2 - D2
Then in a summary cell, calculate the standard deviation of those errors:
=STDEV(H2:H25)
Call this value your Forecast StdDev. For a 68% confidence range, add and subtract one StdDev from each adjusted forecast. For a wider 95% range, use two StdDevs.
In columns I and J, add Best Case and Worst Case:
=G26 + [StdDev cell]
=G26 - [StdDev cell]
You should now see three columns for each future month: a central forecast plus an upper and lower bound. The range will widen or narrow depending on how variable your historical revenue has been.
Step 8: Visualize the Forecast
Highlight columns A, G, I, and J for all future months. Insert a line chart via Insert > Chart. Set A (Month) as the X axis. Plot Adjusted Forecast, Best Case, and Worst Case as three separate lines.
Change the Best Case and Worst Case lines to dashed or lighter colors so the central forecast stands out. Add chart title, axis labels, and a note with the date you built it.
For a clean presentation export, try the Canva integration or simply use File > Download > PDF from within Sheets.
You should now see a chart where the solid line shows your forecast and two flanking dashed lines show the uncertainty band. Anyone glancing at this chart understands the story in under five seconds.
Step 9: Protect and Document Your Model
Before you share the file, lock the cells that contain your historical data and your seasonality table. Select the ranges, right-click, and choose Protect Range. This prevents someone from accidentally overwriting a number that cascades into broken forecasts.
Add a Notes tab with three things: the data source (where your revenue numbers came from), the date the model was last updated, and one sentence explaining the seasonality method used.
Set a monthly calendar reminder to update the Actual Revenue column with real numbers. Comparing actuals to forecasts month by month tells you quickly whether the model is drifting and when it needs a recalibration.
You should now have a shareable, documented forecast workbook that other people can use without breaking it.
Common Mistakes To Avoid
- Using too little history. Fewer than 12 months means your seasonality multipliers are based on one data point per month. Two or three years of history gives much more reliable multipliers.
- Mixing revenue definitions mid-table. If your 2024 numbers are gross revenue and your 2025 numbers are net of refunds, the trend calculation is comparing apples to oranges.
- Forgetting to recalibrate after a major change. If you launched a new product line or lost a big client in month 18, your historical trend is distorted. Consider starting your index from the inflection point rather than forcing the model to average across a structural shift.
- Treating the model as set-and-forget. A forecast built in January and never updated is worse than no forecast by July. The model only works if you feed it fresh actuals every month.
- Rounding multipliers to one decimal place. Rounding 1.34 to 1.3 seems harmless, but compounded across 12 months the error adds up. Keep at least two decimal places in your multipliers.
- Not anchoring formula ranges with dollar signs. Dragging an unlocked formula shifts the reference window and silently breaks the output without any error message.
When To Level Up
This Sheets approach works reliably until a few things start happening. First, when your team grows past two or three people editing the same forecast, version control becomes a real problem. Google Sheets collaboration is fine for reading, but concurrent editing of a formula-heavy model creates conflicts.
Second, when you need to forecast at the product or SKU level rather than total revenue, the manual multiplier approach scales badly. You end up with dozens of tabs and formulas that are hard to audit.
Third, when your business has irregular spikes driven by events like product launches or promotions that do not repeat on a fixed calendar cycle, the static seasonality multiplier misfires.
At that point, look at purpose-built forecasting tools and BI platforms covered in the data analysis tools category. Tools like Causal, Tableau, or Coefficient offer model versioning, automated data pulls, and scenario planning that a Sheets model cannot match. The article on choosing the right forecasting tool for your stage walks through that decision in detail.
Frequently Asked Questions
How many months of data do I need to get a reliable forecast?
Twelve months is the bare minimum to calculate one full cycle of seasonality multipliers. Twenty-four months is the practical sweet spot because it gives you two data points per calendar month, which smooths out one-off anomalies. Fewer than 12 months means your seasonality is guesswork.
Can I use this method for weekly revenue instead of monthly?
Yes. Replace month dates with week-start dates and replace the 12-month seasonality table with a 52-week one. The formulas work identically, but you need at least two full years of weekly data for the seasonality multipliers to mean anything.
What if my revenue trend is not linear?
FORECAST.LINEAR assumes a straight-line trend. If your business is growing exponentially or has clearly leveled off into a plateau, calculate the forecast on the log of revenue instead, then exponentiate the result back. A simpler hack is to only use the most recent 12 months as your trend input and discard older data.
How is this different from using the TREND function?
FORECAST.LINEAR and TREND give the same numbers. FORECAST.LINEAR takes a single x-value and returns a single predicted y-value, which makes it easier to use in a row-by-row table. TREND takes an array of x-values and returns an array of y-values in one go. Either works for this method.
Should I share the forecast with my whole team?
Share a view-only version or a locked copy. If team members need to run their own scenarios, give them a tab where they can change a growth assumption cell and see results update, rather than letting them edit the core formula rows. The revenue dashboard guide on this site covers how to set up a clean read-only view for non-analyst stakeholders.
Bottom Line
Building a revenue forecast in Google Sheets without machine learning is genuinely doable for any small business owner who is comfortable with basic spreadsheet formulas. The workflow is: clean your historical data, build a linear trend with FORECAST.LINEAR, layer on monthly seasonality multipliers, combine them into an adjusted forecast, and wrap it in a best-case/worst-case range. Document it, protect it, and update it monthly. The whole build takes two to three hours the first time and about 20 minutes per monthly update. It is not fancy, but it is honest, auditable, and free. When the model starts straining under team size or data complexity, that is your signal to explore the purpose-built tools at /category/data-analysis/ and invest in something that scales with you.