why use excel pivot tables
DESCRIPTION
In Excel, why use pivot tables over convential tables with formulas.TRANSCRIPT
Why use Pivot Tables for tracking expenses for a budget
1) Track expenses in an efficient manner2) Ability to spot alarming trends in expenses3) Identify Year-To-Date expenses for types and $
paid to vendors4) Other budget analysis functions pivot tables can do
1.1) Track expenses in an efficient mannerThe below tables both show $ expenses for Types( Car Related etc ) for the years 2009, 2010 and 2011. How they function is quite different. In the Non-Pivot Table(NPT), summation formulas have to used to add individual rows and columns. 2009’s total, for example, is Sum( B6:B14). If cell B13 is typed instead the total will be wrong. If another Type is added below Utilities by cutting and pasting, the Sum formula will not adjust for it. The total will still be B6:B14. The same type of error can occur for any row or column. The NPT, furthermore, requires manual addition or deletion of a row/column along with entering any new formulas. The Pivot Table, in contrast, adjusts it’s internal formulas to calculate totals. The Grand Totals and cross total can NOT be wrong. This adjusting also applies to other type of calculations such as %s. The Pivot Table, furthermore, does not need manual row or column changes. More about this on the next slide. The user just has to click “Refresh” for the Pivot table to apply adjustments .
Non Pivot Table P I v o t T a b l e
Row/Col A B C D E Amt $ Year
2009 2010 2011 Type 2009 2010 2011 Grand Total
Type $ Amt $ Amt $ Amt Total Car Related 1,394 1,735 1,689 4,818
6 Car Related 1,394 1,735 1,689 4,818 Fun 274 199 244 717
7 Fun 274 199 244 717 General 310 373 329 1,012
8 General 310 373 329 1,012 Grocery 1,188 1,257 1,088 3,533
9 Grocery 1,188 1,257 1,088 3,533 Home Repair 253 108 141 502
10 Home Repair 253 108 141 502 Insurance 294 300 80 674
11 Insurance 294 300 80 674 Medical 188 70 9 267
12 Medical 188 70 9 267 Tax 1,575 1,615 1,399 4,589
13 Tax 1,575 1,615 1,399 4,589 Utilities 1,046 1,002 873 2,921
14 Utilities 1,046 1,002 873 2,921 Grand Total 6,522 6,659 5,852 19,033
Grand Total 6,522 6,659 5,852 19,033
1.2) Track expenses in an efficient mannerThe information table shown is organized by Year, Month, Type, Paid-To and Amount categories. The information being together in the same table is needed for the Pivot table to calculate the amount totals and percentages. Even though the Paid-To “Sunoco “and “Threshold …” rows are not next to each other, the pivot table will find them for Type “Car Related” calculations. There is not a need to use the function LookUp to find them or a Sum formula to add them. The pivot table, furthermore, allows row and column selection. If Type “Fun “is not wanted then the pivot table will not show a row for it and adjust all total and percentage calculations. Which Types desired for the pivot table are selected by a drop down list. ( The prompt ) There is no need to either highlight and delete the “Fun “ row or change any formulas.
P Year Values
I 2011 2010
V Type $ Amt Amt % $ Amt Amt %
O Car Related 1,689 28% 1,735 25%
T Fun 244 4% 198 3%
General 352 6% 379 6%
T Grocery 1,095 18% 1,267 18%
A Home Repair 141 2% 108 2%
B Insurance 174 3% 478 7%
L Medical 9 0% 67 1%
E Tax 1,399 23% 1,615 24%
Utilities 873 15% 1,002 15%
Grand Total 5,976 100% 6,849 100%
Year Month Type Paid To Amount
I 2011 10 Utilities Citizens Communication 3
N 2011 10 Utilities Dish Network 18
F 2011 10 Utilities Spot Talk 2
O 2011 10 Utilities PeoplePC 6
R 2011 10 Utilities R G & E 20
M 2011 10 Utilities Village of Asgard (Electric) 10
A 2011 10 Utilities Village of Asgard (Water) 15
T 2011 10 Car Related Threshold Credit Union 80
I 2011 10 Tax Asgard School Tax 265
O 2011 10 General Pet Friendly 10
N 2011 10 Car Related Sunoco 20
2011 10 Grocery ALDI 43
2011 10 Grocery Tops 22
2.1) Ability to spot alarming trends in expenses
Comparing YTD 2009 and 2010 expenses (blue line), expenses decreased from $5,384 to $5,296. There was a $88 improvement from Jan - Oct. Comparing 2010 and 2011 YTD expenses there was an $486 increase in expenses. If this YTD difference(red line) continues then 2011 expenses will likely exceed 2010 expenses. Why improvement for 2009-2010 and the opposite for 2010-2011 ?
YTD Expenses Var Jan-Oct Values Year Month Amt $ Amt Diff
2009 5,384 2010 5,296 (88)2011 5,852 468
2009 2010 2011
Amt $ 5384 5296 5852
Amt Diff NaN -88 468
-500
500
1,500
2,500
3,500
4,500
5,500
6,500
YTD Expenses Var Jan-Oct
$ Am
ount
2.2) Ability to spot alarming trends in expenses
The Accumulative YTD Pivot Table further shows the $486 increase from slide 1.2.1 being a concern. 2011 accumulated expenses(Green line) exceed those of 2010(Red line) by the end of June. 2011 expenses stay above 2010 expenses through Oct. If 2010 expenses are considered a budget, then 2011 expenses are OVER the budget. 2010 expenses, in contrast, stay close to the 2009 expenses (Blue line) for the entire 10 months and within a 2009 budget. What are the specific expenses that caused the 2011 situation ?
Accumulative YTD Amt $ Year Month 2009 2010 2011
1 488 391 3762 1,488 1,258 1,139
3 2,126 1,763 1,7824 2,470 2,441 2,2745 2,846 2,813 2,8316 3,455 3,310 3,3957 3,779 3,919 4,2378 4,115 4,189 4,7339 4,677 4,733 5,338
10 5,384 5,296 5,852
1 2 3 4 5 6 7 8 9 10
2009 488 1488 2126 2470 2846 3455 3779 4115 4677 5384
2010 391 1258 1763 2441 2813 3310 3919 4189 4733 5296
2011 376 1139 1782 2274 2831 3395 4237 4733 5338 5852
500
1,500
2,500
3,500
4,500
5,500
Accumulative YTD
$ Am
oun
t
2.3) Ability to spot alarming trends in expenses
1 2 3 4 5 6 7 8 9 10 11 12
2009 488 1000 638 344 376 609 324 336 562 707 559 579
2010 391 867 505 678 372 497 609 270 544 563 719 644
2011 376 763 643 492 557 564 842 496 605 514 NaN NaN
100
300
500
700
900
1,100
Year Expenses by Month
$ Am
ount
The Accumulated Expenses slide showed clearly that by July’s end expenses are above those of 2010. This slide further illustrates that 2011 July and August expenses are above those of 2010. What are the specific expenses that caused the 2011 situation ?
The type expenses that caused the 2011 situation are shown by the YTD Expenses pivot tables. The green bars show that Types “Car Related”($205), “General”($167) and “Fun”($76) had the most increases from 2010 to 2011. Since “Fun” is the most controllable, the question becomes what vendors expenses caused the other spikes.
3.1) Year-To-Date expenses for types and who $ paid-to
Car Re-
lated
Fun Gen-eral
Gro-cery
Home Re-pair
In-suran
ce
Med-ical
Tax Utili-ties
2009
1182 269 251 994 253 74 177 1325 859
2010
1484 168 162 1039 108 80 61 1355 839
2011
1689 244 329 1088 141 80 9 1399 873
100
300
500
700
900
1,100
1,300
1,500
1,700
YTD Expenses by Type
$ Am
ount
Car Re-
lated
Fun Gen-eral
Gro-cery
Home Re-pair
In-suran
ce
Med-ical
Tax Utili-ties
2009
NaN NaN NaN NaN NaN NaN NaN NaN NaN
2010
302 -101 -89 45 -145 6 -116 30 -20
2011
205 76 167 49 33 0 -52 44 34
-150
-50
50
150
250
350
YTD Expenses by Type Variances
$ Am
ount
3.2) Year-To-Date expenses for types and who $ paid-to
Car Re-lated
AAA
Car Re-lated
Bob Kaiser
Car Re-lated
Kwik
Fill
Car Re-lated
Mobil
Car Re-lated
NYS -
DMV
Car Re-lated
Sunoco
Car Re-lated
Thres-hold
Sav-ings Ban
k
General AARP
General ABVI Goodwill
General As-gard
Dog Grommer
General Barnes & Noble
General Ben Franklin
General Canel
Pet Sho
p
General CVS
General JC
Penny
General Knights of Columbus
General Koh
ls
General Lisa's
Dance
General Lowe's
General Macy's
General Of-fice max
General Pet Friendly
General Petsmart
General R.I.T.
General Sam's Club
General Town of
Brighton
General US Pos
t Of-fice
General Wal Mart
General As-gard
Veteri-nar-ian
2009
50 144 50 25 NaN
113 800 5 2 30 NaN
NaN
11 8 38 NaN
30 NaN
NaN
NaN
NaN
NaN
NaN
8 15 29 2 23 50
2010
50 415 54 NaN
17 148 800 NaN
NaN
30 6 2 10 6 NaN
8 NaN
20 NaN
20 21 7 3 2 NaN
NaN
3 NaN
24
2011
52 644 51 4 NaN
138 800 NaN
NaN
15 NaN
NaN
NaN
NaN
19 8 NaN
NaN
21 10 2 39 5 NaN
15 NaN
3 NaN
192
50150250350450550650750850
YTD Expenses by Type and Who $ Paid-To
$ A
mo
un
t
The two vendors that show significant expense increases for 2010 to 2011(Green bar) are Bob Kaiser($229) and Asgard Veterinarian($168). The bars show that expenses for the other vendors varied little or are under $50. Notice that Threshold Savings Bank’s expenses are quite high. The bank expenses are due to monthly car payments and expected. Whatever caused the Bob Kaiser and Asgard Veterinarian expenses are NOT normal compared to the other vendors. The next question is what the $ was spent for. They were likely unavoidable and need to be identified. This leaves the question could other expenses be adjusted for finishing the year on budget.
3.3) Year-To-Date expenses for types and who $ paid-to
Sam's ClubKwik Fill
Pleasure LanesThor Tax Service
AAACitizens Communication
PeoplePCState Farm Cars
Village of Asgard (Electric)Village of Asgard (Water)
Lowe'sSunoco
Village of AsgardDish Network
Asgard VetinarianTops
R G & EAsgard School Tax
Bob KaiserTown and County Tax Bill
Threshold Credit Union (Chev)ALDI
0 100 200 300 400 500 600 700 800 900
5151515252
6267
80849299
138140
178192196
368530
644677
800847
Top 20 Expenses for Jan-Oct 2011
Total
Besides the previous YTD slides, another way to spot alarming expenses is by what are the 2011 top expenses. Expenses such as ALDI, RG&E, Tops, and Threshold(car payment) are usual monthly expenses and should be high for 10 months. The Bob Kaiser and Asgard Vetinarian expenses, however, indicate unusual activity. One is greater than the school tax and the other almost as much as Top groceries.
4.0) Other budget analysis functions pivot tables can do
$ Amt Year Month 2011 2011 TotalType Paid To 1 2 3 4 5 6 7 8 9 10 Car Related 93 151 124 103 111 101 560 249 97 100 1,689Fun 25 44 31 26 34 25 38 21 244General 5 61 169 13 18 38 15 10 329Grocery ALDI 91 83 85 103 98 98 70 95 81 43 847 Sam's Club 7 3 8 4 6 8 36 Tops 20 25 14 22 19 35 18 9 21 22 205Home Repair 21 15 27 77 1 141Insurance State Farm Cars 80 80Medical 4 5 9Tax 337 222 170 140 265 265 1,399Utilities Citizens Communication 6 6 6 6 7 7 7 6 8 3 62 Dish Network 16 18 18 18 18 18 18 18 18 18 178 PeoplePC 4 6 6 6 6 6 15 6 6 6 67 R G & E 60 70 47 50 31 21 25 20 24 20 368 Spot Talk 2 3 1 3 3 1 2 3 2 2 22 Village of Asgard (Electric) 10 9 8 8 7 10 3 8 11 10 84 Village of Asgard (Water) 23 27 27 15 92Grand Total 376 763 643 492 557 564 842 496 605 514 5,852
The above pivot table shows expenses for Types and Paid-To Vendors by Month. The pivot table also shows rows and column totals. Which Types, Vendors and Months are completely selective. In this table selection, the vendor detail is shown for Groceries and Utilities from Jan-Oct 2010. Any of the Types could be expanded for detail or collapsed not to show detail. The months, furthermore, can be confined to March, June and September if only quarter trends are desired. In summary, the pivot table can isolate which month(s) a type and vendor expense occurred. The pivot table’s column/row totals can also verify totals shown on the previous chart.