Learning Microsoft Excel 2007 - Payroll Systems

Learning Microsoft Excel 2007 By Greg Bowden Guided Computer Tutorials www.gct.com.au Payroll Systems Chapter 16PUBLISHED BY GUIDED COMPUTER TUTORIALS PO Box 311 Belmont, Victoria, 3216, Australia www.gct.com.au © Greg Bowden This product is available in Single or Multi User versions. Single-user versions are for single person use at any particular time, just as a single text book would be used. If you intend to use the notes with multiple students the single user version should be upgraded to the multi-user version. Multi-user versions allow the school or institution to print as many copies as required, or to place the PDF files on the school network, intranet and staff laptops. A certificate of authentication is provided with multi-user versions. Bookmarks provide links to all headings and sub-headings, and individual chapters are provided. First published 2007 ISBN: 1 921217 44 8 (Module 1) 1 921217 45 6 (Module 2) PDF document on CD-ROM Every effort has been made to ensure that images used in this publication are free of copyright, but there may be instances where this has not been possible. Guided Computer Tutorials would welcome any information that would redress this situation.© Guided Computer Tutorials 2007 16-1 Chapter 16 Payroll Systems Companies can use Microsoft Excel to keep pay details. In this activity you will create a company payroll table and pay advice slips for the individual employees. The data for the payroll system has been prepared for you, your task will be to complete the formulas. Loading the Payroll Template 1 Load Microsoft Excel, or close the current file. 2 Click on the OPEN icon in the QUICK ACCESS TOOLBAR or in the OFFICE BUTTON. Access the CHAPTER 16 folder of the EXCEL 2007 SUPPORT FILES and load the template: PAYROLL Selecting YES to the READ-ONLY dialogue box. 3 There are two worksheets in the template. Look over the PAYROLL sheet which will show the pay details for all employees. Click on the PAY ADVICE sheet. It will display the pay details for an individual employee. The employee would receive the PAY ADVICE printout when they receive their pay. 4 Save the template in your STORAGE folder under the file name: Ch16 Evesalon Remember to turn off the READ-ONLY RECOMMENDED option. Completing the Payroll Worksheet The first sheet is the payroll sheet showing the pay details for all the employees of the company. 1 Ensure that the PAYROLL worksheet is on the screen. 2 Position the cursor at cell G12. We need to calculate the OVERTIME RATE OF PAY. It is 1.5 times the NORMAL PAY RATE. © Guided Computer Tutorials 2007 16-2 Learning Microsoft Excel 2007 5 The GROSS PAY is the total weekly pay earned by the employee before deductions are taken out. We need to multiply the NORMAL HOURS by the NORMAL PAY RATE and the OVERTIME HOURS by the OVERTIME RATE then add the two results together. NOTE: The brackets in the GROSS PAY formula are not really necessary, but they help to separate the two calculation sections and make the formula easier to understand. 3 In cell G12 enter the formula: = E12 * 1.5 4 Autofill the formula down for the other employees. 6 Move the cursor to cell H12 and enter: = (D12*E12) + (F12*G12) 7 Autofill the formula down for the other employees.© Guided Computer Tutorials 2007 16-3 16 Payroll Systems 8 The SUPERANNUATION is the amount contributed by employees each week to a retirement fund. It is usually a percentage of the employee’s Gross Pay. We will use a rate of 5% here. The Tax Calculation Normally the TAX is calculated through a series of lookups which you did in an earlier chapter. This could be done at the right of the payroll and set not to print when the payroll is printed. To make things a little easier we will use a base tax rate of 25%. 9 Set the cursor at cell I12 and enter: = H12 * 5% then autofill the formula down for the other employees. Set the cursor at cell J12 and enter: = H12 * 25% then autofill the formula down for the other employees.© Guided Computer Tutorials 2007 16-4 Learning Microsoft Excel 2007 Calculating the Net Pay The NET PAY is the GROSS PAY minus the deductions (superannuation and tax), Printing the Payroll The print area needs to be checked so that the whole payroll fits on one page and the ORIENTATION needs to be set to LANDSCAPE. Set the cursor at cell K12 and enter: = H12 -I12 -J12 then autofill the formula down for the other employees. 1 In the PAGE LAYOUT tab of the RIBBON set the ORIENTATION icon to LANDSCAPE and the SIZE icon to your printer’s paper size (it is probably A4).© Guided Computer Tutorials 2007 16-5 16 Payroll Systems 3 PRINT PREVIEW the table then print a copy and check over it. The Pay Advice Worksheet Each pay day the employees are given pay advice sheets with their pay. We can use Microsoft Excel to create a pay advice sheet that can import values from the PAYROLL worksheet and print this pay advice sheet for each employee. The employee CODE is used to link the two sheets together. The CODE consists of the first three letters of the employee’s surname and the number 50. If two or more employee’s have the same first three letters in their surname, the first alphabetical name receives the number 50, the second 51, the third 52, etc. A Naming the Payroll Cells It is advantageous to name the PAYROLL table cells so that entering the formulas in the PAY ADVICE sheet is easier. 1 The PAYROLL sheet should be on the screen. 2 Highlight the whole payroll (cells A1 to K17) and set the PRINT AREA icon in the RIBBON to SET PRINT AREA.© Guided Computer Tutorials 2007 16-6 Learning Microsoft Excel 2007 B Sorting The Payroll Before starting the PAY ADVICE sheet it is necessary to ensure that the PAYROLL table is sorted into CODE order. The lookup formulas will not find the correct values if the PAYROLL table is not in CODE order. 2 Click on a cell to remove the highlight and the PAYROLL table should be in CODE order. 2 Highlight cells A12 to K15, click in the NAME BOX, name the cells: Payroll and press to complete the name. 1 With the cells A12 to K15 still highlighted, click on the SORT & FILTER icon in the HOME tab of the RIBBON and select: SORT A TO Z.