Microsoft Office Excel

Exercise 1
Name Science Math English Total Average Percentage
Daniel 89 87 79 255 85 85 %
Maureen 67 81 69 217 72.33 72 %
John 78 86 86 250 83.33 83 %
Nihar 86 89 86 261 87 87 %
Somnath 75 87 64 226 75.33 75 %
Priya 86 76 53 215 71.66 72 %
Spoorthi 68 61 72 201 67 67 %
Exercise 2
Name Kannada English Hindi Total Average Percentage
Anjali 75 87 64 226 75.33 75 %
Naima 87 68 57 212 70.66 71 %
Nikhil 68 64 67 199 66.33 66 %
Talan 68 69 64 201 67 67 %
Shreya 76 49 58 183 61 61 %
Pragathi 84 69 71 224 74.66 75 %
Anu 85 96 55 236 78.66 79 %
Exercise 3
Name Math Science Social Total Average Percentage
Sharma 78 59 68 205 68.33 68 %
Gupta 68 94 75 237 79 79 %
Malhotra 49 58 47 154 51.33 51 %
Bhatnagar 67 73 79 219 73 73 %
Saxena 75 69 48 192 64 64 %
Haritha 79 64 73 216 72 72 %
Ashwini 86 74 69 229 76.33 76 %
Exercise 4
Name Kannada English Hindi Total Average Percentage
Benjamin 76 84 79 239 79.66 80 %
Augustine 97 67 84 248 82.66 83 %
David 59 68 71 198 66 66 %
Edward 68 76 59 203 67.66 68 %
Charles 49 67 58 174 58 58 %
Elbert 76 84 52 212 70.66 71 %
Kelly 68 85 73 226 75.33 75 %
Exercise 5
Name Test 1 Test 2 Test 3 Total Average Percentage
Frank 72 67 75 214 71.33 71 %
Pace 68 73 85 226 75.33 75 %
Getty 67 58 67 192 64 64 %
Robert 58 67 49 174 58 58 %
Amy 55 59 57 171 57 57 %
John 72 85 64 221 73.66 74 %
Alan 81 73 72 226 75.33 75 %
Exercise 6
Worldwide Sporting Goods
Sales January February March Total Sales Expenses Net Sales Avg Sales
Smith 1,819.21 1,766.55 1,942.88 5,528.64 1,241.00 4,287.64 1,842.88

Page 1
 Microsoft Office Excel
Brown 1,704.38 1,809.01 1,650.28 5,163.67 1,165.00 3,998.67 1,721.22
Wallace 2,009.69 2,195.19 2,159.29 6,364.17 1,650.00 4,714.17 2,121.39
Adams 1,948.44 1,725.56 1,870.26 5,544.26 1,345.00 4,199.26 1,848.09
Total 7,481.72 7,496.31 7,622.71 22,600.74 5,401.00 17,199.74 7,533.58

Exercise 7
Worldwide Sporting Goods – Customer Sales – Last Year
Company Status Qtr 1 Qtr 2 Qtr 3 Qtr 4 Total Average
Sports City 2 15,568 16,523 17,658 18,952 68,701 17,175
Athlete’s Dream 3 21,356 22,547 24,598 23,691 92,192 23,048
Sports Emporium 1 22,571 23,681 22,698 24,995 93,945 23,486
Sports World 4 14,568 15,423 16,532 15,248 61,771 15,443
Tennis joint 3 10,652 9,865 10,156 11,564 42,237 10,559
Athlete’s World 2 20,364 18,596 19,532 20,348 78,840 19,710
Sportsman’s Den 1 21,457 20,567 22,365 22,056 86,445 21,611
World of Sports 3 15,862 16,538 17,568 18,563 68,531 17,133
Specialty Sports 4 18,745 19,865 20,361 20,455 79,426 19,857

Total 161,143 163,605 171,468 175,872 672,088 168,022

Average 17,905 18,178 19,052 19,541 74,676 18,669

Exercise 8
Public School
Name Test 1 Test 2 Test 3 Test 4 Test 5 Test 6 Total Average Per Min Max Result
Veronica 47 75 68 49 57 67 363 60.5 61 % 47 75 Pass
Betty 59 24 67 32 59 62 321 53.5 54 % 32 67 Fail
Archie 68 75 71 49 53 64 380 63.33 63 % 49 75 Pass
Marge 59 64 28 53 57 41 302 50.33 50 % 28 64 Fail
Reggie 31 54 62 55 47 53 302 50.33 50 % 31 62 Fail

Exercise 9
Sl.No Gender Age Ethnicity Planned major Category College
1 male 18 white business business business
2 male 18 white electrical engineering engineering engineering
3 male 19 white biology natural science arts and sciences

Page 2
 Microsoft Office Excel
4 male 18 white math math arts and sciences
5 male 19 white computer science computer science arts and sciences
6 male 19 white computer science computer science arts and sciences
7 male 20 white graphic design fine and performing art arts and sciences
8 male 19 hispanic secondary education education education
9 male 20 african-american business business business
10 male 19 african-american political science social science arts and sciences
11 female 22 White business business business
12 female 19 White french humanities arts and sciences
13 female 18 White anthropology social science arts and sciences
14 female 19 White english humanities arts and sciences
15 female 20 White elementary education education education
16 female 21 White computer science computer science arts and sciences
17 female 19 african-american secondary education education education
early childhood
18 female 21 asian education education
education
19 female 19 hispanic english humanities arts and sciences
20 female 22 african-american political science social science arts and sciences
21 male 19 white theatre fine and performing art arts and sciences
22 male 19 hispanic communications humanities arts and sciences

Exercise 10
Wages Inventory Supplies Sales Profit
Apparel 1,000 5,000 200 7,500 1,300
Hardware 2,200 10,000 200 15,000 2,600
Toys 500 3,500 200 7,500 3,300
Furniture 1,000 300 200 7,025 5,525
Electronics 2,000 9,000 200 22,000 10,800
Food 1,500 4,750 350 9,675 3,075
Totals 8,200 32,550 1,350 68,700 26,600
Exercise 11
Cards and gifts
2008 sales
1st Quarter 2nd Quarter 3rd Quarter 4th Quarter Average 2008 Totals
General Cards 22,188 21,099 22,253 28,231 23,443 93,771
Seasonal Cards 16,277 17,377 17,760 19,819 17,808 71,233

Page 3
 Microsoft Office Excel
Gift Wrap 5,566 5,445 6,125 6,223 5,840 23,359
Stationery 1,289 1,495 1,209 2,188 1,545 6,181
General Gifts 16,926 17,351 13,796 20,459 17,133 68,532
Seasonal Gifts 11,223 12,225 12,654 13,665 12,442 49,767

Maximum 22,188 21,099 22,253 28,231 23,443 93,771

Minimum 1,289 1,495 1,209 2,188 1,545 6,181
Average 12,245 12,499 12,300 15,098 13,035 52,141
Total 73,469 74,992 73,797 90,585 78,211 312,843
Exercise 12
Acme Incorporated Sales Report
Northern District Southern District Western District Eastern District Total

Sales 1 56,789 45,784 78,327 56,783 237,683

Sales 2 76,489 62,646 73,456 67,328 279,919
Sales 3 62,543 54,786 71,980 54,987 244,296
Sales 4 71,239 65,900 83,265 69,736 290,140

Maximum 76,489 65,900 83,265 69,736 290,140

Minimum 56,789 45,784 71,980 54,987 237,683
Average 66,765 57,279 76,757 62,209 263,010
Total 267,060 229,116 307,028 248,834 1,052,038
Exercise 13
Eat Cake Sales Forecast
Month Quarters Year Channel Forecast
NA NA 2005 Retail 945,734
NA NA 2005 Partner 876,098
NA NA 2005 Direct Sales 345,876
NA NA 2005 Catering 234,587
NA NA 2005 Internet 456,098
NA NA 2005 Mall stores 678,435
Exercise 14
Name First Name Genre Income Situation Child
Janjuah Bob Man 20000 Relationship 0
Sara Sumit Man 80000 Married 1
Champney Robert Man 67000 Married 0
Jonas Lee Man 35000 Relationship 0
Samen Michael Man 77000 Single 0

Page 4
 Microsoft Office Excel
Satake Kaori Man 70000 Married 3
Leon Fernando Man 23000 Single 3
Byerly Kimberly Woman 97000 Relationship 1
Warner Norris Man 26000 Divorced 3
Gaudefroy Marie Woman 89000 Relationship 0
Chap Mathew Woman 77000 Single 2
Simpson Aubrey Man 34000 Divorced 0
Fordyce David Man 61000 Relationship 2
Tortelli Olavo Woman 27000 Single 0
Adcock David Woman 72000 Divorced 3
Contat Thibault Man 85000 Relationship 1
Zizzari Stephen Woman 84000 Single 3
Freyer Jean-Luc Man 50000 Single 2
Jones David Man 31000 Single 0
Exercise 15
Weekly Time Card Summary for Hourly Employees
Employee ID Last Name First Name Total Hours Hourly Pay Total Pay
1848 Gul Doug 8 8.55 68.40
1863 Guzik Sue 4 9.55 38.20
1878 Madi Chris 6 10.00 60.00
1913 Haff Richard 7 11.25 78.75
2156 Bolte Ellen 8 11.00 88.00
3788 Miller Cindy 8 15.00 120.00
4110 Miller Jan 6 13.25 79.50
4564 Butler Teresa 9 12.50 112.50
4793 Abels Ginger 5 11.50 57.50
5078 Causey Tim 4 9.50 38.00
5106 Chambers Julie 6 20.00 120.00
5128 Chambers Les 8 16.00 128.00
5145 Abell Bruce 8 14.00 112.00
5714 Miller Kevin 10 13.00 130.00
6566 Miller Rudeana 8 9.25 74.00
6569 Eads Linda 6 10.25 61.50
7708 Russell Susan 4 11.75 47.00

