SPREADSHEET EXERCISE 02
SCENARIO
As a wages clerk in Lobb and Company you have been asked to set up a payroll spreadsheet using EXCEL. You will achieve this task by carrying out the following steps.
1. Power on your workstation.
2. Boot up and load a spreadsheet package.
3. Enter the following headings across the top of the sheet, one heading to each column, starting in column one and finishing in column five:
WORKS NUMBER NAME HOURLY RATE HOURS WORKED GROSS PAY 4. Enter the following works numbers and employee names in the first and second columns, one number and name to each row, leaving a blank row below the headings, and widen the column containing the names of employees from default 10 spaces wide to 15 spaces wide:
12445 WILSON G 12456 CHAMBERLAIN M 13467 EXCEL T 13356 LAMBERT Y 14456 PATEL Y 16678 EDEN F 67899 FERNANDO S 71456 HEALEY T 72334 GORDON M 74556 JOHNSON K 86643 CAMPBELL D 87777 PITT W 5. Enter the following figures for HOURLY RATE and HOURS WORKED for each of the above employees:
7 39
8 38
9.3 35
7.7 34
7.4 31
6.54 20
6.67 40
7.87 40
6.89 30
8.90 35
9.87 34
6.56 40
6. Align all entries to the right.
7. Use appropriate formulae to generate GROSS PAY:
8. Increase the HOURLY RATE for FERNANDO to 10.79:
9. Delete all details for employee whose works number is 86643:
10. Change all money figures to £ format:
11. Enter a further two headings in the two columns after GROSS PAY: DEDUCTIONS, followed by NET PAY
12. Use the relevant formula to generate the DEDUCTIONS figures for each employee (deductions are 37% of the GROSS PAY:
13. Use the relevant formula to generate the NET PAY figures for each employee (NET PAY = GROSS PAY - DEDUCTIONS):
14. Generate a Grand Total wagebill figure by entering a label 'Grand Total' at end of gross pay figures and calculate the total of the gross pay figures next to it.
15. Enter your name at the bottom of your spreadsheet.
16. Save and print out your spreadsheet.
17. As a result of negotiations all employees basic pay-rate has been increased by 15%.
(a) Re-title HOURLY RATE column OLD RATE.
(b) Insert a new column to the right of OLD RATE called NEW RATE.
(c) Calculate the new rate by using a formula:
(NEW RATE = OLD RATE * 115/100.
(d) Recalculate all other pay columns on the basis of the new rate.
18. Produce a print-out of your revised spreadsheet.
19. Quit the system.
NB Please make sure that all your printouts have your name on them!