The threshold, and the case where it is not a number

Two costs, one variable

Everything so far has been description. This lesson is the decision. It is an ordinary break-even, with an honest sensitivity test attached.

The cost of keeping a sheet has a fixed part and a part that grows. Write it as K = a + b × d, where d is the number of decisions taken off the sheet each week by somebody other than its author. That is the variable that matters. The number of readers is not, because a person who opens a file and does nothing costs nothing.

• `a` is the author's own upkeep. Across the sixty-one sheets the median is a little under an hour a month: 10.8 hours a year, USD 123.12 at the department's rate.
• `b` has two halves. The first is checking. Tazerdine timed it at about 4 minutes of re-reading per decision taken by a non-author, which over a year is 39.52 for each decision a week. The second half is the expected cost of being wrong.

That second half is where the whole answer lives. Eighteen months of incidents cost 84,733, or 56,488.67 a year. Across all sixty-one sheets the department takes 347 decisions a week off spreadsheets by somebody other than the author. Spread the annual incident cost over them, and each weekly decision carries 162.79 a year of expected error.

So b is 202.31, and 80.5% of it is the error, not the checking.

Now the cost of moving a sheet into the system, over three years. A median field or report at Tazerdine takes 46 hours to specify, build, test and train: 1,564.00, which is 521.33 a year over three years. Add residual merchandising upkeep of 45.60, and the system's own cost of changing the field later, 204.00. So M is 770.93 a year, and it does not depend on d.

Set them equal. d* = (770.93 − 123.12) ÷ 202.31 = 3.2 decisions a week.

How much of that rests on one incident

A threshold that came out of a division deserves to be attacked before anybody else attacks it. There is exactly one place to attack this one.

Take out the single largest incident from lesson three, the fabric-commitment sheet, 51,300 on its own. The remaining eighteen months cost 33,433, or 22,288.67 a year. The error term falls to 64.23, b falls to 103.75, and the threshold moves to 6.2 decisions a week.

Nothing else on the page moves it that far. Double the build cost and d* goes up by about half. Halve the checking time and it barely moves. The answer is decided by whether you believe the biggest case will happen again, and no factory has enough incidents to know.

That is not a reason to abandon the arithmetic. It is a reason to report it as a range, and to say what the range depends on: between three and six decisions a week, and which end you are at depends on whether the worst thing that has happened to us is the worst thing that can happen to us.

So between 18 and 14 of the sixty-one should move, and between 70.5% and 77.0% of them are correctly where they are. That is the answer to "get rid of the spreadsheets", reached by arithmetic rather than by taste, and it is mostly no.

The rule that overrides the number

The break-even above works out an expected value, and an expected value is a statement about the average of many tries. It is the right tool for a sheet whose errors produce work: a re-check, a rebooking, an argument with a buyer.

It is the wrong tool when a wrong value in the sheet can cause something that cannot be taken back: a container booked, cloth cut, a purchase order issued, a shipment released, a price quoted. For those, d* is not three and it is not six. It is one. One such decision a week is enough, because you do not get to average over the tries. The first one lands and stays landed.

Of the forty-three sheets that survive the arithmetic at Tazerdine, 4 touch a commitment that cannot be taken back. Three of them are in the category this course defended hardest: Hamza's costing models, the ones whose whole value is that their author can take them apart in front of a buyer.

That is the sharpest tension in the course, and it does not resolve by picking a side. A price, once given, is given. The model that produced it is doing a job a system does badly. The number it produced is a commitment a spreadsheet remembers badly.

Computing and remembering are different jobs

The question was never sheet or system. It is this: which half of this thing is computing, and which half is remembering? Only the remembering half has to move.

Hamza's model stays exactly where it is. What changes is that the moment a figure leaves the room, it is written into the system as a record: the price, the date, the assumptions it stood on, and who it was given to. Six fields, entered once, taking about a minute. The sheet computes, and it is allowed to be rebuilt every season. The system remembers, and it is not allowed to be edited.

Applied across the sixty-one: 18 move, 4 are split this way, and 39 stay untouched. Each of those thirty-nine gets one written sentence saying why it is staying, and a date to look again. A sheet retired without a date is a sheet kept forever, and a sheet kept without a reason is one nobody dares delete. Course 22.5 owns what happens to a change that never sets one.

Act as an operations manager with a finance head, who builds break-even cases and always reports them as a range with the term they depend on named. I want my own threshold for when a spreadsheet stops being cheaper than a system field. Here is what I can give you. Author upkeep, in hours a month, for a typical sheet: [FIGURE]. My loaded hourly cost for the people who maintain sheets: [FIGURE]. My loaded hourly cost for system-change work: [FIGURE]. Time to specify, build, test and train one typical field or report: [HOURS]. Time somebody spends re-reading a sheet before taking one decision off it: [MINUTES]. Total decisions a week taken off all my sheets by somebody other than their author: [FIGURE]. My incident history: [LIST EVERY INCIDENT CAUSED BY A SPREADSHEET IN THE LAST TWO YEARS, WITH WHAT IT COST AND HOW MANY DAYS IT WENT UNDETECTED]. My horizon for spreading a build cost: [YEARS]. Do the following. First, build the cost of keeping a sheet as a fixed part plus a part that grows with decisions a week, showing both halves of the growing part separately. Second, build the cost of moving one, including residual upkeep and the cost of changing the system later. Third, solve for the crossing point. Fourth, recompute it with my single largest incident removed, and tell me what share of the growing term that one event was carrying. Fifth, report the answer as a range, and write the sentence I should say when I present it. Sixth, list the sheets I should leave out of the arithmetic entirely, because a wrong value in them becomes a commitment that cannot be taken back, and say what test you used. Do not give me a single number.

What you own at the end of this course

A number and a rule. The number is a threshold in decisions a week, built from your own upkeep, your own checking time and your own incident log, reported as a range with the term it depends on named. The rule is that the number does not apply where a wrong value becomes a commitment nobody can take back.

And the sentence that settles most cases before any of the arithmetic is needed: work out which half of the thing is computing and which half is remembering. Spreadsheets are very good at the first. They have never been any good at the second, and they never say so.