Page 5
 Microsoft Office Excel
Exercise 16
Month Sales Rep Region Contacts Sales Annualized
Jan Bob North 58 283,800 3,689,400
Jan Frank North 35 507,200 6,086,400
Jan Paul South 25 107,600 1,291,200
Jan Randy South 47 391,600 4,699,200
Jan Mary South 39 226,700 2,720,400
Feb Bob North 44 558,400 6,700,800
Feb Jill North 46 350,400 4,204,800
Feb Frank North 74 411,800 4,941,600
Feb Paul South 29 154,200 1,850,400
Feb Randy South 45 258,000 3,096,000
Feb Mary South 52 233,800 2,805,600
Mar Bob North 30 353,100 4,237,200
Mar Jill North 44 532,100 6,385,200
Mar Frank North 57 258,400 3,100,800
Mar Paul South 13 286,000 3,432,000
Mar Randy South 14 162,200 1,946,400
Exercise 17
Year Temp (avg) Temp (max) Temp (min) # of Readings Rainfall (in) Climate Observer
1980 28 32 10 100 110 Tropical George
1981 25 37 -5 100 2 Desert Jim
1982 -3 10 -32 100 4 Arctic Janet
1983 18 28 -15 100 56 Temperate Mike
1984 25 38 13 100 98 Tropical Bill
1985 28 32 10 100 110 Tropical Bill
1986 25 37 -5 100 2 Desert Bill
1987 -3 10 -32 100 4 Arctic Jim
1988 18 28 -15 100 56 Temperate Janet
1989 25 38 13 100 98 Tropical Bill
1990 28 32 10 100 110 Tropical Janet
1991 25 37 -5 100 2 Desert Jim
1992 -3 10 -32 100 4 Arctic Jim
1993 18 28 -15 100 56 Temperate George

Page 6
 Microsoft Office Excel
1994 25 38 13 100 98 Tropical George
1995 28 32 10 100 110 Tropical Bill
1996 25 38 13 100 98 Tropical Janet
1997 28 32 10 100 110 Tropical Bill
1998 25 37 -5 100 2 Desert Bill
1999 -3 10 -32 100 4 Arctic Jim
2000 18 28 -15 100 56 Temperate Janet
2001 25 38 13 100 98 Tropical Bill
2002 28 32 10 100 110 Tropical Janet
2003 25 37 -5 100 2 Desert Jim
2004 -3 10 -32 100 4 Arctic Jim
2005 18 28 -15 100 56 Temperate George
2006 25 38 13 100 98 Tropical George
Exercise 18
Eastern 1992
Quarters 1 Quarters 2 Quarters 3 Quarters 4
Golf 5,000 2,000 1,500 2,000
Safari 9,000 6,000 4,000 5,000
Tennis 1,500 500 600 1,500
Total Sales 15,500 8,500 6,100 8,500

Western 1992
Quarters 1 Quarters 2 Quarters 3 Quarters 4
Windsurfing 1,800 5,000 6,500 1,750
Golf 3,500 2,500 6,430 4,590
Tennis 6,000 3,200 4,070 5,000
Total Sales 11,300 10,700 17,000 11,340

Eastern 1993
Quarters 1 Quarters 2 Quarters 3 Quarters 4
Golf 5,500 1,500 1,400 2,500
Safari 10,000 6,500 4,400 4,500
Tennis 1,000 800 550 1,000
Total Sales 16,500 8,800 6,350 8,000

Page 7
 Microsoft Office Excel
Western 1993
Quarters 1 Quarters 2 Quarters 3 Quarters 4
Windsurfing 1,850 4,000 5,500 1,550
Golf 1,500 2,500 4,075 2,500
Tennis 6,500 2,000 4,590 5,055
Total Sales 9,850 8,500 14,165 9,105

Grand Total Sales 53,150 36,500 43,615 36,945

Exercise 19
Item Category Item Type Item# Current Cost Inventory Final Cost
Flour Pastry 4 7 2 14
Flour Whole Wheat 5 4.2 3 12.6
Flour WW Pastry 14 7 7 49
Flour Whole Wheat 15 4.2 4 16.8
Flour Pastry 24 7 5 35
Icing Butter cream 6 1.75 3 5.25
Icing Royal 7 2.25 5 11.25
Icing Fondant 8 3.5 4 14
Icing Blue Butter cream 16 1.75 15 26.25
Icing Blue Royal 17 2.25 8 18
Icing Blue Fondant 18 3.5 6 21
Mix Chocolate 1 5.25 4 21
Mix Vanilla 2 3 1 3
Mix Marble 3 4.25 2 8.5
Mix Dutch Chocolate 11 7.25 3 21.75
Mix Royal Vanilla 12 4 15 60
Mix Pound 13 5.25 11 57.75
Mix French Chocolate 21 6.25 10 62.5
Mix French Vanilla 22 4 13 52
Mix Marble 23 5.25 2 10.5
Sprinkles Sugar 9 1.05 22 23.1
Sprinkles Sugar Free 19 1.05 16 16.8
Toppings Sugar 10 1.3 19 24.7

Page 8
 Microsoft Office Excel
Toppings Sugar Free 20 1.3 4 5.2

Exercise 20
Fri/Oct/05 Sat/Oct/05 Sun/Oct/05 Mon/Oct/05 Tue/Oct/05 Wed/Oct/05 Thu/Oct/05
100 50 1000 1 -10 65 3
250 51 999 7 -15 66 9
400 52 998 13 -20 67 15
550 53 997 19 -25 68 21
700 54 996 25 -30 69 27
850 55 995 31 -35 70 33
1000 56 994 37 -40 71 39
1150 57 993 43 -45 72 45
1300 58 992 49 -50 73 51
1450 59 991 55 -55 74 57
1600 60 990 61 -60 75 63
1750 61 989 67 -65 76 69
1900 62 988 73 -70 77 75
2050 63 987 79 -75 78 81
2200 64 986 85 -80 79 87
2350 65 985 91 -85 80 93
2500 66 984 97 -90 81 99
2650 67 983 103 -95 82 105
2800 68 982 109 -100 83 111
2950 69 981 115 -105 84 117
3100 70 980 121 -110 85 123
3250 71 979 127 -115 86 129
3400 72 978 133 -120 87 135
3550 73 977 139 -125 88 141
3700 74 976 145 -130 89 147

Exercise 21
Admission No. Last Name First Name Exam 1 Exam 2 Exam 3 Total

000606 Altiveros Melissa 75 72 66 213

000943 Colburn Mary 92 95 91 278

Page 9
 Microsoft Office Excel
000983 Davis Michael 44 52 73 169
001326 Davis Kelly 93 92 86 271
001572 Douglas Theresa 88 74 69 231
002563 Dwyer Helen 92 91 88 271
002637 Gott Michelle 90 77 85 252
002679 Hardy Russell 78 76 79 233
002773 Hobson Jen 98 92 92 282
002779 Holt Nicholas 91 89 83 263
002981 Jackson Scott 95 95 91 281
003789 Jackson Kenneth 73 57 68 198
003874 Lopez Elaine 65 78 71 214
004662 Meese Erin 82 75 72 229
004875 Mittelstadt Stephanie 72 75 80 227
004919 Oestreich Jennifer 71 55 76 202
009690 Steele Kylie 91 72 80 243
009873 Williamson Linda 76 69 75 220

Exercise 22
PF Statement for the month of July
Sl. Wash Special
Particulars Basic VDA HRA CONV Medical Others Total
No. Allowance Allowance
1 H.R. & Admin 50000 1001 20000 800 1250 600 26349 0 100000
2 Security 30000 1001 0 800 1250 0 26949 0 60000
3 Accounts 5000 1001 2000 800 0 600 599 0 10000
4 Maintenance 3000 1001 400 0 0 600 999 0 6000
5 Drivers 6000 1001 2150 800 1250 0 799 0 12000
6 Bouncer 3250 1001 600 250 0 600 799 0 6500
7 F&B Service 3500 1001 600 500 0 600 799 0 7000
8 Front Office 4250 1001 800 600 0 600 1249 0 8500
F&B
9 7500 1001 2600 800 1250 0 1849 0 15000
Production
10 House Keeping 3250 1001 600 250 0 600 799 0 6500

Page 10
 Microsoft Office Excel
Kitchen
11 3250 1001 600 250 0 600 799 0 6500
Stewarding
12 Trainee 2450 1001 400 0 0 600 1049 0 5500
Grand Total 121450 12012 30750 5850 5000 5400 63038 0 243500
Exercise 23
Iron Works
Source of Income
First six months
Jan Feb Mar Apr May Jun Total
Switch
236 583 453 284 1,294 788 3,638
plates
Tin Ceilings 17,894 17,012 12,894 12,884 15,623 17,433 93,740
Door Plates 478,003 593,221 488,534 589,322 499,322 523,855 3,172,257
Lock Sets 187,945 198,422 201,123 293,844 198,843 200,019 1,280,196
Cabinet
18,956 19,842 17,363 18,764 19,846 17,883 112,654
Knobs
Drawer Pulls 38,953 39,847 37,855 39,009 38,755 37,129 231,548
Bathroom
1,930,055 2,847,733 1,895,322 1,984,472 1,758,332 1,846,734 12,262,648
Hardware

