How to Forecast Sales in Excel with ChatGPT

Forecasting your future sales in Excel can feel like you're trying to predict the weather with nothing but a spreadsheet and a gut feeling. But what if you had a data-savvy assistant to guide you through the process? This guide will show you how to use ChatGPT to build a credible sales forecast in Excel, step-by-step, taking the guesswork out of planning for your business's 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

What is a Sales Forecast (and Why Care)?

A sales forecast is simply an educated prediction of your future revenue over a specific period - like the next month, quarter, or year. It's more than just a number on a page, it's a critical tool that informs major business decisions. A good forecast helps you manage inventory, plan your staffing needs, allocate your marketing budget, and set realistic growth targets.

There are many ways to forecast, from looking at historical trends to analyzing your current sales pipeline. We'll focus on a popular and effective method: using your past performance data to project future results, with a little help from AI to get the methodology and formulas right.

Step 1: Gather and Prepare Your Historical Data in Excel

Before you can ask ChatGPT for help, you need to give it something to work with. The foundation of any good forecast is clean, reliable historical data. If your data is a mess, your forecast will be too - a classic "garbage in, garbage out" scenario.

Start by collecting your sales data from whichever platform you use, whether it's Shopify, Stripe, Salesforce, or your own point-of-sale system. Export at least 12-24 months of data to capture any seasonal patterns.

Organize Your Data in a Simple Table

Once you have your raw data, create a simple and clean table in Excel. All you need are two columns:

• Column A: Date (e.g., the first day of each month)
• Column B: Revenue (or Units Sold, depending on what you want to forecast)

Here are a few critical tips for preparing your data:

• Keep It Consistent: If you're forecasting monthly, make sure every row represents one full month. Don't mix weekly and monthly data.
• Format a Date: Select your date column, go to "Format Cells," and choose the "Date" category. This is essential for Excel to understand your time series.
• Format Numbers as Numbers: Ensure your revenue column is formatted as "Currency" or "Number" and has no stray text characters.
• Address Gaps: If you have a month with zero sales, enter a "0." Don't leave the cell blank, as it can skew your calculations.

Your finished table should look something like this:

This clean, simple format is exactly what you need to start building your 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 2: Use ChatGPT as Your Forecasting Strategist

Now that your data is ready, it's time to leverage ChatGPT. Think of it not as a tool that will do the work for you, but as an expert strategist you can consult. You'll describe your data and your goal, and it will recommend the best approach and provide the specific formulas you need.

First, let's ask it for a method. You don't need to be a data scientist to come up with a good prompt. Just be clear and provide context.

Example Prompt for ChatGPT: "I am creating a sales forecast in Microsoft Excel. I have a table with two columns: Column A contains the first day of each month for the last 24 months, and Column B contains the total revenue for each month. My business is seasonal, with higher sales around the holidays. What's a simple but effective forecasting method I can use in Excel to predict sales for the next 6 months?"

ChatGPT will likely suggest a few options, but a common and powerful recommendation for this scenario is using a linear trendline with a seasonal adjustment. In plain English, this means:

1. Linear Trend: We'll calculate the general upward (or downward) growth trend based on your historical data.
2. Seasonal Adjustment: We'll then adjust that simple trend to account for the predictable peaks and valleys that happen each year (like a Black Friday rush or a slow summer).

This two-part method is much more accurate than a simple average because it understands that your business has both long-term momentum and short-term, cyclical patterns.

Step 3: Build the Forecast in Excel with ChatGPT-Generated Formulas

With our strategy decided, let's get ChatGPT to give us the exact formulas to plug into our spreadsheet. We'll do this piece-by-piece.

Calculating the Linear Trend Forecast

First, we'll project the basic trend forward. To do this, you'll need to add the dates for the future months you want to forecast at the bottom of your Date column (e.g., the next six months).

Example Prompt for a Formula: "Great, let's do a linear trend forecast. My historical dates are in Excel cells A2:A25, and my historical revenue is in B2:B25. I have added the first day of the next six months I want to forecast in cells A26:A31. What is the Excel formula I should use in cell B26 to forecast the revenue for the date in A26? I need a formula I can drag down to the other forecast cells."

ChatGPT will return a formula using Excel’s FORECAST.LINEAR function. It should look like this:

=FORECAST.LINEAR(A26, $B$2:$B$25, $A$2:$A$25)

