Payroll calculations under Piece work with guaranteed wage
Excel Skill level: Beginner
For the purpose of this tutorial download this excel file
In many cases labour force is offered guaranteed wage. In this system wage of employee is calculated on the basis of units produced but if wage is below certain amount then he is given the amount promised. This way employee earns a minimum amount of wage if he is unable to work for some reason. In short employee will get whichever is higher of following:
- wage calculated on piecework basis
- minimum wage
Technically it can be written as follows:
piece work wage is higher than minimum wage
piece work wage
The above situation can be executed in Excel using IF function. This function works on exact same basis as discussed above. So we have the same data and in addition to wage calculation we want to apply the logical test of minimum wage as well. For this we will add two more columns named minimum wage and wage payable next to the column where we calculated actual wage.
Step 1: Open worksheet named SPG – 1
Step 2: In cell D4 type: Minimum wage. In cell E4 type: Wage payable
Step 3: In column C calculate actual wages the way you like using the method you learnt in previous tutorial of this series.
Step 4: In cell D5 type: 3,000 which is our minimum wage and drag it down till the end of range or simply double click fill handle.
Step 5: In wage payable column put this formula in first cell below header i.e. cell E5:
This formula checks if value in K2 is bigger than the value in L2 if yes then value in K2 will be used otherwise L2’s value will be taken. Whereas K2 is actual wage and L2 is minimum wage.
You can practice this method in the following inbuilt excel worksheet:
In this above method we used two extra columns but sometimes we have limited work space and we cannot add more columns to the data. In this situation we will have to modify our formula so that it works out the value within single column. To achieve this follow these steps:
Step 1: Make sure worksheet named SPG – 2 is active
Step 2: You don’t have to add any column therefore, in the first cell of third column named ‘wage’ below heading i.e. in cell C5:
Press enter and your wage is calculated as per conditions of minimum wage without the need of additional columns.
To practice this technique use the following worksheet: