How do you know a forecast method actually works?

A hundred and four weeks of Poisson demand from a seed, with trend and season added by formula. Because you built the answer, you can check whether the forecast finds it, which is the only way to know a forecasting method works.

Words on this sheet

  • Seed: The starting number for the random draws. The same seed gives the same draws every time.
  • Holt-winters: A forecasting method that tracks a level, a trend and a repeating season, and tunes its own settings.

Analytics Intermediate Random Number Generation free

Install free (opens the Google Workspace Marketplace in a new tab) All 38 statistics tools

After you install, this is the model to open.

Make a Demand Series to Test a Model

  1. 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.
  2. Click Start from a template and put that name in the search box.
  3. 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

Same seed, same series
104 of 104 draws match the sheet exactly
Sample mean
41.94 on the lambda of 42 you typed
Sample spread
6.23 theory says 6.48: two years of draws land just under it
Range drawn
30 to 60 orders a week

Testing a forecast on real data has a hole in it: you never find out what the right answer was. This template closes that hole by building a series whose truth you wrote down. The Poisson draw column already holds 104 values, and the first thing to do is prove where they came from. Set the distribution to Poisson with a lambda of 42, ask for 104 values with a seed of 2026, and click Run.

The tool writes its own tab and reports 104 values averaging 41.94 and spanning 30 to 60, and they are the same 104 numbers, in the same order, that are already sitting in this one. That is what a seed is for. The three formula columns then turn the raw draws into a series with 0.4% weekly growth, which compounds to about 41% across the two years, and a 52-week season of plus or minus 18% on top.

Poisson is the right shape for a count of things that arrive independently, which is what weekly orders for a slow-moving line are, and its spread is not a separate setting: the standard deviation of a Poisson is the square root of its mean, so a lambda of 42 implies about 6.5 and this particular draw came out at about 6.2. That gap is itself worth noticing, because a hundred and four weeks does not reproduce its own parameters exactly, and neither does your real data.

Now do the thing the template exists for. With Holt-Winters selected, point it at the Synthetic demand column, set the season length to 52 and the horizon to 8, click Run, and compare what it recovers against the truth block beside the data. You know the growth rate is 0.4% a week and the seasonal swing is 18%, so you can see how close the fit gets and, more usefully, how much data it needed to get there: rerun on the first fifty-two weeks only and the seasonal term has one cycle to learn from and gets visibly worse.

That is how you find out what your forecasting method can and cannot do before you trust it with a purchase order. Change one thing at a time. Raise the seasonal amplitude to 0.40 and the season becomes easy to find; drop it to 0.05 and watch the fit lose it in the noise. Generate a fresh set of draws with a different seed and paste them over the Poisson draw column when you want another sample of the same process.

What synthetic data cannot do is contain the thing you did not think to put in it: no promotions, no stockouts, no new entrant. A model that handles this series is not proven, only not yet disproven. To use it for your own line, set the lambda to your average weekly demand and the two factors in the truth block to what you believe your growth and season really are.

The model

It arrives on a tab called Template: Build a Series to Test a Model, carrying these columns:

  • Week
  • Poisson draw (units/week)
  • Synthetic demand (units/week)
  • What we built in
  • Value

Once it is in your sheet

  1. The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
  2. 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.
  3. 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: Statistics in Google Sheets.

Install free (opens the Google Workspace Marketplace in a new tab) All 379 templates