Putting the squeeze on spreadsheets

In this age of electronic spreadsheets, local and wide area networks and the Internet, it’s a cinch to distribute financial data to many different people in an organization. And therein lies a problem. The CEO says he’s too busy for all the details (“Just give me an overview”). The CFO, on the other hand, wants both the overview (“So I can quickly see the trends”) and the line-item details. Meanwhile one manager wants only financial details that focus on her division (“Please don’t overload me with all those other irrelevant numbers”). And another wants only the details for his two divisions (“I really don’t need to see the other stuff”).

Does that mean the accounting department has to spend hours customizing financial statements for each recipient? If so, a job that could otherwise be accomplished with just a few mouse clicks suddenly turns into a distribution nightmare.

PUFF, IT’S GONE

Excel’s Group command solves this problem. It allows a user, with just a single mouse click, to collapse detailed information so only selected results (totals, for example) are visible. Likewise, with another mouse click, underlying details will reappear with the totals.

I will demonstrate how it works with a basic income statement, but be aware the Group command can be used in any other spreadsheet application.

Begin by setting up a basic income statement spreadsheet such as the one in exhibit 1. Of course, you can invent any numbers—just as long as you add the appropriate formulas to produce the totals for lines 6, 15, 22, 28 and 37.

The next step is to set up the groups under the Group function for the five areas you want to be able to collapse: Total Revenues (line 6), Total Plant Expenses (line 15), Total Salaries and Salary-Related Expenses (line 22), Total Selling, Marketing and General and Administrative (line 28) and Total Taxes (line 37). To start, group lines 4 (Rental Revenues) and 5 (Other Revenues) so that a user can make them collapse, leaving only line 6 (Total Revenues).

To do this, highlight lines 5 and 6 (the line numbers in the extreme left edge of the screen) by clicking on 5 and then dragging the mouse cursor to line 6. If you have trouble getting both to stay highlighted, click on 5 and, while holding down the Ctrl key, click on 6. Once both lines are highlighted, click on Data, Group and Outline, Group (see exhibit 2).

You should now see bullets to the left of lines 3, 4 and 5, and a minus sign to the left of line 6 (Total Revenues). The minus sign indicates that the bulleted lines which are embraced by the bracket can be collapsed into line 6 (see exhibit 3).

Now if you click on the minus sign, notice how lines 3, 4 and 5 disappear—leaving only line 6 (Total Revenues). The minus sign adjacent to Total Revenues now changes to a plus sign, indicating it contains underlying data (see exhibit 4).

If you perform the same grouping on each of the other areas, the spreadsheet will shrink (see exhibit 5).

Users can view details for any area by clicking on the appropriate plus sign. When all the plus signs are clicked, the full spreadsheet will resemble exhibit 6.

This procedure can be used with any type of financial statement, schedule or budget. Any number of columns can be added.

The next time you prepare a spreadsheet for presentation, invest a few extra minutes grouping the information. It will give users full control over how much detail they want displayed. Your audience will appreciate it.

DON KIAMIE, CPA, is the CFO and executive vice-president of Windsor Management Corp., New York. His e-mail address is donalbert@mindspring.com.