Part one: Payroll Scenario (20 marks) on a Word document

Prepare pay cycle scenario for an Ontario employer (with a company name) who has four employees (with each employee's name), two of them are paid annual salaries, and the other two employees are paid hourly wages. The four employees are between 20 and 60 years old. The payroll is processed for all the employees on a weekly basis and this pay cycle is for the week ending March 26, 2021. There will be 52 regular pays in the year.

The pay cycle information required in your payroll scenario:

Annual salary rates and overtime hours for the two salaried employees; hourly rates, regular and overtime hours for the two hourly paid employees. The employer pays overtime at time and a half for any approved hours worked in excess of 44 hours per week. The employer pays 4% vacation to hourly employees with each pay and accrues 6% vacation for salaried employees.

A cash non-taxable allowance for all four employees

A cash taxable allowance for the two hourly paid employees

Monthly life insurance paid (to the insurance provider) by employer for all four employees

A provincial family court order of $3,200 for one of the employees, calculate the court order deduction from this pay cycle.

Monthly union dues for all four employees

The two salaried employees contribute 6% of their regular salary to a Registered Retirement Savings Plan each pay cycle

The two hourly paid employees have authorized the employer to deduct $20 per month for the Skills Catalyst Fund

One of the salaried employee's spouse's net income is $10,000 in 2021 and he is going to claim the amount; one of hourly paid employee paid $4,500 tuition fees in 2021; and the rest of two employees have Basic Personal Amount only. Determine the Federal and Ontario TD1 Codes for each of the employees.

The employer's Ontario Health Tax (OHT) contribution rate is 1.95% (based on total gross taxable earnings) and the current year W.S.I.B. premiums at $0.81 per $100 of assessable earnings. Estimate the employer Ontario Health Tax (OHT) contribution and WSIB premiums on this pay cycle assuming that all four employees' gross taxable earnings do not exceed the maximum accessible earnings.

Part two (80 marks) on Excel spreadsheet

Make payroll register and journal entries for both employee and employer on an Excel spreadsheet. Use cell reference (formula) to show your calculations on the payroll register, I will not able to mark your assignment if no cell references (formula, calculations) are used. A mark of zero will be recorded for your assignment if no calculation shown on your spreadsheet. In addition to the basic pay cycle specific information, you are required to include the following column in your payroll register: Gross Earnings, Pensionable Earnings, Insurable Earnings, Gross Taxable Earnings, Net Taxable Earnings, CPP, EI, Federal and Ontario Taxes, other deductions and Net Pay in your Excel payroll register. Show all your calculations on the Excel spreadsheet not on the Word payroll scenario document.