“Where do I start?” I get this question all the time about forecasting. If you’ve felt the same, you’re not alone.
We’re bombarded with posts about AI and ChatGPT promising the perfect forecast.
We feel the pressure to get it right.
When there’s confusion, I go back to basics and build from there: simple, solid foundations.
We start with Excel, then get as fancy as we want.
This article will help you build a solid, automated sales forecast in Excel and start improving accuracy tomorrow.
This is my how-to process, step by step.
1. Unify Your Data with Power Query
If you are like me, I don’t want to spend hours copying and pasting data from multiple sources: ERP exports, CSV files, John’s spreadsheet, Emma’s tracker, and who knows what else..
That’s where Power Query becomes indeed powerful.
In Excel → Data → Get Data, import your historical sales from all files into one table. Power Query refreshes automatically when new data comes in. No more broken formulas or manual merges.
Example:
You have monthly sales files for three regions: North, South, and East. Power Query lets you merge them into one clean table:
| Month | Region | Sales Units |
| Jan | North | 900 |
| Jan | South | 700 |
| Jan | East | 500 |
Tomorrow, when February data arrives, just click Refresh All and your forecast updates instantly. Being able to easily update your data is essential. It keeps your forecasts current and your time focused on analysis.
2. Build a Time Series Table
Next, structure your data chronologically. Put dates (daily, weekly, or monthly) in rows, and products or categories in columns.
Example:
| Month | Product A | Product B |
| Jan | 900 | 450 |
| Feb | 1,000 | 470 |
| Mar | 1,200 | 510 |
This setup is perfect for Excel’s forecasting functions and makes comparison between actual vs forecast effortless.
3. Leverage Advanced Statistical Functions
Now let Excel do the heavy lifting with FORECAST.ETS.
This function automatically detects seasonality so you can model growth and trends without coding.
Example formula:
=FORECAST.ETS(DATE(2025,4,1), B2:B4, A2:A4)
If your sales for Product A (Jan–Mar) are 900, 1,000, and 1,200, Excel will predict around 1,400 for April, recognizing the upward trend.
If your business has seasonal spikes (like ice cream in summer), Excel also captures peaks and troughs using FORECAST.ETS.SEASONALITY.
4. Try Weighted and Exponential Moving Averages
If your demand is more stable, try Weighted Moving Average (WMA) or Exponential Smoothing (EMA) to react faster to recent trends.
Example – 3-month weighted average (with weights 3, 2, 1):
This gives more weight to recent months and adjusts faster than a simple average.
Tip: Create a small helper table in Excel with your weights, so you can easily test different sensitivity levels.
5. Check Forecast Accuracy and Bias
Your forecast is only as good as its accuracy.
Measure WMAPE (Weighted Mean Absolute Percentage Error) and Bias to know how close your forecast is to reality.
Example:
| Month | Actual | Forecast | Error | % Error |
| Jan | 900 | 950 | -50 | 5.6% |
| Feb | 1,000 | 1,050 | -50 | 5.0% |
| Mar | 1,200 | 1,150 | +50 | 4.2% |
Bias shows whether you systematically over- or under-forecast.
A consistent positive bias means you’re under-forecasting and risking stockouts.
6. Create a “Scenario” Sheet for Adjustments
Markets shift. Promotions, price changes, or supply disruptions can impact future demand.
That’s why you need a Scenario Sheet—a small table to test “what-if” adjustments.
Example:
| Event | Adjustment |
| Promo boost | +10% |
| Supplier delay | -5% |
Link these cells to your forecast formula:
=BaseForecast*(1+Adjustment)
If your April forecast was 1,400 units and you expect a 10% promo lift, Excel will instantly update it to 1,540 units.
7. Build an Interactive Dashboard
Turn your data into insights with PivotTables, Slicers, and Charts.
Let stakeholders filter results by region, product, or date and visualize performance vs forecast in one view.
Example Dashboard Idea:
A line chart showing forecast vs actual sales per month, plus a slicer to select different product categories.
It makes your next S&OP meeting run faster.
Quick Win for Tomorrow
Try this tomorrow:
Open Excel → Data → Get Data → Load your past 12 months of sales → Apply FORECAST.ETS for the next 3 months → Calculate WMAPE.
In one day, you will have your first data-driven forecast, and a clear way to measure accuracy going forward.
Remember:
Excel already gives you all the tools you need. Start small tomorrow and watch how much smarter your forecast becomes.
