How to Forecast Revenue in Google Sheets with AI

Predicting future revenue can feel like trying to guess the weather - a mix of educated guesswork and crossing your fingers. But with a bit of historical data and the AI tools built right into Google Sheets, you can create surprisingly accurate revenue forecasts without needing a degree in data science. This tutorial will walk you through several methods for forecasting revenue in Google Sheets, from simple built-in features to powerful formulas and AI add-ons.

First Things First: Prepare Your Data for Forecasting

Before you can predict the future, you need to understand the past. The quality of your forecast is entirely dependent on the quality of your historical data. AI is powerful, but it can't fix messy, inconsistent information - a concept in data analysis known as "garbage in, garbage out." Follow these steps to get your data in shape.

Step 1: Gather Your Historical Data

Your goal is to create a simple, two-column table. One column for the date period and one column for the corresponding revenue.

• Column A: Date. This should be a consistent time interval. Daily, weekly, or monthly data works best. Trying to mix different intervals will confuse the forecasting models.

• Column B: Revenue. This is the total revenue for that specific date period. Make sure this is a pure numerical value - remove any currency symbols (like "$") or commas. You can format the column as currency later, but the underlying data needs to be a number.

Your ideal dataset should look something like this:

(Example of a clean, simple dataset)

Step 2: Clean and Normalize the Data

Once you've collected your data, quickly run through this checklist to ensure it's clean and ready for analysis:

• Check for Duplicates: Make sure there are no repeated date entries that could skew the results.

• Standardize Dates: Ensure all dates are in the same format. Google Sheets is smart about interpreting dates, but a consistent format like YYYY-MM-DD is best practice.

• Handle Gaps: A missing month in your data is a problem. You have a few options: you can either ignore that period (if you have plenty of other data), input an average of the surrounding months, or simply enter a zero if it makes sense for your business (e.g., a planned shutdown). Be thoughtful here, as it will impact your forecast.

• Verify Numeric Values: Double-check that all entries in your revenue column are numbers. An accidental text entry will cause errors in your formulas.

Taking a few minutes to prepare your data is the most important step. A clean dataset is the foundation of a reliable forecast.

Method 1: Google's Built-in AI with the Explore Feature

The fastest way to generate a forecast without writing a single formula is by using Google Sheets' "Explore" feature. It uses natural language processing to understand what you want to do with your data and automatically generates charts and insights.

How to Use the Explore Feature

1. Highlight the two columns containing your historical date and revenue data.

2. Click on the Explore icon in the bottom-right corner of your screen. It looks like a small box with a plus sign or a sparkle on it. Alternatively, go to Tools > Explore.

3. A sidebar will open on the right. Google Sheets will automatically analyze your data and suggest some charts. You might even see a line chart with a dotted "Forecast" line already generated for you.

4. If not, you can simply type a question into the "Ask about this data" search bar at the top of the Explore panel. Try asking something direct like:

   • "Forecast revenue for the next 6 months"

   • "Predict revenue through the end of the year"

5. Google Sheets will generate a line chart showing your historical data along with a projected trend line. You can hover over this chart to see specific predicted values and drag and drop it directly onto your sheet.

This method is fantastic for a quick, "back-of-the-napkin" look into the future. It’s not highly customizable, and you don't have control over the underlying algorithm, but it's a powerful tool for a near-instant visual projection.

Method 2: Using the FORECAST and GROWTH Functions

For more control and a forecast that lives right inside your cells, you can use Google's built-in formulas. The primary function for this is aptly named FORECAST.

The FORECAST function calculates a future value along a linear trend line. Think of it as drawing a straight line through your historical data and extending that line into the future. This is best if your revenue growth is relatively steady and consistent.

Step-by-Step Guide to the FORECAST Function

1. Set up Your Data: Make sure you have your two columns of historical data ready (e.g., Dates in A2:A25 and Revenue in B2:B25).

2. Add Your Future Dates: In the 'Date' column, extend the list with the future dates you want to forecast for (e.g., A26, A27, A28 for the next three months).

3. Enter the Formula: In the cell next to your first future date (e.g., in B26), type the following formula:

Let's break that down:

• A26 is the new date you want to predict the revenue for.

• $B$2:$B$25 is the range of your known historical revenue data. The dollar signs $ make this an "absolute reference," so it won't shift when you drag the formula down.

• $A$2:$A$25 is the range of your known historical dates. This also uses an absolute reference.

1. Drag and Fill: Click on the small square (the fill handle) on the bottom-right of the cell with the formula and drag it down to apply the forecast to all your future dates.

What If Your Growth Is an Exponential Curve? Use GROWTH

If your business is growing rapidly, a straight-line forecast might feel too conservative. This is where the GROWTH function comes in. It performs the same task but assumes an exponential growth curve, which is common for startups or businesses entering a high-growth phase.

The syntax is nearly identical to FORECAST:

So, following our example, the formula would be:

You can even run both formulas side-by-side to create "best case" (GROWTH) and "likely case" (FORECAST) scenarios, giving you a range of potential outcomes.

Method 3: Supercharging Your Sheets with AI Add-ons

If you find yourself hitting the limits of built-in functions, the Google Workspace Marketplace is filled with powerful AI add-ons that can turn your spreadsheet into a data analysis powerhouse.

These tools often connect to advanced AI models like OpenAI's GPT and are specifically designed to handle spreadsheet tasks. They essentially let you "chat" with your data right inside Google Sheets, just as you can with the Explore feature, but with much more power and flexibility.

How AI Add-ons Typically Work

1. Installation: You find and install an add-on from the Google Workspace Marketplace (go to Extensions > Add-ons > Get add-ons).

2. Activation: Once installed, you typically activate the add-on from the Extensions menu, which opens a sidebar.

3. Prompting: You highlight your historical data range, then write a prompt in the sidebar. You can be very specific. For example:

   • "Forecast the data in B2:B25 for the next 12 periods based on A2:A25. Take potential seasonality into account and output the results starting in cell C26."

   • "Analyze the trend in this dataset and generate a pessimistic, realistic, and optimistic sales forecast for the next 4 quarters."

The add-on will then process your request and populate the cells with a detailed forecast, often choosing a more complex statistical model (like ARIMA) that can account for things like seasonality, which the basic FORECAST function cannot do.

Final Thoughts

Forecasting your revenue is an essential practice for smart business planning, and Google Sheets provides an array of tools to get it done. Whether you're using the instant insights from the Explore tab, the reliability of the FORECAST function, or the power of a dedicated AI add-on, you can move from simple guessing to data-driven decision-making right inside a tool you already know and use.

The biggest challenge in forecasting isn't always the formula itself, but the manual work of constantly exporting data from different platforms (like your ads manager, e-commerce store, and CRM) and wrangling it into a clean format inside a spreadsheet. We built Graphed to remove this friction entirely. Instead of pulling CSVs, we connect directly to your data sources like Shopify, Google Ads, and Salesforce. From there, you just ask a simple question in plain English, like "Show me our sales forecast for the next 6 months," and instantly get an interactive dashboard that stays up-to-date automatically.