60.1K

Verified Solution

Link Copied!

please include steps on how to include each questions

please include steps on how to complete each problem**

Irene's Scrapbooking World
Payroll Worksheet
For the Pay Period Ended January 29, 2016
Name	SSN	Tax Status	Allowances	Insurance Plan	Contribute to 401K	Pay Rate per Hour	Overtime Rate	Hours Worked	Gross Pay	401K Deduction	Taxable Wages	Health Insurance	Other Deductions	Other Deductions Comments
Anderson, Marsha	501-85-7894	Married	2	Family		17.5	26.25	92	1,715	64.313	1,650.688	101.2	15	United Way
Campbell, Titus	502-76-7891	Married	4	Family		25	37.5	80	3,000	0	3,000	101.2
Franks, Alice	473-75-7789	Single	1	Single		19.5	29.25	80	2,340	0	2,340	52.75
Hansen, Belinda	472-85-1237	Maried	3	Family		22.5	33.75	72	2,430	0	2,430	101.2
Jackson, Thomas	471-99-4587	Single	1	Single		25	37.5	87.5	2,281.25	0	2,281.25	52.75
Limesand, Edna	991-87-7789	Single	2	No		17.25	8.625	40	345	0	345
Petters, Sally	501-45-1298	Divorced	2	FAMLY		41.5	62.25	60	3,735	0	3,735
Richardson, Denise	502-23-8989	Single	1	Single		13	19.5	80	1,560	0	1,560	52.75	50	Court-ordered child support
Watson, Irene	472-19-6547	Married	4	Family		21.5	32.25	102.5	2,445.625	0	2,445.625	101.2	50	United Way
Totals
Valid Withholding Tax Statuses:							401K Deduction Percentage:				Health Insurance Plan Options:
Single							3.75%				Plan Type	Deduction per Pay Period
Married											Family	$101.20
											Single	$52.75
											None	$0.00

