Excel Problem*Objective: Learn how to find and correct errors in complex spreadsheets used...

Excel Problem*

Objective: Learn how to find and correct errors in complex spreadsheets used for payroll.

REQUIRED

a. Read the article “Ferret Out Spreadsheet Errors” by Mark G. Simkin, in the Journal of Accountancy (February 2004). You can find a copy online by accessing www.aicpa.org.

---

b. Download the worksheet referenced in the article.

---

c. 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.

• 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.

---

d. Create the three data validation rules described in the article (Exhibits 4–7 in the article illustrate how to create the first rule). Print out screen shots of how you create each rule, and save your work. (Note: The article “Block That Spreadsheet Error” by Theo Callahan, in the Journal of Accountancy(August 2002) provides additional examples of data validation rules.)

---

e. 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).

---

f. Follow the article’s 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?

---

g. Enter the following data for new employees (insert new rows in the proper order to maintain the alphabetical listing of employees):

• Name 5 Able, pay rate 5 11.11, regular hours 5 40, overtime hours 5 5

• Name 5 Easton, pay rate 5 10.00, regular hours 5 40, overtime hours 5 0

• Name 5 Johnson, pay rate 5 12.00, regular hours 5 35, overtime hours 5 10

Which audit tests and validation rules change? Why? Print screen shots, and save your work.

*Life-long learning opportunity: see p. xxii in preface.