Drilling for Information

In the late 1980s, most accountants put aside their columnar pads and calculators and advanced to computers. In some respects, that was a big step, but in reality, very little actually changed: The financial reports were simply transferred from paper to the computer screen. Although the efficiency of report generation was improved, the reports were still static and what you saw on the screen, just as what you saw on the paper, was what you got. Unless extra information was added in a footnote or an addendum, there was no way such a static report—whether it was prepared on paper or on the computer—would show where any of the numbers came from or how they were calculated.

Today’s technology has given financial managers the opportunity to make reports both interactive and multidimensional. Mind you, we’re not talking about leading-edge or tomorrow’s technology. Set up properly, Microsoft Excel can allow financial report viewers to dig below the surface of a statement and uncover the source of any number and how it was calculated—in effect, to add new dimensions to otherwise static reports. The technique is commonly called drill-down functionality.

Previously, we described how to distribute financial reports throughout an organization electronically rather than on paper (see “Financial Reports in a Snap” JofA, Apr.00, page 31). Once freed from the paper medium, reports also can become dynamic and interactive. This article demonstrates how to achieve drill-down functionality by creating an interactive selling, general and administrative (SG&A) expense report.

THE ADVANTAGES

In a typical situation, a manager of a sales department receives a monthly SG&A report from the finance department—either on paper or via a computer network (see Exhibit 1).

Exhibit 1

Although the summary is useful, each time you want more information about one of the numbers you’ve got to sift through another, more detailed report for backup details—a time-consuming job. However, if you set up the report with drill-down functionality, all you need to do is double-click on an account name, such as Travel in cell B10, and the software automatically drills down to reveal the underlying detail of the expenses in row 10, as shown in Exhibit 2.

Exhibit 2

Likewise, if you want to see the details on John Smith’s travel accrual, click on the $5,000 item (F10) and the supporting information appears, as shown in Exhibit 3. All the data are just a click away—a savings in time and effort.

Exhibit 3

The PivotTable function has extremely wide applications. For more on how it’s used, see “Add Perspective to Spreadsheets,” JofA, Dec.98, page 9. That article demonstrates many of the basic features of PivotTables and describes how to set one up.

This article focuses on one narrow application of PivotTables, namely drill-down technology, incorporating an advanced feature—linking a database to a spreadsheet. If you are not familiar with the process, refer to “Spreadsheet, Meet Database; Database, Meet Spreadsheet,” JofA, Dec.99, page 33.

HOW TO DO IT

Here’s how the process works: All the original source data for the drill-down file resides, of course, in the general ledger database. Each month (or your preferred reporting period) you export that data into Access and establish a link between Access and Excel. The data then flow into an Excel PivotTable, which manipulates the information so it’s viewable in customizable ways. Although this article presents Access as an intermediate step between the general ledger and Excel, the database is necessary only if you want to automate the data import.

NOW FOR THE STEP-BY-STEP PROCESS

We’ll begin by setting up the reports manually. Later we’ll describe how to automate updating of data. First we need to export data from the general ledger into a text file. If you don’t know how to do that, check with your accounting software vendor. You may be given several exporting options; exporting as a comma-delimited flat file works well. The minimum fields you need to export are month, department number, account number, account description, transaction description and transaction amount.

Import this file into an Access table to which you keep appending the current month’s data. The table should contain all the fields you export from the general ledger. Depending on how much information you want to provide to your managers, you may want to include additional fields—for example, purchase order number, transaction date and invoice number.

To import the file, click on the Tables tab in Access and on the New button to create a new table, as shown in Exhibit 4.

Exhibit 4

Select Import Table (see Exhibit 4) and follow the directions in the following screen and browse to your newly created flat data file. If your export includes field names, check the First Row Contains Field Names box as indicated in Exhibit 5.

Exhibit 5

Proceed through the wizard screens, selecting In a New Table to create your transaction detail table as shown in Exhibit 6.

Exhibit 6

Select No Primary Key, as shown in Exhibit 7 and click Finish.

Exhibit 7

Now that the transaction-level detail table is in Access, set up the department reports in Excel—a separate workbook for each department. Open a new workbook. Select PivotTable Report from the Data menu and then select External data source at the wizard (see Exhibit 8). The external data source tells Excel to obtain the data for the reports from the just-created Access database.

Exhibit 8

Now refer to the Access database by clicking the Get Data button (see Exhibit 9). If your database isn’t listed, select the New Data Source and follow the prompts. (For additional help on this step, refer to the JofA December 1999 article on spreadsheets.)

Exhibit 9

Select your transaction detail table and choose all the fields. Filter the records to include the date range you want. In this example, you want reports that show annual spending figures, so filter to include only transactions that are between the dates of 1/1/2000 to 12/31/2000 (see Exhibit 10).

Exhibit 10

Notice that Exhibit 10 indicates a filter through 12/1/00—not 12/31/00. This is because in the data shown the first day of the month represents that month, so the January 2000 data are represented in the database as 1/1/00. Also, since this is a department workbook, you must filter the records to include only transactions for this department (see Exhibit 11)

Exhibit 11

