From Spreadsheet to Prediction
Last updated:
The starting point: one sheet, one decision
Picture a wholesale distributor with about 40 staff and a few hundred trade accounts. Every Monday the credit controller opens a shared spreadsheet exported from the accounting system, sorts it by days overdue and decides who to chase. She is good at it. She also spends most of Monday doing it, and she knows the sheet does not show the things she really uses: which customers always pay late but always pay, and which ones go quiet before they go under.
This is an illustrative example rather than a named client, but its shape is extremely common. A recurring decision, made by an experienced person, from a spreadsheet that holds more history than anyone looks at. That is exactly where a model tends to help, and we cover the wider pattern in how we turn your spreadsheets into a system.
Week zero: the decision, written down
In the discovery workshop the goal becomes a sentence: predict which open invoices will be paid more than 30 days late, so the credit controller calls those accounts first, where each avoided write-off is worth a few thousand pounds and each hour saved on Monday is worth a little too.
We also learn what she already knows. New accounts are riskier. Customers who start paying in part payments are a warning sign. Seasonal trade customers are slow in January and fine by March. Those observations become the first candidate features, and her Monday rule becomes the baseline the model must beat.
Weeks one and two: the data is not what anyone thought
The spreadsheet turns out to be a view, not the source. The real history lives in the accounting system, and pulling four years of invoices and payments out of it is the first job. It is also where the surprises arrive.
- Credit notes were recorded as negative invoices for two years, then as a separate type
- About a tenth of payments were allocated to the wrong invoice and fixed by hand months later
- Several accounts were merged after an acquisition, so their history starts twice
- Payment terms were changed for some customers without keeping the old terms
- There is no record of which accounts were chased, or when
None of these kills the project. Each needs a decision, and the domain expert makes most of them in an hour. The last one matters most: without knowing who was chased, the model cannot tell whether an invoice was paid because it was chased. We note it as a limitation and start recording it.
Every spreadsheet looks tidy until you ask where its numbers came from.
Weeks three and four: the proof of value
We train on the first three years and test on the most recent twelve months, which the model never sees during training. The baseline, her sort-by-days-overdue rule, is scored on the same months.
| Approach | What it uses | Illustrative result |
|---|---|---|
| Current rule | Days overdue only | Catches late payers once they are already late |
| Simple model | Days overdue, account age, invoice size | Modestly better, mainly on new accounts |
| Gradient boosting | Full payment history and behaviour patterns | Flags a clear share of late payers before the due date |
The figures in a real report are specific; here they are left descriptive because this is an example. The pattern is typical, though: the biggest gain comes from spotting behaviour changes before the invoice is due, which a sort-by-overdue rule cannot see by definition. The proof of value clears the agreed threshold, and the recommendation is go.
Weeks five to eight: putting the prediction where she works
The tempting next step would be a new dashboard. We ask instead where the credit controller already looks, and the answer is the accounting system's customer list and the Monday sheet. So the build does three things.
- A nightly pipeline pulls new invoices and payments and cleans them with the rules agreed in week two
- The model scores every open invoice and writes a risk band and the top three reasons back to a field she can see
- The Monday sheet gains a sorted “call first” tab, generated automatically
Two weeks of shadow mode follow, where the scores are produced but not shown, and we compare them with what she decides. She points out a handful of accounts on payment plans that the model keeps flagging, and those are handled with a simple exclusion. Then it goes live.
After launch: the part most projects forget
Monitoring watches for drift in the data, such as a sudden change in invoice sizes, and tracks real accuracy as payments arrive. The model is retrained on a schedule, and each new version is checked against the old one before it replaces it. The 90-day warranty covers defects; ongoing monitoring and retraining sit on an optional retainer.
The credit controller still makes the decision. She simply starts Monday with a better-ordered list and reasons attached. That is a deliberately modest outcome, and it is what most successful business machine learning looks like. If your own spreadsheet sounds similar, our machine learning service is where a project like this starts.
Frequently asked questions
Can machine learning work from data in Excel?
How much history do we need for a prediction model?
Do we have to replace our spreadsheet?
Is this example a real client?
Running an important decision out of a spreadsheet?
Send us a sample of the sheet and tell us the decision it supports. We will tell you whether there is a prediction hiding in it, and what the first step would be.