A vending business keeps its stock in two kinds of place, and the spreadsheet has to count both. There's storage: the garage or unit where cases wait. And there are the machines, each holding a few hundred dollars of product you can't see until you open the door. A vending machine inventory spreadsheet has a sheet for storage, counted in single items with a reorder level, and a refill record for each machine visit: what the slot holds, what you found, what you loaded. From the refill record you get what each machine sold, which you check against the cash and card total. Snacks also carry best-by dates, and a slow machine is where they run out. Below is a sheet for a made-up route of six machines, the refill record, and the file.
The short version
- Count storage in single items. Buy by the case.
- For each machine visit: par, found, loaded. Sold = par − found, if you fill to par.
- Sold × price should match what the machine collected.
- Load the oldest dates first, and into the busiest machines.
- A slot that's always full at refill is the wrong product.
- TaroStack counts storage on a phone and flags dates. More below.
An example sheet
| Item | Unit | On hand | Reorder at | Unit cost | Value | Status |
|---|---|---|---|---|---|---|
| Potato chips, 1 oz | each | 180 | 150 | $0.42 | $75.60 | OK |
| Pretzels, 1 oz | each | 95 | 100 | $0.38 | $36.10 | REORDER |
| Chocolate bar | each | 210 | 144 | $0.78 | $163.80 | OK |
| Peanut candy bar | each | 64 | 72 | $0.74 | $47.36 | REORDER |
| Granola bar | each | 120 | 96 | $0.55 | $66.00 | OK |
| Cola, 12 oz can | each | 264 | 240 | $0.39 | $102.96 | OK |
Ten rows in all, $712.30 in storage, with four to reorder: pretzels, peanut candy bars, diet cola and energy drinks. This is storage only. What's inside the six machines is counted at each visit.
The refill record
One line for each slot, filled in with the door open.
| Machine 3, office lobby, Nov 20 | Par | Found | Loaded | Sold | Price | Sales |
|---|---|---|---|---|---|---|
| Potato chips | 12 | 3 | 9 | 9 | $1.25 | $11.25 |
| Chocolate bar | 16 | 2 | 14 | 14 | $1.75 | $24.50 |
| Cola | 24 | 2 | 22 | 22 | $1.50 | $33.00 |
| Bottled water | 24 | 14 | 10 | 10 | $1.25 | $12.50 |
| Energy drink | 8 | 2 | 6 | 6 | $3.00 | $18.00 |
| Total | 61 | 61 | $99.25 |
Par is what the slot holds when you leave. Found is what was in it when you arrived. If you always fill to par, what you load is what sold since the last visit.
Three things come out of this table.
What to carry. Sixty-one items for this machine. Add up the loaded column for the route and you know what to pull from storage before you drive.
What's selling. Cola was down to 2 of 24, and water still had 14. This machine wants a second cola slot more than it wants that much water. A machine that empties a slot between visits is losing sales.
Whether the money is right. The machine's cash and card total for the period was $97.50 against $99.25 of sales by count. A dollar or two is a jammed coin or a refund. A steady gap on one machine is a slot priced wrong, a product that vends two at a time, or stock that left without paying.
Dates
| Received | Item | Quantity | Best by | Note |
|---|---|---|---|---|
| Oct 6 | Potato chips | 200 | Dec 8 | Shortest dates: load these first |
| Nov 10 | Potato chips | 150 | Jan 19 | |
| Sep 15 | Chocolate bar | 288 | Jun 1 | |
| Aug 20 | Granola bar | 192 | Dec 1 | Ten days left: busiest machine, or pull |
Chips and baked snacks have the shortest dates. Load the oldest case first, and put anything close to its date in the machine that sells fastest. At each visit, check the dates at the back of every snack spiral: that's where an old bag hides. What you pull goes on the record as waste, with the reason, so you can see which products and which machines cause it.
Chocolate needs a cool shelf in storage and a machine that isn't in the sun.
The business side is in vending machine business plan and vending machine profit margin. Setting the storage levels is in reorder point example, and counting in how to count inventory.
Get the spreadsheet
Download the vending machine inventory spreadsheet. Four sheets: storage inventory with value and REORDER, case dates with days left, a count sheet that works out each difference and what it's worth, and a read-me. Add a copy of the refill table above for each machine. It's an ordinary Excel file with no macros, and it works in Google Sheets, LibreOffice and Numbers.
Where this stops working
Six machines, forty slots each, visited twice a week: nearly five hundred lines a week, written on a clipboard in a lobby and typed in at night, if they're typed in at all. Storage goes down by what was loaded, which nobody subtracts. By the third week the storage sheet says 264 colas and the shelf has 96, which is how the busiest machine runs out of cola while the storage unit is full of pretzels.
How TaroStack does it
TaroStack keeps storage counted and tells you what to buy.
Each product is an item, counted in eaches and bought by the case. Units convert, so a case of 50 received is 50 on the shelf.
Each case is a lot with its best-by date. The oldest goes out first by default, and an alert comes before a date arrives.
Count on a phone, at the storage unit, with no signal. A count shows what each difference is worth.
A reorder list in days of cover, with reorder points suggested from how fast each product really goes.
Every adjustment carries a reason, so pulled, out-of-date product is a number by product.
| The job | Spreadsheet | In TaroStack |
|---|---|---|
| What's in storage now | Last count, less a guess | Counted on a phone |
| What to buy this week | Read down the sheet | The reorder list |
| Which case to load first | Read the box | Oldest lot first |
| What was pulled out of date | A note, sometimes | Adjustments by reason |
TaroStack doesn't read your machines' sales or count their coins: the refill record stays yours. Stock, lots and reordering are on every plan, from $9.99 a month. The first 14 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.
