Vending Machine Route Spreadsheet: What to Track

Most vending operators start with a spreadsheet, and for the first few machines that is a fine choice. It costs nothing, you already know how to use it, and it forces you to write down what you do on each visit. The trouble is that a sheet only helps if the columns are right from day one. This guide covers which columns to build, how to wire up the math, and the point where a sheet starts costing you more time than it saves.

If you want a head start, you can download the free CSV template and open it in Google Sheets or Excel. It has the columns described below.

The columns a route spreadsheet needs

Think of one row as one thing you did at one machine on one visit. That means one product per row when you restock, so a single visit can take several rows. It feels wordy, but it makes the totals easy later.

The template uses these columns:

• date: the visit date, typed as YYYY-MM-DD so it sorts correctly.
• route: a short name like "Tuesday north" so you can filter by day.
• machine: a unique ID such as M01 or "Lobby snack". Do not reuse IDs.
• location: the business or building where the machine sits.
• commission_pct: the percentage you owe the host, if you pay one.
• product: what you added, spelled the same way every time.
• slot: the slot code, like A1 or C4.
• qty_added: how many units you put in on this visit.
• unit_cost: what one unit cost you, including any case discount.
• cash_collected: cash you pulled out of the machine.
• cashless_collected: card or phone payments for the period, from your processor.
• notes: jams, price changes, a stuck coil, anything you will want to remember.

Cash and cashless collected are per visit, not per product. Put them on the first row of the visit and leave the others blank, or put them on a separate line with no product. Pick one habit and stick with it.

You will also want a few calculated columns to the right: product cost (qty_added times unit_cost), commission owed, and profit.

Set it up in Google Sheets or Excel

Keep two tabs. The first is a log tab with one row per entry, laid out as above. The second is a summary tab that reads from the log. Never type totals into the log itself.

Formulas for the log tab

Say your columns run A to L in the order above, with data starting on row 2. Add these to the right:

• Product cost in column M: =H2*I2
• Gross collected in column N: =J2+K2
• Commission owed in column O: =N2*E2/100

The commission formula assumes your percentage is typed as a whole number, like 10 for ten percent. If the host's commission is a different base, such as net of cashless fees, the formula changes. The guide to calculating vending machine commission explains the options.

Formulas for the summary tab

List each machine ID in column A of the summary. Then in column B, total the money for a month with SUMIFS:

• Sales: =SUMIFS(Log!N:N, Log!C:C, A2, Log!A:A, ">=2026-11-01", Log!A:A, "<=2026-11-30")
• Product cost: the same formula, pointing at column M.
• Commission: the same formula, pointing at column O.
• Profit: =B2-C2-D2, which is sales minus product cost minus commission.

Put the start and end dates in two cells at the top of the summary and refer to them, instead of typing dates inside every formula. Then you change two cells to see a different month.

In Excel the same formulas work. In Google Sheets, you can also use a pivot table on the log tab: machine down the side, month across the top, sum of gross collected as the value.

Small habits that save you later

• Use a dropdown (data validation) for machine, product and slot so you never end up with "Coke" and "coke" and "Coca-Cola".
• Freeze the header row.
• Back up the file once a month by making a copy with the date in the name.
• Enter visits the same day. A week later you will not remember the count.

Where spreadsheets break down

A sheet works until it does not, and it tends to fail in the same few places.

Typing on a phone. You are standing in a hallway with a case of chips in your arms. Entering twelve rows into a spreadsheet on a small screen is slow and full of typos. So the entry waits until evening, and the numbers get fuzzy.

Broken formulas. Someone sorts only one column, or you insert a row and a range shifts. The profit number is now wrong and nothing tells you.

Commission rules multiply. One host gets 10 percent of gross, another gets 8 percent after card fees, a third gets a flat monthly amount. Soon your formula is a nest of IF statements nobody wants to touch.

No sense of what sold. A fill log shows what you added, not what the customers bought. To estimate sales you need to compare the fill to the par level you left last time. That is possible in a sheet, but it takes extra columns and care. The guide to tracking inventory by slot covers how to think about it.

Reports. Profit per machine is the number you actually want, and getting it by month, by route and by location means more pivot tables to maintain.

None of this means you should avoid spreadsheets. Use one while you have a handful of machines, keep the columns clean, and you will have good data to bring with you when you outgrow it. The CSV format in the template is plain on purpose, so it imports almost anywhere.

SnackRouteTracker does this math on your phone: you log a visit, and it works out host commission and profit per machine for you, with CSV export. You can try it here.