DSO XmR chart (Excel)
The same chart as the online calculator, in a file you can keep, rerun every month and drop into a reporting pack.
What it does
You enter monthly DSO. The workbook calculates the average and the average month-to-month movement over a baseline you choose, sets the natural process limits from those, and flags the months that look like a signal rather than routine variation. Two charts update with it: DSO by month, and the moving range underneath.
It answers one question. Is this month, or this run of months, different from what the process usually produces? It doesn’t say what changed or who changed it. That still needs someone to look.
How to use it
- Open the DSO XmR sheet. Enter months in column A and DSO in column B, oldest first, starting in row 2, with no gaps. It takes up to 36 months.
- Set the number of baseline months in cell O2. Pick a run of months you believe was stable. Twelve is the default.
- Read the flags in columns I to L, and the charts. Everything else recalculates on its own.
What gets flagged
| Flag | What it looks for |
|---|---|
| Rule 1 | A single month outside the upper or lower limit |
| Rule 2 | Three out of four consecutive months closer to the same limit than to the average |
| Rule 3 | Eight consecutive months on the same side of the average |
| Range signal | A jump between two months larger than the range limit |
The limits are the average ± 2.66 × the average moving range. The range limit is 3.27 × the average moving range. Rules 2 and 3 flag the month where the pattern completes, which can be several months after the change started.
Worth knowing before you trust it
- Use one DSO definition for every month. A new convention, ERP or entity mix starts a new baseline.
- A short baseline gives rough limits. Recalculate as clean months build up, but don’t fold a known shift into the baseline.
- Seasonal months can be flagged every year, or can widen the limits and hide changes elsewhere.
- If consecutive months move together, the limits come out tight and the rules fire more often. If half the months look like signals, check that first.
The reasoning behind all of this is in Does every DSO wobble need an explanation?
Built for Microsoft Excel. No macros and no external links. The file opens empty, ready for your own numbers. Free to use and adapt.