Instructions (also available in a PDF file for download & print: Test 2 Excel Instructions)Download Test 2 Excel Instructions)
In MS Excel (not TestOut), open the XYZ Payroll file (make sure you remember where you downloaded it) and perform the following tasks:
1. Fill in the correct numbers in Regular Hours (40 hours per week) and Overtime Hours (anything over 40 hours per week) for all employees. For example, Danny Ainge worked 49 hours total. You would type 40 in his Regular Hours cell and type 9 in his Overtime Hours cell. Enter zero if no overtime hours worked.
2. Using formulas with cell references and arithmetic operators, calculate Danny Ainge’s pay. DO NOT just type in the numbers
a) Begin with Regular Pay using the Pay Rate and Regular Hours cells
FORMULA USED => Regular Pay = Pay Rate x Regular Hours
b) Now, calculate the Overtime Pay using the Pay Rate and Overtime Hours cells – NOTE: overtime is time & half for this formula
FORMULA USED => Overtime Pay = 1.5 x Pay Rate x Overtime Hours
c) Last, the Gross Pay using the Regular Pay and Overtime Pay cells you just calculated
FORMULA USED => Gross Pay = Regular Pay + Overtime Pay
3. Using the Gross Pay and the FICA Tax Rate percentage, Danny Ainge’s FICA Tax Deduction (for this calculation you will need to use an absolute reference).
FORMULA USED => FICA Tax Deduction = FICA Tax Rate x Gross Income
4. Using the Gross Pay and the Income Tax Rate percentage, calculate Danny Ainge’s Income Tax Deduction (for this calculation you will need to use an absolute reference).
FORMULA USED => Income Tax Deduction = Income Tax Rate x Gross Income
5. Using the Gross Pay and the 2 deductions you just calculated, calculate Danny Ainge’s Net Pay.
FORMULA USED => Net Pay = Gross Income – (FICA Tax Deduction + Income Tax Deduction)
6. Copy Ainge’s formulas down the columns to show the results for Kevin McHale through Bill Cousey.
NOTE: You should not type in formulas for each employee but use the pull down/copy option.
7. Using a Sum function, calculate the Totals for all columns except Pay Rate.
8. In C19, use an Average function to calculate the Average Hours Worked
9. Using the same cell (C19), add the ROUND function in front of the Average function and include 0 decimal places at the end. This will round the result to a whole number.
HINT: Your function should look very similar to this ROUND(Average(X7:X16),0)
10. In K19, use an Average function to calculate the Average Net Pay
11. In A20, type the label – Fewest Regular Hours Worked.
12. In D20, use a Min function to show the fewest regular hours worked
13. Use Page Layout to change orientation to Landscape.
14. Add XYZ Financials as the left Header and Your Name as the right header.
15. Add XYZ Payroll as the middle Footer
16. Change the font of the whole worksheet to one of your choice (not Calibri)
NOTE: select the entire worksheet not just the cells with data in them
17. Increase the font size of the title to 18pt
18. Merge & Center the worksheet title over all used data columns. Add Fill Color of your choice to this cell.
NOTE: This worksheet will initially not fit on one page, even with the Landscape orientation, because of the multi-word column headings.Resize everything to fit properly on one page.
19. Select A6:K6, click on the Wrap Text button above the Merge & Center button. Then shorten the column widths on some of the columns until the worksheet fits on one page (use Page Layout or Print Preview to see this)
20. In cell J2, type in January 18, 2018. Then apply a different date format of your choice
21. Italicize the Names of employees
22. Add the Top & Double Bottom total border to the totals in cells A17 through K17
23. Add an Outside border around the tax information in cells A3 through D4. Add a Fill Color of your choice to these cells
25. Format Danny Ainge’s values from F7 through K7 to Accounting, 2 decimal places.
26. Format F8 through K16 to Comma, 2 decimal places.
27. Format the Totals (F17 through K17) row to Accounting, 2 decimal places.
28. Format the Avg. Net Pay cell to Accounting, 2 decimal places.
29. Format the 2 tax decimals (in the shaded area) to Percent with 2 decimal places
30. Rename Sheet1 to January-1 and apply a color of your choice to the tab
31. Apply Conditional Formatting (Highlight Cells Rules) on Hours Worked to highlight hours less than 40
32. Create a bar chart that displays the employee names and their Pay Rates. This chart should have the following:
A descriptive chart title
A Quick Layout of your choice applied (not layout 1)
Moved to a separate sheet with tab named Pay Rate Chart
Data labels showing Pay Rate numbers
A distinctive style applied (choose your own colors or use a Chart style)
33. Create a column chart that displays the employee names and their Gross Pay and their Net Pay. This chart should have the following (but different from the bar chart):
A descriptive chart title
A Quick Layout applied (not layout 1)
Moved to a separate sheet with tab named
A distinctive style applied (choose your own colors or use a Chart style)
34. Save the file as XYZ Payroll – Your Name.xlsx and upload for grading. Make sure that each of the worksheets is printable on one page only (you will have to do some tweaking with the design to get one worksheet per page) – see my completed example (AKN XYZ Payroll)
35. Save the file as XYZ Payroll – Your Name.pdf and upload for grading. Make sure that each of the worksheets is printable on one page only (one worksheet per page) – see my completed example (AKN XYZ Payroll)
