Is a flat daily plan good enough?

Eight weeks of daily orders with a strong weekly rhythm, forecast fourteen days ahead. The busiest day runs 59% above the quietest, and a flat daily plan is wrong on both of them every single week.

Operations Intermediate Forecasting free

Install free How forecasting works here

After you install, this is the model to open.

The Season Repeats: Forecast It

In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
Click Start from a template and put that name in the search box.
Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.

The answer

Sunday: 591 orders forecast, the weekly peak
Thursday: 371 the trough; Sunday runs 59% above it
Fitted error: 2.1% mean absolute percentage error
Pickers: 8.1 to 12.8 where a flat plan puts 8.9 on every day

Fifty-six days of orders from a mid-sized online retailer, starting on a Monday, with the day of the week beside each reading so you can see the rhythm before the tool touches it. Click Run at season length 7 and horizon 14. The forecast comes back day by day: 524.2 next Monday, then 474.6, 392.4, 371.1 on Thursday, 403.8, 504.5, and 590.9 on Sunday, with 95% bounds on every one, the Monday running 504 to 544.

That week totals 3,261 orders against 3,175 in the week just gone, so the underlying business is still growing about 2.7% a week. The mean absolute percentage error over the fitted period is 2.1%, which is the figure to quote when someone asks how much to trust it. The shape is the point rather than the total. Sunday at 590.9 runs 59% above Thursday at 371.1, and the staffing block turns that into people: a flat plan built on the 56-day average of 409.5 orders puts 8.9 pickers on every day, where the forecast asks for 8.1 on Thursday and 12.8 on Sunday.

Plan flat and you pay for most of a surplus picker on Thursday and run four short on Sunday, every week, and the annual total will look perfectly accurate while both of those happen. That is what separating level, trend and season buys you, and it is what a single growth percentage can never give you. Notice where the confidence bounds go: the Monday two weeks out is 517 to 557, the same width as the Monday next week, because the fitted smoothing carries almost none of each shock forward.

Use the far end of the horizon for shape and the near end for staffing. Rerun with season length set to 0 and the same horizon: the seasonal term disappears, the forecast becomes a straight line that simply carries the last few days forward, and the fitted error goes from 2.1% to 14.3%, which is the cleanest demonstration available that the weekly pattern is real and not decoration.

To use your own data, paste one column of daily orders in date order, keep season length at 7 for daily data or set it to 12 for monthly, and widen the range.

The model

It arrives on a tab called Template: Forecast the Weekly Rhythm, carrying these columns:

Day
Orders shipped
Day of week
Value

with the model computed beside the data:

Average orders a day, last eight weeks	409.5
Pickers a flat plan puts on every day	8.903
Pickers the forecast asks for on Thursday	8.067
Pickers the forecast asks for on Sunday	12.85

Once it is in your sheet

The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.

Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.

Next question

Every model like this one, and the method behind them: Forecasting in Google Sheets.

Install free All 379 templates