Drilling for information.

Click on a number in a financial report and get all the details.

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 call 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 ILLUSTRATION OMITTED]

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 ILLUSTRATION OMITTED]

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 ILLUSTRATION OMITTED]

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 ILLUSTRATION OMITTED]

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 ILLUSTRATION OMITTED]

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

[Exhibit 6 ILLUSTRATION OMITTED]

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

[Exhibit 7 ILLUSTRATION OMITTED]

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 ILLUSTRATION OMITTED]

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.) 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).

[Exhibits 9-10 ILLUSTRATION OMITTED]

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 ILLUSTRATION OMITTED]

To format the PivotTable, drag the gray field header tabs to the locations shown in exhibit 12. Now double-click the account number and account description field tabs and indicate no subtotals, as shown in exhibit 13.

[Exhibits 12-13 ILLUSTRATION OMITTED]

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 ILLUSTRATION OMITTED]

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.

[Exhibit 15 ILLUSTRATION OMITTED]

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 ILLUSTRATION OMITTED]

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 ILLUSTRATION OMITTED]

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:

Sub OpenAll()
  `this macro will open all of the sga detail workbooks.
  `you need to manually add any new departments to the list.

  `open the workbooks listed here. (Add lines as necessary)
  Workbooks.Open ("100.xls")

  `activate this workbook
  ThisWorkbook.Activate
End Sub

For each Excel workbook that you want to open, insert in the OpenAll macro a line that reads: Wookbooks. 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 ILLUSTRATION OMITTED]

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:

Sub RefreshAllOpenBooks()
  `this will refresh the pivot table in each open book

  For Each w In Workbooks
    Workbooks(w.Name).Activate
    `if workbook is visible
    If ActiveWindow.Visible = True Then
    `if workbook is not this workbook
    If w.Name <> ThisWorkbook.Name Then
       Workbooks(w.Name).RefreshAll

`modify the name of your fields as applicable
ActiveSheet.PivotTables("PivotTable1").PivotSelect
 "Account_num[All]", xlLabelOnly
   Selection.ShowDetail = True

ActiveSheet.PivotTables("PivotTable1").PivotSelect
 "Account_desc[All]", xlLabelOnly
 Selection.ShowDetail = True

ActiveSheet.PivotTables("PivotTable1"). PivotSelect
  "Account_desc[ALL]", xlLabelOnly
   Selection.ShowDetail = False

        End If
     End If
  Next w
End Sub

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:

Sub SaveAndCloseAll()
  `this will save and close all open workbooks

  For Each w In Workbooks
     Workbooks(w.Name).Activate
     `if workbook is visible
     If ActiveWindow.Visible = True Then
        `if workbook is not this workbook
        If w.Name <> ThisWorkbook.Name Then
           Workbooks(w.Name).Save
           Workbooks(w.Name).Close
        End If
      End If
   Next w
End Sub

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.

USEFUL REFERENCES

The PivotTable function has extremely wide applications. For more on how it's used, see "Add Perspective to Spreadsheets,"JofA, Dec.98, page 9, or www.aicpa.org/pubs/jofa/dec 1998/lacher.html. 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 Pivot Tables, 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 or www.aicpa.org/pubs/ jofa/dec1999/haase/techwk.html.

EXECUTIVE SUMMARY

* TODAY'S TECHNOLOGY GIVES FINANCIAL MANAGERS the opportunity to create interactive, multidimensional reports. With the click of a few buttons, you can prepare up-to-date financial reports automatically.

* IN ADDITION, THE REPORTS CAN PROVIDE drill-down functionality. Click on a number in the report, and the underlying support data appear.

* THE MECHANISM APPLIES the PivotTable function of Microsoft Excel.

* DATA ARE COLLECTED AUTOMATICALLY from your accounting software database and imported into an Access database, where they are then brought into Excel.

* THE BENEFITS OF SUCH A PROCESS ARE THAT

* You can provide reports that are truly interactive.

* Managers can open one report and see both the summary view and the detail behind any number in the report.

* The report expands and collapses as the user desires.

An Invitation

If you have a special how-to technology topic you would like the JofA to consider for inclusion in this series, or an application shortcut you devised and would like to share with other professionals, contact Senior Editor Stanley Zarowin. His e-mail address is [e-mail].

JEFF LENNING, CPA, is the accounting manager at Interpore Cross International, Irvine, California. He also heads a consulting firm, Click Consulting. His e-mail address is [e-mail]. His Web site is at www.lenning.com/consulting.