How to Forecast Revenue in Google Sheets

Building a revenue forecast might sound like a job for a team of data scientists, but you don't need complex software or a statistics degree to get started. All you need is Google Sheets. This article will show you how to create a practical, dynamic revenue forecast by using built-in functions and building a simple model to project your company’s financial future.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Use Google Sheets for Revenue Forecasting?

While dedicated financial planning software has its place, Google Sheets is often the perfect starting point for businesses of all sizes. It hits a sweet spot of accessibility, simplicity, and flexibility, making it a surprisingly powerful tool for looking ahead.

• It’s Accessible and Collaborative: Google Sheets is free and lives in the cloud, which means your entire team can access and collaborate on the forecast in real-time. There's no single "master file" on someone's desktop, everyone works from the same version.
• It’s Simple to Start: You don’t need to learn a whole new platform. If you've used a spreadsheet before, you already have the foundational skills. The learning curve is gentle, allowing you to build a useful forecast in an afternoon rather than spending weeks in training.
• It’s Fully Customizable: Every business is unique, and a one-size-fits-all forecasting tool often misses the nuances of your specific model. In Google Sheets, you’re in complete control. You can build a model that reflects precisely how your business generates revenue, from sales funnels to subscription models.

Gathering Your Data: The Foundation of Any Good Forecast

Your forecast is only as reliable as the data you put into it. Before you can look forward, you need to look back at your historical performance. The goal is to collect clean, consistent, and accurate revenue data over a solid period, typically monthly for the last 12 to 24 months. Looking at two years of data is ideal because it helps capture any seasonal trends and gives your model a clearer picture of your growth patterns.

Here’s where you might find this data:

• E-commerce platforms: Shopify, BigCommerce, or WooCommerce have detailed reports on sales over time.
• Payment processors: Stripe, PayPal, or Square Enix provide records of every transaction. For subscription businesses, Stripe is a goldmine for cohorts and Monthly Recurring Revenue (MRR) data.
• CRMs: Salesforce or HubSpot track deal stages and when deals are marked "Closed Won," giving a view of sales-driven revenue.
• Accounting software: QuickBooks or Xero will have your final, reconciled revenue numbers.

When you gather your data, format it simply in a Google Sheet. Create two columns: one for the date (e.g., the first of each month, like 1/1/2023, 2/1/2023) and another for the total revenue during that period. Consistency is key here - make sure you're using the same time intervals (monthly is most common) for all your historical data points.

Method 1: Forecasting with Trendlines and Built-in Functions

The quickest way to forecast revenue in Google Sheets is to use the platform's amazing built-in functions. These formulas analyze your historical data to project future performance based on trends. This approach is great for a fast, high-level forecast.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Set Up Your Spreadsheet

Start with a clean sheet. In column A, list the dates of your historical periods (e.g., the first of each month). In column B, enter the corresponding revenue for each of those months. Then, in column A below your historical data, add the future months you want to forecast.

Your setup should look something like this:

• Column A (Date): 1/1/2023, 2/1/2023, ..., 12/1/2023, 1/1/2024, 2/1/2024 (future)
• Column B (Revenue): $10,000, $10,500, ..., $14,000, (empty cells for future dates)

Step 2: Use the FORECAST Function for a Linear Trend

The standard FORECAST function projects a future value along a simple linear trend line. It looks at the straight-line relationship between your dates and your revenue and extends that line into the future. It’s perfect if your growth has been relatively steady.

The formula syntax is:

=FORECAST(x, data_y, data_x)

• x: The date you want to predict revenue for (e.g., cell A14, containing "1/1/2024").
• data_y: Your range of known historical revenues (e.g., $B$2:$B$13). We use dollar signs ($) to make this an absolute reference, so it doesn't change when you drag the formula down.
• data_x: Your range of known historical dates (e.g., $A$2:$A$13), which should also be an absolute reference.

In the first empty cell of your revenue column (say, B14), you would type:

=FORECAST(A14, $B$2:$B$13, $A$2:$A$13)

After hitting enter, you can drag the blue square at the corner of the cell down to automatically apply the forecast to all your future months.

Improving The Trend: Using GROWTH for Exponential Cases

If your business is growing more exponentially than linearly (e.g., doubling performance year over year), the GROWTH function might be a better fit. It works similarly to FORECAST but fits your data to an exponential curve instead of a straight line.

The syntax is identical from a user perspective:

=GROWTH(known_data_y, [known_data_x], [new_data_x], [use_const])

Similar to FORECAST, you would provide your new date as new_data_x along with the known y and x values from your historical periods. Try both and see which projection looks more realistic for your business.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 2: Building a Driver-Based Forecast Model