Maximum 1,930,055 2,847,733 1,895,322 1,984,472 1,758,332 1,846,734 12,262,648

Minimum 236 583 453 284 1,294 788 3,638
Average 381,720 530,951 379,078 419,797 361,716 377,692 2,450,954
Total 2,672,042 3,716,660 2,653,544 2,938,579 2,532,015 2,643,841 17,156,681
Exercise 24
ABC Limited
For the period of 2000 - 2002
REVENUE 2000 2001 2002
Gross Sales 70000 75000 90000
Sales Returns 20000 23000 28000
NET SALES 50000 52000 62000

COST OF SALES 2000 2001 2002

Beginning Inventory 35000 36000 42000
Goods Purchased 12000 16500 18500
Total Goods Available 47000 52500 60500
Ending Inventory 3600 4200 4350
Total cost of Goods
43400 48300 56150
Consumed
Gross Profit 6600 3700 5850

Page 11
 Microsoft Office Excel
OPERATING EXPENSES 2000 2001 2002
Selling 6200 7300 4600
General/Administrative 4450 5275 6000
Total Operating
10650 12575 10600
Expenses
Income before Taxes -4050 -8875 -4750
Taxes on Income -1822.5 -3993.75 -2137.5
Net Profit -2227.5 -4881.25 -2612.5

Note : net sales = gross sales – sales return

Total cost of goods consumed = Total Goods Available - Ending Inventory
Gross profit = net sales - Total cost of goods consumed
Total Operating Expenses = selling + General/Administrative
Income before taxes = gross profit - Total Operating Expenses
Taxes on income = 0.45 * Income before Taxes
Net profit = Income before Taxes - Taxes on Income

Exercise 25
ID Last Name First Name Department Extension
1 Burmeister Allen Development 5325
2 Cole Francis Publications 5277
3 Conrad John Customer Support 5424
4 Dixon Colleen Human Resources 5387
5 Elphin Brenda Publications 5298
6 Franklin Benito Sales & Marketing 5701
7 Griffith Susan Human Resources 5321
8 Henry Kurt Shipping 5167
9 Hirsch Alexa Human Resources 5345
10 Kaplan Dan Accounting 5598
11 Kapur Madeline Accounting 5500
12 Lao Tony Publications 5211
13 Lindquist Donald Sales & Marketing 5727
14 Mercer Ernest Development 5333
15 Murphy Franklin Development 5676
16 Spencer Felicity Publications 5209

Page 12
 Microsoft Office Excel
17 Stephens Maryanne Sales & Marketing 5763
18 Tai Anna Development 5301
19 Thomas George Shipping 5189
20 Tran Margaret Accounting 5571

Exercise 26
Name State City Product Quantity Unit Price Total
Mitron California San Jose CD-ROM 64 0.25 16
Smith California Los Angeles CD-ROM 152 0.25 38
Garcia Massachusetts Boston Smart Card 282 25.3 7134.6
Yokuchi Texas Dallas DVD-ROM 292 0.55 160.6
Garcia Massachusetts Boston DVD-ROM 336 0.55 184.8
Garcia Massachusetts Boston DVD-ROM 304 0.55 167.2
Charlton Texas Dallas Keyboard 331 7.5 2482.5
Yokuchi Texas Dallas CD-ROM 385 0.25 96.25
Yang Nevada Las Vegas Cle USB 285 12.45 3548.25
Yang Nevada Las Vegas Hard drive 164 75 12300
Charlton Texas Dallas DVD-ROM 344 0.55 189.2
Yang Nevada Las Vegas CD-ROM 53 0.25 13.25
Smith California Los Angeles CD-ROM 283 0.25 70.75
Charlton Texas Dallas DVD-ROM 313 0.55 172.15
Julian Texas Houston DVD-ROM 397 0.55 218.35
Charlton Texas Dallas USB 488 12.45 6075.6
Charlton Texas Dallas Smart Card 181 25.3 4579.3
Charlton Texas Dallas USB 460 12.45 5727
Yang Nevada Las Vegas Hard drive 90 75 6750
San
Brown California Printer 260 54 14040
Francisco
Boulard Massachusetts Springfield DVD-ROM 286 0.55 157.3
Mitron California San Jose DVD-ROM 254 0.55 139.7
Garcia Massachusetts Boston Printer 398 54 21492
Boulard Massachusetts Springfield Smart Card 185 25.3 4680.5
Boulard Massachusetts Springfield Hard drive 15 75 1125
Julian Texas Houston Printer 228 54 12312
Mitron California San Jose Smart Card 284 25.3 7185.2

Page 13
 Microsoft Office Excel
Yang Nevada Las Vegas Hard drive 472 75 35400
Charlton Texas Dallas Keyboard 53 7.5 397.5
Yokuchi Texas Dallas Smart Card 372 25.3 9411.6
Boulard Massachusetts Springfield Keyboard 490 7.5 3675
Smith California Los Angeles CD-ROM 1000 0.25 250
Garcia Massachusetts Boston Printer 500 54 27000
Julian Texas Houston Printer 374 54 20196
Boulard Massachusetts Springfield Keyboard 285 7.5 2137.5
Yang Nevada Las Vegas Printer 376 54 20304
Yang Nevada Las Vegas CD-ROM 25 0.25 6.25
Garcia Massachusetts Boston Printer 471 54 25434
Mitron California San Jose Printer 366 54 19764
Mitron California San Jose Smart Card 365 25.3 9234.5

Exercise 27
A to Z Office Supplies
Quarterly Sales 2003
Sales Person Qtr1 Qtr2 Qtr3 Qtr4 Total
Nichols, Todd 1,200 423 521 625 2,769
Green, Cheryl 654 634 326 754 2,368
Finley, Robert 2,567 2,390 3,005 2,389 10,351
Jenkins, Jerry 233 532 525 652 1,942
Franklin, William 354 633 422 255 1,664
Soderman, Pamela 255 525 252 624 1,656
Carpenter, Candace 422 642 642 624 2,330
Sutherland, Patricia 854 364 474 743 2,435
Williams, Walter 2,234 2,567 2,987 2,546 10,334
Coombs, Dallas 356 634 632 743 2,365

Maximum 2,567 2,567 3,005 2,546 10,351

Minimum 233 364 252 255 1,656
Average 913 934 979 996 3,821
Total 9,129 9,344 9,786 9,955 38,214

Page 14
 Microsoft Office Excel
Exercise 28

September

November

December
February

October
January

August
March

Total
April

June
May

July
Income 1500 1000 1000 1800 2700 2700 3300 3630 2310 1732.5 1200 1380 24,252.50
Fixed
750 750 750 750 750 750 750 750 750 750 750 750 9,000.00
expends
Variables
600 500 500 1500 1600 1840 2116 2433.4 1600 540 640 576 14,445.40
expends
Amount of
1350 1250 1250 2250 2350 2590 2866 3183.4 2350 1290 1390 1326 23,445.40
expends
Result 150 -250 -250 -450 350 110 434 446.6 -40 442.5 -190 54 807.10

Note: result = income – amount of expends

Exercise 29
Downtown Internet Café
Annual Forecast
September

November

December
February

October
January

August
March

Total
April

June
May

July

Sales
Espresso 13300 13600 14200 14400 15200 15500 15600 15700 15800 16200 16800 17100 183400

Drip Coffee 5800 6000 6200 6200 6200 6200 6450 6500 6500 6700 6900 7000 76650
Food/Beverages 3600 3800 3800 3600 3800 3800 5000 5100 5200 5800 6250 6400 56150
Merchandise 1000 1100 1100 1500 1500 1500 2100 2200 2200 2300 2400 2500 21400
Computer 400 400 400 600 600 800 775 800 800 900 950 1000 8425
Total Sales 24100 24900 25700 26300 27300 27800 29925 30300 30500 31900 33300 34000 346025

Expenses
Cost of Goods 7225 7480 7690 7620 7940 8015 8835 8935 9020 9540 10020 10215 102535

Cost of
700 770 770 1050 1050 1050 1470 1540 1540 1610 1680 1750 14980
Merchandise
Payroll 9000 9000 9000 7860 8390 8740 9000 9000 9000 9500 9800 10200 108490
Internet 325 325 325 325 325 325 325 325 325 325 325 325 3900
Building 2100 2100 2100 2100 2100 2100 2100 2100 2100 2100 2100 2100 25200

Page 15
 Microsoft Office Excel
Advertising 600 600 600 600 600 600 600 600 600 600 600 600 7200
Capital Assets 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 18000
Miscellaneous 1300 1300 1300 1300 1300 1300 1300 1300 1300 1300 1300 1300 15600
Total Expenses 22750 23075 23285 22355 23205 23630 25130 25300 25385 26475 27325 27990 295905

