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!