Create a new Excel worksheet as shown below to compute the payroll information for Oshawa Software company (Use Boldface for all the labels, draw the borders, and rotate the text as shown). Enter Your name and Date (use a function for the CURRENT date).

SHOW EXCEL FORMULARS WITH YOUR WORK:

1. Rename the worksheet as Payroll, Save the file as Lab3_YourLastName (2 marks)

2, Enter your name as the 6^th Employee. (1 mark)

3. The Hourly Rate is calculated based on basic annual salary (annual salary) in. There are 50 working weeks per year, 5 working days per week and 8 hours per day. Enter the formula for Hourly Rate. (2 marks).

4. Calc. Gross Weekly Pay. Overtime is paid at 1.5x and Holiday pay is at 2x (2 marks).

5. The allowance is calculated as:

If the Gross Weekly Pay is greater than or equal to $2,000, then the allowance is $550, else the allowance is $390. (2 marks)

6. The Pension Contribution is based on a pay scale and equals Gross Weekly Pay * Deduction Rate. (please remember to use absolute address for the Deduction Rate) (3 marks)

7. Use the federal and provincial tax is based on an assumed annual income. Use the Gross Weekly Pay x 52 for the Gross Annual amount. Use either complex IF statements or Vlookups after creating a tax chart for yourself. Use 2017 Tax Tables. Dont forget to convert the taxes to a weekly amount. THIS IS A HARD ONE!!!! (4 marks)

8. Weekly Employment Insurance is deducted at a rate of 1.2% of gross weekly pay and C.P.P. is deducted at 0.97% of gross weekly pay. (2 marks)

9. Enter a formula in the Net Weekly Pay Column to calculate Net Pay as (2 marks):

Net Pay = Gross Pay + Allowances Deductions

10 Vacation Pay is paid based on 4% of the Gross Pay. The value is not paid on each cheque, but banked for later payment or use. (2 marks)

11. Scale is computed based on the Net Pay and the lookup. (2 marks)

12. Create a mini-table near the bottom of the worksheet to: (4 marks)

13. Write the formula to calculate the maximum hourly rate. Use appropriate EXCEL function.

14. Write the formula to calculate the total net pay. Use appropriate EXCEL function.

15. Write the formula to calculate the Minimum net pay. Use appropriate EXCEL function.

16. Write the formula to calculate the number of employees. Use appropriate EXCEL function.

17. Format the chart for currency, decimal places, borders and colours appropriately. (3 marks)

(a) Rename the worksheet as Payroll, Save the file as Lab3 YourLastName ( 2 marks) (b) Enter your name as the 6th Employee. (1 mark) (c) The Hourly Rate is calculated based on basic annual salary (annual salary) in. There are 50 working weeks per year, 5 working days per week and 8 hours per day. Enter the formula for Hourly Rate. (2 marks). (a) Rename the worksheet as Payroll, Save the file as Lab3 YourLastName ( 2 marks) (b) Enter your name as the 6th Employee. (1 mark) (c) The Hourly Rate is calculated based on basic annual salary (annual salary) in. There are 50 working weeks per year, 5 working days per week and 8 hours per day. Enter the formula for Hourly Rate. (2 marks)