Vending Machine Inventory Spreadsheet Template for AI Smart Coolers
A stock worksheet you can build in Excel or Google Sheets
A vending machine inventory spreadsheet should identify the machine, SKU and counting period, calculate expected stock from recorded movements, and compare that result with a physical count. Keep payment refunds separate from physical product returns. This template provides a copyable column layout and formulas for a manual stock check at an AI smart cooler.
Use one row per machine, SKU and counting period. It is a manual worksheet, not a live connection to WEIMI software. For the service-visit process and route packing decisions, use the AI smart cooler restocking guide.
1. Create the inventory worksheet
Create a sheet named Inventory. Enter the following headers in row 1, from column A through O. Quantities are individual sellable units, not cases or orders. Use a consistent SKU code for each product and packaging variant.
| Column | Header | What to enter |
|---|---|---|
| A | Period end | Date and time of the physical count, with a consistent time zone. |
| B | Machine ID | A stable identifier, not just a location nickname. |
| C | SKU | The product identifier used in your stock records. |
| D | Opening units | Verified sellable stock at the start of this period. |
| E | Units added | Restocks and transfers into this machine during the period. |
| F | Units sold | Item quantities actually sold within the same period. |
| G | Non-sale removals | Expired, damaged, sampled or transferred-out units. |
| H | Sellable returns | Physical returns verified as suitable to return to stock. |
| I | Expected units | Calculated closing stock. |
| J | Counted sellable units | Your physical count, excluding unsuitable stock. |
| K | Variance | Counted units minus expected units. |
| L | Unit cost | Your purchase cost per unit, in one stated currency. |
| M | Counted stock cost | Counted sellable units multiplied by unit cost. |
| N | Review status | A reminder to investigate a difference. |
| O | Notes | Reason, reference and reviewer; do not enter customer card details. |
Record the period start in your workbook notes or add a separate column after O. Keep the column positions above unchanged if you use the formulas below. Zero means a confirmed zero; leave unknown inputs blank.
2. Add these formulas in row 2
Enter your data in D–H, J and L. Put the formulas below in I2, K2, M2 and N2, then fill them down for additional rows. The formulas leave calculated cells blank until the required numeric inputs are present.
| Cell | Formula | Meaning |
|---|---|---|
| I2 | =IF(COUNT(D2:H2)<5,"",D2+E2-F2-G2+H2) |
Opening + added − sold − removed + sellable returns. |
| K2 | =IF(COUNT(I2,J2)<2,"",J2-I2) |
Negative means fewer counted units; positive means more. |
| M2 | =IF(COUNT(J2,L2)<2,"",J2*L2) |
Stock at purchase cost, not retail sales value. |
| N2 | =IF(K2="","",IF(K2=0,"Matched","Review")) |
A count match or an investigation reminder. |
These formulas use English function names and commas. Spreadsheet locale settings may require semicolons or localized function names. Format quantity columns as whole numbers and L–M as currency. Protect formula columns against accidental edits.
3. Check the worked example
This is a hypothetical arithmetic example, not WEIMI operating data or a stocking recommendation. Enter the following numbers in row 2 for a sample bottled drink:
| D: Opening | E: Added | F: Sold | G: Removed | H: Returns | J: Counted | L: Unit cost |
|---|---|---|---|---|---|---|
| 24 | 12 | 20 | 1 | 0 | 14 | 1.20 |
The results should be 15 expected units, −1 variance, 16.80 counted stock cost and Review. A missing unit is a difference to investigate; the worksheet does not establish theft or an AI recognition error.
If you change counted units to 15, variance becomes zero, counted stock cost becomes 18.00 and status becomes Matched. If you clear an input in D–H, expected units and dependent variance/status should remain blank. Test these cases before entering a real route.
4. Keep a separate movement log
A summary row is easier to verify when each stock movement has a reference. Create a second sheet called Movements with these columns:
| Timestamp | Machine ID | SKU | Movement type | Units | Reference | Operator |
|---|---|---|---|---|---|---|
| Use a consistent time zone | Match Inventory | Match Inventory | Added / Sold / Removed / Sellable return | Positive whole number | Refill, sale or removal record | Responsible person |
Totals from this log populate E–H for the selected machine, SKU and period. Do not count a transfer twice: it is a removal at the source machine and an addition at the destination. Use a shared transfer reference. If an item was returned before checkout and was never recorded as sold, do not add a second return entry.
A financial refund does not automatically restore inventory. Record a return only when the physical product is back and can appropriately be sold. Follow your food-handling procedure; the spreadsheet cannot decide whether returned or temperature-affected goods are safe.
5. Close the count without hiding differences
- Use the same cutoff time for the physical count and the sales export. Note any sessions that are still processing.
- Check item quantities rather than order counts. A single AI cooler session can contain several products.
- Review missing restocks, samples, expiry removals, transfers and SKU mapping before adjusting stock.
- Keep the original variance and document the resolution in Notes. Do not rewrite sales to force a match.
- Carry the verified closing quantity into the next period. Do not overlap periods or count the same replenishment twice.
For larger routes, manual entry becomes harder to maintain. Ask your supplier to demonstrate the available inventory exports, timestamps, permissions and adjustment workflow for the quoted configuration. This template does not promise any particular software feature or integration.
Frequently asked questions
Can I use this in Excel and Google Sheets?
Yes. Create the columns in the stated order and enter the formulas. Check your locale's formula separators and validate the worked example before using real data. This page provides a manual build-your-own template rather than a downloadable workbook.
Does it automatically import AI vending sales?
No. Enter verified totals or use exports supported by your actual platform. Automatic synchronization would need a separate, verified integration.
Should a refunded sale be removed from units sold?
Not solely because money was refunded. Keep the physical movement and payment records distinct. A sold item that was kept by the customer still left the cabinet.
Is a zero variance proof every transaction was correct?
No. Offsetting errors can produce a matching total. Investigate repeated SKU issues and preserve supporting transaction and service records.
Can this worksheet value my entire business inventory?
It provides a simple counted-stock-at-unit-cost calculation. Warehouse stock, damaged goods, taxes and formal accounting valuation need their own records and appropriate accounting treatment.
Put the worksheet into your operating routine
Start with one machine and a small set of SKUs. Validate the calculations against a physical count before expanding across a route. Keep replenishment planning in the restocking workflow and use this sheet as the count record.
Planning new equipment? Review WEIMI AI vending machines and ask which stock records and exports are available for your intended configuration.
