Excel is able to be programmed to follow the complex calculation rules which are needed to determine the bonus level the associates are eligible for. Creating such an Excel file is usually beyond the average knowledge a user has in this area. In this case the involvement of an Excel expert can be beneficial.
Programming Excel for custom bulk calculations
Not all tasks are spectacular in the office administration – as a matter of facts, few are. But some of them are crucial to do it right. One such example is the employee wage calculation, in this particular case the monthly premium part of it. Well, we don’t want to hurt anybody with some flawed calculation, so let’s see what we can do to replace the current manual way of working.
Making complex calculations for all of our associates, one by one, will inevitably lead to human mistakes. Computers were designed for this very reason.
The computers will carry out the tasks far more quickly and without human mistakes.
Benefits of letting the computer make the complex and repetitive calculations
Project: Automatic calculation of work premiums – actuals vs target
I was entrusted to make a solution for the determination of work premiums offered for shop-floor associates in case they delivered certain results during the monthly production. The actual produced quantity and the associated working time had to be matched with the targets defined for each step of activity, on an “associate – shift – activity” level.
This required the processing of a large amount of data.
Calculation of work benefit for each associates in each shift for every activity is a great leap for a human – but only a small step for a computer
The client asked for a solution where the input figures are downloaded from the ERP (Enterprise Resource Planning) system, certain rules about the benefit determination are programmed, and with a click of a button the computer determines who is entitled to the shift premium and who is not. All of it certainly in a traceable way, so in case of debate the calculation logic can be followed.
Delivered solution: specialised Excel file with automatic shift premium calculation
A custom Excel file was created, where only the raw production data was needed along with the calculation logic.
The main challenge in the manual and also in the automatic calculation was that the premium targets had been determined on a very detailed, low level: a different goal was set per product group and also even at activity level. There were exceptions identified which had to be handled by the computation.
Still the input tables where the goals were set had to be kept at a manageable level.
The final solution in Excel made up from these process steps:
The benefit counts to several hours working time each month, because
Once the basic figures were downloaded from the ERP system and copied into the file, it took literally minutes to have a final result.
Since several detailed lists are created automatically at the same time, it is possible to follow up exactly, in case of a question, how the progress of a given employee was formed during the month with respect to the premium allowance.
