Work snippetInventory + purchasing
Airtable inventory and supplier bills synced with ServiceM8 and Xero
How I would approach stock for a 30-tech plumbing business with three branch stores, so goods received, transfers, job usage and stocktakes all move one ledger, and a supplier bill reaches Xero only when it matches what arrived.
Work snippet with example figures.
- Area
- Inventory + purchasing
- Tools
- Airtable · ServiceM8 · Xero · Make
- Typical scope
- US$2,500–4,200
- Timeline
- About 6 weeks · 4 fixed milestones
Context and systems map
A 30-tech plumbing business with three branch stores, about 1,100 stocked items and a dozen regular suppliers. Stock levels live in spreadsheets, supplier bills are keyed into Xero by hand, and technicians record parts used on jobs in ServiceM8. Nobody can say what is on the shelf or what the stock is worth.
The systems in play
In the example an Airtable base becomes the stock record. Goods received, branch transfers, job usage and stocktake adjustments all move stock there, and supplier bills reach Xero only when the goods and the order agree.
| System | What it does for the business |
|---|---|
| Airtable stock base | Items, locations, suppliers, purchase orders, goods received and a movements ledger. |
| ServiceM8 | Materials used on jobs; the source of job usage. |
| Xero | Supplier bills, the stock value account and the cost of goods sold. |
| Sync flows | Scheduled flows that read job usage, send approved bills and post the monthly stock value journal. Make in the example. |
| Storekeepers | Receive goods, run transfers and stocktakes. |
| Finance lead | Approves bills and the monthly journal. |
How the data moves
- Purchase order
- Goods received
- Supplier bill matched
- Bill in Xero
- Stock on hand
- Job usage from ServiceM8
- Monthly stock value journal
Problem
What the owner sees
- Jobs that go out short because stock was not where the sheet said
- Supplier bills paid for goods that never arrived
- Stock value that is a guess at year end
- Technicians buying parts that are already in another branch
Stock is a ledger of movements, and every movement has a source: a delivery, a transfer, a job, a count. When those are separate spreadsheets the totals drift and nobody can explain why. A single movements table with a source for each line makes every balance traceable.
| Everyday situation | What goes wrong |
|---|---|
| Delivery received but not booked in | The shelf and the sheet disagree from day one |
| Bill keyed with a different quantity | The business pays for what it did not receive |
| Transfers between branches | Stock disappears from one branch and never appears in the other |
| Parts used on a job but not recorded | Stock drains and job margin is overstated |
| Price changes from the supplier | Cost of goods uses an old price |
| Stocktake adjustments written over the balance | The cause of the difference is lost |
Approach
Four steps, in this order. Each one ends with a result that is checked.
- LookReview of the current sheets, 12 months of supplier bills and a physical count of the top 100 items to measure the gap.
- Agree the rulesLocations, units, costing method, reorder points, approval levels and the bill matching rule are written down.
- Build and loadThe base is built, opening stock loaded from a full count, and the flows tested on a past month.
- Run and checkA parallel month with weekly counts, then the bill sync and the monthly journal go live.
| Item | Example setting |
|---|---|
| Costing method | Weighted average, updated on each goods receipt |
| Locations | Three branch stores and each technician’s van as a location |
| Movements | Receipt, transfer, job use, return to supplier, stocktake adjustment. Nothing else changes a balance. |
| Bill matching | A bill is sent to Xero when quantity matches the goods received within 2% and price within 3% |
| Reorder | A weekly list when stock on hand is at or below the reorder point |
| Stocktake | Weekly cycle count of 40 items, full count twice a year |
| Journal | Monthly stock value journal prepared for the accountant to post |
| Approval | Adjustments above 250.00 need the finance lead |
Flow and data mapping
The first table shows the movements that change stock and where each one comes from. The second maps stock and bills between the systems.
| Movement | Source | Effect on stock | Effect on the books |
|---|---|---|---|
| Goods received | Delivery docket against a purchase order | Increases the location, updates the average cost | Accrual until the bill arrives |
| Supplier bill matched | Bill from the supplier | No change | Bill created in Xero, accrual cleared |
| Transfer | Storekeeper form | Decreases one location, increases another | None |
| Job use | ServiceM8 materials on a completed job | Decreases the technician’s van | Cost of goods in the monthly journal |
| Return to supplier | Return form | Decreases the location | Credit note matched to the bill |
| Stocktake adjustment | Count sheet | Corrects the balance, reason recorded | Adjustment in the monthly journal |
| Airtable or ServiceM8 | Xero | Rule |
|---|---|---|
| Item code and name | Item code | Same code in all three systems |
| Supplier | Contact | Matched on supplier id, never on name |
| Purchase order number | Bill reference | On every bill line |
| Quantity received | Bill quantity | Within 2% to release the bill |
| Unit cost | Bill unit price | Within 3% to release the bill |
| Stock value account | Inventory asset account | One account for all branches, tracked by branch |
| Cost of goods used | Cost of sales account | From the monthly journal |
| Job material line | Not sent | Used for stock only; job invoice carries the sale |
Edge cases handled
Agreed in advance with the finance lead and the operations manager, and tested before go-live.
| Situation | How it is handled |
|---|---|
| Partial delivery | Goods received for the part; the order stays open for the balance |
| Bill arrives before the goods | Held in a waiting list, released when the receipt is booked |
| Supplier changes the pack size | Unit conversion on the item; both units shown on the receipt |
| Part used that is not in the system | Added as a new item in a reviewed batch; usage retained |
| Van stock below zero | Allowed once, flagged to the storekeeper, never silently |
| Supplier credit note | Matched to the original bill and the return movement |
| Price change mid-month | New average cost from the receipt date, not back-dated |
| Same bill sent twice | Duplicate by supplier and number, ignored and counted |
Worked example
The tables below follow one item and one month for an example business. They show the shape of the checks, not a client result.
| Step | Quantity | Unit cost | Value |
|---|---|---|---|
| Opening stock | 40 | 12.50 | 500.00 |
| Goods received | 60 | 13.10 | 786.00 |
| After receipt | 100 | 12.8600 | 1,286.00 |
| Used on jobs this week | (34) | 12.8600 | (437.24) |
| Closing stock | 66 | 12.8600 | 848.76 |
| Line | Ordered | Received | Billed | Result |
|---|---|---|---|---|
| Copper pipe 15 mm, 3 m | 100 | 100 | 100 | Released |
| Valve, ball, 20 mm | 60 | 60 | 61 | Released, within 2% |
| Water heater element | 12 | 10 | 12 | Held: billed more than received |
| Fitting kit | 30 | 30 | 30 | Released |
| Item | System | Counted | Difference | Reason |
|---|---|---|---|---|
| Pipe clip 15 mm (box of 100) | 48 | 46 | -2 | Used, not recorded |
| Solder, 500 g | 12 | 12 | 0 | |
| Isolation valve 15 mm | 35 | 37 | 2 | Return not booked |
| PTFE tape | 80 | 76 | -4 | Loss written off |
| Line | Basis |
|---|---|
| Stock value at month end | Closing quantity multiplied by average cost, by branch |
| Cost of goods used | Job use movements for the month |
| Adjustments | Stocktake adjustments with reasons |
Monitoring and alerting
Stock goes wrong slowly, so the monitoring is a weekly rhythm plus a few sharp alerts for things that cost money straight away.
| Signal | Threshold | Alert goes to | First action |
|---|---|---|---|
| Bill without goods | Any bill held more than 5 days | Finance lead | Supplier asked for the delivery docket |
| Negative stock | Any item below zero | Storekeeper | Movement found and corrected |
| Unmatched job usage | Any ServiceM8 part not in the base | Storekeeper weekly | Item added or mapped |
| Stocktake variance | Over 2% of value in a cycle count | Finance lead | Cause recorded; recount |
| Reorder list | Items at or below the reorder point | Storekeeper Monday | Orders raised |
| Sync failure | Any failed run | Manaz Afzal and finance lead | Replayed once the cause is fixed |
Test plan
Every check has a written pass condition and a written fail condition before it is run. A FAIL stops the next step until it is cleared and re-run.
| ID | Check | Method | PASS when | FAIL when |
|---|---|---|---|---|
| T1 | Receipt and average cost | Receive two batches at different prices | Average equals the hand calculation | Any difference |
| T2 | Bill release | Bill with quantity 3% over | Held, not released | Released |
| T3 | Transfer | Move stock between two branches | One decreases, the other increases by the same amount | Any difference |
| T4 | Job use | Complete a test job with parts | Van stock reduced by the parts used | No change |
| T5 | Duplicate bill | Send the same bill twice | Second ignored and counted | Two bills in Xero |
| T6 | Stocktake | Adjust a count | Reason recorded and balance corrected | No reason |
| T7 | Journal | Run the journal for a past month | Equals the stock report by branch | Any difference |
| T8 | Opening balance | Full count against the base | Equal item by item | Any item different |
Go-live and rollback
Stock is loaded from a full count on a Friday after close, then run in parallel with the old sheets for one month.
| Step | Owner | Gate to continue | Rollback trigger |
|---|---|---|---|
| Full count and load | Storekeepers | T8 passes | Any item different after recount |
| Parallel month | Storekeepers | Weekly counts agree within 2% | Variance above 5% for two weeks |
| Bill sync live | Finance lead | T2 and T5 pass | Any wrong bill in Xero |
| Job usage live | Storekeepers | T4 passes | Usage not reaching the base |
| Journal live | Finance lead | T7 passes | Accountant rejects the journal |
| Rollback | Owner | Old sheets reopened, new movements re-entered from the movements table | Decision recorded with the reason |
Scope and milestones
A fixed scope agreed in writing before anything starts. Each milestone is paid when you sign off its deliverable.
| Area | Included |
|---|---|
| Systems Audit | Read-only review of stock sheets, 12 months of supplier bills and a count of the top 100 items, a 45-minute call, written findings and a fixed quote |
| Stock base | Items, locations, suppliers, orders, receipts and the movements table |
| Opening load | From a full count, tied item by item |
| Bill sync | Matching rules and bill creation in Xero |
| Job usage | Materials from ServiceM8 into the movements table |
| Reorder and stocktake | A weekly reorder list and cycle count sheets |
| Journal | Monthly stock value journal for the accountant |
| Handover | Written SOPs, a recorded handover video and files you own, plus 14 days of fixes after go-live |
| Not included | Tax filing and statutory returns. |
| Also outside the scope | Barcode hardware. Warehouse layout. Changing suppliers or prices. Software subscriptions (billed to you by the vendors). |
| Milestone | Deliverable | Timing | Share |
|---|---|---|---|
| M1 Audit and rules | Findings, fixed quote, stock rules in writing | Week 1 | 20% |
| M2 Base and load | Base built, opening stock loaded and tied | Weeks 2 to 3 | 30% |
| M3 Flows | Bill sync and job usage tested on a past month, parallel month starts | Weeks 4 to 5 | 30% |
| M4 Go-live and handover | Flows live, journal, SOPs, handover video, 14 days of fixes | Week 6 | 20% |
Typical scope for this kind of project: US$2,500–4,200
About 6 weeks across 4 fixed milestones. The Systems Audit (US$300, fixed) comes first and confirms the scope.
Describe what is going wrong between your job system and your books, and I will say where I would look first.
Send a short note through the form on the home page. I will reply with where I would start, and whether the US$300 Systems Audit is the right first step.