Net Income 1,350 1,825 2,415 3,945 4,095 4,170 4,795 5,000 5,115 5,425 5,975 6,010 50,120
5.60
Profit Margin 7.33% 9.40% 15% 15% 15% 16% 16% 16% 17% 17% 17% 16%
%

Note: Net Income = Total Sales – Total Expenses

Profit Margin = Net Income / Net Sales

Exercise 30
Save Sable River Foundation
Lifetime Fundraising Summary
Town of
Allentown Chamber City Catonsville Sable Village Strongsville Total
Cary
Corporate 74,029.35 92,278.21 63,081.74 84,210.02 61,644.26 89,820.51 465,064.09
Direct Mail 67,286.06 83,867.23 55,076.48 55,547.28 79,779.02 84,366.19 425,922.26
Fun Runs 54,704.39 66,934.67 64,581.66 28,895.86 32,690.37 64,242.72 312,049.67
Government 30,623.99 58,614.35 51,486.46 36,387.09 51,642.55 40,177.87 268,932.31
Phone-a-thon 16,692.14 24,223.81 9,492.91 17,328.74 12,305.85 21,097.60 101,141.05

Maximum 74,029.35 92,278.21 64,581.66 84,210.02 79,779.02 89,820.51 465,064.09

Minimum 16,692.14 24,223.81 9,492.91 17,328.74 12,305.85 21,097.60 101,141.05
Average 48,667.19 65,183.65 48,743.85 44,473.80 47,612.41 59,940.98 314,621.88
243,335.9
Total 325,918.27 243,719.25 222,368.99 238,062.05 299,704.89 1,573,109.38
3

Exercise 31
Adventure Travels
1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
Car Rentals 124,650 124,875 126,500 129,145 505,170
Airlines 225,000 226,902 229,995 235,390 917,287

Page 16
 Microsoft Office Excel
Tour Packages 1,125,530 1,350,655 1,500,675 1,755,850 5,732,710
Cruise
2,236,480 2,567,892 2,756,215 3,125,689 10,686,276
Packages
Hotels 550,000 550,545 595,655 605,115 2,301,315

Maximum 2,236,480 2,567,892 2,756,215 3,125,689 10,686,276

Minimum 124,650 124,875 126,500 129,145 505,170
Average 852,332 964,174 1,041,808 1,170,238 4,028,552
Total 4,261,660 4,820,869 5,209,040 5,851,189 20,142,758
Exercise 32
Employee ID Last Name First Name Address City State ZIP Code

00212 Schiff Chad 235 N. Cactus Dr. Chesterfield CA 92122

00617 Nichols Cathy 75 Brooklea Dr. Landis CA 92120
00907 Pennington Mark 23 Mill Ave. Landis ca 92120
03225 Morgan Dan 564 S. Lemon Dr. Maldin CA 92121
03406 Lopez Mina 4290 E. Alameda Dr. Maldin CA 92121-3740
03890 Thi Erona 7867 Forest Ave. River Mist CA 92123
04321 Sabin Greg 90 E. Rawhide Ave. Chesterfield CA 92122
04731 Marchant Roberta 564 Palm Avenue Landis CA 92120
05015 Briggs Scott 45 E. Camelback Rd. River Mist Ca 92123-2232
06000 Robertson Kirk 832 S. William Ave. Maldin CA 92121
07287 Roman Anita 2348 S. Bala Dr. Maldin CA 92121-3740
08391 Ruiz Enrique 35 Palm St. Chesterfield CA 92122-1268
11747 Lettow Ryan 358 Maple Dr. Maldin CA 92121-3740
12194 Polonsky Mitch 8701 E. Sheridan Maldin CA 92121-3740
12213 Young Kim 89 College Ave. Landis CA 92120
12230 Reddie Suzanne 932 E. Parkway Dr. Landis CA 92120
12247 Rath Kathy 87 E. Aurora Ave. Chesterfield CA 92122-1268
12258 Delano Gordon 1310 N. Sprindrift Dr. Chesterfield CA 92122-1268
12366 Stacey David 737 S. College Rd. Chesterfield CA 92122-1268
12389 Blackman Jennifer 773 E. Laurel St. River Mist CA 92123-2232
12501 DeLuca Elizabeth 21 W. Southern Ave. Landis CA 92120
12583 Sullivan Marie 78 Omega Drive Chesterfield CA 92122-1268
12594 Reddie Mark 900 W. Campus Dr. Maldin CA 92121
12612 Lettow Melissa 234 N. 1st St. River Mist CA 92123

Page 17
 Microsoft Office Excel
12655 Lahti Jill 5401 E. Thomas Rd. Landis CA 92120

Exercise 33

New Car Sales

January February March Total Sales
Alfred 68,560 87,657 67,865 224,082
Antonie 74,768 100,234 95,465 270,467
Bass 87,546 98,555 83,655 269,756
Bradley 100,765 123,543 34,999 259,307
Bunnis 88,765 100,876 98,655 288,296
Cherisma 35,000 65,987 56,787 157,774
Delva 67,980 87,657 56,766 212,403
Dixon 82,987 102,675 45,673 231,335
Dearborn 98,765 132,987 105,467 337,219
Fleuridor 76,546 126,547 101,675 304,768
Heydt 65,785 87,657 67,865 221,307
Hinton 65,754 100,234 95,465 261,453
King 98,673 98,555 83,655 280,883
Lamons 104,654 123,543 92,876 321,073
Nudelman 88,765 100,876 98,655 288,296
Sun 45,676 65,987 65,478 177,141
Tullos 98,765 87,657 98,675 285,097
Washington 82,987 67,876 34,765 185,628
Zappa 98,765 89,654 87,678 276,097
Zelaya 76,546 99,765 99,678 275,989

Maximum 104,654 132,987 105,467 337,219

Minimum 35,000 65,987 34,765 157,774
Average 80,403 97,426 78,590 256,419
Total 1,608,052 1,948,522 1,571,797 5,128,371

Exercise 34
Animal Angels Volunteers

Page 18
 Microsoft Office Excel
1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Average Total
Bell, Patricia 45 34 102 35 54 216
Carey, Ronnie 106 45 55 106 78 312
Carver, Kathi 30 83 3 3 29.75 119
Chorley, Besty 68 2 235 96 100.25 401
Clark, Jamel 51 30 28 58 41.75 167
Cody, Martin 28 66 6 45 36.25 145
Dickson, Diane 72 19 77 32 50 200
Edwards, Mike 12 20 61 28 30.25 121
Ferguson, Robby 99 12 0 43 38.5 154
Forester, Kimberly 32 66 34 30 40.5 162
Franklin, Stacey 57 98 93 80 82 328
Fulton, Anne 13 25 55 100 48.25 193
Garcia, Maria 5 23 61 36 31.25 125
Gatens, Chris 0 100 58 85 60.75 243
Henderson, James 9 76 51 60 49 196
Ingram, Helen 29 12 33 12 21.5 86
Isbell, Sonya 46 65 47 3 40.25 161
Johnson, Thaman 5 25 18 7 13.75 55
Jones, April 70 83 38 79 67.5 270
Kelly, William 13 78 52 88 57.75 231
Kettonhoeffer, Bill 4 59 57 8 32 128
Kullman, Rodney 28 30 35 16 27.25 109
La Paglia, Sally 39 10 12 134 48.75 195
Lee, Su 56 6 5 0 16.75 67
Legge, Cristan 3 81 73 34 47.75 191
Lopez, Andrew 83 3 165 0 62.75 251
Marcus, Danielle 3 19 24 2 12 48
Merwin, Michael 44 9 95 37 46.25 185
Nelson, Faith 10 115 8 0 33.25 133

Exercise 35
ID Last Name First Name Address City State Zip Code Phone
3333 Arteaga Ella 58 Langley Avenue Aurora NC 28307 (910) 413-4728

Page 19
 Microsoft Office Excel
2222 Barrington Cindy 271 Latrell Road Baltimore NC 28306 (910) 428-4137
7777 Bruner Myra 182 Birchwood Street Refugio NC 28305 (910) 331-5052
5555 Caruso Jill 524 Ridge Road Spring Lake NC 28390 (910) 420-9063
6666 Charlton Kara 52 Hiroko Street Spring Lake NC 28390 (910) 384-4911
1234 Getty William 81 Columbus Road Buena Vista NC 28304 (910) 475-4153
1111 Givens Bryan 72 White Eagle Street Fayetteville NC 28301 (910) 456-5660
4444 Haller Geneva 985 Lisa Street Spring Lake NC 28390 (910) 357-3062
9999 Keane Vickie 47 Carolyn Avenue Middleton NC 28309 (910) 473-7752
8888 Mai Tony 732 Oregon Street Madison NC 28302 (910) 350-9309

Exercise 36

Page 20
 Microsoft Office Excel
