Vending Inventory Spreadsheet: Track Stock and Reorders

How-to basics · Updated November 21, 2026

A vending inventory spreadsheet is a workbook for recording what each machine holds, what needs refilling, and which products should be reordered. In Excel for Microsoft 365, start with one row per product per machine, add fields for capacity, on-hand quantity, reorder point, and unit cost, then use formulas to calculate refill quantities and flag low stock.

What a vending inventory spreadsheet tracks

Vending inventory is different from a warehouse count because the same product can be stored in several machines, each with its own stock level and capacity. A useful spreadsheet therefore tracks inventory at the machine level, rather than keeping only one total for each product. If a site has three machines, a row for bottled water in each machine makes it possible to prepare the right refill for each location.

A basic workbook can answer practical questions: What should the operator bring on the next visit? Which items are close to selling out? How much stock is tied up in products? Which items are nearing their expiration dates? It does not automatically know what has sold; counts or sales data must be entered or imported for the figures to stay useful.

For a small operation, one well-organized worksheet may be enough. A larger route can use separate sheets for machine details, current stock, refill visits, and product or supplier information. The central principle is to use consistent product identifiers and record counts in a repeatable way.

Set up the main inventory sheet

Create a worksheet named Inventory and use one row for each product in each machine. In Excel, enter the headings in row 1, select the range, and choose Insert > Table. Confirm that the table has headers. A table expands as rows are added and makes it easier to filter by machine, product, or status.

Useful columns include:

  • Machine ID and location: a stable identifier and a site or building name. Avoid relying only on a nickname that might change.
  • SKU and product name: use a unique stock-keeping code where available, plus a readable description such as “still water, 500 ml.”
  • Capacity and on hand: the number of units the slot or machine can hold and the latest physical count.
  • Reorder point: the quantity at which the item should be considered for replenishment. This is a trigger, not necessarily the amount to buy.
  • Unit cost and supplier: record the purchase cost and the vendor or source, with a date or note when costs change.
  • Last counted, last restocked, and expiry date: dates help identify stale counts and products that need rotation.

Use the same unit throughout each row. If a case contains 24 cans, decide whether inventory is recorded as individual cans or cases and keep the convention consistent. A case quantity can be stored in a separate field if conversion is important. Mixing cases and individual units is a common reason spreadsheet totals become misleading.

Record counts and refill visits consistently

A spreadsheet is only as current as its inputs. During a visit, identify the machine, count each product, enter the physical on-hand amount, and record the date. If you refill the machine, record what was added as well as the new count. This creates a basic audit trail and helps explain differences between the previous visit and the current one.

For a clearer history, keep a second worksheet named Visits or Transactions rather than overwriting every prior refill. One row per event can include date, machine ID, SKU, quantity removed or added, and a note. The current Inventory sheet can remain a convenient snapshot, while the log preserves the details needed to investigate discrepancies. Do not treat a refill quantity as sales: product may also be removed for damage, expiration, a machine jam, or a count correction.

At each visit, compare the physical count with the recorded count before changing it. If the numbers differ, enter the physical quantity as the new count and note the variance or reason when known. A quick adjustment without a note may fix today’s count but makes recurring shrinkage, recording mistakes, or machine issues harder to spot.

Use formulas for refill amounts and low-stock alerts

Formulas can remove repetitive arithmetic, but they cannot replace accurate counts. For a simple layout with headers in row 1, put Capacity in column D, On hand in E, Reorder point in F, Unit cost in G, Stock value in H, Refill quantity in I, and Status in J. Each formula below is entered in row 2 and filled down. In this example, a machine has capacity 20, 4 units on hand, a reorder point of 6, and unit cost of $1.25.

With capacity in D2 and on-hand quantity in E2, calculate the number of units needed to fill the machine with =MAX(0,D2-E2). For the example, the result is 16 units (20 minus 4). This assumes the operator intends to fill to full capacity; if the target is lower than capacity, use a separate target-stock column instead.

With on-hand quantity in E2 and reorder point in F2, show a simple alert in J2 with =IF(E2<=F2,"RESTOCK","OK"). The example returns RESTOCK because 4 is less than or equal to 6. The formula uses the reorder point as a threshold, not as a recommendation for the purchase quantity.

With on-hand quantity in E2 and unit cost in G2, calculate the value of the stock in H2 using =E2*G2. The example returns $5.00 because 4 units multiplied by $1.25 each equals $5.00. This is an estimated inventory cost, not a selling-price or profit calculation.

With calculated stock values in H2:H100, total the listed inventory value in H101 using =SUM(H2:H100). For the result to be meaningful, include only relevant product rows in that range and make sure each value uses a comparable cost basis. If you use an Excel table, structured references can also make formulas easier to maintain as the table grows.

To make alerts easier to scan, select the Status cells and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Set a format for RESTOCK. On Mac, the same Conditional Formatting options are available from the Home tab, though dialog placement can differ. The color is only a visual aid; filter or review the actual status text as well.

Choose sensible reorder points and targets

A reorder point should reflect how quickly an item sells and how often the route is serviced. If a product usually sells 10 units between visits, a threshold of 2 would probably be too low unless replenishment is available quickly. A practical starting point is to review counts and sales over several visits, then set a threshold that gives enough stock to cover expected demand until the next refill. Demand varies, so adjust the number when seasons, events, or site traffic change.