How to use it:

1. Paste this formula into cell B26 (the first empty cell in your revenue column).
2. Let's quickly break it down:
3. Click on the small square at the bottom-right corner of cell B26 and drag it down to fill cells B27 through B31.

You now have a simple forecast! However, it probably looks like a perfectly straight line, which doesn’t account for your busy and slow seasons. Next, we’ll fix that.

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

Adjusting for Seasonality

To adjust for seasonality, we need to figure out how much a typical month deviates from the average. For example, your December sales might consistently be 150% of your average month, while your February sales are only 80%.

This can get complex, but we can have ChatGPT guide us through a straightforward approach: creating a "seasonal index."

Example Prompt for a Seasonality Formula: "Now I need to adjust this linear forecast for seasonality. I have 24 months of historical data (two full years). How can I create a seasonal index in Excel for each month (Jan-Dec) and apply it to my forecast?"

ChatGPT will guide you through a common method. Here's a simplified version you can follow:

1. Calculate the average for each month. In a new column (say, Column D), find the average sales for each individual month. For example, to find the average of all your "January" sales over the past two years, you could use the AVERAGEIF function:

=AVERAGEIF($A$2:$A$25, ("="&A2), $B$2:$B$25)

This formula looks at the date in A2 (January), finds all matching months in your date range, and averages their revenue.

2. Calculate the overall average. Somewhere off to the side, calculate the average of all of your historical sales revenue:

=AVERAGE(B2:B25)

3. Calculate the Seasonal Index. The index is the monthly average divided by the overall average. So, if January's average is $10,000 and the overall monthly average is $12,000, your index for January is 10000 / 12000 = 0.83. This means January sales are typically 83% of an average month.

4. Create a final, adjusted forecast. Finally, in a new column, multiply your linear forecast by the seasonal index for the corresponding month. If your first forecast value is in cell B26, and your January seasonal index is 0.83, your adjusted forecast would be =B26 * 0.83.

Your seasonally adjusted forecast will now follow the general growth trend but with realistic peaks and troughs baked in.

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 4: Visualize Your Sales Forecast

A forecast is much easier to understand and present when you can see it. A line chart is perfect for this. Once again, you can ask ChatGPT for quick instructions.

Example Prompt for Visualization: "How do I create a line chart in Excel that shows both my historical revenue and my new, seasonally-adjusted forecasted revenue on the same graph?"

Following ChatGPT’s simple instructions:

1. Select all three columns: Date, Historical Revenue, and your Adjusted Forecast.
2. Go to the Insert tab in Excel's ribbon.
3. Click on Line Chart and choose the first 2-D line option.

Excel will instantly generate a chart that visually separates your past performance from your future predictions. For extra clarity, you can right-click the forecast line, select "Format Data Series," and change its color or make it a dotted line to distinguish it from historical data.

Important Considerations and Limitations

Using ChatGPT as a forecasting partner is incredibly powerful, but it's a tool, not a crystal ball. Keep these points in mind:

• Sanity-Check Everything: Does the final forecast look realistic to you? You know your business better than any AI. If numbers look wildly off, review your formulas and historical data.
• Include Qualitative Knowledge: Your forecast is based entirely on the past. It knows nothing about the huge marketing campaign you're launching next month or the new competitor opening up across the street. Adjust your forecast manually to account for future events.
• Remember ChatGPT Can't See Your Data: You are describing your spreadsheet to ChatGPT. The accuracy of its suggestions depends entirely on how clearly you describe your data structure. Also, avoid uploading any spreadsheets with sensitive customer or financial data directly into any AI chat tool for privacy reasons.

Final Thoughts

By blending the computational power of Excel with the instructive guidance of ChatGPT, you can move from guesswork to a data-informed sales forecast. You can build a reliable plan for your business by asking clear questions, providing good data, and using AI to generate the exact methods and formulas you need.

While asking for formulas and pasting them into Excel is a major step up from doing it all manually, it still requires jumping back and forth and organizing CSV files. With Graphed, we've found a way to automate this entire process. You simply connect your data sources like Shopify or Google Ads once, and then ask for your projections in conversational language, like "Forecast our sales for the next quarter, applying last year's seasonality." We instantly create a live, interactive dashboard that updates automatically, helping you move from manual data wrangling to making real-time decisions.