How to Forecast Revenue in Excel
Building a revenue forecast doesn't have to feel like a shot in the dark. With a bit of historical data and the right tools in Excel, you can create a reliable prediction that helps you plan your budget, set realistic goals, and make smarter business decisions. This guide will walk you through a few practical methods for forecasting your revenue in Excel, from simple averages to more advanced, automated techniques.
First, Why Should You Forecast Revenue?
A revenue forecast is more than just a number on a spreadsheet, it’s a critical tool for strategic planning. A solid forecast helps you:
Manage Your Budget: Knowing your projected income lets you allocate resources effectively for marketing, hiring, and new projects without overspending.
Set Achievable Goals: You can set sales targets and marketing KPIs more confidently when they’re based on data, not a hunch.
Secure Funding: If you're looking for investors or a business loan, a well-researched revenue forecast shows them you have a credible plan for growth.
Identify Potential Issues: If your forecast shows a dip in revenue in the coming months, you have time to figure out why and take corrective action now.
Step 1: Gather and Organize Your Historical Data
Your forecast is only as good as the data you put into it. Before you open Excel, you need to collect your historical sales data. The goal is to get at least 12-24 months of data to account for any seasonality, but work with what you have.
Your data could come from several places:
E-commerce Platforms: Shopify, BigCommerce, or WooCommerce.
Payment Processors: Stripe, PayPal, or Square.
CRMs: Salesforce or HubSpot.
Accounting Software: QuickBooks or Xero.
Your own spreadsheets: Many businesses start by tracking sales manually.
Once you’ve collected it, organize your data in a simple two-column format in Excel. Column A should have the date (e.g., month-by-month), and Column B should have the corresponding revenue.
Here’s a simple example setup:
A | B |
Month | Revenue |
------- | --------- |
Jan-23 | $10,500 |
Feb-23 | $9,800 |
Mar-23 | $11,200 |
Apr-23 | $12,100 |
... | ... |
Step 2: Choose Your Forecasting Method in Excel
Excel offers several ways to forecast future values. We’ll cover three popular methods, starting with the simplest and moving to more robust options.
Method 1: Simple Moving Average
A moving average smooths out short-term fluctuations in your data to help you see the general trend. It works by calculating the average revenue over a specific number of recent periods - for example, the last three months.
When to use it: This method works best for businesses with relatively stable revenue that don’t experience dramatic growth or seasonality. It’s a good starting point for a basic forecast.
How to calculate a 3-month moving average:
Set up your data as described above. In our example, revenue numbers are in column B, starting from cell B2.
Click into the cell next to your third data point (cell C4). This is the first period for which you can calculate a 3-month average.
Enter the following formula:
=AVERAGE(B2:B4)
This formula calculates the average of the revenue from January, February, and March.
Drag the fill handle (the small square in the bottom-right corner of the cell) down to apply this formula to the rest of your data. Each new cell will calculate the average of the previous three months.
Your first forecasted amount will be the value in the last calculated cell. It assumes the next month will perform similarly to the average of the last three.
Pros: Very simple to implement and understand.Cons: Lags behind trends. If your sales are suddenly increasing, the moving average won't reflect that growth quickly.
Method 2: Using the FORECAST.LINEAR Function
This function works by performing linear regression. In plain English, it finds the "best-fit" straight line through your historical data points and extends that line into the future. It's more sophisticated than a simple average because it accounts for a consistent rate of growth or decline.
When to use it: Great for businesses that have a clear upward or downward trend in their revenue data over time.
How to use FORECAST.LINEAR:
The function structure is: =FORECAST.LINEAR(x, known_y's, known_x's)
x: The future date you want to forecast the value for.
known_y's: Your historical revenue data (Column B).
known_x's: Your historical date data (Column A).
Add the future months you want to forecast in Column A. For example, if your data ends in Dec-24, add Jan-25, Feb-25, and so on in the rows below.
In the corresponding cell in Column C (or a new "Forecast" column), enter the formula. If your historical dates are in A2:A13 and revenues are in B2:B13, and you're forecasting for the date in cell A14 (Jan-25), the formula would be:
=FORECAST.LINEAR(A14, $B$2:$B$13, $A$2:$A$13)
Note: The dollar signs ($) lock the data ranges, so they don’t shift when you drag the formula down.
Drag the formula down to get forecasts for all your future months.
Pros: Accounts for trends in your data, making it more predictive than a moving average.Cons: It assumes growth will continue in a straight line forever, which isn't always realistic. It doesn't account for seasonality.
Method 3: Excel's Built-In Forecast Sheet
For a more powerful and automated approach, you can use Excel’s “Forecast Sheet” feature. This tool automatically analyzes your time-series data, finds the best forecasting model (including accounting for seasonality), and generates a chart and data table complete with confidence intervals.
When to use it: This is the best option for most users. It’s perfect if your data has seasonal peaks and troughs (e.g., higher sales in Q4 for the holidays) and you want a statistically sound forecast with minimal manual effort.
How to create a Forecast Sheet:
Select your two columns of data (Month and Revenue).
Navigate to the Data tab on the Ribbon.
In the "Forecast" group, click on Forecast Sheet.
A pop-up window will appear showing a preview of your forecast. You can adjust a few options here:
Forecast End: Choose how far into the future you want to predict.
Confidence Interval: This adds upper and lower bound lines to your forecast, showing a range of likely outcomes. A 95% confidence interval is standard.
Seasonality: Excel will usually detect this automatically, but you can set it manually if you know your business cycle (e.g., every 12 months).
Click Create.
Excel will instantly generate a new sheet with a detailed table and a line chart. The chart will show your historical data, the forecasted revenue, and the upper/lower confidence bounds. This gives you a clear, professional visual representation of your business's likely future performance.
Tips for a Better Forecast
Executing the formulas is only part of the process. To make your forecast as accurate as possible, keep the following in mind:
Keep Your Data Clean: Ensure there are no typos, missing months, or duplicate entries. Bad data leads to a bad forecast.
Combine Formulas with Business Context: A formula doesn't know you're launching a major marketing campaign next quarter or that a big client is ending their contract. Adjust your forecast based on your business intelligence. Consider creating "Best Case," "Worst Case," and "Most Likely" scenarios.
Review and Revise Regularly: A forecast is a living document. Review it monthly or quarterly to compare your actual performance against your predictions. This will help you understand what you got right or wrong and refine your future forecasts.
Final Thoughts
Forecasting in Excel moves you from reactive guessing to proactive planning. Whether you're using a simple moving average or the automated Forecast Sheet, having a data-backed view of the future empowers you to make smarter decisions about steering your business for growth.
While Excel is a fantastic tool, we know that getting all your data from Shopify, Google Ads, Salesforce, and a dozen other platforms into a clean, ready-to-use spreadsheet is often the most time-consuming part. We built Graphed to automate that entire process. Imagine connecting your data sources once and then simply asking in plain English, "Show me a revenue forecast for the next 6 months," and getting a live, automated dashboard instantly. You can get back hours of your week and spend your time acting on insights, not wrangling CSV files.