From spreadsheet to system
Real anonymised case. They asked for a spreadsheet that works miracles.
Demonstration with synthetic dataBack to the demonstrations
The original request fitted in one sentence: a spreadsheet that would pull together information from several departments and produce a monthly indicator. Opening it, there were no several departments: there were several versions, each calculating the indicator its own way.
Before
Indicators_v7_FINAL_revised_JP_use-this.xlsx
C12 =VLOOKUP(B12,'Jan (2)'!$A:$F,4,FALSE) → #REF!
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 9 | Service | 4,180 | 86.6 | 86.6 | 9.41 |
| 10 | Service | 4,180 | 84.1 | 86.6 | 9.41 |
| 11 | Collections | 1,885 | 82.0 | 82.0 | 9.10 |
| 12 | Collections | #REF! | #REF! | 82.0 | #REF! |
| 13 | Retention | 676 | 90.9 | 14.6 | |
| 14 | TOTAL | #VALUE! | 87.2 | 85.0 | — |
JanJan (2)FebFeb_JPDO NOT DELETESheet3
- · “Attaching the good one, ignore yesterday's”
- · “Does this one have retention in it yet?”
- · “My total does not match yours”
After
One source, one definition per indicator, and the report assembled without anyone assembling it. The figures shown come from the synthetic dataset.
What changed
No figures attributed to the client: the confidentiality agreement does not allow it and we are not going to invent them.
| Before | After | |
|---|---|---|
| Indicator definition | One per department, none written down | One, written, with an owner |
| Who assembles the report | One person, by hand | Nobody: it runs on a schedule |
| Versions in circulation | Whatever was in the email thread | One, the system's |
| What happens when that person is out | There is no report | Nothing happens |
| Data capture | Partial: what was measured was not captured | Structured at source, including the staff satisfaction measurement |
| The monthly discussion | Which file is right | What to do about the result |