How to calculate monthly consignor payouts from a Shopify or Square sales export
Work out each consignor's payout from the sales report your POS already gives you: net sales × split, refunds included, in a spreadsheet or with ConsignTally.
· 6 min read
This post answers
How do I calculate monthly consignor payouts from my Square or Shopify sales export without replacing my POS?
Too many consignors and too little time! Also the longer you wait to pay your fellow businessers the more stress builds for everybody! So you do the following
- export the month’s sales from Shopify or Square with the consignor on every line (the product vendor in Shopify, or the item category in Square)
- for each consignor, add up net sales which is gross sales minus discounts minus refunds and multiply by that consignor’s split
- you prob don’t need to do this but make sure to subtract anything you already paid vendors
We will look at 2 ways to do your payouts!
What a payout calculation needs
Whatever tool you use, a exquisite monthly payout needs the same five things:
- Every sale line for the month, tagged with its consignor. In Shopify, the easiest way is to set each consigned product’s Vendor to the consignor’s name. In Square, put the consignor’s name in the item’s Category.
- Discounts. If you discounted a consignor’s item, they normally share that discount.
- Refunds and returns. A returned item shouldn’t stay in the consignor’s earnings.
- Each consignor’s split aka How they that dough. The percentage of the net sale that goes to them; the shop keeps the rest cause we need to make money too right?!.
- What you’ve already paid. So a consignor isn’t paid twice, and unpaid balances don’t get forgotten. Which both are huge owies.
The formula for each consignor is then:
payout = (gross sales − discounts − refunds) × split − (already paid… this is might be 0 already)
The example month
Here’s a small October for a shop with three consignors:
| Consignor | Split | What sold |
|---|---|---|
| Mountain Goods | 50% | A $280.00 daypack, two pairs of socks for $60.00, a $198.00 rain jacket with an $18.00 discount, and one pair of socks ($30.00) was later returned |
| Fern Studio | 40% | A $120.00 planter, two plant hangers for $110.00, and a $110.00 terrarium with an $8.75 discount |
| Wildflower Co. | 65% | Three candles for $84.00, a $45.00 print with a $12.00 discount, and two soap bars for $18.00 |
Fern Studio has already been paid for the month. The other two haven’t.
Method 1: the spreadsheet
Start with the sales export from your POS and get it into one sheet with a row per sale line and columns for the consignor, gross sales, discount and refund.
Step 1: add a net column. For each row: = Gross − Discount − Refund. Shopify reports discounts and returns as negative numbers, so check the signs; you may need = Gross + Discounts + Returns instead.
Step 2: total net sales per consignor. On a summary sheet, use SUMIFS (available in both Excel and Google Sheets, now we getting mathy), for example =SUMIFS(Net, Consignor, "Mountain Goods").
For the example month:
| Consignor | Gross | Discounts | Refunds | Net |
|---|---|---|---|---|
| Mountain Goods | $538.00 | $18.00 | $30.00 | $490.00 |
| Fern Studio | $340.00 | $8.75 | $0.00 | $331.25 |
| Wildflower Co. | $147.00 | $12.00 | $0.00 | $135.00 |
Step 3: apply the split. Multiply each consignor’s net by their split: Mountain Goods $490.00 × 50% = $245.00, Fern Studio $331.25 × 40% = $132.50, Wildflower Co. $135.00 × 65% = $87.75.
Notice what the refund did! Without it, Mountain Goods’ net would have been $520.00 and their take home would have been $260.00. The returned socks took $30.00 off net sales, which is $15.00 off their payout. Forgetting to pull refunds into the sheet is one of the easiest ways to overpay a consignor and for you to lose money!!
Step 4: track what’s paid. Add a “Paid” column and an “Owed” column (= Share − Paid). Fern Studio is paid; Mountain Goods and Wildflower Co. are still owed $245.00 and $87.75.
Step 5: write the statements. Filter the sheet to one consignor, copy their lines into a statement, and send it :sunglasses:
This works alright! For a few consignors its not too shabby. It gets a little arduous for a few reasons:
- We have to clean up the columns of the export the same way every time ouch
- Consigner splits are tracked by you in your head or a foreign tab and can be forgotten
- You have to remember to update the cell to “PAID” which you could forget…
- a lot of copypasta
Method 2: ConsignTally
ConsignTally does all of these calculations with a bit more robustness. It sits next to Shopify or Square rather than nuking them and lets you keep going the way you were.
1. Import the export. Upload the CSV on the Import sales page. ConsignTally recognises a Shopify sales report (columns like Product vendor, Gross sales, Discounts and Returns) and Square’s item sales export (where refund rows become refunds). A simple CSV with a date, a consignor column and an amount works too. It guesses which column names the consignor, and you can pick a different one before importing. You see a preview of every line, any rows it will skip and why, and which consignors are new. Imports run in the background, and re-importing an export that overlaps one you’ve already loaded is safe. Lines already imported are skipped.
2. Set each consignor’s split. New consignors found in an import start at 50%. On the Consignors page you set each one’s real percentage once. The example had 50%, 40% and 65%. If a particular item was taken in on different terms, override the split for that line on the statement.
3. Read the month’s balances. The Payouts page shows every consignor with sales that month, with net sales, earnings, balance and status. For the example month:
| Consignor | Split | Net sales | Earned | Balance | Status |
|---|---|---|---|---|---|
| Mountain Goods | 50% | $490.00 | $245.00 | $245.00 | Unpaid |
| Fern Studio | 40% | $331.25 | $132.50 | $0.00 | Paid |
| Wildflower Co. | 65% | $135.00 | $87.75 | $87.75 | Unpaid |
Net sales already have discounts and refunds taken out, and each line’s share is rounded to the cent before being added up, so a statement’s lines always add up to its total. A Total outstanding column shows each consignor’s balance across all months, and a warning appears if anything is still owed from an earlier month.
4. Mark payouts. When you’ve paid someone by cheque, transfer or however you normally do it you click Mark paid. ConsignTally records a payout for the remaining balance and the status changes to Paid. If you clicked it by mistake, Undo payout removes it. ConsignTally doesn’t move money itself; it keeps track of what you’ve paid.
5. Send statements. Each consignor has a monthly statement page listing every line date, order, item, quantity, gross, discount, refund, net, split and their share, followed by the totals, any payouts with their dates, and the balance due. Here’s Mountain Goods’ October statement:
| Item | Gross | Discount | Refund | Net | Split | Share |
|---|---|---|---|---|---|---|
| Summit Daypack | $280.00 | $280.00 | 50% | $140.00 | ||
| Merino Trail Socks ×2 | $60.00 | $60.00 | 50% | $30.00 | ||
| Ridgeline Rain Jacket | $198.00 | −$18.00 | $180.00 | 50% | $90.00 | |
| Merino Trail Socks (return) | −$30.00 | −$30.00 | 50% | −$15.00 | ||
| Total | $538.00 | −$18.00 | −$30.00 | $490.00 | $245.00 |
The return appears as its own line with a negative share, so the consignor can see exactly why their payout is $245.00 rather than $260.00. You can print the statement or download it as a CSV, and there’s a single CSV for the whole month’s payouts if you want it for your books. ConsignTally doesn’t email statements to consignors yet, so you send them yourself.
How this compares with full consignment systems
Tools like ConsignCloud, SimpleConsign and Ricochet are complete consignment management systems so think Salesforce or Oracle. Depending on the product, they cover things like item intake and tagging, consignor accounts and portals, inventory and pricing, and payouts. If you’re setting up a shop from scratch, or you want consignors to log in and check their own balances, a full system might still be the right choice. Definitely see their current features and pricing on their own sites if your leaning this way.
ConsignTally is intentionally much smaller. It’s for shops that are stoked with the point of sale they already have and just want the monthly payout step done properly: import the export, apply the splits, account for refunds, track who’s paid, and produce a statement for each consignor. There’s no new checkout to learn and nothing to migrate.
Try it on your own export
You can load the same example month in ConsignTally with one click and see the numbers above for yourself, then import a real month’s export from Shopify or Square. Start a 14-day free trial of ConsignTally; after the trial it’s $29 a month.
Frequently asked questions
- Do refunds and discounts come out of the consignor's share?
- In ConsignTally, yes. Each line's net amount is gross sales minus discounts minus refunds, and the consignor's split is applied to that net amount. A returned item reduces the consignor's earnings in the month the refund happened.
- What happens if a customer returns an item after I've already paid the consignor?
- The refund is counted in the month it happened. If that leaves the consignor's balance for the month below zero, ConsignTally marks the month as Overpaid instead of Unpaid, so you can see they owe the difference or settle it against the next payout.
- Can one item have a different split from the consignor's usual percentage?
- Yes. Every consignor has a default split, and on the monthly statement you can override the split for an individual line item. The override applies only to that line.
- Does ConsignTally replace Shopify or Square?
- No. You keep selling through Shopify or Square. ConsignTally only reads the sales export they already produce and works out who is owed what; it doesn't process payments or send money to consignors.
- Does ConsignTally email statements to consignors?
- Not yet. Each consignor gets a monthly statement you can print or download as a CSV, and you send it yourself.