How to Forecast Revenue in Excel with ChatGPT

Creating a revenue forecast in Excel often feels like a tedious formula-writing exercise, but it doesn't have to be. By combining the power of Excel with the conversational intelligence of ChatGPT, you can build an accurate forecasting model without manually looking up and building complex formulas. This article will show you how to prepare your data, prompt ChatGPT for the right functions, and create a visual revenue forecast to help you make smarter business decisions.

What is Revenue Forecasting, Anyway?

Revenue forecasting is the process of predicting future income based on historical data, market trends, and business knowledge. It's not about gazing into a crystal ball, it's about making an educated guess to help guide your strategy. A solid forecast helps with:

Budgeting & Resource Allocation: Know how much money you can safely spend on hiring, marketing, or new equipment.
Setting realistic goals: Create sales targets and KPIs that are ambitious yet achievable.
Securing Investments: Show potential investors or lenders a clear picture of your company's growth potential.
Strategic Planning: Make informed decisions about pricing, product development, and market expansion.

In short, it’s the financial roadmap for your business. And with the right tools, it's easier to create one than you might think.

Step 1: Gather and Structure Your Historical Data in Excel

Before you can forecast the future, you need a clear record of the past. The quality of your forecast depends entirely on the quality of your source data. For most businesses, this means pulling your historical revenue data for at least the last 24 to 36 months.

The key here is consistency. Your data should be collected at regular intervals—monthly is the most common and generally the most useful, but quarterly or weekly can also work depending on your business model.

Start a new Excel sheet and structure your data in two simple columns:

Column A: Date. Enter the first day of each month (e.g., 1/1/2022, 2/1/2022, 3/1/2022). Excel recognizes this format and can use it for calculations and charts.
Column B: Revenue. Enter the total revenue generated during that month.

Make sure this data is clean and complete. Missing months or typos will throw off your entire forecast. Double-check your numbers before moving on.

Step 2: Use ChatGPT to Generate Your Forecasting Formula

Now for the fun part. Instead of digging through Excel's function library, you can simply ask ChatGPT to generate the right formula for your exact spreadsheet layout. We’ll use one of Excel's most effective and straightforward forecasting functions: `FORECAST.LINEAR `.

This function calculates, or predicts, a future value along a linear trend line by using existing historical values. In simple terms, it looks at your past revenue, draws a "line of best fit" through it, and extends that line into the future.

Prompting ChatGPT for the Formula

Go to ChatGPT and write a clear, specific prompt. Providing context about your spreadsheet structure is the secret to getting a perfect, copy-and-paste-ready formula.

Let's say your historical data runs from cell A2 to B25 (24 months) and you want to forecast the next month’s revenue, which you've set up in row 26.

Here’s a great prompt to use:

“I have historical monthly revenue data in an Excel sheet. My dates are in cells A2:A25, and the matching monthly revenue is in cells B2:B25. I want to forecast the revenue for the future date in cell A26. Can you give me the Excel formula to do this using FORECAST.LINEAR?”

ChatGPT will likely return an answer like this:

Let's quickly break down what that formula means:

A26: This is the date you want to predict the revenue for (the 'x' value).
B2:B25: This is your range of known historical revenue data (the 'known_y's').
A2:A25: This is your range of historical dates (the 'known_x's').

Simply copy that formula from ChatGPT and paste it into cell B26 in your Excel sheet. Excel will instantly calculate your forecasted revenue for that first future month.

Extending the Forecast Over Multiple Months

You probably want to forecast more than just one month ahead. You could just drag the formula handle down from cell B26, but you’ll run into a problem. Excel will automatically shift the reference ranges (e.g., B2:B25 will become B3:B26), which will mess up your calculations.

Again, you can ask ChatGPT to solve this for you. Let's ask it to modify the formula with absolute references, which "lock" the ranges in place.

Our refined prompt:

“That worked perfectly, thanks! Now, I want to drag this formula down from B26 to B37 to forecast the next 12 months. How do I modify the formula to use absolute references for my historical data ranges so they don't change when I drag it down?”

ChatGPT will return the updated formula with dollar signs ($) added:

The $ tells Excel to always refer back to that exact block of cells (B2:B25 and A2:A25), no matter where you copy or drag the formula. Now, when you paste this into B26 and drag the corner handle down, it will correctly calculate the forecast for each subsequent month.

Step 3: Visualize Your Forecast with a Chart

Numbers in a spreadsheet are useful, but a visual chart makes your forecast much easier to understand at a glance. You can ask ChatGPT for the step-by-step instructions to create one.

A good prompt for this would be:

"How do I create a line chart in Excel to visualize this forecast? I want to show both my historical revenue (B2:B25) and my forecasted revenue (B26:B37) on the same chart, with the dates from column A as the horizontal axis."

ChatGPT will break down the process:

Select Your Data: Highlight all three columns: your dates, historical revenue, and forecasted revenue. Include the column headers.
Insert Chart: Go to the "Insert" tab in Excel's ribbon. In the "Charts" section, click on "Recommended Charts." Excel will often suggest a line chart as the best option. If not, go to the "All Charts" tab and select "Line."
Format Your Chart: Give your chart a clear title, like "Monthly Revenue: Actual vs. Forecast." It's also a good practice to format the forecast line differently to distinguish it from actuals. You can right-click the forecast line segment, select "Format Data Series," and change its color or make it a dashed line.

Step 4: Go Deeper with Scenario Planning

A single forecast represents your "most likely" outcome, but what if conditions change? The real power of forecasting comes from scenario planning—creating best-case and worst-case versions of your forecast.

Let's ask ChatGPT to help us build these out.

Prompt for scenario planning:

“Next to my forecast in column B, I want to create a 'Best Case' forecast in column C and a 'Worst Case' forecast in column D. The 'Best Case' should be 20% higher than my original forecast, and the 'Worst Case' should be 15% lower. What are the formulas for cells C26 and D26?”

ChatGPT will provide simple formulas:

For the Best Case (in cell C26): =B26*1.20
For the Worst Case (in cell D26): =B26*0.85

Enter these into the corresponding cells and drag them down for all your forecasted months. Then you can easily add these new data series to your chart to visualize the range of potential outcomes.

Final Thoughts

By pairing Excel's calculation power with ChatGPT's ability to instantly generate formulas and instructions, you can produce a valuable revenue forecast in minutes, not hours. This method removes the friction of remembering syntax and lets you focus on what the data is telling you, making data analysis much more approachable for anyone on your team.

While this workflow is fantastic for building a specific report, the biggest challenge is keeping it updated with live data from all your different platforms. Instead of manually downloading CSVs every month from Shopify, Google Analytics, or Salesforce to update your spreadsheet, we designed Graphed to automate this entire process. You can connect your data sources once and then simply ask questions in plain English, like "Show me a dashboard forecasting our Shopify revenue for the next 6 months based on our marketing spend," and Graphed builds a live, interactive dashboard for you in seconds.