A hot sauce founder came to us with a spreadsheet problem she did not know she had.
She had placed her product in 14 retail locations on consignment. No purchase orders. The stores take the product, sell what they sell, and she invoices based on what moved. Simple in theory.
In practice, she was tracking it in her head. Which stores got deliveries last week. How many units she dropped off. What sold. What was still on the shelf. When she last invoiced each store. Whether the invoice was paid.
She knew the numbers were off. She just did not know by how much.
When we built the tracking file and reconciled three months of activity, the number was 3,800. That was the revenue she had earned but not invoiced. Product that had sold at retail. Money the stores owed her. Cash that was sitting in a gap between her memory and her bank account.
Fourteen locations. Three months. Nearly 4,000 dollars gone, not because anyone was cheating, but because nobody was counting.
What the File Does
We built a consignment tracking workbook with four tabs. It handles the complete lifecycle of consignment inventory: what you send out, what sells, what you invoice, and what you reconcile.
Tab 1. Delivery Log
Every time you deliver product to a consignment location, it goes here. Date, store name, store ID, product, quantity delivered, and unit cost. This is your outbound record. It answers: "What did I send, where did I send it, and when?"
The store ID matters. We will come back to that.
Tab 2. Sales Log
When you collect sales data from a store (whether they email you a report, you count the shelf, or they send you POS data), it goes here. Date, store ID, product, quantity sold, retail price, and your revenue share. This is where you see what actually moved.
The revenue share column is critical. Not every consignment deal is the same split. Some stores take 30%. Some take 40%. Some take a flat per-unit fee. If you do not track the split per store, your invoice will be wrong.
Tab 3. Invoice Tracker
When you generate an invoice based on sales data, it gets logged here. Invoice number, store ID, period covered, total amount, date sent, and date paid. This is your accounts receivable for consignment. It answers: "Who owes me money, how much, and for how long?"
Tab 4. Reconciliation
This is the tab that found the 3,800. It pulls data from the Delivery Log and the Sales Log to calculate: total units delivered minus total units sold equals units still on shelf. For each store. For each product.
If the reconciliation shows you delivered 100 units to a store and they reported 60 sold, there should be 40 on the shelf. Next time you visit, count the shelf. If there are only 32, eight units are unaccounted for. That is shrinkage, damage, theft, or a reporting error. Either way, it is your product and your money.
5 Steps to Run It
Step 1. Set Up Your Store IDs
Assign a unique ID to every consignment location. Do not use the store name alone. Store names change, get misspelled, or have multiple locations with the same name. Use a simple code: HS-001, HS-002, HS-003. The prefix is your brand (in this case, hot sauce). The number is sequential.
Put the store ID, store name, address, contact person, contact email, consignment terms (revenue split), and payment terms in a master reference list on a fifth tab or a separate sheet. Every other tab references the store ID, not the store name.
This is why Store IDs matter: when you have 14 locations and one of them is "Corner Market" and another is "Corner Market & Deli" and a third is "Corner Mkt," you need a system that does not depend on consistent spelling. HS-007 is HS-007 no matter who enters it.
Step 2. Log Every Delivery the Day It Happens
Not at the end of the week. Not when you get around to it. The day you drop off product, you open the Delivery Log and enter the date, store ID, products, and quantities. If you do it in the car after the delivery, it takes 90 seconds.
If you wait, you will forget a delivery. You will forget how many units. You will estimate. Estimates compound into gaps. Gaps compound into lost revenue.
Step 3. Collect Sales Data on a Fixed Schedule
Decide on a cadence: weekly, biweekly, or monthly. Then stick to it. Every store, same cadence, no exceptions.
How you collect the data depends on the store. Some stores will email you a report. Some will let you count the shelf. Some will give you POS data. The method matters less than the consistency. Pick a day. Collect from every store. Enter it into the Sales Log.
The hot sauce founder was collecting data "when she remembered." Some stores had current data. Some were two months stale. The 3,800 gap came almost entirely from three stores where she had not collected sales data in over eight weeks.
Step 4. Invoice Within 48 Hours of Sales Data
The moment you have sales data for a period, generate the invoice. Not next week. Not at the end of the month. Within 48 hours.
Why 48 hours: the longer you wait to invoice, the longer you wait to get paid. And the longer the gap between the sale and the invoice, the harder it is to resolve discrepancies. A store will investigate a question about last week's sales. They will not dig into something from two months ago.
Step 5. Reconcile Monthly
At the end of every month, run the Reconciliation tab. For each store, compare total units delivered versus total units sold versus units on shelf (from your last count or the store's report). Flag any discrepancy greater than 5%.
Then act on it. Visit the store. Count the shelf. Ask questions. The reconciliation is not a report you file. It is a trigger for action.
The 2 Failure Modes
We have seen this system break in two ways.
Failure Mode 1. Deliveries Logged, Sales Data Not Collected
You know what you sent out. You do not know what sold. Your Delivery Log is current. Your Sales Log has gaps. Your Invoice Tracker is empty for weeks at a time.
This is the most common failure. It is also the most expensive. Every week of uncollected sales data is a week of revenue you have earned but cannot invoice. At 14 stores with an average of 200 per store per week in consignment revenue, one missed week is 2,800 in delayed cash.
The fix: Tie sales data collection to a calendar event. Every Monday at 9 AM, the task is: collect sales data from every consignment location. It does not leave the to-do list until every store is entered.
Failure Mode 2. Reconciliation Shows a Gap and Nobody Acts
The spreadsheet shows 8 units unaccounted for at a store. You note it. You move on. Next month, it is 12 units. Then 18. The gap grows because nobody asks the question: "Where did those units go?"
The fix: Any discrepancy above 5% triggers a store visit within 7 days. You count the shelf yourself. You talk to the store manager. You find out whether it is shrinkage, damage, a reporting error, or something else. Then you decide whether the consignment relationship is working.
One of the hot sauce founder's 14 stores had a chronic reconciliation gap. When she finally visited, she found that the store was moving her product to a back shelf to make room for a competitor. The units were there, but they were not selling because nobody could see them. That is not a spreadsheet problem. It is a shelf placement problem. But she would never have known without the reconciliation.
Why Store IDs Matter
Here is what happens without Store IDs:
You enter "Corner Market" in the Delivery Log. Your assistant enters "Corner Mkt" in the Sales Log. Your invoice says "Corner Market & Deli." Your reconciliation cannot match them because the system sees three different stores.
Now multiply that across 14 locations and 12 months of data. The reconciliation breaks not because the math is wrong but because the inputs are inconsistent.
Store IDs solve this permanently. HS-007 is HS-007. It does not matter who enters it. The system matches on the ID, not the name. Every tab references the same ID. The reference tab maps the ID to the full store details. One source of truth.
This is infrastructure, not cleverness. It takes 15 minutes to set up and saves hours of cleanup downstream.
Where to Extend It
The four-tab workbook handles the core workflow. As your consignment operation grows, here is where it stretches and where you outgrow it.
Multi-Currency
If you sell consignment in both Canada and the US, add a currency column to the Sales Log and the Invoice Tracker. Use the exchange rate as of the invoice date. Do not convert in your head.
Sales Rep Commission
If you have reps placing product in stores, add a commission column to the Invoice Tracker. Commission is calculated on collected revenue, not invoiced revenue. Track both, pay on collected.
Multi-Warehouse
If you ship consignment from more than one location, add a "ship from" column to the Delivery Log. Your reconciliation now needs to account for inventory in transit between your warehouse and the store.
When to Outgrow the Spreadsheet
The spreadsheet works for up to about 30 consignment locations and 500 transactions per month. Past that, you need a system that handles data entry at speed, automates the reconciliation, and integrates with your accounting software.
The signals that you have outgrown it: data entry takes more than 30 minutes per week, reconciliation discrepancies are increasing because of input errors, and you are spending more time managing the spreadsheet than managing the consignment relationships.
At that point, move to a lightweight inventory management tool (inFlow, Cin7, or a custom Airtable build) and use the spreadsheet logic as the specification for what the new system needs to do.
Get the Template
Download the Consignment Tracker workbook and start using it this week: vantelira.com/toolkit/consignment-tracker
It includes all four tabs, a pre-built Store ID reference sheet, and formulas for the reconciliation. You will need to enter your own store information and consignment terms. The rest is ready to use.
Ops Intel
Signals we are tracking this week:
CPG Q1 earnings across the sector showed a consistent theme: brands with disciplined inventory management outperformed on cash flow even when top-line growth slowed. Consignment inventory is the hardest type to manage because it sits outside your warehouse and outside your direct visibility. If you are not tracking it rigorously, you are subsidizing someone else's retail operation with your cash.
Cocoa futures are holding above 8,500 per metric ton. For any consignment product containing chocolate or cocoa, your margin per unit is thinner than it was six months ago. Thinning margin makes consignment tracking more important, not less. Every unaccounted-for unit at a consignment location is a unit you cannot afford to lose.
The EU's Falsified Medicines Directive is expanding track-and-trace requirements to new product categories. While this applies to pharmaceuticals, the underlying principle, every unit serialized from manufacturer to point of sale, is where consignment tracking for CPG will eventually go. Building the habit now, even in a spreadsheet, puts you ahead of the regulatory curve.
US Manufacturing PMI came in at 48.7 for April, below the 50 threshold that separates expansion from contraction. For small CPG brands that manufacture domestically, a contracting manufacturing sector means your co-packers may have more capacity and more willingness to negotiate. Good time to lock in production slots and pricing.
Manufacturing AI adoption is accelerating. A Deloitte survey found that 62% of manufacturers plan to deploy AI in production planning within 24 months. For small brands, this means your co-packers and suppliers will start using AI-driven demand forecasting. The brands that benefit most will be the ones that provide clean data: accurate forecasts, consistent ordering patterns, and reliable consignment sell-through data. Your spreadsheet discipline today becomes your competitive advantage when your supply chain partners start running AI tomorrow.