Excel: The power of mapping

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 “Cash and Cash Equivalents.” The difference in labels prevents the use of clever lookup formulas such as VLOOKUP. 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 “Cash 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. With auto-expansion, the table automatically expands to include new data typed or pasted immediately under the table. Any formulas that reference the table will automatically include the new data. A structured table reference is a naming system that allows references to the table and selected areas within the table¬¬, for example, a specific column.

Excel names tables using a simple naming convention: Table1, Table2, Table3, and so on. You can always rename a table by using the TableTools > Design > Table Name setting. Structured table references begin with the table name, followed by the desired table area within square brackets. For example, the reference for a column named Account in a table named TrialBalance would be TrialBalance[Account]. Excel automatically enters the correct reference when you select the desired area with your mouse.

The next requirement in this automation process is an efficient and reliable way to pull values from the data source into the map and then from the map into the report. That tool is the SUMIFS function.

SUMIFS INTRODUCTION

The SUMIFS function is officially known as a conditional summing function. In practice, it adds only those rows that meet a condition or conditions.

Here is one way to think about it: Add up this column of numbers, but include only those rows where this column is equal to this value. For example, add up the amount column but include only those rows where the account is equal to cash. The function arguments use similar logic. First is the column of numbers to add. Then the remaining arguments come in pairs: first the criteria range and then the criteria value.

The syntax for the SUMIFS function follows:

=SUMIFS(sum_range, criteria_range1, criteria1, …)

Where:

• sum_range is the range of numbers to add;
• criteria_range1 is the first criteria range;
• criteria1 is the first criteria value; and
• … up to 127 criteria range/value pairs are supported.

The SUMIFS function in this example is used to pull the values from the data table into the map and then from the map into the report.

WALK-THROUGH

This walk-through goes through the process of using the Tables and SUMIFS tools to produce an automated recurring report. To follow the walk-through, download the free Excel file at excel-university.com/map.

START HERE

The first worksheet is the Start Here sheet, which stores the year-end date and then computes the current-year and prior-year values based on the date entered, as shown below.

For convenience, this worksheet was prepared with a few Excel features, described below, none of which is strictly necessary, just personal preference. First, the key cells are named. The year-end date input cell is named date_ye, the current-year cell is named date_cy, and the prior-year cell is named date_py. They were named by selecting each cell and typing the desired name into the name box, just to the left of the formula bar. The current-year cell was computed with the YEAR function, which returns the year of the date. The input cell was shaded with the input cell style (Home > Cell Styles > Input) and the calculated cells with the calculation style (Home > Cell Styles > Calculation), just to make them pretty and easy to identify.

TRIAL BALANCE

The next worksheet contains the trial balance, which was exported from our accounting system. To take advantage of the special properties of tables, convert this ordinary range to a table by selecting a data cell and then clicking the Insert > Tables command icon. Doing so helps ensure that in the next period, when an updated trial balance is flowed, any additional rows will automatically be included because the tables auto-expand.

The trial balance table is shown below.

Note that the table has Account, Period, and Amount columns. Because comparative reports are being prepared, the Period column ensures that the formulas pull the correct values.

Another option is to change the default table name, Table1, to a more meaningful name, such as tbl_tb, by updating the Table Tools > Design > Table Name field.

MAP

The next worksheet is the map, which translates the account names to the report labels, identifies groups, and retrieves the values from the trial balance.

Type in the account names and report labels, and then use the SUMIFS function to retrieve the values from the trial balance, as shown below.

The PerTB column represents the account names per the trial balance, the PerBSheet column represents the report labels on the balance sheet, and the PerPL column represents the report labels per the income statement. Setting up multiple report label columns makes it possible to send the same amount values into multiple reports, even if the labels are different on each report. The CurrentYear column represents the current-year amounts, and the PriorYear column represents the prior-year amounts, based on the current- and prior-year cell values on the Start Here sheet. If an updated trial balance includes a new account, you will need to manually add a row to the map to provide Excel with the proper report labels.

The formula used to populate the CurrentYear column, which retrieves the values from the trial balance table for all rows in the defined current year, is:

=SUMIFS(tbl_tb[Amount],tbl_tb[Period],date_cy,tbl_tb[Account],[@PerTB])

Where:

• tbl_tb[Amount] is the column of numbers to add;
• tbl_tb[Period] is the first criteria range, the trial balance period column;
• date_cy is the first criteria value, the current year per the Start Here sheet;
• tbl_tb[Account] is the second criteria range, the trial balance account column; and
• [@PerTB] is the second criteria value, the map’s account name.

A similar formula populates the PriorYear column. (Please note that the SUMIF function can’t be used here because it has only one criteria range.)

Now that the map is built and properly retrieving values from the trial balance, all that remains is the report.

REPORT

The SUMIFS function pulls the values from the map into the balance sheet, as shown below.

The formula in cell D15, which computes the current-period amount for cash, is:

=SUMIFS(tbl_map[CurrentYear],tbl_map[PerBSheet],B15)

Where:

• tbl_map[CurrentYear] is the current-year column in the map table;
• tbl_map[PerBSheet] is the balance sheet label column in the map table; and
• B15 is the report label on the balance sheet.

This formula can be filled down throughout the report.

Note that the report headers are dynamic, and they retrieve the values from the Start Here sheet.

UPDATING

In the next period, simply paste in an updated trial balance, and the values flow into the map and into the report automatically thanks to the SUMIFS formulas. In the next year, change the year-end date on the Start Here sheet, and the correct values will be pulled into the report. If any new accounts are added, take a moment and add them to the map so that the workbook knows which report line the new account belongs to. Similarly, if an account name changes, update it in the map.

ADDITIONAL RESOURCES

Download the Excel sample file at excel-university.com/map.

Jeff Lenning (jeff@clickconsulting.com) is the founder and president of Click Consulting Inc. in Irvine, Calif.

To comment on this article or to suggest an idea for another article, contact Jeff Drew, senior editor, at jdrew@aicpa.org or 919-402-4056.

AICPA RESOURCES

JofA articles

• “Microsoft Office 2013,” April 2013, page 32
• “Pivotal Advance Boosts Excel’s Power,” Sept. 2011, page 40

CPE self-study

Advanced Excel: Practical Applications for the Accounting Professional (#745754)

Conference

Practitioners Symposium and Tech+ Conference, June 9–11, Las Vegas

For more information or to make a purchase or register, go to cpa2biz.com or call the Institute at 888-777-7077.

Information Management and Technology Assurance (IMTA) Section and CITP credential

The Information Management and Technology Assurance (IMTA) division serves members of the IMTA Membership Section, CPAs who hold the Certified Information Technology Professional (CITP) credential, other AICPA members, and accounting professionals who want to maximize information technology to provide information management and/or technology assurance services to meet their clients’ or organization’s operational, compliance, and assurance needs. To learn about the IMTA division, visit aicpa.org/IMTA. Information about the CITP credential is available at aicpa.org/CITP.