Retail · Consignment · Hong Kong

Consignment settlement automation for a Hong Kong lifestyle retailer

Three hundred suppliers, two sales systems, one set of settlement rules — and every statement shows how the payment was reached.

200 hoursestimated time saved a month
Target300consignment suppliers settled each month
Target0items paid for that were not on consignment

Representative example — a composite of typical Hong Kong workflows, written from the duties in current job ads. Company names, documents and figures are sample data.

A lifestyle retailer with 4 shops and an online store sells goods from about 300 consignment suppliers. On consignment, the supplier owns the goods until they are sold; each month the retailer keeps its commission and pays the supplier the rest. Getting each settlement right keeps suppliers willing to put their best products on the shelves.

At a glance

The files they already use

  • Shop POS sales export
  • Online store sales export
  • Consignment lists and agreements
  • Returns and refunds
  • Promotion and display fee terms
AI systembuilt around their rules
  • Shop and online codes mapped to one item
  • Sales net of till discounts
  • Commission by product category
  • Returns, promotion share and display fee deducted
  • Only items on the supplier's list

Done automatically

  • Settlement statement per supplier
  • Payment list — for approval
  • Consignment stock report
  • Statement email — drafted

The accounts supervisor approves the payment list before any supplier is paid.

How they used to do it

At every month-end, the accounts clerk exported sales from the shops' POS system and from the online store. The two used different product codes for the same item, so the clerk matched them by hand in Excel, supplier by supplier, using a lookup list the clerk kept up to date by hand.

Each supplier's agreement had its own commission for each category, a monthly display fee and sometimes a share of a promotion. Returns were found by searching the refund records. The statements were typed into a Word template, and suppliers often called to ask how the figure was reached.

The difficulties

  • Two sets of codes. The shops and the online store coded the same candle differently, so a supplier's online sales could easily be left out of its settlement.
  • Wrong commission. A supplier with two categories was charged one rate for everything, and the difference had to be paid the next month.
  • Returns missed. Refunds were recorded in a separate file, so a returned item was sometimes still paid to the supplier.
  • Items not on consignment. A product bought outright could be settled as if it were on consignment.
  • Month-end queries. Suppliers could not see how their payment was reached, so every statement brought a phone call.

Their formula, broken down

A consignment payment looks like one number. Behind it are two sales systems, two commission rates and three deductions. Here is September's settlement for Supplier D, a maker of handmade home fragrance and stationery.

Their formulaPayable = each category's net sales × (1 − its commission) − customer returns − agreed promotion share − monthly display fee
  1. Home fragrance, after 35% commission

    Shop and online codes are first mapped to the supplier's items. Candles: 85 sold × HK$180.00 = HK$15,300.00, less HK$900.00 of till discounts. Reed diffusers: 30 sold × HK$260.00 = HK$7,800.00, less HK$300.00. Net sales HK$21,900.00.

    net sales HK$21,900.00 × (1 − 35% commission)HK$14,235.00

  2. Stationery, after 30% commission

    Notebooks: 120 sold × HK$85.00 = HK$10,200.00, less HK$200.00 of till discounts. Net sales HK$10,000.00.

    net sales HK$10,000.00 × (1 − 30% commission)HK$7,000.00

  3. Take off the customer return

    One reed diffuser sold in August was returned and refunded in September. The supplier's share of it is taken back.

    1 reed diffuser HK$260.00 × (1 − 35% commission)−HK$169.00

  4. Deduct the promotion share and display fee

    The supplier agreed to share HK$600.00 of the autumn promotion, and pays a display fee of HK$800.00 a month.

    −promotion HK$600.00 − display fee HK$800.00−HK$1,400.00

Payable to Supplier D for SeptemberHK$19,666.00

What the system produces

settlement.alveek.ai/2609/S-017Sample data

September sales of Supplier D's goods, both channels

Shop POS export.xlsxOnline store export.xlsxConsignment list.xlsx
ProductShops, pcsOnline, pcsNet sales HK$
Soy candle 200 g (POS 480112 · online CND-200)602514,400
Reed diffuser 100 ml20107,500
A5 notebook903010,000
Mini candle 60 g (POS 480119)80544
Net sales on the consignment list31,900

Settlement statement, Supplier D, September

S-017-2609.pdfPayment list (for approval)
ItemBasisHK$
Home fragrancenet sales HK$21,900.00 × (1 − 35% commission)14,235.00
Stationerynet sales HK$10,000.00 × (1 − 30% commission)7,000.00
Less customer return1 reed diffuser, HK$260.00 × 65%−169.00
Less promotion shareAgreed share, autumn promotion−600.00
Less display feeMonthly, per agreement−800.00
Payable to Supplier D19,666.00

How it was calculated

