Excel support in shift premium calculation

Excel unique calculation sample
In such cases when the benefit of every associate has to be calculated one by one, manually doing this is far from being efficient. That’s when custom mass calculation in Excel shall be opted for.

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

Even if the office admin is experienced, it takes time to make the calculation
If the task is repetitive in nature, each iteration is a new chance to make a mistake
Sometimes it is difficult or impossible to check the accuracy of the results
The training of a substitute for a complex task requires massive amount of time
These types of tasks are best candidates to teach to the computer, because they are based on strict rules
The computer can carry out the calculation in minutes, while the office admin would spend hours with it
The free time gained can be spent for real problem-solving tasks where human interaction is really needed

Project: Automatic calculation of work premiums – actuals vs target

Excel custom calculation operation butons
Button-operated process steps

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:

Excel custom calculation summary screen
Final result summary table
The monthly, detailed production data is downloaded from the ERP, directly into the Excel file
The associate list is maintained in the file, adjusted if necessary
With a press of a button automatic checking reports are run which highlight whether there is any missing master data
If necessary, the premium targets can be adjusted
Once every prerequisite is cleared, the premium calculation lists are prepared
Multiple tables are created, each showing the results on a separate detail level: a quick overview can be gained easily, but if needed, the complete shift pattern of a certain associate can be examined
The overview report shows only the final verdict: the shop floor worker is entitled to the shift premium or not

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.

Scroll to Top