You are the accountant for Zack Zoomer's Car Rental. The weekly payroll needs to be recalculated...

 

  

Transcribed Image Text:

You are the accountant for Zack Zoomer's Car Rental. The weekly payroll needs to be recalculated as something just doesn't seem right to the owner. All relevant facts are presented below. Your assignment is to take those facts and design your own workbook to present to the owner. One Week of Payroll Data for Employees Employee Thompson, Rolfe ● Wilkirk, Rebecca Belitz, Dan Jones, Justin Sawall, Lauren Trap, Robert Moran, Jonie Dependents 3 4 427145 2 Rate per Hour 8.25 9.05 10.25 10.55 11.50 13.25 11.25 YTD Soc. Sec. 1,527.00 1,307.25 2,130.51 3,408.24 2,365.23 1,252.89 3,139.70 Hours Worked 24.50 32.25 14.75 45.50 62.25 31.50 42.75 • Bold the entire worksheet. Create a title and subtitle. For the title, use the Title Style, Calibri Font size 36. For the subtitle, use the Heading Style, Bodini MT Font size 12. Place a date on your file using the function to return today's date. Format the date to MM/DD/YY. • Make an assumption table which will include items directly below. Your choice on whether it is above the data or below the data. • Social Security Tax Rate is 7.65% with a Maximum YTD Social Security is $3,208.53 and no employee should pay more than this YTD including this week's pay. ● Medicare Tax Rate is 1.45%. State Tax Rate is 4.35%. ● Allowance Per Dependent is $18.09. ● Federal Tax Rate is 22%. You should use Absolute Cell References to reference your Social Security Tax Rate, the Maximum Social Security figure, the Medicare Tax Rate, the State Tax Rate, the Allowance Per Dependent, and the Federal Tax Rate. You will calculate the following columns of data, which are to be displayed directly to the right of Hours Worked in this exact order: . Gross Pay-If Hours Worked <= 40, then Rate per Hour * Hours Worked, otherwise Rate per Hour * Hours Worked + 0.5 * Rate per Hour * (Hours Worked-40). **NOTE SS formula will produce negative SS for those over the maximum, which is acceptable for full credit. If you want to fix the formula so it pulls $0 for those over the maximum, two bonus points are possible. Medicare Medicare Tax Rate * Gross Pay Federal Tax-If (Gross Pay - Dependents * Allowance Per Dependent > 0, then Federal Tax Rate* (Gross Pay-Dependents * Allowance Per Dependent), otherwise 0. State Tax-State Tax Rate * Gross Pay Net Pay-Gross Pay - (Soc. Sec + Medicare + Federal Tax + State Tax) • % Taxes-(Soc. Sec. + Medicare + Federal Tax + State Tax) / Gross Pay Your worksheet should total the following columns: Hours Worked, Gross Pay, Soc. Sec., Medicare, Federal Tax, State Tax, and Net Pay ● Soc. Sec. If Social Security Tax Rate * Gross Pay + YTD Soc. Sec. > Maximum Social Security, then Maximum Social Security - YTD Soc. Sec., otherwise Social Security Tax Rate * Gross Pay. ● The % Taxes column should have a percentage formula in the total line showing the Total % of Taxes. Format of numbers: ● Dependents (comma, no decimals) Maximum Social Security, Rate Per Hour, YTD Soc. Sec., Hours Worked, Gross Pay, Soc. Sec., Medicare, Federal Tax, State Tax, and Net Pay (comma, 2 decimals) . % Taxes, Social Security Tax Rate, Medicare Tax Rate, Federal Tax Rate, and State Tax Rate (percent, 2 decimals) • Allowance per Dependent (accounting, 2 decimals) Prepare the document for printing using Landscape, Fit to One Page. Save the workbook using the normal naming convention "Your Name Zack Zoomer.xlsx", noting this tab as "Weekly Payroll Report v1" ● ● ● ● ● Save the workbook. Copy the contents of the "v1" into a new tab, naming it "v2". Add the following new employees who transferred into our department from another this week. Calculate their items shown in the step above. Format the page to print landscape orientation, fit to one page. Employee Daley, Jane Smith, Joe Bob Dependents 3 4 Rate per Hour 8.75 7.25 YTD Soc. Sec. 1,707.00 1,255.50 Hours Worked 27.25 36.50 Save the workbook. Copy the contents of "v2" into a new tab, naming it "v3". Update the Medicare Tax Rate to reflect 2.85%. Notice the effect this has on each employee. Format the page to print landscape orientation, fit to one page. Save the workbook. Copy the contents of "v3" into a new tab, naming it "v4". Update the State Tax Rate to reflect 5.50%. Notice the effect this has on each employee. Format the page to print landscape orientation, fit to one page. Save the workbook. You are the accountant for Zack Zoomer's Car Rental. The weekly payroll needs to be recalculated as something just doesn't seem right to the owner. All relevant facts are presented below. Your assignment is to take those facts and design your own workbook to present to the owner. One Week of Payroll Data for Employees Employee Thompson, Rolfe ● Wilkirk, Rebecca Belitz, Dan Jones, Justin Sawall, Lauren Trap, Robert Moran, Jonie Dependents 3 4 427145 2 Rate per Hour 8.25 9.05 10.25 10.55 11.50 13.25 11.25 YTD Soc. Sec. 1,527.00 1,307.25 2,130.51 3,408.24 2,365.23 1,252.89 3,139.70 Hours Worked 24.50 32.25 14.75 45.50 62.25 31.50 42.75 • Bold the entire worksheet. Create a title and subtitle. For the title, use the Title Style, Calibri Font size 36. For the subtitle, use the Heading Style, Bodini MT Font size 12. Place a date on your file using the function to return today's date. Format the date to MM/DD/YY. • Make an assumption table which will include items directly below. Your choice on whether it is above the data or below the data. • Social Security Tax Rate is 7.65% with a Maximum YTD Social Security is $3,208.53 and no employee should pay more than this YTD including this week's pay. ● Medicare Tax Rate is 1.45%. State Tax Rate is 4.35%. ● Allowance Per Dependent is $18.09. ● Federal Tax Rate is 22%. You should use Absolute Cell References to reference your Social Security Tax Rate, the Maximum Social Security figure, the Medicare Tax Rate, the State Tax Rate, the Allowance Per Dependent, and the Federal Tax Rate. You will calculate the following columns of data, which are to be displayed directly to the right of Hours Worked in this exact order: . Gross Pay-If Hours Worked <= 40, then Rate per Hour * Hours Worked, otherwise Rate per Hour * Hours Worked + 0.5 * Rate per Hour * (Hours Worked-40). **NOTE SS formula will produce negative SS for those over the maximum, which is acceptable for full credit. If you want to fix the formula so it pulls $0 for those over the maximum, two bonus points are possible. Medicare Medicare Tax Rate * Gross Pay Federal Tax-If (Gross Pay - Dependents * Allowance Per Dependent > 0, then Federal Tax Rate* (Gross Pay-Dependents * Allowance Per Dependent), otherwise 0. State Tax-State Tax Rate * Gross Pay Net Pay-Gross Pay - (Soc. Sec + Medicare + Federal Tax + State Tax) • % Taxes-(Soc. Sec. + Medicare + Federal Tax + State Tax) / Gross Pay Your worksheet should total the following columns: Hours Worked, Gross Pay, Soc. Sec., Medicare, Federal Tax, State Tax, and Net Pay ● Soc. Sec. If Social Security Tax Rate * Gross Pay + YTD Soc. Sec. > Maximum Social Security, then Maximum Social Security - YTD Soc. Sec., otherwise Social Security Tax Rate * Gross Pay. ● The % Taxes column should have a percentage formula in the total line showing the Total % of Taxes. Format of numbers: ● Dependents (comma, no decimals) Maximum Social Security, Rate Per Hour, YTD Soc. Sec., Hours Worked, Gross Pay, Soc. Sec., Medicare, Federal Tax, State Tax, and Net Pay (comma, 2 decimals) . % Taxes, Social Security Tax Rate, Medicare Tax Rate, Federal Tax Rate, and State Tax Rate (percent, 2 decimals) • Allowance per Dependent (accounting, 2 decimals) Prepare the document for printing using Landscape, Fit to One Page. Save the workbook using the normal naming convention "Your Name Zack Zoomer.xlsx", noting this tab as "Weekly Payroll Report v1" ● ● ● ● ● Save the workbook. Copy the contents of the "v1" into a new tab, naming it "v2". Add the following new employees who transferred into our department from another this week. Calculate their items shown in the step above. Format the page to print landscape orientation, fit to one page. Employee Daley, Jane Smith, Joe Bob Dependents 3 4 Rate per Hour 8.75 7.25 YTD Soc. Sec. 1,707.00 1,255.50 Hours Worked 27.25 36.50 Save the workbook. Copy the contents of "v2" into a new tab, naming it "v3". Update the Medicare Tax Rate to reflect 2.85%. Notice the effect this has on each employee. Format the page to print landscape orientation, fit to one page. Save the workbook. Copy the contents of "v3" into a new tab, naming it "v4". Update the State Tax Rate to reflect 5.50%. Notice the effect this has on each employee. Format the page to print landscape orientation, fit to one page. Save the workbook.

Answer rating: 100% (QA)

Assumptions Income taxes are withheld at a rate of 22 Federal Insurance Contributions Act FICA taxes ... View the full answer