A formula-based forecast is a great start, but a driver-based model gives you more control and a deeper understanding of your business. Instead of looking just at the final revenue number, you break revenue down into its core components - the "drivers."

For a sales-driven business, your revenue formula might be:

Revenue = # of Leads x Lead-to-Customer Conversion Rate % x Average Revenue per Customer

By forecasting each of these drivers independently, you can build a more robust and flexible revenue model. This technique lets you play with scenarios. For instance, what happens to revenue if you boost your marketing efforts and double your lead count? Or what if you improve your sales process and increase the conversion rate by 5%?

Step 1: Set Up an 'Assumptions' Tab

Create a dedicated space in your sheet, or even a new tab, for your key assumptions. This is where you'll list the drivers of your business and your best guess for their future values. This makes your model easy to update - if one of your assumptions changes, you only have to update it in one place, and your entire forecast will recalculate automatically.

Your 'Assumptions' section might look like this:

• Monthly Leads Growth Rate: 5%
• Lead-to-Customer Conversion Rate: 10%
• Average Revenue per Customer: $500

Step 2: Build Your Model Structure

On your main forecasting sheet, lay out your funnel in columns from left to right. It might look something like this:

• Column A: Month
• Column B: # of Leads
• Column C: # of New Customers
• Column D: Monthly Revenue

Step 3: Connect Everything with Formulas

Now, it’s time to bring your model to life with formulas that reference your assumptions.

• # of Leads (Column B): Start your first month with a baseline number (e.g., your last month's actual leads). For each following month, use a formula to grow it based on your assumption. For example, if cell B2 is last month's leads and your growth rate is in 'Assumptions'!B1, the formula in B3 would be:

=B2 * (1 + 'Assumptions'!$B$1)

• # of New Customers (Column C): This is simply your leads multiplied by your conversion rate assumption. The formula in C2 would be:

=B2 * 'Assumptions'!$B$2

• Monthly Revenue (Column D): Finally, calculate revenue by multiplying your new customers by your average revenue per customer. The formula in D2 would be:

=C2 * 'Assumptions'!$B$3

Once you’ve set up the formulas for the first forecasted month, you can drag them down to populate the rest of your forecast period. Now you have a working model you can use to test different scenarios by simply changing the values in your "Assumptions" section.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Visualizing Your Forecast in Google Sheets

A table of numbers is useful, but a chart tells a more compelling story. Visualizing your forecast helps you and your stakeholders quickly grasp trends and key takeaways.

A combination chart is perfect for this. It can show your historical revenue as a solid line and your forecasted revenue as a dotted line, clearly distinguishing between what has happened and what you project will happen.

1. Highlight Your Data: Select your Date, Historical Revenue, and Forecasted Revenue columns.
2. Insert a Chart: Go to Insert > Chart. Google Sheets will likely suggest a combination chart by default, but if not, you can select it from the "Chart type" dropdown in the chart editor.
3. Customize: Use the chart editor to customize your chart. Add titles, adjust the colors, and consider making the forecast series a dashed line to visually separate it from the actual historical data. This simple visualization makes your forecast immediately understandable at a glance.

Creating More Reliable Forecasts

Forecasting is part art, part science. While formulas provide the science, sound judgment provides the art. Here are a few final tips for making your forecast even more reliable:

• Create Multiple Scenarios: The future is uncertain. Instead of a single forecast, create three: a Best Case, Worst Case, and Most Likely scenario. You can do this by creating three different sets of assumptions in your driver-based model. This gives you a range of possible outcomes to plan around.
• Account for Known Events: Is a major marketing campaign launching in Q3? Are you hiring two new salespeople next month? Adjust your model's assumptions for specific future periods to reflect these business plans.
• Update It Regularly: A forecast is a living document, not something you create once and forget about. Review your forecast monthly or quarterly. Compare your projections with actual results, understand why they differed, and adjust your assumptions accordingly. Each update will make your future forecasts more accurate.

Final Thoughts

Google Sheets offers a surprisingly powerful and completely free way to build a revenue forecast, whether it's a simple trend line or a more detailed, driver-based model. By organizing your historical data and using the right formulas, you can create a dynamic tool that helps you plan for the future, test business scenarios, and take control of your company's growth.

Of course, the most tedious part of forecasting in a spreadsheet is often the first step: manually gathering all your historical data. We know that spending half of your Monday downloading CSVs from Shopify, your CRM, and Google Analytics - then cleaning it all up - is a massive time sink. With Graphed you connect your data sources in just a few clicks. This automates the entire collection process, so your performance data is always live and in one place. You can even use simple language to ask "show me revenue by campaign" or "forecast next quarter's sales," instantly generating a real-time dashboard without any of the manual wrangling.