This site uses cookies to store information on your computer. Some are essential to make our site work; others help us improve the user experience. By using the site, you consent to the placement of these cookies. Read our privacy policy to learn more.

Drilling for Information

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

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.

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 jeff@lenning.com .
His Web site is at www.lenning.com/consulting
.

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

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 .

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.

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/dec1998/lacher.htm
. 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 or www.aicpa.org/pubs/jofa/dec1999/html/techwk.html.

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 .

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 .

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

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.

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

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

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 .

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
.

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

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.

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
(“drilldown100.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.

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.

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 zarowin@mindspring.com
.

Are you working with the best technology? Do you know how to help your clients determine if their technology stack measures up? In this free report, J. Carlton Collins, CPA, explains how to answer those questions via a technology assessment engagement.