Payable = each category's net sales × (1 − its commission) − return − promotion share − display fee

= HK$14,235.00 + HK$7,000.00 − HK$169.00 − HK$600.00 − HK$800.00 = HK$19,666.00

Flagged for a person to confirm

  • Mini candle 60 g (POS 480119, 8 sold, HK$544.00) is not on Supplier D's consignment list — kept out of this settlement until the buyer confirms whose stock it is.
  • One A5 notebook was recorded at HK$60.00 at Shop 3 against its HK$85.00 list price, with no discount code — flagged for the accounts supervisor to check whether it was a till error before the statement goes out.

In this example: sales from both channels are mapped to the supplier's items, each category gets its own commission, and an item that is not on the supplier's list is held back instead of being paid.

How we helped

We build one record per supplier — the consignment list with each item's shop and online codes and list price, the commission by category, the display fee and any agreed promotion share. Each month the system reads both sales exports and the refunds, and produces every supplier's statement and a payment list for approval.

  1. Sales in. The shops and the online store keep exporting sales as they do now; the system reads both files and the refund records.
  2. Mapped and checked. Every code is mapped to a supplier's item. Items not on a consignment list, sales below list price with no discount code and stock that would go negative are flagged.
  3. Settled by each agreement. Commission is applied by category, then returns, promotion share and display fee are deducted.
  4. Approved, then paid. The statements and payment list are reviewed by the accounts supervisor; statement emails are drafted for a person to send.

What the system handles

Consignment settlement

Monthly statements for every supplier, by category commission, in the retailer's own format.

Code mapping

One list linking shop, online and supplier codes, with new codes flagged for a person to map.

Consignment stock

Stock by supplier and shop, updated from deliveries, sales and returns, with negative stock flagged.

Supplier payments

A payment list for approval, and each payment recorded against its statement.

Bank reconciliation

Supplier payments and shop takings are matched to the bank statement each month.

Before and after

TopicBeforeAfter
CodesShop and online codes were matched by hand in Excel, from a list kept by one clerk.One shared code list; any new code is flagged for mapping.
CommissionRates were looked up in each agreement, so a supplier could easily be charged the wrong rate.Each category's rate is stored with the supplier and applied every month.
ReturnsRefunds were searched for in a separate file.Every refund is linked to its item and deducted in the right supplier's settlement.
ExceptionsItems not on consignment and unexplained sales below list price were found only if a supplier complained.They are flagged before the statement goes out.
Month-endThree hundred statements took most of the first week of every month.Statements are ready soon after the month closes; the supervisor only reviews and handles the flags.

How much time it saves

  • About 300 supplier settlements a month, each taking around 50 minutes: mapping codes across two exports, applying commissions, finding returns and typing the statement.
  • 300 statements × 50 minutes = 15,000 minutes = 250 hours a month.
  • With the system, the supervisor reviews each statement in about 10 minutes: 300 × 10 = 3,000 minutes = 50 hours.
  • Time saved ≈ 200 hours a month — about 25 working days.
  • At HK$110 an hour (about HK$19,000 a month for one full-time clerk, MPF included), that is about HK$22,000 a month — before counting overpayments on returns and items not on consignment.

These are the working assumptions behind the figure, not an audited result.

How a project like this runs

  1. Free assessment. We sit with your accounts clerk through one month-end and collect both sales exports, ten supplier agreements and their last statements.
  2. Built on your own statements. The settlement rules are rebuilt from your past statements and tested until they reproduce the amounts paid exactly.
  3. Side by side. For two month-ends your clerk settles by hand while the system runs on the same exports, and the two are compared supplier by supplier.
  4. Live, then maintained. Once they match, manual settlement stops. New suppliers and items are added as they come, and we fix anything that comes up.

Questions owners usually ask

  • Do we need to change our POS system or online store?
    No. The system reads the exports they already produce. Only the code list needs to be set up once.
  • Each supplier has a different agreement. Is that a problem?
    No. Commission by category, display fee and promotion shares are stored per supplier and applied every month.
  • Does the system pay suppliers?
    No. It prepares the payment list; the accounts supervisor approves it and a person makes the payments.

The results

  • Every supplier's settlement is worked out from both sales channels and shows how the payment was reached.
  • Commission, returns, promotion shares and display fees are applied by each supplier's own agreement.
  • Items not on consignment, sales below list price with no discount code and negative stock are flagged before anyone is paid.
PythonClaudeExcelEmail

Representative example — a composite of typical Hong Kong workflows, written from the duties in current job ads. Company names, documents and figures are sample data.

← Back to all case studies

Does your company have a formula like this?

Send us one real example — a quotation, an invoice or a month-end statement — and we will show you how the system would work it out. The assessment is free.

Get a free assessment →