Exercise 37
Region Rep Item Units Unit Cost Total
East Jones Pencil 95 1.99 189.05
Central Kivell Binder 50 19.99 999.50
Central Jardine Pencil 36 4.99 179.64
Central Gill Pen 27 19.99 539.73
West Sorvino Pencil 56 2.99 167.44
East Jones Binder 60 4.99 299.40
Central Andrews Pencil 75 1.99 149.25
Central Jardine Pencil 90 4.99 449.10
West Thompson Pencil 32 1.99 63.68
East Jones Binder 60 8.99 539.40
Central Morgan Pencil 90 4.99 449.10
East Howard Binder 29 1.99 57.71
East Parent Binder 81 19.99 1,619.19
East Jones Pencil 35 4.99 174.65
Central Smith Desk 2 125.00 250.00
East Jones Pen Set 16 15.99 255.84
Central Morgan Binder 28 8.99 251.72
East Jones Pen 64 8.99 575.36
East Parent Pen 15 19.99 299.85
Central Kivell Pen Set 96 4.99 479.04
Central Smith Pencil 67 1.29 86.43
East Parent Pen Set 74 15.99 1,183.26
Central Gill Binder 46 8.99 413.54
Central Smith Binder 87 15.00 1,305.00
East Jones Binder 4 4.99 19.96
West Sorvino Binder 7 19.99 139.93
Central Jardine Pen Set 50 4.99 249.50
Central Andrews Pencil 66 1.99 131.34
East Howard Pen 96 4.99 479.04
Central Gill Pencil 53 1.29 68.37
Central Gill Binder 80 8.99 719.20
Central Kivell Desk 5 125.00 625.00

Page 21
 Microsoft Office Excel
East Jones Pen Set 62 4.99 309.38
Central Morgan Pen Set 55 12.49 686.95
Central Kivell Pen Set 42 23.95 1,005.90
West Sorvino Desk 3 275.00 825.00
Central Gill Pencil 7 1.29 9.03
West Sorvino Pen 76 1.99 151.24
West Thompson Binder 57 19.99 1,139.43
Central Andrews Pencil 14 1.29 18.06
Central Jardine Binder 11 4.99 54.89
Central Jardine Binder 94 19.99 1,879.06
Central Andrews Binder 28 4.99 139.72

Exercise 38
Aficionado Guitar Parts
Monthly Accounts Receivable Balance Report
Beginnin Service New
Customer Credits Payments Purchases
g Balance Charge Balance
Cervantes, Katriel 803.01 56.92 277.02 207.94 12.90 689.91
Cummings, Trenton 285.05 87.41 182.11 218.22 0.43 234.18
Danielsson, Oliver 702.13 79.33 180.09 364.02 12.17 818.90
Kalinowski, Jadwiga 438.37 60.90 331.10 190.39 1.28 238.04
Lanctot, Royce 378.81 48.55 126.15 211.38 5.61 421.10
Raglow, Dora 710.99 55.62 231.37 274.71 11.66 710.37
Tuan, Lin 482.74 85.01 129.67 332.89 7.37 608.32
Totals 3,801.10 473.74 1,457.51 1,799.55 51.42 3,720.82

Maximum 803.01 87.41 331.10 364.02 12.90 818.90

Minimum 285.05 48.55 126.15 190.39 0.43 234.18
Average 543.01 67.68 208.22 257.08 7.35 531.55

Note: service charge = 2.75% * ( beginning balance – credits – payments – purchases )

New balance = beginning balance + credits + payments + purchases + service charges

Exercise 39
Product Name Product Number Yield Input Quantity Output Quantity

Page 22
 Microsoft Office Excel
TYH-1MN3 1123 98 % 5000 4905
TUP-2MN4 1456 98 % 4950 4876
TYH-1MN3 1356 99 % 3780 3769
TUP-2MN4 1487 99 % 4950 4777
NVB-9MN1 1876 96 % 4900 4435
TYH-1MN3 1001 90 % 5000 4570
TYH-1MN3 1100 91 % 4500 4641
Exercise 40
Transaction Category Description Withdrawal
Check 201 Mortgage Faceless Mortgage Corp 780
Debit Card Grocery Mr. G's 114.42
Automatic Withdrawal Electric ERG/Synergy 37.09
Automatic Withdrawal Credit Card Largo Credit Corp 150
Debit Card Grocery Mr. G's 71.14
ATM ATM Cash 100
Automatic Withdrawal Cell Phone Busy signal Phone Co 42.64
Automatic Withdrawal Cable Costly Cable Co 28.64
ATM ATM Cash 30
ATM ATM Cash 70
Credit Card Grocery Wings' R Us 115.35
Automatic Withdrawal Water City Utilities 28.72
ATM ATM Cash 100
Automatic Withdrawal Electric ERG/Synergy 39.63
Automatic Withdrawal Gas Nat's Gas Service 112.59
Debit Card Grocery Mr. G's 178.49
ATM ATM Cash 50
Check 202 Mortgage Faceless Mortgage Corp 780
Direct Deposit
Automatic Withdrawal Credit Card Largo Credit Corp 150
ATM ATM Cash 40
Automatic Withdrawal Cell Phone Busy signal Phone Co 43.76
ATM ATM Cash 100
Automatic Withdrawal Cable Costly Cable Co 28.64
Check 190 Void

Page 23
 Microsoft Office Excel
ATM ATM Cash 80
ATM ATM Cash 70
Automatic Withdrawal Water City Utilities 28.72
Automatic Withdrawal Electric ERG/Synergy 39.45
Automatic Withdrawal Gas Nat's Gas Service 120.77
ATM ATM Cash 80
Check 203 Mortgage Faceless Mortgage Corp 780
Direct Deposit
ATM ATM Cash 70
Credit Card Grocery Mr. G's 169.84
Debit Card Grocery Mr. G's 44.08
Automatic Withdrawal Credit Card Largo Credit Corp 150
ATM ATM Cash 90
Automatic Withdrawal Cable Costly Cable Co 28.64
Automatic Withdrawal Cell Phone Busy signal Phone Co 50.74
Debit Card Grocery Mr. G's 131.51
ATM ATM Cash 30
Automatic Withdrawal Water City Utilities 28.72
Debit Card Grocery Mr. G's 81.79
Automatic Withdrawal Electric ERG/Synergy 35.52
Debit Card Grocery Mr. G's 94.59
Automatic Withdrawal Gas Nat's Gas Service 73.87
Direct Deposit
Check 204 Mortgage Faceless Mortgage Corp 780
Debit Card Grocery Mr. G's 133.64
Exercise 41
Application # First Name Last Name Address City
000123 Marilyn Foster 505 Lincoln Ave. Brentwood
000125 Mikhail Siederhoff 17 Spyglass Cir. Oceanview
000138 Yvonne Brady 301 Canoe Ave. Palmdale
000139 Adelle Cavenough 999 Seaspray Dr. Palmdale
000146 Olef Borisnov 403 Skiff Cir. Brentwood
000147 Lenny Brewster 2100 Palm St. Lexington
000150 Carolyn Welsh 186 Alameda St. Brentwood

Page 24
 Microsoft Office Excel
000158 Danielle Nash 725 Waterway Ln. Oceanview
000167 Yong Wu 326 Rockwell St. Oceanview
000173 Jessica Clegg 532 A St. Palmdale
000177 Christopher Finkle 323 Lorry Ave. Lexington
000180 Paul Vishon 2012 Hunt Rd. Oceanview
000199 Terence Pratt 1724 Lakewood Rd. Lexington
000206 Ahmed Jamal 511 Cabrillo Cir. Brentwood
000209 Lily Hammond 64 Spring St. Palmdale
000211 Ernest Helms 490 Wilson Ave. Palmdale
000222 Roberta Drew 203 Scanlan Rd. Palmdale
000232 Leon French 188 10th Ave. Oceanview
000241 Wendy Thompson 1947 Cypress Dr. Lexington
000256 Mohammed Patan 2097 5th Ave. Oceanview
000263 Bob Alvarez 616 Vasser Way Oceanview
000264 Vincent Ashton 387 Alamo Way Lexington
000300 Herve Umeza 812 Grant Ave. Brentwood
000301 Phillip Roberts 1221 Ferret St. Oceanview

Exercise 42
Data Entry for Financial Forecast Analysis
2006 2007 2008 2009 Four Year Totals
Sales
Sales 2,010,000.00 2,060,000.00 2,121,800.00 2,391,800.00 8,583,600.00
Other 0 0 0 0 0
Total Sales 2,010,000.00 2,060,000.00 2,121,800.00 2,391,800.00 8,583,600.00

Less Cost of
Goods Sold
Materials 320,000 329,600 339,488 389,088 1,378,176
Labor 500,000 515,000 530,450 545,450 2,090,900
Overhead 125,500 128,750 132,613 186,363 572,725
Other 0 0 0 0 0
Total Cost of
945,000 973,350 1,002,551 1,120,901 4,041,801
Goods Sold

Gross Profit 1,065,000 1,086,650 1,119,250 1,270,900 4,541,800

Operating
Expenses
Salaries and
190,000 190,000 190,000 190,000 760,000
wages
Employee
50,000 51,500 53,045 54,545 209,090
Benefits

Page 25
 Microsoft Office Excel
Payroll taxes 30,000 30,900 31,827 32,727 125,454
Rent 5,000 5,150 5,350 5,455 20,909
Utilities 3,000 3,090 3,183 3,273 12,545
Repairs and
1,000 1,030 1,061 1,061 4,152
maintenance
Insurance 1,000 1,030 1,061 1,061 4,152
Travel 2,133 2,197 2,263 2,263 8,856
Telephone 11,000 11,330 11,670 12,670 46,670
Postage 4,000 4,120 4,244 4,244 16,607
Office supplies 6,000 6,180 6,365 6,365 24,911
Advertising 1,000 1,030 1,061 1,061 4,152
Marketing /
3,000 3,090 3,183 3,183 12,455
promotion
Professional fees 1,000 1,030 1,061 1,061 4,152
Training and
1,000 1,030 1,061 1,061 4,152
development
Bank charges 1,000 1,030 1,061 1,061 4,152
Depreciation 1,000 1,030 1,061 1,061 4,152
Interest Expense 6,000 6,000 6,000 6,000 24,000
Other 0 0 0 0 0
Total Operating
317,133 320,767 324,510 328,150 1,290,560
Expenses

