Moving a pharmaceutical distributor off spreadsheets, entirely

The challenge

The distributor's entire operation lived in spreadsheets and offline paperwork, including the parts a regulator cares about. Which batch went to which customer, what it expired on, whether it stayed cold. That arrangement survives longer than anyone expects, because the people running it are good at it. What it cannot survive is a recall, an audit, or growth: two people holding different versions of the same stock file, a reconciliation that takes a week, and no way to answer a question about last quarter without opening forty files.

Our approach

1. 01

   Treated the spreadsheets as the specification rather than as legacy to be discarded. Years of business rules were encoded in their formulas, their colour coding and their conventions, and most of those rules had never been written down anywhere else.

2. 02

   Made the batch, not the product, the unit of stock. In pharmaceutical distribution a quantity on hand is close to meaningless. What matters is which lot it belongs to, when it expires and what happened to it in transit. Everything downstream, from picking to recall, follows from getting that one modelling decision right.

3. 03

   Migrated the historical records into the new system instead of starting from a zero balance, so the first day of operation could be reconciled against the last day of the spreadsheets rather than taken on faith.

4. 04

   Made the platform the single system of record from the day it went live, and retired the parallel process. A spreadsheet kept running 'just in case' becomes the real system again within a month, and then there are two.

5. 05

   Scoped access by role so purchasing, the warehouse floor and finance read the same stock figure rather than each maintaining their own copy of it.