How to create a sales forecast in Excel with AI

A sales forecast uses past sales data to estimate future revenue and support planning for inventory, staffing, and budgets. Excel has forecasting tools that look at past sales and create projections easily. Use the Forecast Sheet feature to build forecasts manually, or try Copilot in Excel as an AI-assistant to organize data and spot forecast patterns.

Learn how to build a sales forecast from past data and find trends with AI. Open a spreadsheet in Microsoft Excel to follow along.

Elements of sales forecasting

Gather the following information before starting a sales forecast.

Historical sales data

Past sales performance is the foundation for any forecast. Gather monthly or quarterly revenue figures to give the forecast enough data to identify patterns.

Time period

The timeframe being analyzed and forecasted affects the level of detail in the results. Monthly forecasts work well for short-term planning, while quarterly or yearly forecasts support longer-term budgeting and growth decisions.

Sales trends

Long-term growth, decline, or stability in sales performance shapes future expectations. A business growing 8% year-over-year will forecast differently than one with flat or declining revenue.

Seasonal patterns

Holiday spikes, back-to-school periods, and end-of-quarter rushes cause sales to rise and fall at predictable times. Include these patterns in the forecast to prepare for changes throughout the year.

Assumptions

Price increases, new products, market changes, or more competition can all shift future sales. A forecast that accounts for these assumptions gives a more realistic picture of what to expect.

Forecast value

Forecast values are estimated sales numbers for future periods. These values estimate revenue for the next 6, 12, or 24 months depending on business needs.

Confidence range

Best-case and worst-case forecast boundaries show a range of possible outcomes. For example, a forecast might project sales between $45,000 and $55,000, giving planners a realistic spread to work with. Use this range as a planning input to prepare for different outcomes.

Scenarios

Alternative forecast outcomes based on changing assumptions help prepare for different situations. Conservative, expected, and growth scenarios let businesses plan multiple possibilities.

Visualizations

Charts that compare past and projected performance make forecast results easier to understand. A forecast line chart showing historical sales alongside future projections helps communicate trends clearly.

Forecast accuracy

Compare forecasted numbers with actual results at the end of each month or quarter. Review these gaps over time to spot patterns and adjust assumptions for more accurate forecasts going forward.

With these elements in place, the next step is building the sales forecast in Excel.

How to generate a sales forecast using Forecast Sheet 

Forecast Sheet feature in Excel uses historical time-based data to generate projected values and a chart. Follow these steps to create a data-driven sales forecast from past performance.

1. Open Excel for the web and import historical sales data from existing spreadsheets, including dates and sales values.

2. Prepare and format the data as an Excel table so it can be analyzed.

3. Select the timeline and sales-value columns, then navigate to the Data tab, and apply the Forecast Sheet function.

4. Choose the chart and forecast end date, then select create.

5. Review the results and adjust the forecast period, assumptions, or visualizations as needed.

How to identify future sales trends with AI 

Trend-based forecasting uses historical growth or decline to estimate how sales could change in upcoming periods. This method helps businesses estimate future revenue based on historical data.

1. Open Excel and prepare the historical sales data, including multiple months, quarters, or years.

2. Open Copilot in Excel and ask AI to analyze the sales data in the chat.

3. Review the summary and data insights and compare it with the sales records to confirm.

4. Ask Copilot to identify patterns that can affect sales in the future.

5. Review the assumptions and projections before using them in business planning.

Utilize this simple Copilot prompt to find upcoming sales trends.

Try this Copilot prompt"Summarize the long-term sales trend and highlight periods of growth or decline."Click to copy this Copilot prompt

Learn more about essential Excel formulas that support sales forecast analysis and calculations.

How to create a seasonal sales forecast with AI

Seasonal sales forecasting accounts for recurring patterns such as holiday spikes, back-to-school periods, or end-of-quarter increases. While the FORECAST.ETS function can model seasonal patterns from historical data, Copilot in Excel is the easiest starting point for identifying recurring sales trends and exploring future demand.

This method helps retailers, tourism businesses, and subscription services prepare for predictable demand changes.

1. Open Excel and prepare historical sales data that includes multiple seasonal cycles, such as monthly or quarterly sales over several years.

2. Create a chart to identify recurring spikes, dips, or seasonal patterns in the data.

3. Use Copilot in Excel to analyze the data for seasonal trends.

4. Review the summary and compare the findings with sales records to confirm that the patterns are accurate.

5. Review the suggested trends, assumptions, and observations before using them to support inventory, staffing, marketing, or budgeting decisions.

Utilize this simple Copilot prompt in the chat to find seasonal sales trends and adjust inventory, staffing, or marketing plans accordingly.

Try this Copilot prompt"Identify any seasonal trends in this data and help analyze the reason for these patterns."Click to copy this Copilot prompt

Seasonal forecasts also support cash flow forecasting by helping businesses prepare for periods of higher or lower revenue.

Create a reusable sales forecast template

Building a reusable sales forecast template saves time and helps produce more consistent results across reporting periods.

• Forecast faster with a reusable framework: reuse a proven structure with pre-built formulas, charts, and layouts instead of creating forecasts from scratch each month or quarter. Update the sales data, refresh the calculations, and review the revised forecast before sharing it.

• Create more consistent forecasts: standardized inputs, calculations, and assumptions help teams compare results across months, quarters, or business units with confidence. A consistent format also makes it easier to spot changes over time.

• Respond quickly to changing business conditions: update sales data and assumptions in one place to generate new projections, scenarios, and reports when business priorities shift. A flexible template adapts to new information without rebuilding the entire forecast.

Ask Copilot to help create a reusable sales forecast template for the business. Describe the sales data structure, reporting period, and key metrics to include, and Copilot can suggest an outline. Review the suggested fields, formulas, and assumptions, then adapt the structure to the business’s reporting requirements.

A sales forecast turns historical performance into a practical guide for planning. With Forecast Sheet in Excel and Copilot, businesses can organize sales data, identify trends, generate projections, and explore different scenarios in one tool.

Explore creating more business spreadsheets in Excel, including budget templates and tools for analyzing spreadsheets with AI.