INSTRUCTIONS
Documents and Videos will be found under “Modules – Excel”
1 Open worksheet labled “Payroll Worksheet”
2 Adjust Colums so that you don’t see the #’s
3 Sort employees into alphabetical order. Make sure that all of the data is also sorted along with the employee.
4 Create a new column for “Last Name”. View file “Splitting Values Seperated by Commas” and separate First and Last names into columns A & B.
5 Create a new column for Department and add the department for each employee as detailed on the solution.
The next steps must be done WITH FORMULAS.If you don’t use correct formulas then you will not receive credit.
6 Write a formula that will calculate the “regular pay” for Pieter Brucker. Use the autofill function to calculate regular pay fr the rest of the employees.
7 Write a formula to calculate overtime pay for Pieter Brucker. Drag the formula down for the rest of the employees. Overtime pay is 1.5 times regular pay.
8 Write a formula to calculate “gross pay” for Pieter Brucker. Drag the formula down for the rest of the employees.
9 Watch Video “Excel – Absolute Reference”. Write a formula (using absolute reference), to calculate Gross Pay “with raise”. Write to formula to reference to cell J1. Drage the formual through to the rest of the employees. Test the formula to see if it works by changing the “Proposed Raise” in cell J1 to 15%. All of the number in the “with raise” column should change.
10 Using AutoSum create total sums for columns B, C, E, F, G, H
11 Add a new employee. Luca Pacioli as Office Staff making $24/hr working 40 hours with 20 overtime hours. Make sure that the sums across the bottom change when you add the numbers. If they don’t then you have done something wrong with your formula.
12 Round all numbers to have 0 decimal places.
13 Format the spreadsheet as you see on the solution with borders, bold, italics and highlights as shown in the solution.
14 Use the Average Function to get an average of hours worked and average hourly rate. Look up how to use this function online.
15 Watch SUMIF video. Using the SUMIF function, sum the total Pay by Department. Watch the SUMIF video. Please note that if you drag the formula down to cells C36:C41 that you will need to make the “range” and s”sum range” absolute.
16 Use “AutoSum” funtion to total cells C:35 through C41. The totals of cell C42 and J28 should be equal
17 Using the “Quick Analysis” tool create a clustered column chart. Highlight cells C4:C41 and click on the “Quick Analysis” tool in the right corner of the cell range..
18 Your spreadsheet should look exactly like the solutution posted on the solution Worksheet.
NEXT – complete the 3 VLOOKUP and Chart Questions