We've been looking closely at the NHS Template for SPC charts and came across a few problems with it.

Version Control

The latest "Statistical process control tool" template can be downloaded from
https://www.england.nhs.uk/statistical-process-control-tool/

We downloaded our copy on 25th August 2021. The file was created on 02/05/2017 11:02 but the filename is SPC-tool-excel-201-20200915, which may suggest a date of 15th September 2020 for this release.

Defects

Here's a screen shot of the chart we generated with the Template here.

Obviously in error


Note the the "variation icon", the leftmost of the two icons. That's shown as an orange "F".
The rules for this icon are:

If last 6 performance points are:

  • better than target = show P icon
  • worse than target = show F icon
  • a mix of better and worse = show ? icon

Defect 1: Inconsistent compute and display of icons

The rules are arguably ambiguous as to what happens should a point be precisely "on target", but the template chart code colours this point blue, which is correct as it's actually a little better than the target.

However the variation icon, which depends directly on this point and the subsequent five compared against the target is coloured orange.

They can't both be right, so at least one of them is wrong.

Defect 2: The "variation icon" is incorrect

It's coloured orange and marked F - it should be a blue L.

As above, all the last 6 points exceed the target requirement, so the chart happens to be correct in this case, and the "variation icon" drawn on it must be wrong.

For this example the sixth from last point (point 16 in the full resolution data table shown here) is 01-03-2021 and has a database value of precisely 0.91445881182747. If one was to round that to two decimal places you'd get 0.92, which would appear to be "on target", but the spreadsheet claims the target is not met. Our guess is that the spreadsheet may be incorrectly rounding the data, and also incorrectly and inconsistently testing compliance against the target.


Notes

  1. Recorded as Public View Issue #1604: NHS Template SPC-tool-excel-201-20200915 multiple errors

  2. This isn't an exhaustive list of issues.