## 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