Payroll
Derek Harvest Moon Insurance Co. 1 Georgian DriveNo.20220125 Barrie, ON L4M 3X9 Pay to the order of Derek DavisAmount Paid EMPLOYEE IS PAID BY DIRECT DEPOSIT - THIS IS NOT A CHEQUE Destiny Duncan 405 Bayfield StCEO and President Barrie, ON L4M 3C5 Pay Period Start dateSunday, January 09, 2022Pay Date:Prepared by:Employee #: Pay period end dateSaturday, January 22, 2022Friday, January 28, 2022 Hours and EarningsDeductions DescriptionRateHours/AMTEarningsCurrent YTDDescriptionRateTotalCurrent YTD Salary1,900.00Taxes15.00%285.00 Overtime600.00CPP5.70%108.00 Vacation Pay100.00EI1.58%- 0 TOTAL EARNINGS:- 02,600.00TOTAL DEDUCTIONS:- 0393.00 Total Earnings (gross)Previous YTD values: Total DeductionsCPP108Taxes285Overtime600 Net PayEI30Earnings1900 Vacation Pay100 Dakota Harvest Moon Insurance Co. 1 Georgian DriveNo.20220125 Barrie, ON L4M 3X9 Pay to the order of Dakota DixonAmount Paid EMPLOYEE IS PAID BY DIRECT DEPOSIT - THIS IS NOT A CHEQUE Destiny Duncan 405 Bayfield StCEO and President Barrie, ON L4M 3C5 Pay Period Start dateSunday, January 09, 2022Pay Date:Prepared by:Employee #: Pay period end dateSaturday, January 22, 2022Friday, January 28, 2022 Hours and EarningsDeductions DescriptionRateHours/AMTEarningsCurrent YTDDescriptionRateTotalCurrent YTD Hourly Wages1,225.00Taxes15.00%184.00 Overtime175.00CPP5.70%70.00 Vacation Pay56.00EI1.58%20.00 TOTAL EARNINGS:- 01,456.00TOTAL DEDUCTIONS:- 0274.00 Total EarningsPrevious YTD values: Total DeductionsCPP70Taxes184Overtime175 Net PayEI20Earnings1225 Vacation Pay56 Diego Harvest Moon Insurance Co. 1 Georgian DriveNo.20220125 Barrie, ON L4M 3X9 Date: DDMM YYYY Pay to the Order of$ /100 Dollars Destiny Duncan 405 Bayfield StCEO and President Barrie, ON L4M 3C5 Pay Period Start dateSunday, January 09, 2022Pay Date:Prepared by:Employee #: Pay period end dateSaturday, January 22, 2022Friday, January 28, 2022 Hours and EarningsDeductions DescriptionRateHours/AMTEarningsCurrent YTDDescriptionRateTotalCurrent YTD Commission Pay1,000.00Taxes15.00%150.00 Overtime- 0CPP5.70%57.00 Vacation Pay40.00EI1.58%16.00 TOTAL EARNINGS:- 01,040.00TOTAL DEDUCTIONS:- 0223.00 Total EarningsPrevious YTD values: Total DeductionsCPP57Taxes150Overtime0 Net PayEI16Earnings1000 Vacation Pay40 Questions Question 1: Why is it important to study payroll calculations? Provide an example. (be sure to give 1 reason and 1 example) Type your answer below (be sure to show your work, in text format): Question 2: How would the payment structure change if it became semi-snnual payments? (provide an explanation and math to back it up) Type your answer below (be sure to show your work, in text format): Question 3: How will you use this knowledge in your future courses? (search your program and find out where this comes up again, you may use a personal example as well, be sure to do more than one small sentence) Type your answer below (be sure to show your work, in text format): .MsftOfcThm_Accent1_lumMod_75_Fill_v2 { fill:#1482AC; } 1 MATH1034 – EXCEL PROJECT – PART 2 The History: Employees can be paid for their services in a variety of ways. The main methods of pay, or remuneration, are salaries, hourly wage rates, and commission. While the computations involved in preparing employee pay records, or the payroll, are simple, utmost care is needed to ensure that all calculations are accurate. Compensation of employees by salary is usually on a monthly or yearly basis. Monthly salaried personnel get paid in a variety of time frames. Staff can be paid in the following times: monthly, semi-monthly, every two weeks (aka bi- weekly), weekly, or according to special schedules such as those used by some boards of education to pay their teachers. A year has 52 weeks. It is very easy to confuse the terms, refer to your instructor lessons to verify what each one means and the values that go with them. Gross earnings or total pay is the amount an employee makes every pay period (a period is typically stated on a pay cheque or statement of pay and is a length of time, and starts and stops, as defined by the organization). This is the overall amount by which an employee is compensated for their work. From this value, dedications are made which can consist of; government taxes (federal and provincial or combined as one), benefits (employee paid), pension (also employee paid), union fees, etc. Calculations of gross earnings per pay period are simple, however, the computing of overtime for salaried staff can be more problematic as they are not paid on an hourly basis. (See the textbook on page 25-27 for more details on pointers and pitfalls) Check the textbook on page 27-28 for a complete listing of the different types of commission. Essentially commission is an amount of money that is earned either as a rate of sales (example; you sold $100,000 of bookshelves at a furniture store and receive 5% = $5,000 as your wages for the time frame) or as a rate of the stores sales (note there are other kinds of commissions, these are the two most common, based on individual or based on location sales). Along with these items, staff can sometimes earn bonuses, or can take an advance on their wages. A bonus could be dependent on the entire company’s success, or by location such as commissions. An advance is when an employee borrows money against their own future pay and they would then pay that back to their organization with interest. Wages typically refers to an employee who is paid hourly by a specific rate (page 29 in the textbook). Gross pay for these employees is the number of hours worked multiplied by their rate up to the max hours allowed by the governing body in their province (note there are exceptions, and you are encouraged to learn these using the Canadian website: https://www.canada.ca/en/services/jobs/workplace/federal-labour-standards.html for the Labour Standards Act, and then the Ontario website: https://www.ontario.ca/laws/statute/00e41 for the Employment Standards Act. Note that all provinces have their own. Once employee reach their maximum hours worked, anything above that is paid in overtime (Refer to page 29 in the textbook to learn how overtime is calculated and paid out) https://www.canada.ca/en/services/jobs/workplace/federal-labour-standards.html https://www.ontario.ca/laws/statute/00e41 2 MATH1034 – EXCEL PROJECT – PART 2 The Reasoning: Learning to calculate and do payroll is very important. Often, front desk clerks do more than one job. They can be office managers, bookkeepers, switchboard operators, and do many other office functions. Understanding the laws of the province you are working in and following them to the letter is imperative for protecting the organization you work for against legal action, but also so that employees are paid fairly and appropriately which decreases turnover. Hiring is expensive! This assignment will provide some real-life experience that you can take with you into your job searches and use as an employable skill, much like the HST component of this assignment you have already completed in Part 1. The Scenario: You work for Harvest Moon Insurance Co at the front desk and are responsible for creating the pay cheques for all employees. This company pays its employees bi-weekly, and the staff provide you with their time sheets at the end of the week on Sundays for the following Friday payment. All cheques are signed by the president of your company Destiny Duncan. Each of the following employees are paid by direct deposit: Derek Davis – This employee receives a salary of $90,000 a year for a 44-hour work week. This pay includes 10 hours overtime paid out at a rate of time and a half Dakota Dixon – Worked 22 hours and forty-five minutes in week 1, and 25 hours and fifteen minutes in week 2 and is paid a rate of $22.50 per hour The following employee is paid by commission: Diego Daniels (paid by cheque) – sold a total of $90,000 in life insurance policies during the period and earns 2.15% in commission. Each employee (hourly or commission) has the following deductions: CPP – 5.70% EI – 1.58% Taxes – 15% All three employees have earned 4% vacation time on their paycheques (added to gross income). 3 MATH1034 – EXCEL PROJECT – PART 2 The Assignment (total of 34 marks): Step 1 (22 marks): Create a paystub for each employee in the template provided. Be sure to update the year-to-date amounts to include the current payment. (Remember, do NOT change any existing formulas/functions or formatting, add to the template, do not remove anything) Step 2 (no marks but asmt will NOT be marked if not complete): As with assignment 1, provide a “formulas” tab, you may put all employees in this same tab. Step 3 (6 marks): Fill out the cheque for the commission employee only as he does not have direct deposit Step 4 (6 marks): Answer the questions in the excel file Step 5 Save the file in the following manner: LastNameFirstname_StudentID_Excel_Assignment_Part2 and submit it to Blackboard. Naming the file appropriately as indicated above allows your file to be marked properly, make certain it is done right. The Rubric: Paystub Marking Rubric – 22 marks (Step 1): Item 2 Marks (mastery) 1 mark (satisfactory) 0 marks (needs improvement) Prepared by and Employee # - in 6 locations (2 in each employee pay stub) Added Name in “Prepared by” and Student ID in “Employee #” in all three locations for each 2 marks Missed one – 3 locations (of the 6 mentioned in mastery – 1 mark Missed more than 3 locations or entered nothing 0 marks Salary earnings Calculated properly, used formulas/functions 2 marks Miscalculated but used formulas/functions 1 mark Did not use formulas or functions (value typed in) or entered nothing 0 marks Overtime for salary employee Calculated properly, used formulas/functions 2 marks Miscalculated but used formulas/functions 1 mark Did not use formulas or functions (value typed in) or entered nothing 0 marks Vacation Pay for salary employee Calculated properly, used formulas/functions 2 marks Miscalculated but used formulas/functions 1 mark Did not use formulas or functions (value typed in) or entered nothing 0 marks