Level 1 - Troubleshooting Formulas and Data Entry in a Payroll Data Workbook for Irene's Scrapbooking World Human Resources Similar to other small businesses, Irene's Scrapbooking World outsources the processing of its payroll to its accounting firm. Twice per month, Irene Watson, the owner of Irene's Scrapbooking World, creates a workbook that contains her employees' payroll information to send to the accounting firm of Wipson & Lynn, LLP. Wipson & Lynn then uses that information to prepare the paychecks for the employees and calculate Irene's Scrapbooking World's payroll liabilities to the government and other entities, and to prepare its payroll-related reports, such as the Federal 941 forms. Wipson & Lynn charges Irene based on the number of paychecks it processes for her company. A number of Irene's payroll-related workbooks have contained data-entry errors that the firm's personnel had to fix before it could process the payroll. Wipson & Lynn also charges Irene for the time it takes to track down and fix those errors. Irene has asked you to use the Excel data validation and protection tools to troubleshoot the current payroll workbook and set up the workbook to help prevent crrors from occurring in the future. 1. Open the workbook named Payroll.xlsx located in the Chapter 10 folder, and then save the file as Scrapbooking Payroll.xlsx 2. On the Documentation worksheet, enter your name and the current date in the appropriate cells. Review the documentation information 3. Switch to the Payroll Register worksheet. Set up an appropriate validation rule, input message, and error alert for the Tax Status data in cells C7C15 4. Set up an appropriate validation rule, input message, and error alert for the Allowances data in cells D7:015. (Hint: Employees can take 0 or more withholding allowances.) 5. Set up an appropriate validation rule, input message, and error alert for the Insurance Plan data in cells E7:E15. (Hint: The valid health plan options are stored in cells L22:L24.) 6. Identify and correct all data that does not meet the validation settings you created in Steps 3 through 5. 7. Correct any other flagged or unflagged errors. 10 7. Correct any other flagged or unflagged errors. 8. Change the properties of the cells so that users can edit only the hours worked information for each employee when the worksheet is protected. 9. Hide the column containing the SSN numbers. 10. Insert a comment in cell D6, stating that "Allowances are the number of dependents claimed for income tax withholding purposes." 11. Enable worksheet protection. Do not specify a password. 12. Enable the workbook structure protection 13. Save and close the Scrapbooking Payroll.xlsx workbook. Chapter Exercises ITP B E F M N G H Irene's Scrapbooking World Payroll Worksheet For the Pay Period Ended January 29, 2016 To Plan 401K Deduchon 0431 Name SSN Status 7 Anderson Marsha 501.85-7804 Married 8 Campbell, Tu 502-70-7801 Mamed 0 Franks. Alge 473-75-7789 Single 10 Hansen, Belinda 472.85-1237 Marie 11 Jackson, Thomas 471.90-4567 Single 12 Limosand, Edina 001:87.7700 single 13 Petters, Sly 501-45-1205 Divorce 14 Richardson, Denise 602,23-1000 ingle 15 Watson trene 472-10-0547 Married 10 Totale 17 18 10 20 Valid Withholding Tax Statuses: 21 tunge 22 Mamed 23 24 25 Insurance Contribut Pay Rate Overtime Allowances to 401K per Hour Rate 2 Family TRUE 17.50 2025 4 Family TRUE 25.00 37.50 1 Single FALSE 19.50 2025 3 Fay TRUE 22 50 3375 1. Single 2500 37.50 2 No TRUE 1725 863 2 FAMLY FALSE 4150 6225 11 Single TRUE 13.00 19 50 Family TRUE 21.50 3225 Hours Gross Worked Pay 22.00 10.00 3000 7200 8750 40.00 345.00 00.00 00 100 50 D Taxable Health Other Other Deductions Wages Insurance Deductions Comments 1.050.00 101.20 15.00 United Way 3.000.00 10120 2340 00 59.75 2430.00 101.20 2012 62.75 345.00 WNA 3.735.00 ONIA 10000 52.75 50.00 Court ordered ched uport 2/44583 10120 5000 Unted Way TRUE 401K Deduction Percentage IN Health Insurance Plan Options Plan Type Deduction per Pay Period Family 11020 Single 562.75 None 50.00 27 28 30 Level 1 - Troubleshooting Formulas and Data Entry in a Payroll Data Workbook for Irene's Scrapbooking World Human Resources Similar to other small businesses, Irene's Scrapbooking World outsources the processing of its payroll to its accounting firm. Twice per month, Irene Watson, the owner of Irene's Scrapbooking World, creates a workbook that contains her employees' payroll information to send to the accounting firm of Wipson & Lynn, LLP. Wipson & Lynn then uses that information to prepare the paychecks for the employees and calculate Irene's Scrapbooking World's payroll liabilities to the government and other entities, and to prepare its payroll-related reports, such as the Federal 941 forms. Wipson & Lynn charges Irene based on the number of paychecks it processes for her company. A number of Irene's payroll-related workbooks have contained data-entry errors that the firm's personnel had to fix before it could process the payroll. Wipson & Lynn also charges Irene for the time it takes to track down and fix those errors. Irene has asked you to use the Excel data validation and protection tools to troubleshoot the current payroll workbook and set up the workbook to help prevent crrors from occurring in the future. 1. Open the workbook named Payroll.xlsx located in the Chapter 10 folder, and then save the file as Scrapbooking Payroll.xlsx 2. On the Documentation worksheet, enter your name and the current date in the appropriate cells. Review the documentation information 3. Switch to the Payroll Register worksheet. Set up an appropriate validation rule, input message, and error alert for the Tax Status data in cells C7C15 4. Set up an appropriate validation rule, input message, and error alert for the Allowances data in cells D7:015. (Hint: Employees can take 0 or more withholding allowances.) 5. Set up an appropriate validation rule, input message, and error alert for the Insurance Plan data in cells E7:E15. (Hint: The valid health plan options are stored in cells L22:L24.) 6. Identify and correct all data that does not meet the validation settings you created in Steps 3 through 5. 7. Correct any other flagged or unflagged errors. 10 7. Correct any other flagged or unflagged errors. 8. Change the properties of the cells so that users can edit only the hours worked information for each employee when the worksheet is protected. 9. Hide the column containing the SSN numbers. 10. Insert a comment in cell D6, stating that "Allowances are the number of dependents claimed for income tax withholding purposes." 11. Enable worksheet protection. Do not specify a password. 12. Enable the workbook structure protection 13. Save and close the Scrapbooking Payroll.xlsx workbook. Chapter Exercises ITP B E F M N G H Irene's Scrapbooking World Payroll Worksheet For the Pay Period Ended January 29, 2016 To Plan 401K Deduchon 0431 Name SSN Status 7 Anderson Marsha 501.85-7804 Married 8 Campbell, Tu 502-70-7801 Mamed 0 Franks. Alge 473-75-7789 Single 10 Hansen, Belinda 472.85-1237 Marie 11 Jackson, Thomas 471.90-4567 Single 12 Limosand, Edina 001:87.7700 single 13 Petters, Sly 501-45-1205 Divorce 14 Richardson, Denise 602,23-1000 ingle 15 Watson trene 472-10-0547 Married 10 Totale 17 18 10 20 Valid Withholding Tax Statuses: 21 tunge 22 Mamed 23 24 25 Insurance Contribut Pay Rate Overtime Allowances to 401K per Hour Rate 2 Family TRUE 17.50 2025 4 Family TRUE 25.00 37.50 1 Single FALSE 19.50 2025 3 Fay TRUE 22 50 3375 1. Single 2500 37.50 2 No TRUE 1725 863 2 FAMLY FALSE 4150 6225 11 Single TRUE 13.00 19 50 Family TRUE 21.50 3225 Hours Gross Worked Pay 22.00 10.00 3000 7200 8750 40.00 345.00 00.00 00 100 50 D Taxable Health Other Other Deductions Wages Insurance Deductions Comments 1.050.00 101.20 15.00 United Way 3.000.00 10120 2340 00 59.75 2430.00 101.20 2012 62.75 345.00 WNA 3.735.00 ONIA 10000 52.75 50.00 Court ordered ched uport 2/44583 10120 5000 Unted Way TRUE 401K Deduction Percentage IN Health Insurance Plan Options Plan Type Deduction per Pay Period Family 11020 Single 562.75 None 50.00 27 28 30

Answer & Explanation Solved by verified expert

See Answer