The project client is the Russian subsidiary of a major international tobacco manufacturer with a distribution network comprising several hundred thousand points of sale.
The company employs a complex bonus system to reward sales representatives for meeting KPIs. Prior to the project, bonus calculations were performed manually using dozens of Excel spreadsheets, leading to several issues:- processing over 40 spreadsheets to close a single period required significant resources,
- calculation errors were frequently discovered, potentially resulting in incorrect bonus payouts to employees,
- data from the Sales Incentive Program (SIP) was not consolidated.
Key Challenges
- Analyze source documentation and period-end closing logic, document calculation algorithms for metrics (planned, actual, KPI) across various employee categories, and design the data mart structure.
- Transform data types, create and map reference data, develop procedures for automated KPI calculation and data snapshot generation during period-end closing, and build data marts for the processed data.
- Validate algorithms using period-end data snapshots and compare the automatically calculated final metrics against those derived manually in Excel.
Implemented our solution
Assessment and Analysis
The project began with an assessment of the existing approach. ICL Services analysts worked with the client to define the operating parameters for the future algorithm: the system had to calculate remuneration autonomously—pulling data from the data warehouse—with minimal human intervention.
Key challenges included the sensitivity of payout data and the large number of factors influencing the final amount. During this phase, experts analyzed the established bonus calculation system, translated it into technical formulas, and defined the logic for the data marts.
Development
Based on the approved requirements, experts created reference datasets and developed procedures for KPI calculations.
The analysis phase resulted in the creation of seven data marts (tables) that served as the foundation for automating calculations. The data warehouse was selected as the core tool because it ensures proper data organization, the necessary processing speed, and transparency across all calculations.
Testing
The team compared the algorithm's output with data from Excel spreadsheets and verified the system's accuracy. No critical discrepancies were found. An additional outcome of the testing was the generation of reports on bonus accruals and KPI achievement—reports that had previously been unavailable in the client's analytical data warehouse. ICL Services experts successfully integrated the new solution without disrupting the company's business processes.
Results
- The bonus calculation process has been fully migrated to the corporate data warehouse: employees no longer need to manually consolidate over 40 Excel spreadsheets, as the data is automatically processed by an algorithm to generate the required report for each employee.
- The time required to close the period has been reduced to a minimum: while this task previously took a dedicated employee several days each month, the system now performs all calculations automatically, freeing up specialists' time for more important tasks.
- The successful launch of the project strengthened the client's trust: ICL Services is continuing its collaboration with them on other projects as well.
