Excel

Excel Assignment Overview Just like the PowerPoint project, you can complete this assignment working together with a partner (only one). There is no need each partner to submit the assignment separately - just make sure both names appear in the file name and in the payroll register. Java Jive imports coffee and tea from around the world. Prepare) a worksheet that will calculate the weekly payroll for Java Jive’s five employees. The payroll register contains the following headings:

Headings Description

Employee Name Employee’s Name (first, last)

Benefits Level The employer has two levels (1or 2)of benefit coverage Reg. Hours Hours worked at regular pay rate

O/T Hours Overtime hours worked at 1.5 times regular hourly pay rate

Pay Rate Regular hourly pay rate

Gross Pay Total pay for weekly pay period

Dental Taxable benefit dental premiums paid by employer weekly

Insurance Taxable benefit insurance premiums paid by employer weekly

Medical Taxable benefit medical premiums paid by employer weekly Taxable Income Gross pay plus taxable benefits

Federal Tax Federal income tax withholding amount

Prov. Tax Provincial income tax withholding amount

Employ. Insurance Employment insurance withholding amount

Govt. Pension Government pension plan withholding amount

Total Deductions Total deductions

Net Amount Pay for pay period after all deductions

Instructions:

1. Open the attached “Excel Assignment.xlsx” file and name it “LastName_FirstInitial - Excel Assignment.xlsx”.

2. Set the page orientation to landscape. Change the student name(s) to your name(s).

3. Wrap the text in the column headings A4:J4 and A14:H14 in Sheet 1 and set the column width to (approximately) 10 for columns B to J.

4. Calculate the Gross Pay (F5:F9) using the following formula: Pay Rate times Regular Hours plus 1.5 times Pay Rate times O/T Hours.

5. Display the Taxable Benefits (G5:I9) in the following way: apply a formula/function to allocate and return the appropriate weekly amount of Dental, Insurance, and Medical based on his/her Benefits Level and the corresponding taxable benefit to this code in Sheet 2. The assumptions, the taxable benefit rates, and the tax rates (all in Sheet 2) may be subject to changes, so all formulas should be created in a way so that they would reflect any changes in Sheet 2 automatically.

6. Calculate the Taxable Income (Gross Pay plus Taxable Benefits).

7. Use the Taxable Income (J5:J9) to automatically locate the Federal and Provincial Tax withholdings from the Tax Table on Sheet 2. For example: Federal Tax = Taxable Income * Federal Tax %.

8. Calculate the Employ. Insurance and Govt. Pension contributions based on the Gross Pay (Note: Gross Pay not Taxable Income). The contribution percentages are located in the Assumption area in Sheet 2. Calculate the Total Deductions as a sum of all deductions (Federal Tax, Provincial Tax, Employ. Insurance, and Govt. Pension).

9. Calculate the Net Amount by subtracting the Total Deductions from the Gross Pay.

10. Calculate the totals in B20:G20

11. Insert cheque number 121 in H15 and create a formula that will automatically number all the rest of cheques in sequence.

12. Format the title as Arial 16 pt., bold, italic and merge and centre it across columns A:J.

13. Format all dollar values as: number, 2 decimal places, 1,000 separators and no dollar sign.

14. Centre the contents of the Benefits Level (B5:B9) and the Cheque No. (H15:H19) columns.

15. Format the borders and headings as shown in the example below.

16. Create a customized header that displays “Payroll Register’ in the left box and the current date in the right box. Apply one of the pre-set footers that displays automatically the file name in the centre box.

17. Centre the spreadsheet horizontally on the page.

18. Create a three dimensional Pie Chart on Sheet 1 (below the second table) that shows the percent of Net Amount for each employee. Include the following title: “Percentage of Net Amount”. Add data labels that show percentages in the outside end of the data points. Show a legend that displays the names of all employees. Pull out the wedge representing the employee with the lowest percentage of net amount. Rotate the pie chart so that the wedge you pulled out points at the lower-left corner of the chart. Modify the Y-rotation and the Perspective settings of the chart.

19. Preview Sheet 1. Make the necessary adjustments to ensure that the spreadsheet can be printed on one page (if needed resize the chart, change the page margins, etc.) and upload it to eLearn.