Keep the reorder point separate from the refill target. The reorder point tells an operator when to act; the target says how much stock to carry after acting. A machine may trigger a refill at 6 units but be filled to 20. Another product may be replenished only to 15 because it sells slowly or has a short shelf life. This distinction reduces the risk of treating every low-stock alert as an instruction to purchase a full machine capacity.

Pack sizes also matter. If a supplier sells a product only in cases of 12, the spreadsheet’s calculated machine refill can still be 5 units, while the purchasing decision may require a full case. Keep machine replenishment quantities and supplier order quantities conceptually separate, and note case sizes so staff do not mistake one for the other.

Manage several machines and routes

Use stable IDs for machines and locations, then use Excel table filters to view one site or one route at a time. A product name alone is not a unique record when the same item appears in multiple machines. The combination of machine ID and SKU identifies the specific inventory line. Consistent IDs also help match a stock sheet to a visit log or a sales export.

For a small route, filtering the main table may be all that is needed. For more analysis, a PivotTable can summarize units by product, machine, or location. In Excel for Microsoft 365 on Windows, select a cell in the table and choose Insert > PivotTable. A PivotTable summarizes the data provided; it will not correct missing counts, inconsistent product names, or duplicate records. Refresh it after the source data changes.

Consider access and change control when multiple people update the workbook. Store a shared file in a location where authorized staff can access the current copy, and agree who edits counts, product details, and formulas. Protecting formula cells can reduce accidental edits, but protection is not a substitute for keeping a backup or checking changes.

Common spreadsheet problems to prevent

  • Duplicate product names: “Cola” and “Cola 12 oz” may refer to different items. Use SKUs or another consistent code and keep descriptions specific.
  • Stale counts: display a last-counted date and review rows that have not been verified recently. A low-stock formula based on an old count can prompt an unnecessary trip or miss a real shortage.
  • Negative or impossible quantities: validate entries so counts cannot exceed capacity or fall below zero without an intentional adjustment. Excel’s Data > Data Validation tools can restrict entries to whole numbers within a chosen range.
  • Overwriting formulas: distinguish input columns from calculated columns with formatting, and protect formula cells if appropriate. Check that formulas filled down cover newly added rows.
  • Treating stock value as sales: unit cost multiplied by quantity estimates stock cost. It does not show revenue, margin, spoilage, or cash collected.

Before relying on totals, sort or filter for blank IDs, missing counts, and dates that appear out of order. A few minutes of validation can reveal a typo such as 400 units in a machine with a capacity of 40. Keep a dated backup before making a major structural change, especially if the workbook is used for routine ordering.

Expiry dates, storage, and product handling

For products with date-sensitive stock, recording an expiry date can help staff rotate older items first and remove items that should no longer be sold. A single expiry field on the machine inventory row is insufficient if the same SKU has units from different batches with different dates. In that case, use a separate batch or stock log with quantity and expiry date per batch, or a clearly managed note that identifies which units are being rotated.

Spreadsheet reminders do not establish whether a product is safe to sell. Staff should follow the product label, supplier guidance, machine instructions, and applicable local requirements for storage and handling. Record relevant temperature or inspection checks only when the operation actually performs them, and do not use a spreadsheet entry as a replacement for required procedures.

When a spreadsheet is enough—and when to upgrade

A spreadsheet is often suitable when the route is small, counts are entered manually, and a few people can keep one shared file accurate. It is inexpensive to adapt and makes it easy to add columns for a specific product mix. It becomes less suitable when there are frequent visits, many machines, multiple users editing simultaneously, or a need for live sales and telemetry data.

Alternatives include a dedicated vending-management system, inventory software, or a point-of-sale platform connected to machines that support data feeds. Such systems may automate some reporting, but capabilities, integrations, and costs vary by provider and equipment. Before switching, identify the actual problem: inaccurate counts, slow route planning, purchasing errors, or lack of sales visibility. Compare those needs with the software’s supported machines, data export options, training requirements, and recurring fees. A well-maintained spreadsheet can also remain useful for exports, audits, or a small pilot.

Frequently Asked Questions

What should a vending inventory spreadsheet include?
At minimum, include machine ID, product or SKU, capacity, physical on-hand count, reorder point, and last-counted date. Unit cost, supplier, refill quantity, and expiry details are useful additions when they support an actual operating task.

How do I calculate how many items to refill?
Subtract the current on-hand quantity from the chosen target quantity, and do not allow a negative result. If the target is the machine’s capacity, the Excel formula is =MAX(0,D2-E2) when capacity is in D2 and on hand is in E2. The result is only as accurate as the entered count and target.

Should inventory be tracked by machine or by product?
For refill work, track each product by machine because stock levels differ by location. You can summarize the same SKU across machines for purchasing, but retain the machine-level rows so a total does not hide which machine is empty.

Can Excel automatically know what sold?
Not from a manually maintained inventory table alone. Sales need to be entered, imported from a report, or received through a compatible system integration. Otherwise, the spreadsheet shows the latest recorded count, not a live machine balance.

How often should vending stock be counted?
Count it at each service visit as a practical baseline, then adjust visit frequency to sales speed, spoilage risk, and how quickly low stock causes a problem. High-demand items or date-sensitive products may need more frequent attention than slow-moving, shelf-stable items.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.