Excel Homework very easy Due in 2 hours

Excel Assignment

Overview

Java Jive imports coffee and tea from around the world. Prepare (please note that you can work with one partner on this assignment) 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 9.5 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, that is why 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: accounting, 2 decimal places.

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 preset 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.). Print this page.

20. Save your work on a flash drive, diskette or a CD (if so, make sure it is readable on a standard CD Rom).

21. Print the formulas in Sheet 1 on one additional page. Make sure that the formulas in columns B, D, F, G and H are displayed fully. To be able to fit all this on one page, feel free to resize the remaining columns.

22. Submit the printouts and the file on the due date in an envelope. Note that your assignment must be submitted in an envelope; otherwise it will not be accepted for marking.