# Payroll - a model produced for a company to work out its total wage bill

Extracts from this document...

Introduction

Nicola Bowskill

GCSE coursework – Payroll.

The model has been produced for the company to work out its total wage bill, this includes the total hours, overtime hours, amount of standard pay and the overtime pay and of course the total amount.

The type of data being used is numerical, textural and currency. The data being used is the name of each teacher, the total hours worked per week, the amount of overtime hours, standard pay and overtime pay. There is also information about hourly rate and overtime rate.

I am now going to look at the formulas used and how they are produced.

Fiq 1. A screen dump of the payroll model.

Fiq 2. A screen dump of formulas to produce the payroll model

In fiq 1, C4 shows the overtime the teacher has worked. In fiq 2 you can see that the formula used is =B4-35, this is done by taking the total hours worked by the teacher concerned (B4) and subtracting 35, the normal working hours.

Middle

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.

Conclusion

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.

This student written piece of work is one of many that can be found in our GCSE T-Total section.

## Found what you're looking for?

- Start learning 29% faster today
- 150,000+ documents available
- Just £6.99 a month