Obtain a copy of the article "How to Debug Excel Spreadsheets" by Rayman Meservy and Marshall Romney published in the Journal of Accountancy (November 2015, pp. 46-52) from either your school library or from the website www.aicpa.org. The spreadsheet referenced in the article is available for download from the course website. Download the spreadsheet and follow along with the steps in the article. Write a report that answers the following questions (these are not completely answered in the article). Include screenshots to support your answers. Hint: The questions below are listed in the sequence in which you will encounter them when working through the steps described in the article.

1 How do you know when the "Trace Precedents" rule has located the cell that contains the source of a chain of errors?     2 2 Which cells are affected by the error in cell AL4?            

3 Explain the nature of the circular reference in the original formula in cell AB6.        

4 When you used the "Error Checking" tool, which cells did Excel find? For which of those cells did Excel suggest the correct solution? For which cells did you decide to ignore Excel's error message? Why?  

5 In the section "Other Error-Checking Tips," the article points out that the formula for dropping the lowest score ignores blanks. Instead of doing the nonpermanent solution described in the article, create a permanent solution that will successfully handle any future missing quizzes or assignments (i.e., fix the formula so that it will correctly drop a blank cell instead of the lowest non-blank cell).    

6 Write a data validation rule that would prevent the kind of error that exists in cell U53, so that you do not have to rely on manually identifying such an error and manually correcting it.  

7 The final paragraph of the section "Other Error-Checking Tips" asks whether there remain any other cells that have values amid a column of formulas. Did you find any?  

8 The final section of the article asks you to examine the formulas to see if they are correct. Did you find any logic errors? Explain.                    

The report is formatted for you and the first problem is shown as an example.        

Problem - Example: error in a formula

Cell(s) where problem located - E19

Explanation: Why this is a problem - Overtime pay is miscalculated as 1.5x total hours worked, rather than just overtime hours.

Solution - Correct the formula so that it only applies overtime rate to hours worked in excess of 40.