Create a spreadsheet that might be used to track payroll expenses for a week. Create a two-row spreadsheet where the top row is column headers and the second row holds calculations that determine the values identified above.There will be no data in this spreadsheet beyond the calculations that you create.Your spreadsheet will track the payroll expenses that are associated with employee pay and the payroll expenses that the employer pays. Your job is to determine how much money the employee will be paid, how much employee tax the employee is charged, and how much payroll expense the employer must pay.Assumptions:We're going to assume that all employees are paid by the hour and we're not going to concern ourselves with overtime. If you want to add in overtime, I'll add into your grade a bit. Overtime is any time you work beyond 40 hours and you get paid time and a half for those hours. To calculate overtime, you'll need to use an IF statement.Time is in tenths of an hour (for example, 30.2 hours, 43.5 hours)All employees are benefittedEmployee Revenue:hours worked * pay rate = weekly pay (gross pay)Employee Expenses:Employee Social Security is 6.2% of gross payEmployer Expenses:Employee gross payrollEmployer Social Security is also 6.2% of gross pay (yes, 12.4% of your pay goes to Social Security)Federal Unemployment Tax is 2.5% of gross payAlaska State Unemployment Tax is 1.09% of gross payVacation pay is 4% of gross pay (2 weeks over a 50 week year)Sick pay is 3.8% of gross pay (2 weeks over a 52 week year).At the end of the row I want to know the total employee net pay (gross pay minus employee security tax) and the total employer expense (including employee pay after employee social security tax is removed
Create a spreadsheet that might be used to track payroll expenses for a week. Create a two-row spreadsheet where the top row is column headers and the second row holds calculations that determine the values identified above.There will be no data in this spreadsheet beyond the calculations that you create.Your spreadsheet will track the payroll expenses that are associated with employee pay and the payroll expenses that the employer pays. Your job is to determine how much money the employee will be paid, how much employee tax the employee is charged, and how much payroll expense the employer must pay.Assumptions:We're going to assume that all employees are paid by the hour and we're not going to concern ourselves with overtime. If you want to add in overtime, I'll add into your grade a bit. Overtime is any time you work beyond 40 hours and you get paid time and a half for those hours. To calculate overtime, you'll need to use an IF statement.Time is in tenths of an hour (for example, 30.2 hours, 43.5 hours)All employees are benefittedEmployee Revenue:hours worked * pay rate = weekly pay (gross pay)Employee Expenses:Employee Social Security is 6.2% of gross payEmployer Expenses:Employee gross payrollEmployer Social Security is also 6.2% of gross pay (yes, 12.4% of your pay goes to Social Security)Federal Unemployment Tax is 2.5% of gross payAlaska State Unemployment Tax is 1.09% of gross payVacation pay is 4% of gross pay (2 weeks over a 50 week year)Sick pay is 3.8% of gross pay (2 weeks over a 52 week year).At the end of the row I want to know the total employee net pay (gross pay minus employee security tax) and the total employer expense (including employee pay after employee social security tax is removed
Step by step
Solved in 2 steps
