Automating the calculation of bonuses for management personnel based on a KPI system.
Problem, task, challenge:
- decentralized bonuses calculation in separate files for each department, manual consolidation into a single report resulted in high labor costs and risk of applying different calculation methodologies to different departments;
- a large number of source data extracted in dissimilar formats from 8 different non-integrated systems;
- complex KPI methodology that affects various areas and undergoes changes almost every quarter;
- impossibility to perform KPI and bonuses calculation more than once every quarter as it was time-consuming;
- lack of user documentation.
It took management staff a total of 3-4 days to perform and check the calculation.
Result (the value received):
|
Centralized calculation for all departments allowed to reduce labor costs of department directors by delegating the calculation of bonuses to one dedicated employee.
|
|
Capability to calculate bonuses every month and reduce the risk of errors through deep automation of business logic.
|
|
Automatic data fetching and consolidation on all KPIs, which reduced labor costs for integration of information from 10 non-integrated data sources. This enables fast update of calculations in case source data change suddenly.
|
|
Calculation flexibility and quick adaptation to changes in KPI system, the list of departments and employees, which reduces labor costs for bonus calculation.
|
|
Centralized management of KPI system and classifications through directories enabled all departments to apply the same logic for bonuses calculation.
|
Calculation transparency and structuring, as well as step-by-step instructions allow to transfer the functional responsibility to a new employee without significant costs.
|
|
Persistence of classifications from period to period.
|
|
Capability to make edits in special manual entry fields to leave an audit trail. |
Proposed technical solution:
- Calculation centrally performed by one employee for all departments that takes 3-4 man-hours. Final calculation architecture is as follows:
- System of directories for the flexible configuration of indicators set, their shares in bonuses amount and target values, as well as data mapping from different systems.
- Automatic loading and consolidation of a large amount of data in different formats via buttons provided by the special FIN.by Tools add-in (automatic initial data processing using Power Query technology).
- Automatic calculation of key performance indicators for each department.
- Automatic reconciliation of target and actual indicators.
- Automatic bonuses calculation with a breakdown for each employee in one table based on performance of KPI indicators by departments.
- Option of manual editing allows to adjust automatically calculated bonuses amounts in unforeseen situations, control over the presence of manual edits.
- Check system with hints on how to fix errors.
- Preparation of the upload template for export to the BI system.
- Detailed User’s manual.
The calendar time for development and implementation at the client is 2 months.