By Curtis D. Frye

As a financial analyst, you have a goal to discover information about your business that can help you make strategic decisions. This information, called business intelligence, takes many forms. For example, if you work in a nonprofit organization, you may need to know when donations are at their lowest level so that you can increase your fundraising efforts during those months. If you work in a for-profit organization, you may need to know when to hire extra customer service representatives to handle increased call volumes at your call center.

To better understand your business intelligence, you need the means to transform your data so that it supports meaningful analysis. You can use the PivotTable tool in Microsoft Office Excel to analyze your data from multiple perspectives and discover patterns and exceptions, which helps you make decisions that improve your organization's performance.

Preliminary data analysis

A PivotTable report presents your data dynamically and emphasizes different aspects of the data. Consider the case of a vice president of a defense contractor company who has received a complaint from a client. The client feels that the defense contractor spent too little time planning for the client's project during the first half of the year and then rushed to catch up from July through December.

The VP considers the complaint a serious one because the client is a lucrative source of work for the future. He begins to determine the source of the client's complaints. The first step in his investigation is to review an Excel worksheet summary of how the department in charge of the project allocated its labor during the past calendar year. The following figure shows a portion of this data list.

This data list is extensive, and it's difficult to derive meaning from it. To make the data easier to review, the VP creates a compact, easy-to-read PivotTable report that highlights each client's projects and monthly labor costs. In the portion of this report shown below, the code number is C441 for the client who complained, and the project number is PJ742.

In this PivotTable report, the labor costs for project PJ742 during the early months of the year are in parentheses, indicating the negative dollar amounts that represent unspent labor costs for the particular month. It's clear from the report that the department spent much less on labor than the amount that was budgeted for the project.

The VP finds that the PivotTable report still contains too many fields for meaningful analysis. To transform the labor costs from monthly allocations to quarterly allocations, in the PivotTable report the VP places his pointer on the Month field header and drags the field header off the body of the report. The report now displays only the quarterly labor summaries.

According to this quarterly summary, labor expenditures for project PJ742 were significantly below expectations during the first two quarters of the year; the expenditures are indicated by the dollar amounts in parentheses, which represent unspent labor costs. But labor costs exceeded the planned budget during the last two quarters.

After checking with the department head, the VP discovers that the department's staffing level remained relatively consistent throughout the year. This means that personnel availability cannot account for the discrepancy in labor costs.

Searching for patterns

The VP then searches for patterns that might help him make sense of the labor expenditures. The current configuration of the VP's PivotTable report compares the labor expenditures for different projects. He notices that project PJ844 shows labor costs that were directly opposite to those shown for project PJ742. That is, the labor costs for project PJ844 were well over budget during the first two quarters and substantially under budget during the last two quarters of the year.

The VP is suspicious about this spending pattern, and his next step is to discover whether a common link exists between the two projects. To learn whether other fields will help analyze the PivotTable data, the VP right-clicks on any cell in the Pivot Table report. He then clicks Field List from the drop-down list box to display the Field List dialog box.

The Field List dialog box displays the name for every field in the original data list. It also identifies which fields appear in the current PivotTable report configuration. Field names displayed in bold type appear in the current PivotTable report; field names displayed in regular type do not appear in the current report configuration.

In the Field List dialog box, two fields are unused in the PivotTable report's current configuration: Month, which was used in an earlier configuration of the PivotTable report, and Project Leader.

To analyze the department's labor expenditures by project leader, the VP clicks the Client field header in the PivotTable report and drags it off the report. In the Field List dialog box, he clicks the Project Leader field header and drags it to the row area of the PivotTable report, positioning it to the left of the Project field header.

This new PivotTable configuration relates labor cost allocations to each project leader. The report shows that the two projects in question, PJ742 and PJ844, were both assigned to project leader EN17840. With this connection in mind, the VP will talk with the project leader to learn why one project received attention in the early months of the year while another project languished.

As this scenario shows, PivotTable reports can help you dynamically rearrange your Excel data to highlight different aspects of the data. By using PivotTable reports, the VP was able to take a detailed data list of labor costs and, without having to create a new worksheet, rearrange the data list to display the quarterly labor expenditures for each client's projects. This configuration helped the VP discover a pattern — two projects had opposite labor expenditures for the year. But it wasn't until he rearranged the data again to emphasize a project leader's involvement with both projects that an apparent connection was identified.

About the author   Curtis D. Frye is an industry analyst and author of Microsoft Office Excel 2003 Step by Step and several other books from Microsoft Learning.