Excel: The Power of Mapping: CPAs Can Employ Tables and the SUMIFS Function to Save Time and Reduce Mistakes When Creating Recurring Reports
Jeff Lenning · Journal of accountancy online/Journal of accountancy · 2014
[ILLUSTRATION OMITTED] Many financial systems do a fine job of generating standard reports, but accountants often need more. In those cases, accountants can create custom solutions in Excel, but that approach has drawbacks. For instance, a typical process may involve exporting some type of data, perhaps a trial balance, and then opening it in Excel. Once the data is in Excel, the person preparing the report may need to manually reformat the data, aggregate some numbers, and/or change some report labels or headers. It's a process that can be time-consuming and prone to error, especially when it has to be repeated regularly, such as with the preparation of financial statements from a trial balance. The good news is that Excel provides the tools necessary to automate much of the process. This article explores features, functions, and techniques that allow for the creation of recurring reports in Excel. STRATEGY This article focuses on building a balance sheet from a trial balance exported from an accounting system such as QuickBooks, but the underlying strategy can be implemented in a number of situations. The overriding concept is that data is exported in an Excel-compatible format so that it can be opened in Excel and saved in a worksheet within an Excel workbook. These values need to find their way into the recurring report, in this case the balance sheet, in an automated way Two typical problems encountered when trying to get the trial balance data to flow into the balance sheet are that the category labels are different and that multiple accounts may need to be aggregated to flow to a single report line. What does it mean that the labels are different? Consider, as an example, the item labeled checking in the trial balance. The value of checking is reflected in the balance sheet being produced but under the label and Cash Equivalents. The difference in labels prevents the use of clever lookup formulas such as VLOOKUE Instead, a typical approach would be to use a direct cell reference, such as=A1, to retrieve the value. The problem with direct cell references is that there is no guarantee that account values will reside in the same cell each period. For example, Checking might be in cell A1 one month, but in cell A2 the next. Such changes generate errors and inefficiencies. Further complicating the report generation is that multiple accounts often need to be summed up to compute a balance sheet line item. For example, the three accounts Checking, Savings, and Certificates of Deposit need to be aggregated in the balance sheet item and Cash Equivalents. Again, direct cell references, such as =A1+A3+A5, can be used, but the possibility looms that when the updated trial balance arrives, the accounts will be in different cells, resulting in errors and inefficiencies. Both issues can be addressed with a mapping table, or map for short. With a map, the data doesn't flow directly from the trial balance to the balance sheet report. Instead, the data flows from the trial balance into the map and then from the map into the balance sheet. The map contains the information Excel needs to fully automate the data flow, including translating the labels and aggregating account values. Building the map is fairly easy. Indeed, all that is needed is a single Excel feature, Tables, and a single Excel function, SUMIFS. Both were introduced with Excel 2007 for Windows and are unavailable in earlier versions. TABLE INTRODUCTION While the map and trial balance data could be stored in ordinary ranges, it is better to store them in a table. To use the Tables feature, enter the initial data into ordinary cells, then convert the data range into a table by selecting any cell within the range and clicking on Table on the Insert tab. The conversion of an ordinary range into a table applies several special properties, two of which are auto-expansion and structured table references. …