Exercise 43
Last Name First Name Doctor Name Specialty Samples Lunches
Hollars Stacey Hollars, Stacey Family Medicine 10 no
Smith Carol Smith, Carol Emergency 9 yes
Griesemer Joshua Griesemer, Joshua Cardiology 5 no
McConnell Alicia McConnell, Alicia Sports Medicine 15 yes
Probus Kelly Probus, Kelly Emergency 9 yes
Rodgers Michael Rodgers, Michael Family Medicine 1 yes
Murphy Tina Murphy, Tina Cardiology 5 no
Bates Alex Bates, Alex Pediatrics 7 no
Gladish Judith Gladish, Judith Pediatrics 5 no
Law Tara Law, Tara Pediatrics 8 no
Ash Cheryl Ash, Cheryl Radiology 10 yes
Blanton Samuel Blanton, Samuel Cardiology 13 yes
Griesemer Joshua Griesemer, Joshua Cardiology 8 no
Jones Jennifer Jones, Jennifer Sports Medicine 4 yes
McConnell Alicia McConnell, Alicia Sports Medicine 7 no
Ash Cheryl Ash, Cheryl Radiology 3 no
Gladish Judith Gladish, Judith Pediatrics 2 no

Page 26
 Microsoft Office Excel
Smith Carol Smith, Carol Emergency 15 yes

Murphy Tina Murphy, Tina Cardiology 3 yes

Smith Carol Smith, Carol Emergency 0 yes
Probus Kelly Probus, Kelly Emergency 0 yes
Rodgers Michael Rodgers, Michael Family Medicine 0 no
Bates Alex Bates, Alex Radiology 2 no
Jones Jennifer Jones, Jennifer Sports Medicine 10 no
Murphy Tina Murphy, Tina Cardiology 15 yes
Bates Alex Bates, Alex Radiology 10 yes
Jones Jennifer Jones, Jennifer Sports Medicine 5 no
Law Tara Law, Tara Pediatrics 6 yes
Blanton Samuel Blanton, Samuel Cardiology 10 yes
Law Tara Law, Tara Pediatrics 15 no
Griesemer Joshua Griesemer, Joshua Cardiology 11 yes
Jones Jennifer Jones, Jennifer Sports Medicine 9 no
Maris Jonathon Maris, Jonathon Emergency 14 no
Barnes Ethan Barnes, Ethan Pediatrics 10 no
Partner Debra Partner, Debra Family Medicine 15 yes
McCoy Sarah McCoy, Sarah Emergency 14 yes
Smith Carol Smith, Carol Emergency 3 yes
Thompson Jennifer Thompson, Jennifer Cardiology 14 yes
Bates Alex Bates, Alex Radiology 14 yes
Law Tara Law, Tara Pediatrics 2 no
Blanton Samuel Blanton, Samuel Cardiology 6 no
Griesemer Joshua Griesemer, Joshua Cardiology 10 yes
Probus Kelly Probus, Kelly Emergency 13 no
Rodgers Michael Rodgers, Michael Family Medicine 12 yes
Hollars Stacey Hollars, Stacey Family Medicine 6 yes
Blanton Samuel Blanton, Samuel Cardiology 5 no
Griesemer Joshua Griesemer, Joshua Cardiology 10 yes
Jones Jennifer Jones, Jennifer Sports Medicine 10 no
Smith Carol Smith, Carol Emergency 6 yes

Page 27
 Microsoft Office Excel
Griesemer Joshua Griesemer, Joshua Cardiology 12 yes
Exercise 44
Part Number Type Department Cost Quantity Total Value
11164539 RT Department 2 55.30 15 829.50
11164540 AC Department 5 69.58 7 487.06
11164541 AB Department 4 47.87 1 47.87
11164542 RT Department 2 16.22 5 81.10
11164544 AC Department 5 54.36 -
11164545 DE Department 5 74.45 13 967.85
11164546 DE Department 2 52.03 11 572.33
11164547 DE Department 4 25.74 8 205.92
11164548 DB Department 5 39.12 14 547.68
11164549 DE Department 4 10.97 3 32.91

11164550 DB Department 1 18.56 12 222.72

11164551 RT Department 2 45.80 17 778.60
11164556 DE Department 4 88.39 2 176.78
11164557 AB Department 2 79.08 12 948.96
11164558 DE Department 1 15.28 7 106.96
11164559 AC Department 1 2.77 15 41.55
11164560 RT Department 5 40.96 -
11164561 RT Department 3 25.07 12 300.84
11164562 DE Department 2 0.84 12 10.08
11164563 DE Department 1 9.42 3 28.26
11164564 AC Department 5 16.02 7 112.14
11164565 RT Department 2 77.83 4 311.32
11164567 AB Department 3 1.54 15 23.10
11164568 AC Department 1 9.81 1 9.81
11164569 DE Department 3 1.77 8 14.16
11164570 DB Department 3 7.99 13 103.87
Exercise 45
Grow More Hospitality Services Private Limited
Salary Statement for the month of December 2011
                       
Sl. Wash Spl.
ID No. Name Basic VDA HRA CONV Medical Others Total
No. Allow Allow

Page 28
 Microsoft Office Excel
                       

Administration                   
1 GM001 Allen, Timothy J 50,000 1,001 20,000 800 1,250 600 26,349 0 1,00,000
2 GM002 Kershaw, David 30,000 1,001 0 800 1,250 0 26,949 0 60,000
3   Lapham, Carrie A 5,000 1,001 2,000 800 0 600 599 0 10,000
    Total 85,000 3,003 22,000 2,400 2,500 1,200 53,897 0 1,70,000
                       

Security                  
4   Abel, Mary E 3000 1001 400 0 0 600 999 0 6000
    Total 3000 1001 400 0 0 600 999 0 6000
                       

Accounts                  
5 113 Sentell, Tricia J 6000 1001 2150 800 1250 0 799 0 12000
6   Proffer, Brian 2250 1001 550 0 0 600 99 148 4648
7 652 Beamish, Coleen 3250 1001 600 250 0 600 799 0 6500
8   Mason, Timothy 3500 1001 600 500 0 600 799 0 7000
9   Rose, Gina M 4250 1001 800 600 0 600 1249 0 8500
10   Streby, Paul G 7500 1001 2600 800 1250 0 1849 0 15000
    Total 26750 6006 7300 2950 2500 2400 5594 148 53648
                       

Maintenance                  
11 824 Terry, Andrea 3250 1001 600 250 0 600 799 0 6500
    Total 3250 1001 600 250 0 600 799 0 6500
                       

Drivers                  
12 114 Banerjee, Ruma 3250 1001 600 250 0 600 799 0 6500
13   Baptist, Jan 2450 1001 400 0 0 600 1049 0 5500
14   Bartlett, Lisa 2450 1001 400 0 0 600 1049 0 5500
15   Carney, Linda M 2450 1001 400 0 0 600 1049 0 5500
16   Koonmen, Jamie 2450 1001 400 0 0 600 1049 0 5500
    Total 13050 5005 2200 250 0 3000 4995 0 28500
                       

Page 29
 Microsoft Office Excel
Bouncer                  
17   Dyer, Judy A 4500 1001 700 700 0 600 1499 0 9000
18   Calma, Grace C 4500 1001 700 700 0 600 1499 0 9000
    Total 9000 2002 1400 1400 0 1200 2998 0 18000
                       

F & B Services                  
19   Kidd, Reva J 6000 1001 2400 800 0 600 1199 0 12000
20   Jaffurs, Sheri E 7500 1001 2600 800 1250 0 1849 0 15000
21   Haley, John J 4500 1001 700 700 0 600 1499 0 9000
22   Glover, Cory M 3500 1001 600 500 0 600 799 0 7000
23   Flynn, Crystal A 2500 1001 750 0 0 600 149 0 5000
24   Ellis, Joel 2250 1001 550 0 0 600 99 342 4842
25   Cherem, Barbara 3000 1001 400 0 0 600 999 0 6000
26   Bur, Kevin P 2500 1001 750 0 0 600 149 0 5000
27   Clyde, Paul S 4000 1001 1600 0 0 600 799 0 8000
28   Clyde, Paul S 2250 1001 550 0 0 600 99 342 4842
29   Clark, Susan M 2250 1001 550 0 0 600 99 342 4842
30   Book, C Jane 3000 1001 400 0 0 600 999 0 6000
31   Bonk, Mario 2250 1001 550 0 0 600 99 342 4842
32   Pomo, Darrin 2250 1001 550 0 0 600 99 342 4842
33   Salata, Diane L 2250 1001 550 0 0 600 99 342 4842
34   Zhou, Chuan 2250 1001 550 0 0 600 99 342 4842
35   Xue, Xiaolin 4000 1001 1600 0 0 600 799 0 8000
36   Nichols, Larry 3000 1001 400 0 0 600 999 0 6000
37   Total 59250 18018 16050 2800 1250 10200 10932 2394 120894
                     
  Grand Total 199,300 36,036 49,950 10,050 6,250 19,200 80,214 2,542 303,542

