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, page 92, 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. …