Vending Machine Inventory: Spreadsheet or Software?

A spreadsheet is the right vending inventory system for a lot of operators, and the wrong one for a lot of others. The dividing line is not how many machines you have; it is whether the numbers you need are typed by a person or produced by the machines, and whether more than one person needs them on the same day. Here is how to tell which side you are on, and what to put in the spreadsheet if you stay.

What a vending inventory sheet has to track

Whatever the tool, the job is the same. For every machine you need to know what is in it now, what it sells, what it should be filled to, and when it was last touched. For the warehouse you need what is on the shelf and when it expires. For the money you need what each machine should have had in the cashbox against what was pulled. A working spreadsheet for a small route usually ends up with these tabs:

The pick list and the stock estimate are formulas, not data: estimated stock is the last count minus the sales rate times days since, where the sales rate is units added over the last several visits divided by the days between them. That formula is the whole reason to keep the sheet. If you are only logging visits and never computing from them, you have a diary, not an inventory system.

Where a spreadsheet is genuinely enough

• One person does everything. You fill, you count, you pick, you reconcile. There is no hand-off, so there is no version problem.
• Up to roughly ten to fifteen machines on a fixed loop. At that size you can hold the route in your head and the sheet is a memory aid and a cash record.
• No telemetry and no DEX. If every number comes from your own count, there is nothing for software to automate but the arithmetic, and a spreadsheet does arithmetic.
• You like it. An operator who actually updates a spreadsheet every night beats one who bought software and stopped opening it in March.

If that is you, the only advice is to keep the tabs above, enter the count the same day, never overwrite a count (add a row), and back the file up somewhere other than the laptop in the truck.

Where it breaks

Spreadsheets fail vending operators in a predictable order.

1. A second person. The moment a driver counts and you reconcile, the file is either emailed back and forth, edited in two copies, or open in a shared drive with both of you in the Visits tab at once. Shared spreadsheets fix the locking and not the problem: nobody knows which cell changed, and a driver on a phone in a basement does not have the file at all.
2. Machine-produced data. A DEX file or a card-reader portal gives you per-selection sales for every machine every day. Keying that into a sheet by hand is hours a week and is never done; importing it needs a parser; and once the numbers come from the machine, the spreadsheet's only job is the arithmetic a program does for free.
3. History. After a year a Visits tab has several thousand rows and the sales-rate formulas slow down or are quietly narrowed to the last few visits. Questions like "which selections sold nothing in three weeks across all machines" or "what expired before it sold" become a lookup project rather than a glance.
4. Trust. A number in a cell does not say where it came from. Was that stock figure counted on Tuesday, estimated from a count in August, or typed by someone from memory? Software that tags each figure with its source lets a driver decide whether to recount; a sheet cannot.
5. Offline edits. Basements, hospital sub-levels and factory floors have no signal. A sheet on a phone either does not open or makes a local copy that overwrites someone else's work when it reconnects.

What to look for if you move

Vending software ranges from a glorified checklist to enterprise systems built for hundreds of trucks. For an independent operator moving off a spreadsheet, the useful questions are narrower than a feature list:

• Does it read your data in? Your machines, products and planograms exist in that sheet. If the first week is retyping them, it will not happen. The same goes for inventory exports from any card-reader portal you already use.
• Does it take DEX files and telemetry? Even if you have none today, the first card reader you install should drop its reads straight in.
• Does the driver's phone work with no signal? Counts entered at the machine and sent later, not a web page that fails to load.
• Does every number say where it came from? Counted, from a DEX read, from telemetry, imported, or estimated, and an honest blank when it does not know.
• Does it do the warehouse side? A pick list in cases and a warehouse stock that goes down when the kit is loaded, or you will be back in a spreadsheet for that half of the job.
• Can you get your data out? A CSV export of everything, any time, without asking. If the answer is no, you are not moving off a spreadsheet, you are moving into a lock-in.
• Does it fit the size you are, and the size you plan to be? A free tier for a handful of machines lets you try it on real data before anything is at stake.

A sensible migration

Do not switch everything on a Monday. Export the machines and products tabs to CSV and import them; keep the spreadsheet running for two weeks of visits alongside the new system; compare the pick lists it generates with the ones you would have built; and only retire the sheet when the software's estimate is at least as good as yours. The spreadsheet remains your backup export format forever, which is as it should be.

More guides: all Adelyons guides