Payroll calculation in Excel – Piecework with Guaranteed wage – How To

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:

If

piece work wage is higher than minimum wage

then

piece work wage

Else

Minimum wage

Method 1

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:

=IF(C5>D5,C5,D5)

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:

Method 2

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:

=IF((B5*$B$13)>3000,B5*$B$13,3000)

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: