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

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.