Dealer scheme calculation software: Excel, ERP code or an engine
Every manufacturer already has scheme calculation software. In most companies it is a workbook with a dealer sheet, an invoice dump and a column of nested IF formulas, maintained by one person in commercial who cannot take leave in the first week of the month. This page compares that with the two alternatives and shows how to test whichever you choose.
Dealer scheme calculation software takes invoice data and a set of scheme rules and works out what each dealer has earned. There are three ways to do it: Excel, custom code inside the ERP, or a dedicated scheme engine. Excel is flexible but breaks on retroactive slabs, growth over last year, clubbed dealer codes, backdated schemes and returns. ERP code is accurate but slow to change. A dedicated engine is configured by the commercial team and shows the dealer the working. Whichever you pick, test it by reproducing the worked examples printed in your own scheme circulars.
The three options side by side
| Excel workbook | ERP custom code | Dedicated scheme engine | |
|---|---|---|---|
| Who builds a new scheme | A commercial executive, the same day | IT or an external consultant, through a change request | Commercial or sales operations, by configuration |
| Time to go live | Hours, but only as an after-the-month calculation | Weeks for anything that is not a standard rebate condition | Minutes to days if the scheme fits an existing scheme kind |
| When the dealer learns his number | After the period, by credit note or a forwarded sheet | After the period, by credit note | During the period, with progress and the next target |
| Audit trail | File versions on someone's laptop | Strong: postings in the ledger | Rule version, invoices counted, approvals and overrides in a payout ledger |
| Handles unusual circulars | Yes, by formula, with risk | Only with development | Yes if the kind exists; otherwise a gap to be handled openly |
| Typical failure | Silent formula error, wrong lookup, stale invoice dump | Scheme not built in time, so run in Excel anyway | Poor master data or a late invoice feed |
| Best suited to | Few dealers, few schemes, stable rules | A small number of long-running rebate agreements | Many dealers, frequent circulars, dealers who need to see progress |
This is not an argument that Excel is always wrong. A brand with 40 dealers and two annual schemes does not need an engine. The case for software grows with three things: the number of dealers, the number of circulars a month, and how much the scheme depends on the dealer knowing where he stands before the period ends.
Where Excel calculation breaks
The examples below are illustrative. They use one slab sheet throughout: nothing on the first 500 bags, ₹4 a bag for a dealer whose monthly lifting is 501 to 1,000 bags, ₹6 a bag for 1,001 to 1,500, paid on all bags.
1. Retroactive slabs
A dealer lifts 1,200 bags. The circular means ₹6 on all 1,200 bags, which is ₹7,200. The workbook was copied from last quarter's scheme, which paid each slab's rate only on the bags inside that slab: 500 bags at ₹4 plus 200 bags at ₹6, which is ₹3,200. The formula runs without error and the dealer is short by ₹4,000. Nobody notices until the dealer complains, because both numbers look plausible. The incentive slab design guide explains the two settlement bases.
2. Growth over last year
The scheme pays ₹10 a bag on lifting above 110 percent of the same month last year. The dealer lifted 1,000 bags last year and 1,200 this year, so the threshold is 1,100 bags and the payout is 100 bags at ₹10, which is ₹1,000. But the dealer's firm changed constitution in January and got a new code. The lookup against last year's sheet finds nothing and returns zero, the threshold becomes zero, and the workbook pays ₹10 on all 1,200 bags: ₹12,000. That is an overpayment of ₹11,000 on one dealer, and it looks like a star performer.
3. Clubbed dealer codes
The same family runs two billing codes. The circular allows clubbing. Code A lifted 600 bags and code B lifted 550. Calculated separately, each sits in the ₹4 slab: ₹2,400 plus ₹2,200, a total of ₹4,600. Clubbed, the lifting is 1,150 bags in the ₹6 slab: ₹6,900. The dealer is underpaid by ₹2,300 unless someone remembers the clubbing list, which usually lives in an email.
4. Backdated schemes
A circular issued on the 20th is effective from the 1st. The invoice dump in the workbook was pulled from the announcement date, or the executive forgets that a different, overlapping scheme was already calculated on the invoices of the first 19 days. Backdating is normal in Indian trade. It needs every earlier invoice in the window to be re-read against the new rule, which is tedious by hand and automatic in an engine.
5. Returns and credit notes
A dealer lifts 1,020 bags and is paid ₹6 a bag, ₹6,120. In the first week of the next month 40 bags come back and a credit note is raised. Net lifting is 980 bags, which is the ₹4 slab: ₹3,920. The scheme has overpaid ₹2,200, and the workbook for last month is closed. Unless returns are fed back into the period they belong to, billing and returning to cross a slab is free money. The rebate claims process post covers the validation side of this.
None of the five is exotic. Each one occurs every month in a dealer base of a few hundred, and each produces a number that looks reasonable. That is the real weakness of spreadsheet calculation: it fails silently.
Where ERP custom code breaks
ERP rebate functionality is sound for stable agreements: an annual turnover discount with three slabs, accrued monthly and settled by credit note. The trouble starts with frequency and variety. A monthly circular with a growth condition, a premium-grade bonus and a dealer-code list is a development request, and the request takes longer than the scheme runs. In practice the ERP handles the two or three permanent schemes and everything tactical falls back to Excel. The ERP also has no dealer-facing view, so the dealer still waits for the credit note to learn what he earned. The right role for the ERP is the book of record: it supplies invoices and it posts the final credit note. The SAP and Tally integration guide shows that hand-off.
What a dedicated engine changes, and what it does not
A dealer scheme engine holds each scheme as configuration, takes invoices and returns from the ERP every day and recalculates. The commercial team builds the scheme. The dealer sees his position while the month is still open. Returns, clubbing and backdating are handled by the same rules every time.
It does not fix a circular that is ambiguous, a dealer master with duplicate or unmapped codes, or an invoice feed that arrives late. It also has limits of expression: if a circular contains a condition that no scheme kind supports, the honest answer from a vendor is that it needs an extension, not that it will be handled somehow. Ask where those limits are before signing.
How to test calculation accuracy
Reproduce every worked example in your circulars
Most circulars print one or two examples: a dealer lifting so many bags earns so much. Each is a ready test case written by the people who own the scheme. The software must return exactly the printed number. Unotag sets these up as automated tests before go-live, so every later change to the engine is checked against them.
Run a parallel period
Take a closed month. Calculate it in the software and compare with what was actually paid, dealer by dealer. Every difference has one of three causes: the old process was wrong, the configuration is wrong, or the circular was ambiguous. All three are worth finding.
Test the edges deliberately
A dealer exactly on a threshold. A dealer one unit below. A dealer with a return that drops him a slab. Two clubbed codes. A dealer with no purchase last year in a growth scheme. A scheme backdated across a month end. A dealer who hits the cap.
Check the data, not just the logic
Totals of invoice quantity and value in the software should match the ERP for the same period and dealer set. A correct formula on an incomplete feed still gives a wrong payout.
Keep the tests
A test that ran once at go-live is a demo. Tests that run on every change are a control. Ask the vendor which of the two they mean.
For ready-made test cases, how to calculate a dealer scheme payout works six scheme types in rupees, and the QPS scheme calculator lets you check slab arithmetic independently. A periodic scheme audit does the parallel run for past periods.
Evaluation checklist
- Scheme kinds supported, checked against your last twelve months of circulars, not a brochure list.
- Conditions on value and on quantity; incentives as percentage, per unit, fixed and slab; payout on all units or above the threshold.
- Eligibility by geography, sales office, dealer type, price cluster and an uploaded dealer-code list; clubbing of old and new codes.
- Backdated schemes and recalculation on returns and credit notes.
- Cost projection on real purchase history before a scheme is saved.
- Dealer view with progress, next target and the invoices counted, in the dealer's language.
- Approval chain, hold, cap and manual override, all recorded in a payout ledger.
- Settlement output your finance team can use: a credit-note file for the ERP, or UPI payout.
- Invoice feed from your ERP, with a fallback of Excel upload, and a visible data-freshness date.
- Worked examples from your circulars reproduced as automated tests before go-live.
Unotag's engine meets each item on this list and is delivered as Unotag's app, on WhatsApp, or as an SDK inside your existing dealer app. Pricing runs from ₹30,000 to ₹3 lakh a month by monthly active users. The dealer scheme engine page has the detail.
Key takeaways
- There are three ways to calculate dealer schemes: Excel, ERP custom code and a dedicated engine. The right one depends on dealer count, circular frequency and whether dealers need to see progress.
- Excel breaks silently on retroactive slabs, growth over last year, clubbed dealer codes, backdated schemes and returns.
- ERP code is accurate for stable agreements but too slow for monthly circulars, and it gives the dealer no view.
- Test any option by reproducing the worked examples in your own circulars, running a parallel closed month and probing the edges.
Frequently asked questions
What is dealer scheme calculation software?
It is software that applies trade scheme rules to dealer invoices and works out the payout due to each dealer. It can be a spreadsheet, custom code in the ERP or a dedicated scheme engine that is configured by the commercial team and visible to the dealer.
Can dealer schemes be calculated in Excel?
Yes, and for a small dealer base with a few stable schemes it is adequate. It becomes risky with retroactive slabs, growth-over-last-year conditions, clubbed dealer codes, backdated circulars and returns, because formula and lookup errors produce plausible but wrong payouts.
Can SAP or Tally calculate dealer schemes?
An ERP can run standard rebate agreements and post credit notes. Frequent tactical circulars with unusual conditions usually need custom development, so many brands keep the ERP as the invoice source and settlement ledger and calculate schemes in a dedicated engine.
How do I check that scheme calculation software is accurate?
Reproduce every worked example printed in your scheme circulars, run a closed month in parallel against what was actually paid, test edge cases such as thresholds, returns and clubbed codes, and reconcile invoice totals with the ERP.
What is dealer incentive calculation software used for?
It is used to calculate slab, growth, target and cash discount incentives for dealers from invoice data, show dealers their progress during the period, route payouts through approval and produce a credit-note file or payout for settlement.
How does scheme calculation software handle returns and credit notes?
A sound system nets returns against the period the original lifting belonged to and recalculates the slab. If the dealer drops a slab after a return, the payout reduces or the difference is recovered in the next settlement.
How much does dealer scheme calculation software cost in India?
It depends on the model: licences, per dealer fees or usage. Unotag charges by monthly active users, from ₹30,000 to ₹3 lakh a month. Weigh this against overpayments, dispute handling time and the cost of schemes that were never launched because they could not be calculated.