Vending Machine Inventory Spreadsheet: Setup and Formulas

A vending machine inventory spreadsheet tracks what is loaded, what remains, what sold, and what needs restocking. Start with one row for each machine, product, and service visit, then use formulas to calculate sales and refill quantities from the counts you enter.

What the Spreadsheet Tracks

Unlike a general stock list, a vending inventory sheet needs to connect a product to a specific machine and a particular visit or count period. The same snack can sell at different rates in two locations, and each machine may have different capacity or product selections. Recording those details lets you plan restocks by machine rather than guessing from a business-wide total.

A basic spreadsheet can answer practical questions: What is in each machine now? What should be loaded on the next visit? Which items are selling quickly? Did the number of units sold match the cash or electronic sales report? A spreadsheet does not automatically know what is inside a machine; its usefulness depends on timely, accurate counts or an import from compatible machine telemetry.

Choose a Useful Row Structure

For a small route, record one row per machine-product combination per visit. For example, a service record for Machine 01 and bottled water on November 3 is one row; the next visit creates another row for that same machine and product. This preserves a history instead of replacing yesterday’s count with today’s. Include a visit date so records can be sorted and filtered chronologically.

Use a stable product code, or SKU, as well as a readable product name. Names can vary in spelling, and package sizes can be easy to confuse. A code such as WAT-500 can distinguish a 500 mL bottle from a larger bottle with a similar name. Give each machine a unique identifier too; a location name alone may be ambiguous if there is more than one machine at a site.

Keep a separate product list for shared details such as description, supplier, and unit cost, and a machine list for location and capacity. The visit log then records counts and activity. This arrangement reduces repeated typing and makes it easier to update a product name or cost consistently. For a very small operation, one well-organized table can work, but avoid creating a separate worksheet for every visit if you want to compare history later.

Recommended Columns

A practical service log can include these fields. Add only fields that have a clear use, and keep a consistent meaning for each count:

Visit date, machine ID, and location: identify when and where the count was taken.
SKU and product name: identify the exact item being counted.
Opening quantity: units recorded in the machine after the previous visit, or the initial count when tracking begins.
Units added: product loaded during the current visit.
Closing quantity: the physical count remaining after service.
Par level or target quantity: the desired stock level for that product in that machine.
Unit price and unit cost: separate the amount charged to customers from the amount paid to acquire the item.
Notes or exception: record a jam, expired item, damaged package, or count that needs checking.

Make the unit of measure explicit. A quantity of 12 might mean twelve cans, twelve cases, or twelve individual items; those are not interchangeable. If products are purchased by the case but loaded individually, record the conversion factor in a product list and be clear about which unit the log uses.

How to Use It During a Restock

A repeatable visit process makes the numbers more reliable. Before a route, review low-stock items and prepare the quantities to take. At the machine, count the remaining products, note items removed as expired or damaged, and record everything loaded. After service, compare the physical count with the quantity the machine or sales report says should remain, where that information is available.

Enter the visit date and select the machine and product identifiers from consistent lists.
Record the opening count and units loaded. If you remove spoiled or damaged items, record that separately rather than treating them as sales.
Count the closing stock after filling the machine and enter that number.
Review calculated sales and suggested refill quantities, then investigate unusual results before relying on them.
Save or sync the workbook and keep a dated backup according to your normal recordkeeping process.

For a new machine or product, establish an initial physical count and mark it as the starting point. Do not treat an unknown opening count as zero unless the machine was actually empty. Otherwise, the first calculated sales figure will be misleading.

Excel Formulas for Sales and Refills

The following examples use Microsoft Excel for Microsoft 365 on Windows and ordinary cell references. Assume row 1 contains headers, with opening quantity in E, units added in F, closing quantity in G, units sold in H, target quantity in I, and suggested refill in J. Put formulas in row 2 and fill them down for later records. Keep counts in the same unit and check that all three count fields refer to the same service interval.

With opening quantity in E2, units added in F2, and closing quantity in G2, enter this in H2 to estimate units sold:

=E2+F2-G2

If the row contains an opening count of 8, 12 units added, and a closing count of 5, the result is 15 units sold. This is an inventory-balance calculation, not proof that every missing unit was purchased. A damaged item, free vend, or counting error can also affect the difference, so record known removals separately.

With target quantity in I2 and closing quantity in G2, enter this in J2 to calculate a nonnegative suggested refill:

=MAX(0,I2-G2)

If the target is 20 and 5 remain, the suggested refill is 15. If 22 remain against a target of 20, the result is 0 rather than a negative quantity. The target should reflect the machine’s actual capacity and your service plan; a formula cannot decide whether a product belongs in a particular machine.

If you want a sales-value estimate, use the selling price for the same service interval. With units sold in H2 and selling price per unit in K2, enter this in L2:

=H2*K2

