The PivotTable feature in Microsoft Office Excel 2003 is an excellent tool that can help you organize, summarize, and analyze Excel data. By using PivotTable reports, you can easily create new views of your data without having to create separate worksheets.

Read on to learn how a controller at a manufacturing company used PivotTable reports to evaluate the potential costs of reclassifying contract workers to employee status.

Case study

During a regulatory audit of Contoso, Ltd., the state workers' compensation agency found that Contoso workers who were currently categorized as contractors should be recategorized as employees. Reclassifying workers is a difficult and costly process. Before acting on the audit recommendations, the Contoso controller wants to assess the company's costs for paying workers' compensation insurance, unemployment taxes, Social Security and Medicare taxes, and health insurance taxes for those workers who are to be reclassified from contractor to employee status.

The controller turns to her Excel workbook of employee and contractor information. The controller's workbook contains two worksheets:

• Database   The Database worksheet includes payroll data. A portion of this worksheet contains the following data:
  • Employee name
  • Gross pay for the year
  • Pay range
  • Position
  • Employment status: contractor or employee
  • Full-time or part-time worker

     Note   Your Excel workbook of employee and contractor information can contain much more data. PivotTable reports can be used by you to extract only the data needed for the targeted analysis.



• Formulas   The Formulas worksheet calculates the following employee costs:
  • Workers' compensation (WC) rate   The WC rate column calculates the cost of workers' compensation by using a sample 3% rate for janitorial workers. A 2% rate is used to calculate the WC rate for all other workers because they are in lower-risk job categories.
  • State unemployment tax act (SUTA) cost   The company's SUTA cost is calculated by using a sample rate of 5.4% for wages up to $7,000 per individual.
  • Federal unemployment tax act (FUTA) cost   The company's FUTA cost is calculated by using a sample rate of 0.8% for wages up to $7,000 per individual.
  • Federal Insurance Contributions Act (FICA) taxes and health insurance tax (HIT)   FICA taxes (Social Security and Medicare taxes) are paid on wages up to $90,000, and HIT is paid on all wages. The company's FICA and HIT costs are calculated by using a sample rate of 6.2% FICA + 1.45% HIT for wages up to $90,000, and 1.45% for wages over $90,000.

     Note    Employment and benefit rules and tax rates vary from state to state. Your state may have different tax rates than those used by Contoso, Ltd.

  

To analyze the company's approximate cost for reclassifying contract workers to employee status, the controller uses these steps to create a PivotTable report in her Excel workbook.

Step 1: Start the PivotTable and PivotChart Wizard

1. Select any cell on the employment spreadsheet. On the Data menu, click PivotTable and PivotChart Report.
2. On the first page of the PivotTable and PivotChart Wizard, under Where is the data that you want to analyze?, click Microsoft Office Excel list or database.
3. Under What kind of report do you want to create?, click PivotTable and then click Finish. A blank PivotTable report appears on a new worksheet.
4. Click anywhere on the PivotTable report. The PivotTable Field List window appears.

Step 2: Arrange the PivotTable report

The controller chooses the type of data to include on the PivotTable report by transferring field headings in the PivotTable Field List window to the appropriate places on the PivotTable report.

1. In the PivotTable Field List window, select Row Area in the drop-down list at the bottom of the window.
2. Transfer the field headings in the following order:
   • Click Status, and then click Add To.
   • Click Position, and then click Add To.
   • Click Pay Range, and then click Add To.
3. In the PivotTable Field List window, select Data Area in the drop-down list at the bottom of the window.
4. Transfer the field headings in the following order:
   • Click Pay Range, and then click Add To.
   • Click Pay, and then click Add To.
5. To filter the PivotTable report so that it shows only information for the company's contractors, click the arrow on the Status field button, and select Contractors.

Step 3: Add formulas to the PivotTable report

The controller followed these steps to incorporate the formulas on the Database worksheet into the PivotTable report.

1. On the Formulas worksheet, copy the column headings and formulas. In this example, the controller copied cells H1:M50.
2. On the PivotTable report, paste the data so that it doesn't cover any of the contract employee data.
3. On the PivotTable report, copy the formulas in cells H4:M4. In each row containing Count of Pay Range, paste the formulas, starting in column H.

An abbreviated portion of the PivotTable report (with some columns hidden) follows. The last column on the PivotTable report calculates that Contoso, Ltd. would have to pay more than $80,000 in employment and benefit taxes if the company reclassifies its contract workers to employee status.

Conclusion

Using the powerful and flexible PivotTable feature, the Contoso controller was able to sort her company's employee data to separate contract workers from employees. She was then able to calculate the cost of applying employment and benefit taxes to the compensation of contract workers. Armed with these results, the controller can now help the company make smart decisions when responding to the audit recommendations.

Now that you understand the power of PivotTable reports, you can adapt the steps taken by the controller to extract company employment data for a number of purposes, including:

• Analyzing total wages for various employee types
• Calculating departmental payroll totals
• Identifying workers by job description to compute the cost of correcting or updating a workers' compensation insurance rate
• Sorting workers by location when computing the cost of workers' compensation compliance on a state-by-state basis