Objective: Learn how to find and correct errors in complex spreadsheets used for payroll.
a. Disable data validation on the hours worked column in order to input the following erroneous data:
• Change hours worked for Adams to 400,
• Change hours worked for Englert to 4, and
• Change hours worked for Hartford to –40.
Create a chart like that shown in Exhibit 2 of the article. Which of the errors are easily found by the chart? What are the strengths and limitations of creating such charts to detect errors? Print out your chart and save your work.
b. Create the three data validation rules described in the article. Print out screen shots of how you create each rule, and save your work.
c. Follow the instructions for using the formula auditing tool. Print out a screen shot showing use of the tool to circle invalid data (yours should be similar to Exhibit 9 in the article).
d. Follow the instructions to run the “trace precedents” audit tool. Print screen shots that show the results, and save your work. How useful is this tool? What are its limitations, if any?
e. Enter the following data for new employees (inserting new rows in proper order to maintain alphabetical listing of employees):
• Name = Able, pay rate = 11.11, regular hours = 40, overtime hours = 5
• Name = Easton, pay rate = 10.00, regular hours = 40, overtime hours = 0
• Name = Johnson, pay rate = 12.00, regular hours = 35, overtime hours = 10
Which audit tests and validation rules change? Why? Print screen shots, and save your work.