Lessons · Lesson 4 of 5
Read your spreadsheets as a specification
Turn the sheets that hold what no system holds into a ranked list of missing fields, and price each one against the sheets it would retire.
Lesson 4 of 5 · 20 min
Twelve sheets, four questions
Twelve of Tazerdine's sixty-one files hold the record of something no system at the factory holds. An audit would call those a problem. Ghita treated them as evidence.
She asked one question of each: what field is this sheet asking for? Twelve sheets collapsed into four answers, because most of them were asking for the same thing in different departments.
This is worth stopping on. It is the cheapest requirements exercise anyone will ever run. Nobody held a workshop. Nine people had already voted with their own time, over three years, and the vote was recorded in file sizes and edit counts. A shadow spreadsheet is a requirement somebody was willing to pay for out of their own week. That is a stronger signal than anything a workshop produces, because it has already been paid for.
| The field the sheets are asking for | Sheets | Readers | Upkeep, hours a year | Upkeep and risk, a year | Cost to build | Payback |
|---|---|---|---|---|---|---|
| A forecast date beside the plan date and the actual date | 5 | 9 | 214 | 4,339.60 | 748.00 | 2.1 months |
| Carton count and pack status at delivery-line level | 3 | 7 | 96 | 5,673.50 | 2,074.00 | 4.4 months |
| A trim arrival date per component, not per purchase order | 3 | 6 | 88 | 1,623.20 | 5,032.00 | 37.2 months |
| A written reason attached to a changed date | 1 | 4 | 11 | 125.40 | 6,120.00 | 48.8 years |
The upkeep column is hours of merchandising time at 11.40 an hour. The build column is hours of system-change work at 34.00 an hour. The risk added into the fourth column is the incident history of the sheets themselves. For the second row that is the split file in lesson two, 9,158.20 once in two years, which spread over a year is 4,579.10.
Two of these are obvious, one takes two years, and one never pays
The first row is the column course 10.3 spent a whole lesson on: somewhere to record what you now believe, so a plan date does not have to be either overwritten or left standing. It costs 748.00 and repays in nine weeks. There is no argument to have.
The second row repays in 4.4 months even though it costs nearly three times as much, because it carries the risk of the split file. Take the risk out entirely and it still repays in 22.7 months. So the weakest number on the page does not change the decision. Say that out loud when you present it. Spreading one event over a year gives you the softest figure in any business case, and the only defence is to show the answer with it and without it.
The fourth row is the interesting one. A written reason against every changed date is a good idea, and everybody agrees it is a good idea. At Tazerdine it would cost 6,120.00 to build, against a sheet that costs 125.40 a year to keep. The payback is forty-nine years. So the sheet stays, with a written reason for keeping it and a date to look again. That is the discipline course 22.5 sets out for anything running alongside a system.
The assumption inside every payback on that page
Each payback assumes the sheet dies when the field arrives. Check that before you promise it, because it is often false. A sheet holds the missing field and three other things nobody will ever build: the buyer's own reference numbers, a colour-coded note about which contact answers on a Friday, a column of nicknames for vessels.
At Tazerdine two of the four sheets would have survived at roughly thirty per cent of their upkeep. Redo the second row on that assumption and the payback moves from 4.4 months to 6.27 months, which changes nothing. Redo the third row and it moves from 37.2 months to 53.1 months, which turns a decision into an argument.
A field that removes most of a sheet is worth what the arithmetic says. A field that removes half of one is worth half of that, and the other half of the sheet is still there, still able to fork.
What deleting them would have cost
The plan Ghita was originally asked for would have removed all twelve. Their upkeep is 409 hours a year, so on paper it saves four hundred and nine hours.
It saves none of them. The work those hours are doing does not stop existing because the file does. It moves into email, into telephone calls, and into memory, where it costs more, leaves no record, and cannot be read by the person covering next week's leave. Course 10.3 records what happened at another factory when exactly this instruction was issued. The file went underground rather than away, and became several private files nobody else could see. That is the same work at a higher price, with the trail removed.
Check yourselfA department has fourteen shadow spreadsheets. Management wants them gone by the end of the quarter. What do you do first, and what do you refuse to do?Show the answer
First, ask each one what field it is asking for, and group the answers, because fourteen sheets will not be fourteen requirements. Then price each field against the sheets it would actually retire, showing the payback with and without any single large incident. What you refuse is the deadline. A sheet can be deleted by the end of the quarter and a missing field cannot be built by then. Doing the first without the second moves the work somewhere you cannot measure and calls it progress. Some of the fourteen will never pay for a field. Keep those, in writing, with a review date.
Prompt · Classify my spreadsheets and find the fields they are asking for
Before you agree to any plan that removes spreadsheets, and once a year after that.
Act as an operations analyst who has audited shadow spreadsheets in a manufacturing business. You have no interest in defending them or in abolishing them. I want my department's sheets classified, and then read as a list of requirements. Here is my inventory, one line per file: [PASTE IT, WITH FOR EACH FILE - A ONE-LINE DESCRIPTION OF WHAT IT HOLDS, HOW MANY PEOPLE OPEN IT, HOW MANY DECISIONS A WEEK ARE TAKEN OFF IT BY SOMEBODY OTHER THAN ITS AUTHOR, ROUGHLY HOW MANY HOURS A MONTH SOMEBODY SPENDS MAINTAINING IT, WHO BUILT IT, AND WHEN IT WAS BUILT]. Do the following. First, put every file into exactly one of five classes: it answers a question asked for the first time; it is a one-off; it is a calculation whose author has to be able to take it apart in front of somebody; it holds a copy of something one of my systems already holds; it holds the record of something no system of mine holds. Where you cannot tell, say which fact you would need, and ask for it rather than guessing. Second, for the files in the last class, ask each one what field is missing from my system. Group the files by that answer, so I get a list of missing fields rather than a list of files. Third, for the files in the copy class, say what is wrong with the report my system already produces. Express it as a column, a grouping or a period. Fourth, tell me which files would move into email, telephone calls or memory if they were simply deleted. Estimate the hours a year that represents, using the maintenance figures I gave you. Fifth, name the files whose class you are least sure about, and say what would settle each one. Do not recommend deleting anything, and do not tell me spreadsheets are a bad tool.
AI can make mistakes — check anything you act on.
What you own at the end of this lesson
A method that turns the thing you were told to remove into the specification for what to build. Group the sheets by the field they are asking for. Price each field against the upkeep and risk of the sheets it retires. Show the answer with and without the one big event. And check whether the sheet actually dies.
And the rule that comes out of it. The sheet you cannot delete is the specification. The sheet you can delete was never the problem.
Next: the threshold. The number of decisions a week at which a sheet stops being cheaper than the system, and the one case where the number is one.