Sixty-one spreadsheets, and what each one was for

The audit that was supposed to end them

Tazerdine Apparel makes woven trousers and light outerwear. It has fourteen sewing lines and three buyers: Ambersall, Drewhurst and Calmoor. Its merchandising department is nine people. In September the group's operations director asked Ghita Chraibi, who runs the department, for a plan to remove spreadsheets from it.

She did not argue. She did not obey either. She counted them first.

She found sixty-one files. That is everything in the department's shared folder, plus everything opened on the nine laptops in the previous ninety days. Then she asked three questions about each file, and only three: how many people read it, how often is it used, and what happens if it is wrong.

The top three rows are 35 sheets. That is 57.4% of the department. Every one of them is doing a job a spreadsheet does better than any other tool available. The bottom two rows are 26 sheets, 42.6%. Between them they hold every incident in the third lesson of this course.

That is the finding Ghita took back to the operations director. It is also why this course is not an argument against spreadsheets. Deleting all sixty-one would have destroyed thirty-five good tools in order to reach twenty-six bad ones. It would still have been called a success, because nobody counts the work that moves into email.

The first job: a shape nobody has specified yet

In February, Ambersall asked whether Tazerdine could quote on a programme where the shell fabric and the lining are bought on different terms, from different countries, against one style. Nobody in the department had a column for that. Nobody in the industry had one either, because Ambersall had invented the arrangement the week before.

Ghita asked what it would take to answer the question inside the planning system. The answer was three new fields, a change to the costing view, and a report. That is 31 hours of specifying, building, testing and training. And three weeks before anyone could type into it.

She built it in a spreadsheet in 2 hours 10 minutes and had an answer that afternoon. In April, Ambersall dropped the programme.

People usually say the advantage is that a spreadsheet is quick to build. That is true, but it is not the point. The real advantage is that a spreadsheet lets you put off deciding. Most of those 31 hours were not building. They were deciding what the columns are: what a two-origin style is, what happens when one leg is cancelled, whether the lining carries its own delivery date. A system makes you decide all of that first. A sheet does not. You find the columns by typing them, and you can change your mind at eleven o'clock at night.

Putting off the decision is worth most when the question is likely to die. Of the 17 first-time-question sheets, 11 were dead within three months. Their median life was 34 days. So 64.7% of those shapes never needed specifying at all, and no requirements process could have told you which ones in advance.

The second job: a one-off

Twelve of the sixty-one were built for one occasion and then finished: a floor plan for a buyer visit, a comparison of two trim suppliers, a re-cost of a cancelled order.

The arithmetic here is not close, and you do not need to put risk into it. A one-off sheet at Tazerdine costs about 3.5 hours to build. The cheapest change the planning system has ever absorbed was 22 hours, for one field and one report column, and that was a field somebody already understood. A tool that costs six times as much to build, and is then used twice, cannot pay for itself. Better maintenance does not rescue it, because there is no maintenance.

The trap in this row is not the sheet. It is the one-off that turns out not to be one. Two of the twelve had been rebuilt every season for three years. That makes them the fourth row wearing the second row's clothes, and Ghita moved them.

The third job: a calculation whose author must see every step

Hamza Zerouali costs the Ambersall autumn programme. Fourteen rows: cloth, trims, cut-make, wastage, finance, freight. He does it in a spreadsheet. The planning system has a costing engine that would do it for him.

The two barely disagree. On style TZ-6120 the system returned an FOB of 6.9420 against the sheet's 6.9385. FOB is the price of the goods loaded on the ship, with the buyer paying the sea freight from there. The gap is 0.0035, and it comes from where each one rounds the trim allowance. Hamza thinks the system is the more correct of the two. Track 8 owns which of them is right.

He still uses the sheet, and he is right to. The reason has nothing to do with accuracy. In the room with Marged Brackwood of Ambersall, the question is never what is the price. It is what happens if the cloth moves four cents. Then what if we drop the second pocket. Then what if you hold the yarn. He answers each one in a few seconds by pointing at a row, and the buyer watches him do it.

A number the author cannot take apart is worth less in a negotiation than a slightly worse number he can. That is not a complaint about systems. A costing engine is built to be authoritative, and being authoritative means not showing its working every time somebody asks. The sheet is a different tool for a different room.

The two rows that are not doing a spreadsheet's job

Fourteen sheets hold a copy of something the system already holds. Every one of them is a report nobody built. The reason is nearly always that the report the system does produce has the wrong columns, the wrong grouping or the wrong period. Those complaints are cheap to fix and expensive to leave. A copy has to be refreshed by hand, and a copy that has not been refreshed looks exactly the same as one that has.

Twelve sheets hold the record of something no system at Tazerdine holds at all. These are the important ones. Course 10.3 met the same shape from the planning chair. A planner kept six hundred date revisions in a private file, because the calendar had a plan field and an actual field and nowhere to put what she now believed. Deleting that file without adding the missing column does not fix the calendar. It deletes the calendar. The fourth lesson of this course puts a price on 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.

What you own at the end of this lesson

A defensible answer to "get rid of the spreadsheets", which is neither yes nor no: a count, a classification, and a reason written against each file. Three jobs are genuinely a spreadsheet's work: the unspecified shape, the one-off, and the calculation that has to be visible. Two jobs are not, and that is where the money is.

Next: what happens when one of those load-bearing files is copied, both copies are looked after carefully, and nobody finds out for forty-one days.