A complicated inheritance

BY J. CARLTON COLLINS, CPA

Q: I inherited an Excel workbook containing lengthy formulas, and I am having trouble determining exactly how these formulas work. Is there a magic trick for figuring out how complicated formulas work?

A: Excel provides a couple of tools that might help. The first tool is Evaluate Formula. To use this tool, highlight the complicated formula, then from the Formulas tab’s Formula Auditing group, select Evaluate Formula. This action will display the formula in the Evaluate Formula dialog box (pictured below).

With each click of the Evaluate button, Excel solves the next step of the formula to help you follow and understand the logic.

A second tool that might help reveal a formula’s logic is Trace Precedents. To use this tool, highlight the complicated formula, then from the Formulas tab’s Formula Auditing group, select Trace Precedents. This action inserts arrows depicting the flow of data from its source to the formula.

Click the Trace Precedents tool a second time to insert additional arrows depicting the flow of data to the second level of data (preceding the initial first level of source data). If desired, continue clicking Trace Precedents until all data flowing to the complicated formula is mapped to the original values entered in the workbook. This arrow mapping might help you better follow the flow of data through the workbook. ( Note: You must place your cursor on a single cell for Trace Precedents to work. It won’t work if you select multiple cells. This means you must move your cursor to each cell where you would like to trace the data flow.)

For example, examining the flow of data in the screenshot pictured below reveals that the total formulas on rows 59 and 61 do not line up with the same columns containing the data (i.e., the January totals are contained in the February column, and so on). This might explain why the reader was having trouble making sense of these formulas.

SPONSORED REPORT

How to audit high risk areas

Revenue recognition, internal control over financial reporting, accounting estimates and going concern are areas of audit that have emerged as particularly challenging and complex.

NEWS

Revenue recognition revisited

A reexamination of new revenue recognition rules has led to tinkering with the standard that is considered the biggest achievement of the convergence efforts of FASB and the International Accounting Standards Board.

INTERVIEW

Staying focused at the top

Olivia Kirtley, CPA, CGMA, an accomplished corporate director with almost 20 years of experience serving on boards, talks about strategic, risk, and compliance issues that keep board members up at night.