PLEASE EXPLAIN STEP BY STEP HOW TO DO THE FOLLOWING TABLE IN MICROSOFT EXCEL (WITH SCREENSHOTS) AND SHOW FINAL TABLES
Urgent please
You are the accountant for a company "X" for Car Rental. The weekly payroll needs to be recalculated as something just doesnt 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 Rate Dependents per Hour 3 8.25 4 9.05 2 10.25 7 10.55 1 11.50 4 13.25 5 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 Maximum Social Security, then Maximum Social Security - YTD Soc. Sec., otherwise Social Security Tax Rate * Gross Pay. **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 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. 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 Rate Dependents per Hour 3 8.75 4 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. a . Save the workbook . Copy the contents of "3" 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. One Week of Payroll Data for Employees Employee Thompson, Rolfe Wilkirk, Rebecca Belitz, Dan Jones, Justin Sawall, Lauren Trap, Robert Moran, Jonie Rate Dependents per Hour 3 8.25 4 9.05 2 10.25 7 10.55 1 11.50 4 13.25 5 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 Maximum Social Security, then Maximum Social Security - YTD Soc. Sec., otherwise Social Security Tax Rate * Gross Pay. **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 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. 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 Rate Dependents per Hour 3 8.75 4 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. a . Save the workbook . Copy the contents of "3" 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