Exercise 46
Randolph Academy
336 Main St, Randolph, NY 14772, United States
Income & expenditure for the year ended
31st march 2010

Page 30
 Microsoft Office Excel
PARTICULARS 2009 – 2010 ( Rs. ) 2008 – 2009 ( Rs. )

INCOME :
Donation
In INR 9,527,408 12,314,346
In Foreign Currency Equivalent to INR 170,791 25,758

Bank Interest
On Bank FDRs 401,147 195,983
On Bank Account 34,799 30,817

Project Receipts 3,740,464 3,373,850

Other Income 849 0

TOTAL 13,875,457 15,940,754

EXPENDITURE :
Administrative Expenses 4,261,021 3,807,880
Donation 325,000 275,000
Project Expenses 8,305,035 8,743,968
Affiliation Fees 61,900 66,950

TOTAL 12,952,956 12,893,798

SURPLUS / ( DEFICIT ) BEFORE DEPRECIATION 922,501 3,046,956

Less: Depreciation 813,776 850,089
SURPLUS / ( DEFICIT ) TRANFERRED TO CORPUS
108,725 2,196,867
FUND
Exercise 47
Randolph Academy
336 Main St, Randolph, NY 14772, United States
Income & expenditure for the year ended

Page 31
 Microsoft Office Excel
31st march 2010
PARTICULARS 2009 – 2010 ( Rs. ) 2008 – 2009 ( Rs. )

Schedule No. 1
Membership Fund
Opening Balance 203,500 0
Received During the year 66,000 203,500
Total 269,500 203,500

Schedule No. 2
Corpus Fund
Opening Balance 11,029,427 8,832,560
Excess of Income over Expenditure 108,725 2,196,867
Total 11,138,152 11,029,427

Exercise 48
Balance Sheet - Years 1 to 4
           
  Opening Year 1 Year 2 Year 3 Year 4
ASSETS          
           
Current Assets          
Cash 507,604 451,000 464,530 478,466 492,820
Notes Receivable 4,300 1,200 3,200 3,000 3,400
Accounts receivable, net 393,928 350,000 360,500 371,315 382,454
Inventory 450,204 400,000 412,000 424,360 437,091
Other 11,255 10,000 10,300 10,609 10,927
Total Current Assets 1,367,291 1,212,200 1,250,530 1,287,750 1,326,692
           
Long-Term Assets          
Property, plant, and equipment 2,701,221 2,400,000 2,472,000 2,546,160 2,622,545
Less accumulated depreciation 450,204 400,000 412,000 424,360 437,091
Net property, plant, and equipment 2,251,018 2,000,000 2,060,000 2,121,800 2,185,454
Other long-term assets 58,765 55,000 55,900 56,827 57,782
Total Long-Term Assets 2,309,783 2,055,000 2,115,900 2,178,627 2,243,236
           
Total Assets 3,677,074 3,267,200 3,366,430 3,466,377 3,569,928
LIABILITIES AND SHAREHOLDERS' EQUITY          
           
Current Liabilities          
Short-term debt 675,305 600,000 618,000 636,540 655,636

Page 32
 Microsoft Office Excel
Current maturities of long-term debt 112,551 100,000 103,000 106,090 109,273
Accounts payable 112,551 100,000 103,000 106,090 109,273
Income taxes payable 33,765 30,000 30,900 31,827 32,782
Accrued liabilities 101,296 90,000 92,700 95,481 98,345
Other 18,008 16,000 16,480 16,974 17,484
Total Current Liabilities 1,053,476 936,000 964,080 993,002 1,022,792
           
Long-Term Liabilities          
Long-term debt less current maturities 692,156 601,200 624,200 645,630 668,308
Deferred income taxes 112,551 100,000 103,000 106,090 109,273
Other long-term liabilities 85,903 79,000 80,650 82,350 84,101
Total Long-Term Liabilities 890,610 780,200 807,850 834,070 861,682
           
Total Liabilities 1,944,086 1,716,200 1,771,930 1,827,072 1,884,474
Shareholders' Equity          
Common stock 100,000 100,000 100,000 100,000 100,000
Additional paid-in capital 1,000 1,000 1,000 1,000 1,000
Retained earnings 56,275 50,000 51,500 53,045 54,636
Other 1,575,712 1,400,000 1,442,000 1,485,260 1,529,818
Total Shareholders' Equity 1,732,988 1,551,000 1,594,500 1,639,305 1,685,454
           
Total Liabilities and Shareholders' Equity 3,677,074 3,267,200 3,366,430 3,466,377 3,569,929

Exercise 49
Cisco Systems India Pvt. Ltd.
Emp. Code 16701 Name Joseph Branch/Site Cisco Systems
Department Operation Designation Tech. Support Grade S4
Bank Name SVC Account No. 4780 Division Facility Management
P.F No. KN/BN/358 ESIC No. 289456781 Pan No.
Comp. Off 4/0 Days Present 22 W.Off/Pd.Off 4/0
LWP/Absent 0/5 Days Paid 30 Month Feb 2010
CL/CL Balance 0/0 SL/SL Bal 0/0 PL/Balance 0/0
MAL/Balance 0/0 PAL/Bal 0/0 L6/Balance 0/12
Exercise 50
Salary Slip
Employee Name James
Designation Asst. Teacher
School Name St. Anne high school, Lawrence road, Amritsar 143 001
Income tax PAN AAAPP1111L
Month and year December 2010
Emoluments Amount Rs. Deductions Amount Rs.
Basic pay 15,000 Profession tax 200
House rent allowance 3,000 General provident fund 4,000
Dearness allowance 7,500 Contributory provident fund -
Medical allowance 100 Life insurance corporation 5,000
Vehicle allowance Postal life insurance 240
Washing allowance Group insurance 150
Other allowance Income tax (TDS) 1,000

Page 33
 Microsoft Office Excel
Other allowance Credit society 300

Total deductions 10,890

Gross pay 25,600 Net pay 14,710

*Net pay = Gross pay – Total deductions

Exercise 51
Sl. No City Bank Address
1 Agra ICICI Ground floor, 18, A/51, Fatehbad Road, Agra - 282001
2 Agra ICICI C- 80 / 4, Kamla Nagar, Agra - 282005
3 Ahmedabad ICICI JMC House Opp.Parimal Garden, Off. C.G. Road, Ambavadi Ahmedabad 380 006
4 Ahmedabad ICICI 2, Shashi Shopping Complex, Swaminarayan Chowk, Paldi Vasna, Ahmedabad
5 Ahmedabad Centurion Sarkhej Gandhinagar Highway
6 Ahmedabad HSBC ShopNo.UL12,IndraprasthaTower,Drive-InRoad,Ahmedabad380054
7 Amritsar ICICI Airport Authority of India, Rajsansi Airport, Amritsar - 143 001
8 Amritsar ICICI S.C.F 39, Kabir Park, Opp Gurunanak Dev University, Amritsar - 141003.
9 Amritsar ICICI Nidhi, 2- Lawrence Road, Amritsar 143 001.
Aurangaba
10 HDFC Shop No. 16 , Ground Floor
d
Aurangaba
11 HDFC Parle Biscuits, Bahadurgarh
d
12 Bangalore ICICI 1292, 25th Main Road, Jayanagar, 9th Block, Bangalore - 560069.
13 Bangalore ICICI No 641, 11th Main , 46th Cross, 5th Block, Banglore – 560041
14 Bangalore ICICI Asea Brown Boveri Ltd., Plot No.5 & 6, II Phase, Peenya Industrial Area 560058.
15 Bangalore ICICI Annapurna, IOCL Pump R.V.Road, Next To Vijaya College, Bangalore - 560009.
16 Bangalore ICICI 80/7, Elephant Rock Road, Block III, Jayanagar, Bangalore 560 041.
17 Bangalore ICICI 366-367, 1st A Main, VII Block, Koramangala, Bangalore 560 095.
18 Bangalore HDFC No. 6, Royal Arcade

Exercise 52
Projected Balance Sheet
Integral India Software Development Centre Pvt. Ltd.
No. 99, mittal towers, 3rd main, 2nd cross, Bangalore – 560 001

Page 34
 Microsoft Office Excel

January

February

March

April

May

June

July

August

September

October

November

December
Balance
sheet

Cash 10,000 10,050 12,000 10,000 13,000 20,000 21,000 22,000 18,250 26,000 22,000 25,450
20,000 18,000 20,000 17,000 16,000 17,900 17,850 19,000 25,000 26,000 21,000 18,000
Inventory
Accounts 12,000 15,000 13,900 17,000 21,000 13,000 15,000 18,000 20,000 15,700 14,000 16,000
receivable
Prepaid 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000
expenses
Other
0 0 0 0 0 0 0 0 0 0 0 0
current
assets

Total current 43,00 44,05

46,900
45,00 51,00
51,900
54,85 60,00
64,250
68,70
58,000 60,450
0 0 0 0 0 0 0
assets

Exercise 53
Pro-Forma Balance Sheet
Xyz company
From (2003 - 2006)
ASSETS 2003 2004 2005 2006
Current Assets
Cash 54,000 57,000 59,000 64,000
Net accounts receivable 3,67,000 3,96,000 4,26,000 4,35,000
Inventory 1,77,000 1,91,000 2,03,000
Temporary investment 12,000 12,000 12,000 12,000
Prepaid expenses 2,000 2,000 2,000 2,000

