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
