Troubleshooting formulas and Data Entry in a Payroll Data Workbook for Irene's Scrapbooking World
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 errors from occurring in the future.
Complete the following steps:
1. Open the workbook named Payroll.xlsx and then save the tile 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 C7:C15.
4. Set up an appropriate validation rule, input message, and error alert for the Allowances data in cells D7:D15. (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.
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 tbr 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.
Attachment:- payroll.xlsx