Skip to content
← Back to glossary

Forecasting Using Excel

Forecasting in Excel uses spreadsheet formulas, tables, and charts to estimate future demand from historical data. It can work well for a small operation with a stable demand pattern, one model owner, and a planning cycle that does not need frequent automatic updates.

The spreadsheet should separate raw actuals, cleaned history, assumptions, the baseline forecast, manual adjustments, and accuracy results. This structure makes the model easier to audit and reduces the risk that a copied formula or overwritten value silently changes the forecast.

How to build a demand forecast in Excel

  1. Put one demand measure and one time interval in each row. Keep dates, queues, channels, and locations in separate columns.
  2. Mark missing data, outages, one-time events, and other anomalies. Do not delete real peaks that the operation may see again.
  3. Create a simple baseline, such as the average of comparable prior periods or a seasonal moving average.
  4. Keep event and management adjustments in separate columns with an owner and reason.
  5. Hold back recent periods for testing. Compare each method on data it did not use to fit the forecast.
  6. Track WAPE and bias, then use the method that performs best at the interval used for staffing.

Basic spreadsheet formulas

A simple baseline can use: forecast = average demand from comparable periods ร— seasonal index + known event adjustment. Staffing then uses: workload hours = forecast demand ร— average task minutes รท 60. Add occupancy, shrinkage, service targets, and skills before the result becomes a staff schedule.

Why this matters for planners and team leads

Excel is useful because planners can inspect every assumption and start without a software project. It becomes risky when several people edit copies, data must refresh every day, many queues need separate models, or the workbook connects to staffing and schedule decisions at short intervals.

Move to forecasting software when maintaining the workbook takes more time than reviewing the forecast, when formula errors are hard to detect, or when teams cannot reproduce which data and assumptions created the published plan.

Example in practice

A team forecasts Monday ticket demand with the average of the previous eight comparable Mondays. It keeps holiday weeks out of the normal baseline, then adds a separate adjustment for a planned campaign. The workbook compares the result with actual tickets and calculates WAPE and bias.

This is manageable for one queue. If the team must repeat the process for many queues, 15-minute intervals, several seasonal patterns, and daily updates, automatic data connections and model backtesting become more reliable than copied spreadsheet tabs.

Frequently asked questions

When is Excel good enough for forecasting?
For a small operation with a stable demand pattern, one model owner and a planning cycle that does not need frequent automatic updates. Those three conditions matter more than the size of the spreadsheet.
How should the workbook be structured?
Separate raw actuals, cleaned history, assumptions, the baseline forecast, manual adjustments and accuracy results into distinct areas. That structure makes the model auditable and stops a copied formula or overwritten value from silently changing the forecast.
How should the data be laid out?
One demand measure and one time interval per row, with dates, queues, channels and locations in separate columns. A layout built for reading rather than for calculation is the usual reason a spreadsheet model cannot be extended later.
Where should manual adjustments live?
In their own columns, each with an owner and a reason. Adjustments buried in the baseline cannot be reviewed, and nobody can tell afterwards whether the error came from the method or from an override.
How do you test a spreadsheet forecast?
Hold back recent periods, compare each method on data it did not use to fit, and track WAPE and bias. Then use whichever performs best at the interval actually used for staffing, which is often not the interval that looks best annually.
What are the limits of forecasting in Excel?
It depends on one owner, it does not update itself, and it degrades as queues, channels and intervals multiply. The failure is usually organisational rather than mathematical: the model works and only one person can safely change it.

Put this into practice

See how Soon handles forecasting using excel in your shift scheduling workflow.

See the product
Workforce forecasting softwareHow to forecast staffing needs