Data for a more complex pivot table

Figure 17-3 shows part of a worksheet database. This table contains 15,840 rows containing hierarchical budget data for a corporation. There are 5 divisions, and each division contains 11 departments. Each department has four budget categories, and each budget category contains several budget items. Budgeted and actual amounts are included for each of the 12 months.

This workbook is available on the companion CD-ROM.

|Ü budget.xls

HBE3I

1

A

B

C

D

F

F

G

H -

DIVISION

DEPARTMENT

CATEGORY

ITEM

MONTH

BUDGET

ACTUAL

=!

2

IM. America

Data Processing

Compensât

on

Salaries

Jan

2583

3165

3

IM. America

Data Processing

Compensât

on

Benefits

Jar

4436

2980

4

IM. America

Data Processing

Compensât

on

Bonuses

Jail

3768

3029

5

IM. America

Data Processing

Compensât

on

Commissions

Jar

3133

2G15

6

IM. America

Data Processing

Compensât

on

Payroll Taxes

Jar

3559

3770

7

N. America

Data Processing

Compensât

on

Training

Jar

3099

3559

8

IM. America

Data Processing

Compensât

on

Conferences

Jar

2931

3199

9

IM. America

Data Processing

Compensati

ion

Entertainment

Jar

2632

2633

10

N. America

Data Processing

Fac

litv

Rent

Jar

2033

2508

11

IM. America

Data Processing

Fac

lity

Lease

Jar

3450

2631

12

IM. America

Data Processing

Fac

lity

Utilities

Jar

4111

3098

13

IM. America

Data Processing

Fac

lity

Maintenance

Jar

3D70

2G70

14

IM. America

Data Processing

Fac

lity

Telephone

Jar

3827

4329

15

IM. America

Data Processing

Fac

lity

Other

Jar

3043

3322

16

IM. America

Data Processing

Supplies &. Services

General Office

Jar

2642

3218

17

IM. America

Data Processing

Supplies & Services

Computer Supplies

Jar

3Ü52

4098

18

IM. America

Data Processing

Supplies & Services

Books &. Subs

Jar

4346

3361

19

IM. America

Data Processing

Supplies &. Services

Outside Services

Jar

2069

3717

20

IM. America

Data Processing

Supplies &. Services

Other

Jar

3328

3116

21

IM. America

Data Processing

Equipment

Computer Hardware

Jar

3088

2728

22

IM. America

Data Processing

Equipment

Software

Jar

4226

2675

23

N. America

Data Processing

Equipment

Photocopiers

Jar

3780

3514

24

IM. America

Data Processing

Equipment

Telecommunications

Jar

3093

3664

25

IM. America

Data Processing

Equipment

Other

Jar

2051

4380

26

IM. America

Human Resources

Compensation

Salaries

Jar

3004

3501

27

IM. America

Human Resources

Compensation

Benefits

Jar

2059

4493

28 H

IM. America

Human Resources

Compensation

Bonuses

Jar

3020

2676

► H \Sheetty

M I

Figure 17-3: The data in this workbook will be summarized in a pivot table.

Figure 17-4 shows a pivot table created from the data. Notice that the pivot table contains a calculated field named Variance, plus four calculated items (Q1, Q2, Q3, and Q4), which calculate quarterly totals.

Was this article helpful?

0 0

Post a comment