You are the accountant for Zack Zoomer's Car Rental. The weekly payroll needs to be recalculated...
Fantastic news! We've Found the answer you've been seeking!
Question:
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.
Expert Answer:
Answer rating: 100% (QA)
Assumptions Income taxes are withheld at a rate of 22 Federal Insurance Contributions Act FICA taxes ... View the full answer
Related Book For
Accounting concepts and applications
ISBN: 978-0538745482
11th Edition
Authors: Albrecht Stice, Stice Swain
Posted Date:
Students also viewed these accounting questions
-
You are the accountant for Clear Water Bay Company, an equipment manufacturer. In order to help customers finance their purchases, Clear Water Bay often leases, rather than sells, the equipment....
-
You are the accountant for Nello Company, which manufactures specialty equipment. Nello has been in financial difficulty, so its suppliers require purchases to be paid in cash. Furthermore, Nello has...
-
You are the accountant for the largest manufacturer of sheet steel. The companys hottest product is the RX-6, which provides most of the firms revenue. Management is considering dropping the RX-5...
-
Your client, Mr. Brakes Inc., owns and operates an auto-motive repair shop in Cooperstown, New York. Mr. Brakes specializes in replacing and repairing brakes on cars, sport utility vehicles, and...
-
Use the model in File C09 to solve this problem. West Coast Chemical Company (WCCC) is considering two mutually exclusive investments. The projects expected net cash flows are as follows: a....
-
In Chapter 7 you developed a use case diagram, a class diagram, and a system sequence diagram for the use cases Recording a traffic ticket and Scheduling a court date. Based on those solutions or...
-
Discuss why generally accepted accounting principles are important to investors and to a properly functioning, efficient capital market. How do accounting rules help capital markets be (or become)...
-
Dacahr Bros., Inc., is an automobile maintenance and repair company with outlets throughout the western United States. Henley Turlington, the company controller, is strating to assemble the cash...
-
12 A bank results when the bank's customers demand to be repaid the funds they are owed en masse. A bank results when a bank is forced to liquidate its assets and close because the value it receives...
-
Georgia Orchards produced a good crop of peaches this year. After preparing the following income statement, the company believes it should have given its No. 3 peaches to charity and saved its...
-
The Polaris Company uses a job-order costing system. The following transactions occurred in October: a. Raw materials purchased on account, $210,000. b. Raw materials used in production, $190,000...
-
Why does a business use reversing entries as part of its procedures for accounting for accrued interest income?
-
Which accounting concept is being applied when an adjusting entry is made at the end of the fiscal period to record accrued revenue?
-
Rena Jacques and George Nadler are partners in a paint and decorating store. The store operates on a yearly fiscal period. At the end of each year, an accountant is hired to prepare financial...
-
TayVon Johnson, a new accounting department employee, questions the practice of recording interest income when a note is dishonored. Instead, he believes that the interest earned on the note should...
-
Because of a temporary cash shortage, Balister Company requested an extension of time on its purchases on account. Balisters regular vendor, Custom Products, requires that a 12% note be signed for...
-
can help me this assignmets data can be find which ever you comapny you chose and their website serach amazon annual report based on that answer all the question Name of Corporation: Short Answer b...
-
Assume a simple Keynesian depression economy with a multiplier of 4 and an initial equilibrium income of $3,000. Saving and investment equal $400, and assume full employment income is $4,000. a. What...
-
Refer to the data in PE 20-3. Based on DuPonts return on investment (ROI) formula, which company performed better during the period? Data from PE 20-3 Feldman Company Bower Company Beginning total...
-
Eugene Electric makes and sells two kinds of portable music players'a CD player and an MP3 player. The sales forecasts for these players for the next four quarters are as follows: At the beginning of...
-
The Store Next Door reported the following asset values in 2011 and 2012: In addition, The Store Next Door had sales of $3,200,000 in 2012. Cost of goods sold for the year was $1,900,000. Compute The...
-
Eaton Company issued \(\$ 600,000\) of eight percent, 20 -year bonds at 106 on January 1, 2013. Interest is payable semiannually on July 1 and January 1. Through January 1, 2019, Eaton amortized \(\$...
-
Zealand Company sells a motor that carries a 3-month unconditional warranty against product failure. Based on a reliable statistical analysis, Milford knows that between the sale and the end of the...
-
On December 31, 2016, Clarke, Inc. borrowed \(\$ 900,000\) on a seven percent, 10 -year mortgage note payable. The note is to be repaid in equal annual installments of \(\$ 128,140\) (payable on...
Does That Mean I Fail A Humorous Look At Pilot Checkrides 1st Edition - ISBN: 143493005X - Free Book
Study smarter with the SolutionInn App