Spreadsheet Exercise: Pay and Tax

To calculate wages from hourly rate and hours worked we multiply these two values together to find the total pay.

We then calculate the tax payable on each wage and subtract it to give the net pay.

The tax rate will be placed at the top of the spreadsheet and its cell will be used as an absolute (fixed) reference in formulae. We could add National Insurance as well.

Set up the following items in a new worksheet:

• Enter some basic data for names, hours worked and hourly rates for 6 people.
• Use SUM to calculate the total hours worked.
• Enter a formula to calculate the Gross Pay.
• Enter a formula to calculate the tax. This will make reference to the cell that contains the percentage amount (23%). To use Copy-Drag on this formula you will have to use ‘$B$2’ in the formula so the reference to that cell is fixed (absolute).
• Create a formula to calculate the Net Pay.
• Use SUM to calculate the total amounts for Gross Pay, Tax and Net Pay, underneath the individual items.
• Add a column for Hours April, enter some data for each person and make sure that the data for Total Hours, Gross Pay, Tax and Net Pay are correctly updated.
• Format all money items as currency.
• Right justify all headings in row 3 except ‘Name’.
• Add a chart to show names and Total Hours (hold down Ctrl to select non-adjacent columns)
• Choose File/Print Preview and click on the Setup button. Set the Page Orientation to Landscape (this is a wide spreadsheet). Choose the Header/Footer tab and click on Custom Footer. Add your name to one of the panels and the page number in another panel.

Use the Back Button to Return to the previous page