To format the PivotTable, drag the gray field header tabs to the locations shown in Exhibit 12.

Exhibit 12

Now double-click the account number and account description field tabs and indicate no subtotals, as shown in Exhibit 13.

Exhibit 13

After formatting the Account_num and Account_ desc fields, return to the PivotTableWizard as shown in Exhibit 12. Click Next and specify the location of your PivotTable. Also verify that Enable drilldown is checked (as shown in Exhibit 14) by clicking on PivotTable Options.

Exhibit 14

You now have the basic PivotTable. To bring up the summary level report when first opening the Excel file (as shown in Exhibit 1), simply click the Transaction description gray field tab (cell C7 in Exhibit 1) and the hide detail button on the PivotTable toolbar, as circled in red in Exhibit 15.

Congratulations, you have set up the basic report. Save it as “100.xls.” To get more detail, double-click in any of the following areas:

• Account name: To show the various transaction descriptions within that account.
• Specific amount: To list all the transactions on a new tab.
• Travel expense total (or any of the row totals): To list on a new tab all the transactions for travel occurring in all months.
• Column totals (such as Jan-00 total): To list on a new tab all January transactions.

AUTOMATING REPORTS

Manually updating these reports each month would be time-consuming. So the next steps show how to automate the process.

Start by creating a drill-down report administrator workbook in Excel, which we’ll call the “admin.xls,” which will be the control center for the monthly update process. Thus, when you are ready to update the reports every month, simply open the admin.xls workbook and proceed through each step outlined in it. Each step has a control (either a hyperlink or a button) that triggers an action (see Exhibit 16).

Exhibit 16

Step 1: Import the general ledger flat file into Access. After the general ledger exports the transaction-level data, we need to bring it into Access with a macro which assumes that the export file you create always has the same name and is stored in the same folder.

In Access, click on the Macros tab and New. Select TransferText and fill in the arguments as applicable (see Exhibit 17). The TransferText command automates importing delimited text files into the appropriate table.

Exhibit 17

For some imports, you may need to set up a Specification Name field to identify each field data type. Refer to Access’s Help for further assistance on this.

Now, still in Access, create the form to make navigation easy by clicking the Forms tab and New. Select the Insert Hyperlink command and then the just-created macro and save the form. Go to Tools, Startup and specify the Display Form. Now when you open this database, the form opens automatically, and clicking the hyperlink imports the file.

To set up the admin.xls control center, open admin.xls and click on Insert, Hyperlink and browse to the Access database. Each month, when you’re ready to create your reports, open admin.xls and click on the Step 1 hyperlink to open your Access database’s opening form. Then click the hyperlink in the opening form to automatically import the current flat file. Close the Access database and you’ll be back to your admin.xls spreadsheet and ready for Step 2.

Step 2: Opening the department workbooks. Automate the opening of the department workbooks in Excel with a macro—call it, say, OpenAll. Create the macro with the Visual Basic Editor (VBE) by selecting Tools, Macro, Visual Basic Editor. Once in VBE, go to Insert, Module, and enter the following text exactly as shown—line breaks and all:

For each Excel workbook that you want to open, insert in the OpenAll macro a line that reads: Workbooks.Open (“XXX.xls”), where XXX is the file name of the workbook.

The macro assumes that the department workbooks reside in the same folder as the admin.xls file. If they don’t, add the path to the open command. For example, Workbooks.Open (“\drilldown\100.xls”).

After you finish the macro, close the VBE and insert a button by selecting View, Toolbars and the Forms menu. Click on the button command on the forms toolbar (see Exhibit 18) and click in the appropriate place in your spreadsheet (I placed my button in the Step 2 section of my workbook). When prompted to assign a macro, indicate the macro you just created, OpenAll. Now when you click the button, all your drill-down reports will open.

Exhibit 18

Step 3: Refreshing the PivotTables. You now need to set up an additional macro to refresh the PivotTables. Open the VBE and enter the following text under your previous macro, OpenAll:

Note: Where it says, “‘modify the name of your fields as applicable,” you need to replace Account_num and Account_desc with the corresponding names of your fields if they are different. Also, if you named your PivotTable something other than the default name, “PivotTable1,” you need to change the text in the code above to reflect the name you selected. Those six lines of code are not mandatory, so you can delete them if they cause problems. They tell Excel to hide the detail so that users initially see the summary level, with all details hidden.

Now close the editor and insert a button at the Step 3 section of the workbook and assign this macro, Refresh AllOpenBooks. Now when you click this button, all your workbooks will pull the current month information into your PivotTable from Access. When running the macro, only the department and admin.xls workbooks should be open.

Step 4. Saving and closing the workbooks. Open the VBE and insert the following text under your previous macro, RefreshAllOpenBooks:

Close the editor and create a button at Step 4 in your workbook and assign this macro, SaveAndCloseAll to the button. When you click this button, your department workbooks will save and close.

Although it took a lot of steps to create this automated drill-down feature, you’ll surely bless it each month when you can provide a full, interactive report with just a few mouse clicks. Now that you have the basic steps, you can create drill-down reports for other financial activities.