The spreadsheet that runs your operation

Somewhere on your shared drive there is a file called something like "Settlement tracker MASTER v14 (use this one).xlsx". It matches the processor's settlement report against a ledger export, flags the breaks and produces the figure finance posts every morning. The person who built it is the only one who can explain how.

A spreadsheet is often the fastest way for an operations team to cover something the core systems can't do yet. A new scheme fee appears or a partner changes the layout of its settlement file, and someone builds a workbook in an afternoon to deal with it, which is a sensible thing to do. The trouble comes a year later, when the operation depends on the file every day and nobody ever decided that it should.

We find these files most often in reconciliation, which is part of why we think reconciliation is an operations problem first. They also turn up in dispute tracking, fee billing, KYC case logs and partner reporting.

How a workbook turns into a system

It usually starts with a temporary job. Say you need to match transactions across two acquirers while you move payment providers. The overlap ends, but the workbook has turned out to be handy, so it stays. New tabs appear for new currencies. Someone saves a copy for each month, then a copy of that copy for the second entity. Before long the formulas point at tabs that have been renamed or at last month's file.

The daily routine around it grows as well. Download the CSV from the processor portal and paste it into the second tab. Delete the extra header row the portal adds on Mondays. Sort by column F, paste values over the formulas in column H, refresh the pivot. None of this is written down, because the person who built it never needed it written down.

Two risks follow. The first is the person: when the builder is on leave, the process either stops or someone runs it from the memory of having watched it once. The second is that spreadsheet errors are usually silent. A filter left on, a paste that stops a few hundred rows short, a lookup that picks the first of two matches, a date column read as text: the output looks normal, and the problem surfaces weeks later as an unexplained difference on the bank reconciliation.

A simple test tells you which side of the line a file is on. If a ledger posting, a payout or a figure you report to a partner depends on its output, it is a system, and it deserves the treatment you would give any other system doing that job.

Replace it or formalize it

Replacing every important spreadsheet with proper software sounds tidy, and it is usually the wrong first move. Some of these files are doing what spreadsheets are good at, and a rebuild would freeze logic that is still changing. We ask four questions about each one.

1. Does money move because of it?

   If the output drives a ledger posting or a number you give your sponsor bank, it needs real controls, whatever tool it lives in. A workbook used for analysis can be handled more lightly.

2. Has the logic stopped changing?

   While the business is still settling its rules, a spreadsheet is a reasonable home for them, and building them into a system now means building them twice. Once the rules have held for a couple of quarters, the file is a candidate for replacement.

3. How much of the effort is moving data around?

   If most of the daily time goes on downloading, pasting and reformatting, the fix is an integration or a scheduled export. The spreadsheet may shrink to a review step at the end, which is fine.

4. Can a system you already pay for do it?

   The reconciliation module or case tool you already license often has the feature, unconfigured, because the spreadsheet was working by the time the system arrived. Check before you buy anything new.

A spreadsheet becomes a system on the day a posting or a payout depends on it, whether or not anyone decided that it should.

What formalizing involves

For the files that stay, the work is modest and mostly about ownership and change control.

Give each file a named owner and a deputy who has run it end to end in the last month. Having watched it run doesn't count. The deputy's first run should happen with the owner sitting nearby, because that is when the unwritten steps come out, like the Monday header row and the column F sort. Write those steps down in the order they happen and keep the procedure in the same folder as the file. A page or two is usually enough.

Then settle where the file lives and who can change it. There should be one live version in one location, with each month's output saved as a read-only copy in a dated folder. Separate the input tabs from the logic tabs and protect the logic, so that a careless paste can't land on a formula. Add a change log tab that records each change with a date and the name of whoever checked it. A change to the matching logic deserves the same maker-checker treatment as a change to a payment limit, since its effect on the ledger can be just as large.

Last, build in checks that catch the silent errors. The row count and total amount on the input tab should equal the count and total in the source file, and matched plus unmatched items should add back to the input. A reviewer signs off the output before anything is posted, and the sign-off is recorded somewhere outside the workbook, so it survives the next time someone overwrites the file.

If you don't know how many of these files you have, ask each team lead two things: which files they open every working day, and who they would call if one of them broke. The list is usually longer than anyone expected. Most entries need an owner and a locked logic tab, which is a few days of work. A few will belong on next year's systems plan, and the four questions above will give you the case for each.

Discuss your operations