Step 5 Creating a New Field to Present the Subtotals

The PivotTable report contains the summary of the Accounts Types, Level 3.

There are more important subtotal levels, such as Gross Income and Gross Profit, as well as additional essential calculations required to analyze the company's business results.

In this step you will learn how to group items from BS, P&L Level3 to create a new Field to present the subtotals of Profit & Loss Level 1, as shown in Figure 13-11.

Chapter 13

E3 Microsoft Excel - Pivot Table TB in Columns!

File Edit View Insert Format Tools Data Window Help

E3 Microsoft Excel - Pivot Table TB in Columns!

File Edit View Insert Format Tools Data Window Help

A

B

D

E

F

1

BS, P&L Level 1

Profit & Logs'1

-

7:

Accout Name

(All)

m

■3

BS, P&L Level 2

(All)

%

4

Cash Flow

(All)

*

D

6 7

Data V

Profit & Loss Level 1 t

BS, PKL Level 3

2003

2002

2002 VS 2003

3

Gross Income

(80,088)

P.700)

7.56% 10.36%

3

Cost of goods sold

(71,388)

10

Gross Income Total

65,060

62,732

2,268

3.43%

11

Operating Expenses

General & Administrate

n

(10,255) (6,550) (2,631.) (1,640)

(3,367) (5,350)

(888) (600) 1,263 (108)

8.66% 3.16°! (47.16)% 6.53%ó

12

Marktzins

13

Research & Development

(3,360)

14

Amortization

(1,532)

15

Operating Expenses Total

(21,136)

(20,803)

(327)

1.55%

16

Other Income (expenses^

Mon Operating Expenses

183 4,032 142

147

36 672 ■242

13.67% 16.67% 170.42%

17

Non Operating Income

3,360

18

Special Gain ( Loss:..y

(100)

13

Othei Income (expenses} Total

4,357

3,407

350

21.80%

20

Income Taxes ¡Income Taxes

(3,560)

(2,334)

(576)

16.1B%

21

Income Taxes Total

(3,560)

(2,384)

(575)

16.13%

22':

Net Income (Lossf foi the vear

44,721

42,406

2,315

5.18%

23

Figure 13-11: PivotTable Report After Adding New Level 1 Group

Field

^ To add new group Field:

1. Copy worksheet 72 — PT Income Statement by clicking the worksheet tab, pressing <Ctrl>, dragging it and then releasing the <Ctrl> key and the mouse.

2. Change the worksheet name to 73 — PivotTable PL Group.

3. Ensure that the PivotTable report has only one Row Field (the BS, P&L Level3 Field), as shown in the figure below (Row Field is on the left side of the Data area in the PivotTable report).

E3 Microsoft Excel - Financial Statements

File Edit View Insert Format Tools Data Window Help

E3 Microsoft Excel - Financial Statements

File Edit View Insert Format Tools Data Window Help

A

B

: » '

o

E

1

□3. P&L Level 1

Profit & Loss g

2:

P&L Level

(All)

"V

.3 .

Accout Name

(All)

W

4

BS P&L Level 2

ÎAII1

b

6

Data

-

7

BS. PSLLevel3

Balance 2003

% of Sales 2003

2002

S

Sales

145,148

145,148

100 00%

131,180

9

Cost of goods sold

(30,088)

-80,088

5518&

(71,388)

1G

Genera! & Administration

(10,255)

-10,255

7.07%

0.367)

11

Marketing

(6,550)

-6,550

4.51%'

(5,950)

12.

Research & Development

p.631)

-2,691

1.85%.

(3.960)

13

Amortization

(1.640)

-1,640

1.13%

(1,532)

14

Non Operating Expenses

183

183

0.13%

147

15

Non Operating income

4,032

4,032

"2.78%'

3,360

16

SpeciahG.ain f Losè.)

142

142

O.TO'ïi

(100)

17

Income Taxes

(3,560)

i3,560

2:45|í:.

(2,38 A)

18

Graml Total

44.721

44,721

42,406

4. Select Sales and Cost of goods sold from the BS, P&L Level 3 Field.

5. Right-click and select Group and Show Detail.

6. Choose Group.

7. Change the text Groupl in cell A7 to Gross Income by typing the new text into the cell.

E2 Microsoft Excel - Financial Statements

File Edit View Insert Format lools Data Window Help

E2 Microsoft Excel - Financial Statements

File Edit View Insert Format lools Data Window Help

■A

B

: m

D

1

BS. P&L Level 1

Profit Ed'srS' ~

ts

Accoüt'^^frie

(All)

ES. P&L Level:?

.(AU)

m

5

Data -r

Riinnincj

6

sBk. s

BS, P&L Level 3 ■«■

2003

Balance 2003

7

SfQU.p1

itedB

145,148

8

Ccit ct qoods sold

9

Total

íK.BBU

'GS,060

»

Öperatinq Expenses?

'General ¿ âlîiî

(10,255)

-10,255

11

Marketing

(8,550)

,6,550

t'ï

Resegfch & Uevb|.jf:rret>o

Hjpfe

«

Anto^ätiön

(1,640)

-1,640

14

Qperatinq Expénsies/Eota

(21,136)

-21,136

»

.Either Incióíóé. Expenses)

Woti 10 p e ratifia'Expé n se.s*

183.

iaa

ä

4ï$É

•4'Ä

17

Special G%i ( Lös^.-i

142

142

M

Öther lncome:(6¥t)ensÉá) Total.

4,367

4,367

ra'

neütme Taxes

Income Taxfes.

•0.560)

-3,560

m

Intjfnë Tsxes Totst-

' ,(3,560)

-3;,560

21

Grand Total

44,721

8. Select additional items from the BS, P&L Level 3 Field and perform the Group action to the group items.

9. Drag the BS, P&L Level3 Field to the Page area (upper corner above the PivotTable report).

The final result should look like this:

E3 Microsoft Excel - Pivot Table TB in Columnsl

File Edit View Insert Format lools Data Window Help

E3 Microsoft Excel - Pivot Table TB in Columnsl

File Edit View Insert Format lools Data Window Help

A

B

e

D

E

1

BS, P&L. Level 1

Profit & Loss

is

Accout Name

ffillf

.3

BS, PSL Level 3

(All)

4

BS. P&L Level 2

(All)

5

Cash Flow

(All)

6

7

Data

8

Piofit & Loss Level 1 m

2003

2002

2002 VS 2003

9

Gross Income

65,080

.(21,136) 4,357 (3,560)

(576)

3.49%' 1.55% 21.80% 16.13%;

10

Operating Expenses

¡20,809) 3,407

11

Oilier Income (expenses)

12

Income Taxes

(2,984)

13

Net Income (Loss! foi the year

44,721

42,406

2,315

5.18%

14

0 0

Post a comment