Putting the Squeeze on Spreadsheets

Users can select how much detail they want to view.
BY DON KIAMIE

  

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

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 .

SPONSORED REPORT

Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.

QUIZ

News quiz: IRS warning on cyberattacks and a change in pension rules

Once again, the IRS sounds the alarm about a threat from cyberthieves. See how much you know about this and other recent news with this short quiz.

CHECKLIST

Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.