For 15 units sold at 2.50 each, the calculated sales value is 37.50 in the workbook’s currency. This is a gross sales estimate, not profit: it does not subtract product cost, commissions, refunds, taxes, card fees, or other expenses. Apply a currency number format to price and value columns so amounts are easy to read.

Make the Workbook Easier to Maintain

Format the visit log as an Excel table by selecting the data and choosing Insert > Table. Tables extend formatting and calculated-column formulas as rows are added, and their filter controls help you view one machine, product, or date range. Give the table and columns clear names if you later use structured references. In Excel for Mac, the same Insert > Table command is available; wording and ribbon layout can vary slightly by release.

Use Data > Data Validation to create drop-down lists for machine IDs, SKUs, and other controlled entries. This helps prevent near-duplicate values such as Machine 1 and Machine 01 from becoming separate categories in a report. Freeze the header row with View > Freeze Panes if you work with a long log, and use filters rather than deleting older records you may need for comparisons.

For a basic warning, select the closing-quantity cells and choose Home > Conditional Formatting > Highlight Cells Rules to flag low values or unusual negatives. A negative calculated sale count may indicate an incorrect opening count, a missed adjustment, or a typing error. Formatting is a prompt to investigate, not a substitute for a physical count.

Reconcile Counts and Handle Exceptions

Inventory movement is not always the same as customer sales. A product may be removed because it expired, was damaged, was used to test a dispenser, or was given away. If those units are included in the sales calculation, the spreadsheet will overstate paid sales. Add columns for waste, samples, or other adjustments when they occur often enough to matter, and define how each field is used.

A useful control is to compare spreadsheet sales with the machine’s sales or payment report for the same dates. Differences can result from timing, refunds, cash collection periods, free vends, miscounts, or a product mapped to the wrong selection. Compare like with like: a machine report covering Monday through Sunday should not be compared with a spreadsheet count covering Tuesday through the next Monday.

When a mismatch appears, preserve the original count and note the correction rather than silently overwriting history. Add who made the change and why if multiple people maintain the file. For cash handling or formal accounting records, follow the reporting process required by the business; an inventory spreadsheet alone does not replace those records.

Use the Data to Plan Better

After several visits, the log can show which items need frequent replenishment and which repeatedly remain near their target. Compare products over similar time spans and locations. A busy office machine and a machine in a seasonal venue are not necessarily comparable. Averages based on only one service interval can be distorted by an event, a holiday, or an unusually long gap between visits.

Use observed demand to review par levels, but leave room for delivery lead time and visit frequency. If a product sells faster than the route can replenish it, a higher target may reduce stockouts if the machine has capacity and the product remains within its date limits. If stock consistently remains unsold, consider a lower target or a different selection. Track expiry dates where relevant; a high target is not helpful if it increases waste.

Keep price and cost history in mind. If a supplier changes the purchase price, overwriting the only cost field can make older margin estimates inaccurate. Either store the cost used for each purchase or keep dated cost records. Reported sales value and item cost are useful operational indicators, but they are not a complete profit calculation.

Spreadsheet or Inventory App?

A spreadsheet is often sufficient for a small route where counts are entered manually and one person or a small team can maintain a shared file. It is flexible, inexpensive to start, and easy to adapt to unusual products or machine layouts. It also relies on people to enter consistent data, manage versions, and notice errors.

Dedicated vending or inventory software may be more suitable when a business needs integrations with machine telemetry, route planning, automated alerts, multiple user roles, or consolidated reporting across many machines. Features vary by provider, so confirm that a product supports the machine models, payment systems, and workflows in use before choosing it. Some businesses use a spreadsheet for analysis alongside a separate system for transactions, but avoid maintaining conflicting versions of the same count without a clear source of truth.

Frequently Asked Questions

What should a vending machine inventory spreadsheet include?
A useful starting point is visit date, machine ID, SKU, opening count, units added, closing count, target quantity, and notes for exceptions. Add price and cost fields if you need sales estimates or purchasing analysis, and define whether counts are individual items or cases.

How do I calculate how many items sold?
Subtract closing stock from opening stock plus units added. For example, 8 opening units plus 12 loaded minus 5 remaining equals 15 units accounted for as sold or otherwise removed. Record waste and other non-sale removals separately if you need a closer sales figure.

How do I know how much to restock?
Subtract the closing count from the target quantity, but do not use a negative refill amount. The target should be feasible for the machine and suitable for demand, visit timing, and product shelf life. Check the machine’s capacity before loading the suggested amount.

Can Excel track several vending machines in one sheet?
Yes. Use one consistent log and include a machine ID on every row. A filterable Excel table lets you view one machine at a time while retaining a combined history. Avoid relying only on location names if a site has multiple machines.

Why does my spreadsheet show negative units sold?
A negative result means the closing count exceeds the opening count plus recorded additions. Check for a wrong count, an omitted load, inconsistent units, or a row that combines different service periods. Correct the source data and document the reason rather than hiding the result.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.