Payroll calculation in Excel – Straight Piecework Wage System – How To

Straight Piecework Wage System

Excel Skill level: Absolute Beginner

For this tutorial download this excel file

In this system a fixed wage is given for each unit produced and whatever number of units are produced by the labour are multiplied with that particular rate. In short it is:

no. of units produced x wage rate per unit

In our case units produced by each employee and the wage rate per unit of (Rs. 5 per unit) is already provided to us.

Method 1 – Baby steps!

Step 1: Open worksheet named SP – 1

>snapshot straight piece work

Step 2: We can use Excel as a calculator to carry out mathematical calculations. Like we have units produced by each employee in cell B2. First employee is Farid and he produced 350 units and wage rate is 5 per unit so in cell C5 put this formula:

=350*5

Press enter and wage is calculated for you. Notice if you have to insert a formula you start with an “=” sign.

You can practice this method in the following live worksheet:

Problem

This approach makes no sense at all because if we really have to do it this way then we could have used calculator with big buttons instead. So follow the next approach.

Method 2 – Catching efficiency in simplicity

Step 1: Open worksheet named: SP – 2

Step 2: In cell C5 (column C and row 5) type in this formula:

=B2*5

This formula takes the value from cell B2 that gives us number of units and multiplies it with 5 which is our wage rate per unit. Yes in Excel (*) named asterisk or star is used to apply multiplication operation or product.

To calculate wage for other employees simply click and hold fill handler and drag it down the column and cells will be populated with respective wages for each employee. Excel plays a little magic here i.e. as you move down excel automatically picks up the units for every next employee on its own. This is because of relative cell reference. Due to this as we move to next row excel automatically updates cell reference to next row without you doing it manually.

Now if you change the number of units excel automatically updates the wage for you instantly.

To practice this method you can use the following live worksheet and just implements the steps learnt above.

Problem

What if you like to change the wage rate? In the approach we used we hard coded the wage rate inside formula. Therefore, we will have to repeat the whole process again if wage rate changes. But there is a better solution to that.

Method 3 – Referring to automation!

Step 1: Open worksheet named SP – 3

Select any cell where you can mention the wage rate. I chose cell B13 and inserted wage rate of 5 there. Now instead of multiplying wage rate directly, multiple the cell reference where you mentioned the wage rate i.e. B13. So the formula this time in cell C5 will become:

=B5*B13

Press enter and you get the wage of first employee. Now drag the cell down the way you did it before or simply double click the fill handler and it will do it for you.

Problem! the wage of first employee is calculated perfectly but for others it is given zero! WHY???

Double click cell C3 or select cell C3 and press function key F2 it will put you in editing mode. Notice formula is =B6*B14. Now the blue box just left of F3 is correct as we want units made by next employee but notice the green box. It has jumped from F10 to next cell and as F11 is empty that is why total wage is zero.

So we reckon that as we dragged C5 cell down B5 jumped to B6 and B13 jumped to B14. Now we are half stuck because we do want B5 to jump next row but we don’t want B13 part of the formula to leap in the next row. Solution? Absolute referencing! Change the formula in cell C5 like this:

=B5*B$13

Notice the dollar $ signs in the formula. Remember B or alphabets denote columns and numbers denote rows so B13 means 13th row in the B column. Appending $ sign with column and/or row makes it an absolute reference or a static reference. Meaning it will not change if it is dragged. In the above approach when we dragged cell C5 after inserting formula row number updated automatically this is because it was not made static or absolute. So now if you drag the cell C5 wages will be calculated perfectly.

The following worksheet help you practice above steps

To learn more about absolute and relative cell reference in Excel visit:

Summary:

We learned about calculating wages on straight piece work basis using Excel. In this we also learnt how to use cell references to fetch value to apply mathematical operations. And how relative and absolute cell references make our life easier in Excel.