Are bookings growing, or is that the zigzag?
Paste the bookings for each week and get back the smoothed trend and how much the window changes it. Twenty-six weeks of confirmed bookings that zigzag every other week. A four-week average turns them into a line that rises every single week, a three-week average does not, and the reason is a lesson about your data rather than about the tool.
Operations Starter Forecasting free
After you install, this is the model to open.
Smooth the Booking Curve
- 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
- The trend, smoothed
- 139 to 203 the four-week average, first window to last, never falling
- Growth across the half year
- +45% first four weeks 555 bookings, last four 802
- The zigzag it removed
- ±18 the average gap between a raw week and the curve
Twenty-six weeks of confirmed bookings, and the raw column is unreadable: it goes 142, 118, 161, 134, up and down every single week, because the corporate clients book in the week their purchase orders clear. The Pay run column is where that rhythm comes from. Click Run at an interval of 4. The smoothed column rises in every one of the twenty-three weeks it covers, from 138.75 to 203.00, with no reversal anywhere, and the first four weeks against the last four say the business grew about 46% across the half year.
Now run it again at an interval of 3 and watch the answer fall apart: the last six smoothed values come back 197.00, 184.33, 199.33, 196.00, 209.67 and 196.00, and twelve of the twenty-four smoothed values are lower than the one before them. A three-week window always contains either two high weeks or two low ones, so it carries the fortnightly rhythm straight through.
A four-week window contains exactly two of each, every time, and cancels it. The interval is not a smoothness dial, it is a statement about the cycle in your data, and the right one is a whole multiple of that cycle. There is a counterintuitive number in the report worth understanding before you quote it. The standard error on the last line is 19.41 at an interval of 4 and 8.42 at an interval of 3, so the window that gives you the honest trend scores worse.
Standard error here measures how far the smoothed line sits from the raw points, not how useful it is, and a jagged line hugs jagged data better by construction. If you are forecasting next week for a rota, the tighter window wins. If you are deciding whether the season is growing, the wider one does. Neither is smoothing; both are choices.
What a moving average cannot do is see a turn coming, because it lags by roughly half the window, so at an interval of 4 it is telling you about two weeks ago. To use your own data, paste one column of bookings in time order under the Confirmed bookings heading and widen the input range.
The model
It arrives on a tab called Template: Smooth the Booking Curve, carrying these columns:
- Week ending
- Confirmed bookings (count)
- Pay run
with the model computed beside the data:
| Bookings in the last four weeks | 812 |
| Growth across the half year | 0.4631 |
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
- Can you spot the bad batch before inspection does?Predict the Defect Before It Ships
- Does the new capacity pay if growth stops?Should We Add the Second Line?
- How often does this plan beat the deadline?Will We Be Ready for Peak?
- Could your sample keep checking the same head?Pull the Units to Inspect
- What repeats in your numbers that nobody planned?Find the Cycle You Did Not Know Was There
- Should you buy more than you expect to sell?How Much Stock, When Demand Is a Guess
Every model like this one, and the method behind them: Forecasting in Google Sheets.