Add Perspective to Spreadsheets

For years most accountants functioned as information gatherers and financial historians. But today, with computers automating many of those tedious, manual jobs, CPAs are swiftly evolving into information analysts. The timing of this transformation is not any too soon: Because of the now ubiquitous personal computer, business managers are drowning in mountains of data and seeking ways to transform those raw numbers into business strategy.

Although computers were quick to create the data avalanche, they lagged in ways to eliminate, or at least shrink, it even though they were touted as number-crunchers. Spreadsheet software applications available before the early 1990s, for example, lacked the industrial strength to perform the kind of analysis that database software could do with the click of a button. To effectively analyze huge blocks of financial information generally required teaming the spreadsheet with a database application, a marriage of necessity that at the time was both clumsy and mismatched.

The advent of the software suite in which several key applications from the same vendor work relatively seamlessly together, made the marriage in some cases somewhat less disharmonious but still not very user-friendly to anyone with less than an expert knowledge of database software. Often the user had to turn to a database programmer to forge the complex links between the two applications. It wasn’t until Excel version 5 came along in 1994 that a spreadsheet application could tackle such a project single-handedly.

The solution was provided by a new function called a PivotTable, which does not only perform powerful data analysis but is easy to use. Without any special programming training, a CPA can execute some fancy financial spreadsheet analysis without turning to a database programmer for help or, in many cases, without even linking the spreadsheet to a database (see the sidebar The Competition Steams Up, below).

This article focuses on Excel rather than its competitors because it contains a more powerful data analyzer than either Lotus 1-2-3 or Quattro Pro, and because Excel is a more widely used spreadsheet application. The article walks you through several examples that illustrate how the PivotTable function can enhance an accountant’s work.

THE JOBS IT CAN DO
As shown in the examples, a PivotTable automatically reformats tables of data in a spreadsheet. What makes it so handy is that you don’t have to enter new formulas. Thus, you can analyze and compare different sets of totals by rotating and rearranging the rows and columns of the PivotTable with a few mouse movements. PivotTable also can cross-tabulate data from another Excel worksheet. And it can even import and analyze large amounts of data stored in an external database. If you’ve ever had to do some of these chores manually, you know how tedious and time-consuming they can be.

Here are some examples of where the PivotTable function can solve typical financial problems.

Example 1: As a Budgeting Tool
During a typical budgeting process, the job of consolidating and summarizing forecasted revenue and expense is labor-intensive. With standard spreadsheet techniques, hundreds of cells of data must be entered and organized and formulas added to total the data. Last-minute additions and revisions of data, which are typical, often create havoc with the inflexible formulas in the cells that are designed to calculate totals. Reorganizing data and totals after entering the formulas becomes a complex and confusing task and it invites errors. On top of that, the final spreadsheet usually is so large it doesn’t lend itself to answering the typical what-if questions that often arise.

Exhibit 1 is an example of a budget worksheet for XYZ Co. As you can see, subtotal formulas are unnecessary. Because the budget totals are generated automatically, the detail data can be stored separately on another worksheet. This arrangement makes it easy to add new data categories to the detail sheet, and thus the revised totals adjust automatically.

Exhibit 2 shows how a PivotTable reshapes the same data automatically, providing a different perspective: a department/product view. The new perspective is created with a few mouse clicks and by dragging and dropping row and column headings on the worksheet, a process illustrated later in the article.

Example 2: As a Sales Analyzer
Sales analysis involves capturing and analyzing data to discover trends and key relationships. Decision makers need to see many cuts of information, for example, by customer type, product and location. But when there are many variables, it’s difficult, if not impossible, to see the proverbial forest for the trees. A PivotTable in effect filters the forest view so users can arrange the trees to better see the once-hidden relationships and trends.

The spreadsheets in exhibits 3 to 6 are all based on the same underlying data. But exhibit 3 shows the analysis by quarter, exhibit 4 by product, exhibit 5 by customer type and exhibit 6 by region.

