D4 shows the amount owed to the teacher from normal pay, i.e. excluding overtime pay. This is done by using the number of hours worked by the member of staff (B4) and multiplying it by the hourly rate of £8, this is in B12 and as it used of all teachers it is called an absolute reference.
Fiq. 1 also shows the overtime pay owed to the teacher, if we take E4 as an example it takes the overtime worked by the staff (C4) and multiplies it by the overtime rate (B13), like the hourly rate this is an absolute reference as it is the same for all teachers.
To find out the total pay for each teacher you look in column F. the formula for F4 simply adds the total paid for normal hours and the overtime paid owed.
The formula for F10 shows the total wage bill for the six teachers in the week, it is done by totalling up the figures above it, i.e. F4, F5, F6, F7, F8, F9.
Every week someone will have to re type certain data to work out the new weeks wages. There will be for of the values that might have to be changed:
- The total hours worked
- The hourly rate
- The overtime rate
- The minimum number of contract hours.
This would have various affects on the output data, if the total hours worked is lower than the original then the total pay will go down and visa versa. Likewise if the any of the other variables are altered it will have an affect on the total wage bill.
I have made the above changes to the model and produced this:
Fiq 3. Shows changes made to the model
As you can see this shows that increasing the total hours for B. Bryson will increase overtime pay, and the total pay as well as the total wage bill. If I decrease the minimum hours from 35 hours to 30 hours then the overtime pay, total pay and total wage bill will all go up. Where I have increased hourly rate from £8 to £9 this will have an affect on all the teachers’ standard pay, total pay and the total wage bill. Thirdly, if I increase the overtime pay it will also affect all the teachers it will increase the overtime pay, total pay and total wage bill. These two variables affect all the teachers because they are absolute references.
On the original model I am going to change the hourly rate to a measly £4 an hour and change the C. Claytons hours from 35 to 45. I believe that the changes in hourly rate will affect the standard pay, total pay and the total wage bill by decreasing them. I also believe that the changes in the hours that C. Claytons has worked will increase the overtime pay in E6, the total pay in F6 and the total bill.
Fiq. .4. Shows changes made to original model.
As a result of the change made in hours the following changes occurred:
Fiq 5. Shows the affects of the changes in hours.
As an affect of the change made to the hourly rate the following changes occurred:
Fiq 6. Shows the changes made in the hourly rate
If the contract changes and the minimum working hours becomes 30 then the formula will have to be changed for column C, this means that the overtime pay, total pay and total wage bill will all go up.
Fiq. 7. Shows the changes made in the contracted hours
The model doesn’t look at income tax, so two new columns need to be added, one to show the amount of tax to be paid and the amount owed to the staff, Net Pay. The tax will be 22% so the total wage bill and the entire total pay now call Net pay will be down by 22%.
Fiq.8. Shows the changes made by adding tax allowance.
This is done by:
This doesn’t take into account other factors such as National Insurance, tax rates etc. This would have to be looked into further.
