The copy that forked, and both halves stayed in use

Nobody did anything careless

The Ambersall autumn tracker is one of the twelve sheets from the last lesson that hold what no system at Tazerdine holds. It carries 38 delivery lines. Against each line it holds the confirmed ship date, the booked vessel, the carton count, the packing status and the trim arrival. The planning system holds carton count and packing status against the order, not against the delivery line. That is why the sheet exists at all.

Here is the whole sequence. Every step in it is the sensible move.

1. 3 May. Anass Fettah sends the tracker to Marged Brackwood at Ambersall as the weekly status. He sends the file rather than a summary because she has asked for the detail twice.
2. 6 May. She replies with three amended dates marked inside the attachment. Marking the file is more precise than writing the dates out, and she is right about that.
3. 6 May. Anass saves the returned file into the shared folder. It will not overwrite the original, because the folder copy is open on somebody else's screen. So he saves it beside the original with a longer name, and puts a note in his diary to merge them on Monday.
4. 8 May. Drewhurst cancels a delivery and the diary note never gets read.
5. From 11 May, people open whichever of the two files appears first in the folder listing. Both files are looked after carefully, by people typing true things into them.

The careful act is step three. Overwriting a file somebody else had open would have been the reckless thing to do, and it would have prevented all of this. That is the lesson. A fork is not produced by carelessness. It is produced by handling a collision correctly when the tool has no way to resolve one.

Forty-one days later, on 16 June, a forwarder's tally at the loading bay found 4,800 pieces of TZ-6120 packed to a carton specification that had been replaced on 22 May. Replaced in one copy.

What the reconciliation cost, and why it is the small number

At the moment of detection the two files differed in 23 cells, across 11 of the 38 lines. Ghita and Anass sat down and reconciled them in 5.5 hours each. That is 11 person-hours at the department's loaded rate of 11.40 an hour: USD 125.40.

Most people would report that number, and it is not the cost of the fork. The cost of the fork is the decisions taken off the wrong copy.

Seventy-six decisions in forty-one days is 1.85 a day. 32 of them, or 42.1%, were taken off the stale copy. Of those thirty-two, 7 came out wrong: 21.9%.

Now read the other twenty-five carefully. They are the part that makes a fork hard to find. Twenty-five decisions were taken off the wrong file and came out right, because the two copies happened to agree on the cell that decision read. Nothing about them looked different. Nobody felt uneasy. A fork does not make every decision wrong. It makes a fifth of them wrong at random, and you cannot tell which fifth without redoing all thirty-two. That is why reconciliation is expensive. It is not the twenty-three cells.

The seven

Add the reconciliation and the fork cost USD 9,158.20. The mean cost of one wrong decision was 1,290.40. Keep that number. It is what a single stale cell is worth at this factory, and everything below multiplies it.

The formula, and the only term you can move

The whole thing fits together like this:

decisions in the period × share taken off the stale copy × share of those that go wrong × cost of one wrong decision

For this fork that is 76 × 42.1% × 21.9% × 1,290.40, which comes back to 9,032.80.

Now look at which terms you can actually change. The cost of a wrong decision is set by your business. The share taken off the stale copy is roughly the share of the department holding the wrong file, and you do not know that number until afterwards. The share that goes wrong is set by how far apart the two copies drifted, which is not a lever either.

Decisions in the period is the lever, and it is nothing but the detection time. At 1.85 decisions a day the relationship is a straight line: halve the days and you halve the cost exactly. If the fork had been found on day seven instead of day forty-one, the cost would have been 1,542.19 instead of 9,032.80. That is USD 7,490.61 avoided, 82.9% of it, with nothing else changed.

The check that takes ninety seconds

The obvious control is to compare the copies, and it is useless. It requires you to know the copies exist. Nobody at Tazerdine knew there were two files. The control has to work without that knowledge.

So the sheet declares its own identity. Three cells at the top, computed rather than typed: the number of rows, the sum of one numeric column, and the date of the most recent edit. At the Monday call, whoever is presenting reads those three values aloud. Everybody else has the file open. If two people read different numbers, there are two files, and it took ninety seconds to find out.

Fifty-two of those a year is 1.3 hours, or USD 14.82 of merchandising time. Set that against the 7,490.61 a seven-day detection would have saved on this one event, and the ratio is 505 to one. This control does not need a business case. It needs somebody to put three formulas in row one.

Act as an internal auditor who has priced data divergences in a working business. You separate the cost of repairing a record from the cost of the decisions taken off it. Two copies of one working file have been found to differ. Here is what I know: [DESCRIBE THE FILE AND WHAT IT DRIVES]. The two copies last agreed on [DATE, OR SAY YOU DO NOT KNOW AND WHAT THE EARLIEST POSSIBLE DATE IS]. They were found to differ on [DATE], by [WHAT FOUND IT]. They differ in [NUMBER] cells. During the period, the following kinds of decision were taken by reading this file: [LIST THEM, AND FOR EACH ONE THE ROUGH NUMBER TAKEN IN THE PERIOD AND WHAT A WRONG ONE TYPICALLY COSTS]. Do the following. First, separate the repair from the damage, and say plainly which of my numbers belongs to which. Second, estimate how many decisions were taken off each copy. State the assumption you are using to split them, and say what would sharpen it. Third, estimate how many of those came out wrong. Be explicit that most decisions taken off a stale copy come out right by coincidence, which is why they all still have to be re-checked. Fourth, give me the total as a chain of four factors I can recompute myself. Then recompute it for a detection time of seven days, so I can see what detection alone is worth. Fifth, design a check that would find the next divergence WITHOUT anybody knowing a second copy exists, and price it in minutes a week. Sixth, tell me which of my inputs the answer is most sensitive to. Do not recommend forbidding copies.

What you own at the end of this lesson

A way of pricing a split file that survives an argument, because it separates the repair from the damage. And a control cheap enough to actually run: a sheet that says out loud how many rows it has, read by more than one person once a week.

Next: four ways a spreadsheet can be wrong while agreeing with itself completely, and the single property they all share.