Example 3: As a Database Link
Most spreadsheet users have lived through the frustrating task of manually importing data to a spreadsheet from another financial application or database. In most cases, the user must first import data into columns in a worksheet and then check to be sure the right numbers line up in the correct columns. The data must then be sorted and subtotal formulas added. In subsequent financial periods, when new data are brought in from the database, this cumbersome process has to be repeated.

A PivotTable greatly streamlines the process by calculating totals directly from data stored in a database. Because the PivotTable can even be linked to the records and fields in the database, any change in the database automatically recalculates the spreadsheet totals. If the sales analysis PivotTable in exhibits 3 through 6 were linked directly to a sales transaction database, new quarterly data could be recalculated by simply clicking on the Refresh PivotTable button.

HOW IT WORKS
Now that you’ve seen what the PivotTable can do, lets go through a step-by-step demonstration of how to implement the function.

1. Create a spreadsheet file similar to that in exhibit 1 . Highlight it. (For those who don’t know how to highlight, position the cursor over one corner of the area to be calculated, hold down the left mouse button and drag the cursor over the area until its all dark.) The highlighting step is not actually necessary in this example because the only information on the page is the PivotTable target. If other data were on the page, the highlighting would be necessary so the PivotTable function would know which data it’s being ordered to work on.

2. Click on Data, PivotTable Report. When the screen asks for the location of the data (exhibit 7), click on the radio button for Microsoft Excel list or database and then click on Next and a new screen appears (exhibit 8). If you had not highlighted the range in step 1, you would have had to type in that information. Then click on Next and a new screen will appear (exhibit 9).

3. Since we want to examine the sales and profit data from the division and region perspective, use the drag-and-drop feature of the mouse to drag both Sales and Profit into the box as shown in exhibit 9 . Likewise, drag Division to the ROW space and Region to the COLUMN space.

4. Click on Next and a new screen will appear (exhibit 10) that gives you the choice of where to put the PivotTable, in a new worksheet or in the existing one. (I opted for the new worksheet.) Then click on Finish.

5. The PivotTable now creates a new worksheet in your working file and switches to it (exhibit 11). Notice that now the original data are observed from the perspective of division and region. If you want the data laid out differently, use the drag-and-drop feature. For example, you can grab the word Region and drop it to the right of the Division column and the spreadsheet view (exhibit 12) will adjust accordingly. You can rearrange the three key variables (Region, Division and Data) in any way to achieve the best perspective.

GETTING UP TO SPEED
Here is a three-step program to help you get up to speed with PivotTables:

1. Review Excels own help function by clicking on Help in your taskbar type in PivotTable. If you’re in In Excel 95/5.0, the display topic will be Retrieving and Analyzing Data; choose the subtopic Creating a PivotTable or Analyzing Data with PivotTables. In Excel 97, click on Analyzing Data with PivotTables. You will be surprised by the amount of valuable information and examples built into the Excel help system.

2. Study examples of PivotTables developed by others. Check out my Web page for this at www.lacher.com . The examples work with any of the versions of Excel.

3. Create a PivotTable with your data and spend some time playing with the tables. After you have taken this step, the concepts will become clear and you can concentrate on how best to use the function in your work. Remember, too, that Excel provides instructional wizard screens to help you create and modify PivotTables.

The advantages of the PivotTable are obvious, yet many spreadsheet users, including CPAs, shy away from new functions that appear difficult or whose usefulness is not immediately apparent. This is a function worth investing the time to learn. It will save you countless hours of building formulas in worksheets and give you the opportunity to tackle data analysis in a way you never thought possible. As a result, the PivotTable function will add value to data analysis and presentation of data.

JOHN F. LACHER, CPA, provides Excel and Access consulting services over the Internet via e-mail and remote conferencing. His e-mail address is lacher@lacher.com and his Web page address is www.lacher.com.