What repeats in your numbers that nobody planned?
Sixty-four weeks of sessions with two cycles running at once. The frequency spectrum separates them cleanly, and the autocorrelation function on exactly the same data reports that one of them does not exist.
Operations Advanced Statistics free
After you install, this is the model to open.
Find the Cycle You Did Not Know Was There
- 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 loud cycle
- 16 weeks frequency 4 at magnitude 19,860
- The hidden one
- 4 weeks frequency 16 at 7,644, running underneath the big wave
- The noise floor
- 2,316 the next tallest bar; both cycles stand well clear
- Why you missed it
- r = 0.076 autocorrelation at lag 4 sits inside its band; Fourier still finds it
Sixty-four weeks of sessions. Click Run: the report is one row per frequency, and a tall magnitude at frequency k means a cycle that repeats every 64 divided by k weeks. Frequency 0 comes back at 268,808 and is not a cycle at all, it is the average level, so ignore it every time. After that, two bars stand out: frequency 4 at 19,860 and frequency 16 at 7,644.
Sixty-four over four is sixteen weeks and sixty-four over sixteen is four weeks. There are two cycles running at once, a strong sixteen-week rhythm and a weaker four-week one, and the next largest bar after those is 2,316, less than a third of the smaller of the two, which is the noise floor. Now run Autocorrelation on the same column with a maximum lag of 20 and read what it says.
Lag 16 comes back 0.735, well outside its plus or minus 0.245 band, so the sixteen-week cycle is found. Lag 4 comes back 0.076, inside the band. On the evidence of the autocorrelation function there is no four-week cycle in this data, and the frequency spectrum says there plainly is. Both tools are working correctly and the disagreement is the lesson.
Autocorrelation asks how much a series resembles itself at one lag at a time, and at a lag of four the sixteen-week wave has moved a quarter of a turn and works against the match, so the four-week signal is left competing against everything else in the series and loses. Fourier does not compare the series to itself; it decomposes the whole series into cycles at once, so a weak rhythm sitting underneath a strong one still gets its own bar.
Use autocorrelation when you expect one dominant cycle and want a quick answer. Use Fourier when you suspect more than one, or when autocorrelation has come back empty and you do not believe it. The What was running column is where the finding becomes a decision: the four-week cycle is the email cadence and it is on the calendar, and nothing on the calendar repeats every sixteen weeks.
That is a rhythm the business is producing without meaning to, and it is worth a meeting. Two limits. Fourier cannot handle a trend, which shows up as energy smeared across the low frequencies, so subtract a fitted line first if your series is climbing. And the point count has to be a power of two, 16, 32, 64, 128 and so on up to 4096, so trim or pad your own series to one of those before you point the input range at it.
The model
It arrives on a tab called Template: Find the Hidden Cycle, carrying these columns:
- Week
- Sessions (count)
- What was running
- How to read the output
- A tall bar at frequency k means a cycle repeating every 64/k weeks
with the model computed beside the data:
| Period the frequency below implies (weeks) | 16 |
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
- Should you buy more than you expect to sell?How Much Stock, When Demand Is a Guess
- One store takes more. Real, or a good fortnight?Are These Two Stores Really Different?
- How few units tell you the batch average?How Many Units Do We Have to Check?
- Which of your stores should share a target?Which Stores Behave the Same Way?
- Is unit cost driven by the price or by the volume?Will Unit Cost Land Where the Budget Says?
- The business case says yes. What are the odds?Is the New Machine Worth It?
Every model like this one, and the method behind them: Statistics in Google Sheets.