Total current assets 6,12,000 6,58,000 7,02,000 5,13,000

Fixed Assets
Long-term investments 42,000 43,000 43,000 46,000
Land 6,56,000 6,56,000 6,84,000 7,27,000
Buildings (net of depreciation) 9,03,000 9,28,000 9,83,000 10,21,000
Plant and equipment (net) 6,08,000 6,31,00 6,42,000 6,54,000
Furniture and fixtures (net) 61,000 65,000 68,000 72,000

Total fixed assets 22,70,000 17,55,100 24,20,000 25,20,000

TOTAL ASSETS 28,82,000 24,13,100 31,22,000 30,33,000

LIABILITIES
Current liabilities
Accounts payable 2,56,000 2,52,000 2,58,000 2,77,000
Short-term notes 24,000 25,000 26,000 28,000
Current portion of long-term notes 14,000 14,000 14,000 15,000

Page 35
 Microsoft Office Excel
Accruals and other payables 14,000 14,000 14,000 14,000

Total current liabilities 3,08,000 3,05,000 3,12,000 3,34,000

Long-term liabilities
Mortgage 8,97,000 9,31,000 9,78,000 10,21,000
Other long-term liabilities 4,43,000 14,16,000 15,05,000 15,97,000

Total long-term liabilities 13,40,000 23,47,000 24,83,000 26,18,000

Shareholders’ equity
Capital stock 3,00,000 3,00,000 3,00,000 3,00,000
Retained earnings 9,44,000 9,60,000 10,05,000 10,07,000

Total shareholders’ equity 12,44,000 12,60,000 13,05,000 13,07,000

Total liabilities and equity 28,92,000 39,12,000 41,00,000 42,59,000

Exercise 54
Sales
Sales Report January February March Total Sales per Region Total Sales per Employee
Region 1 Amos, Andy 52,000 35,000 45,500 837,100 132,500
Boyd, Betty 45,000 27,500 52,000 124,500
Cox, Carole 65,000 32,500 51,500 149,000
Dell, Denice 75,000 80,500 75,000 230,500
Evans, Everett 45,000 70,500 85,100 200,600
Region 2 Fountain, Fran 35,000 37,500 78,520 875,020 151,020
Garner, Gabe 27,500 81,000 70,500 179,000
Henry, Herb 32,500 45,000 37,500 115,000
Ingram, Ian 80,500 82,500 81,000 244,000
Jackson, Jake 70,500 70,500 45,000 186,000
Region 3 King, Karl 25,000 37,500 82,500 1,067,000 145,000
Lemmons, Leslie 81,000 85,000 75,000 241,000
Murdoch, Martin 56,500 60,500 45,000 162,000
Neil, Nelly 94,000 97,000 50,000 341,000
Ormond, Oprah 70,000 75,500 32,500 178,000
Region 4 Pine, Phyllis 48,500 45,500 35,000 804,100 129,000
Quartz, Cody 52,000 52,000 27,500 131,500
Russell, Ryan 51,500 51,500 32,500 135,500
Stevens, Stuart 75,000 52,000 80,500 207,500

Page 36
 Microsoft Office Excel
Tate, Thomas 85,100 45,000 70,500 200,600
Region 5 Unger, Ulysses 78,520 65,000 37,500 920,990 181,020
Varner, Vince 45,850 75,000 81,000 201,850
Webb, Wyatt 75,850 45,000 45,000 165,850
Younts, Yolanda 65,890 35,000 82,500 183,390
Zimmerman, Zack 78,880 27,500 82,500 188,880

Exercise 55
Database Marketing
Sl. No. City/State Quantity Price
1. Mumbai 60 lakh 20,000
2. Delhi 1 crore 30,000
3. Bangalore 60 lakh 20,000
4. Hyderabad 24 lakh 15,000
5. Gujarat 1.5 crore 35,000
6. Maharashtra 1.2 crore 35,000
7. Haryana 90 lakh 30,000
8. Kerala 35 lakh 15,000
9. Andhra Pradesh 2 core 50,000
10. Madhya Pradesh 25 lakh 20,000
11. Tamil Nadu 1 crore 30,000
12. Uttar Pradesh 2.5 crore 60,000
13. Punjab 40 lakh 20,000

Exercise 56
Sl. No. Category City/State Quantity Price
1. All India Addresses Database All India 15 lac addresses 10,000
2. HNI Mumbai Mumbai 2 Lakh ( 95% Accurate ) 8,900
3. Job Seekers All India 4 Lakh ( 95% Accurate ) 10,000
4. CEO Personal Email Ids All India 50,000 ( 90% Accurate ) 4,500
5. Companies in Mumbai Mumbai 6 Lakh ( 85% accurate ) 10,000
6. Companies in Delhi Delhi 5 Lakh ( 85% Accurate ) 10,000
Exercise 57

Family Budget Planner

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec To Avg
t
Income
Wages & Tips 1000 500 1000 2000 500 3500 1300 1200 3200 1800 1000 500
Interest Income 2000 1000 1100 2500 600 3000 1600 1400 3600 2000 1600 700
Dividends 3000 1500 1200 3000 700 2500 1900 1600 4000 2200 2200 900

Page 37
 Microsoft Office Excel
Gifts Received 4000 2000 1300 3500 800 2000 2200 1800 4400 2400 2800 1100
Reimbursement’ 5000 2500 1400 4000 900 1500 2500 2000 4800 2600 3400 1300
s
Savings 6000 3000 1500 4500 1000 1000 2800 2200 5200 2800 4000 1500
Other 7000 3500 1600 5000 1100 500 3100 2400 5600 3000 4600 1700

Total

Exercise 58
Family Budget Planner
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec To Avg
t
Home Expenses
Mortgage / Rent 3500 3500 3500 3500 3500 3500 3500 3500 3500 3500 3500 3500
Electricity 500 550 600 650 700 750 800 850 900 950 1000 1500
Gas / Oil 750 800 850 900 950 1000 750 800 850 900 950 1000
Water / Sewer 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000
Phone 600 610 620 630 640 650 660 670 680 690 700 710
Cable 300 300 300 300 300 300 300 300 300 300 300 300
Internet 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000
Furnishings 350 400 450 500 550 600 650 700 750 800 850 900
Lawn / Garden 300 300 300 300 300 300 300 300 300 300 300 300
Home Supplies 100 200 300 400 500 600 100 200 300 400 500 600
Maintenance 1000 1000 1000 1000 1000 1000 1050 1050 1050 1050 1050 1050
Improvements 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000
Other - - - - - - 2200 3600 - 7500 - -

Total
Exercise 59
Family Budget Planner
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec To Avg
t
Daily Living
Groceries 600 650 600 650 600 650 600 650 600 650 600 650
Personal Supplies 500 500 500 500 500 500 500 500 500 500 500 500
Clothing 3000 3500 3000 3500 3000 3500 3000 3500 3000 3500 3000 3500
Cleaning Services 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000
Dining/Eating out 4000 6000 2000 3000 5000 4000 3000 2000 1000 2000 3000 1000
Dry Cleaning 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000
Salon / Barber 300 300 300 3000 300 300 300 300 300 300 300 300
Other - - - - - - - - - - - -

Total
Exercise 60
Family Budget Planner
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec To Avg
t
Children
Medical 500 1000 1500 2000 2500 3000 500 1000 1500 2000 2500 3000

Page 38
 Microsoft Office Excel
Clothing 4000 4500 5000 5500 6000 6500 4000 4500 5000 5500 6000 6500
School Tuition 8000 8000 8000 8000 8000 8000 8000 8000 8000 8000 8000 8000
School Lunch 3000 3000 3000 3000 3000 3000 3000 3000 3000 3000 3000 3000
School Supplies 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000
Babysitting 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000
Toys / Games 6000 6000 6000 6000 6000 6000 6000 6000 6000 6000 6000 6000
Other - - - - 400 300 200 100 500 - - 600

Total
Exercise 61
Family Budget Planner
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec To Avg
t
Transportation
Vehicle Payment 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000
Fuel 3000 3000 3000 3000 3000 3000 3000 3000 3000 3000 3000 3000
Bus / Taxi 700 700 700 700 700 700 700 700 700 700 700 700
Repairs 300 300 300 300 300 300 300 300 300 300 300 300
Registration 400 400 400 400 400 400 400 400 400 400 400 400
Other - - - - - - - - - - - -

Total
Exercise 62
Family Budget Planner
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec To Avg
t
Health
Doctor / Dentist 700 700 700 700 700 700 700 700 700 700 700 700
Medicine / Drugs 300 300 300 300 300 300 300 300 300 300 300 300
Health Club Dues 200 250 300 350 400 450 200 250 300 350 400 450
Emergency 1000 1500 1000 1500 2000 3500 1000 2500 3000 3200 2500 3000
Other - - - - - - - - - - - -

Total
Exercise 63
Family Budget Planner
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec To Avg
t
Business Expense
Deductible 600 600 600 600 600 600 600 600 600 600 600 600
Non-Deductible 300 300 300 300 300 300 300 300 300 300 300 300
Other - - - - - - - - - - - -
Other - - - - - - - - - - - -

Total

Page 39