Consignment and Commission Reporting
How a manufacturer moved commission calculations out of Excel into live Zoho Analytics reports, with consignment stock adjustments and bin-level tracking handled in Books.
Client details generalized to protect confidentiality.
Manufacturers or distributors calculating commission manually, especially where consignment stock complicates what counts as a sale.
The Challenge
Commission was calculated in Excel from exported sales data, which took days each cycle and produced figures reps disputed. Consignment complicated it further: stock held at customer locations was not sold until consumed, so a shipment was not a commissionable sale, and telling the difference required manual adjustment. Bin-level location of consignment stock was tracked in a separate sheet that was rarely current.
The Solution
Commission Reporting in Analytics
Commission calculates in live reports from transaction data rather than being rebuilt in Excel each cycle.
- Commission logic as Analytics formulas
- Rates by product and rep
- Consignment consumption as the trigger
- Per-rep statement views
Consignment Stock Adjustments
Consignment shipments and consumption are handled distinctly, so commission follows consumption rather than shipment.
- Consignment shipment as a transfer
- Consumption posted on customer usage
- Adjustment entries for reconciliation
- Consigned value by customer
Bin-Level Tracking
Consignment stock is tracked to bin level, so what is at each location is known rather than estimated.
- Bin locations per consignment site
- Quantity by bin
- Cycle count support
- Discrepancy reporting by location
Reconciliation and Dispute Resolution
Reps see the transactions behind their commission, which is what ended most disputes.
- Drill-through from commission to transactions
- Adjustment history visible
- Period comparison per rep
- Exception flags before publication
Apps in This Solution
Zoho Books
Zoho Analytics
Zoho Inventory
Under the Hood Technical detail
- Commissionable Event
- Commission triggers on consignment consumption rather than shipment, which is the correct treatment and was the source of most of the Excel complexity. Encoding it once in Analytics removed the manual adjustment entirely.
- Formula Location
- Commission logic lives in Analytics rather than in spreadsheets or in the transaction records themselves. Defining it once means every rep’s statement uses the same calculation, which is the actual fix for disputes.
- Bin Granularity
- Bin-level tracking exists because consignment discrepancies are location-specific, and a site-level count cannot tell you which bin is short. Cycle counting at bin level is what made the discrepancy reporting actionable.
- Drill-Through
- Reps can trace a commission figure to its underlying transactions. Disputes dropped not because the numbers changed but because they became inspectable, which is usually the case with commission disagreements.
- Notable Constraint
- Consignment consumption is reported by the customer, so accuracy depends on their reporting timeliness. The consigned value report highlights sites that have not reported in a defined period, so a reporting gap is visible rather than reading as zero consumption.
The Results
- Commission calculates in live reports instead of days of Excel work.
- Commission follows consignment consumption rather than shipment.
- Reps can trace their figures to transactions, which ended most disputes.
Commission Rebuilt in Excel Each Cycle?
We have built commission reporting and consignment handling across Zoho Books, Inventory, and Analytics. Tell us how yours is calculated.