Your Perfect Assignment is Just a Click Away
We Write Custom Academic Papers

100% Original, Plagiarism Free, Customized to your instructions!

glass
pen
clip
papers
heaphones

CUNY Brooklyn College Quantitative Analysis Project Worksheet

CUNY Brooklyn College Quantitative Analysis Project Worksheet

In this step, your hard work bears fruit. What does it all mean? Your boss tasked you with providing an analysis of where he ought to focus
his efforts in the next fiscal year. Can you make any projections of how company demographics might affect the viability of the industry?
What are the most numerous product lines? What do the sales numbers tell you?
Please write a 250- to 300-word essay that includes:
?
a one-paragraph narrative summary of your findings, describing patterns of interest,
?
an explanation of the potential relevance of such patterns, and
?
a description of how you would investigate further to determine if your results could be perceived as good or bad for the company.
ht to focus
industry?
pany.
Workforce Profile Analysis
Section 1: Data (Complete all Columns in Data Set)
Format for:
Number
Credit union #
1001
1005
1008
1010
1013
1020
1021
1025
1027
1028
1029
1030
1033
1037
1039
1043
1044
1049
1050
1051
1054
1055
1057
1058
1061
1064
Date
mm/dd/yy
Text
Currency
Currency
Opening date
Primary account type
Home Equity Funding
Auto Loan
Mortgage
Savings Account
Personal Loan
Home Equity Funding
Mortgage
Credit Card
Personal Loan
Credit Card
Health Insurance
Health Insurance
Auto Loan
Savings Account
Health Insurance
Home Equity Funding
Personal Loan
Auto Loan
Auto Loan
Savings Account
Mortgage
Savings Account
Checking Account
Home Equity Funding
Auto Loan
Auto Loan
Total assets
$396,622,512
$797,752,098
$2,187,433,248
$144,226,368
$354,556,488
$612,962,064
$270,424,440
$117,183,924
$410,143,734
$925,452,528
$175,775,886
$93,146,196
$360,565,920
$36,056,592
$91,643,838
$207,325,404
$195,306,540
$820,287,468
$33,051,876
$22,535,370
$99,155,628
$612,962,064
$135,212,220
$1,784,801,304
$252,396,144
$1,261,980,720
Assets per member
7/25/2002
5/3/2000
12/2/2002
9/27/2006
2/27/2013
6/24/2011
3/13/2012
3/14/2010
6/19/2002
8/23/2012
5/5/2010
5/25/1999
9/25/2013
8/21/2001
4/9/2010
5/3/2010
6/22/2011
10/24/2007
5/1/2013
10/10/2012
10/30/2013
8/13/2016
11/15/2000
6/6/2009
12/13/2004
10/12/2006
1069
1078
1080
1087
1089
1090
1093
1094
1096
1098
1102
1103
1104
1105
1107
1108
1109
1116
1118
1121
1122
1123
1124
1130
1131
1136
1143
1148
1149
1154
1156
1159
1161
1164
1165
1167
2/11/2015
3/16/2012
12/3/2014
11/8/2014
2/15/2015
8/5/2006
4/15/2004
11/21/2004
3/7/1999
10/17/2003
11/8/2013
1/8/2016
4/10/2006
3/1/2017
3/15/2001
12/29/2011
1/11/2007
8/4/2006
6/17/2008
4/29/2008
10/3/2007
6/28/2008
3/23/2004
12/3/2014
5/22/2004
12/29/2013
12/17/2004
7/20/2000
8/22/2003
9/10/2008
12/1/2004
7/31/2005
2/3/2010
12/27/2003
7/8/2000
10/10/2005
Checking Account
Checking Account
Auto Loan
Home Equity Funding
Credit Card
Auto Loan
Checking Account
Checking Account
Credit Card
Home Equity Funding
Personal Loan
Auto Loan
Personal Loan
Credit Card
Personal Loan
CD
Auto Loan
Mortgage
Health Insurance
Credit Card
Health Insurance
Checking Account
Student Loan
Health Insurance
Retirement Plan
Auto Loan
Auto Loan
Auto Loan
Checking Account
Savings Account
Mortgage
Savings Account
Health Insurance
CD
Auto Loan
Mortgage
$63,099,036
$202,818,330
$522,820,584
$9,014,148
$730,145,988
$66,103,752
$157,747,590
$662,539,878
$45,070,740
$108,169,776
$408,641,376
$1,298,037,312
$108,169,776
$129,202,788
$865,358,208
$112,676,850
$360,565,920
$1,135,782,648
$222,348,984
$432,679,104
$265,917,366
$1,604,518,344
$1,460,291,976
$138,216,936
$252,396,144
$67,606,110
$347,044,698
$3,004,716
$324,509,328
$905,921,874
$139,719,294
$1,099,726,056
$139,719,294
$294,462,168
$276,433,872
$306,481,032
1170
1179
1181
1182
1183
1189
1196
1200
1204
1207
1208
1213
1215
1216
1218
1219
1220
1222
1223
1224
1226
1229
1232
1239
1243
1244
1246
1247
1249
1253
1258
1259
1260
1262
1263
1266
9/25/2013
5/28/2007
10/10/2010
11/28/2012
12/9/2007
5/31/2010
1/18/2011
1/24/2011
9/30/1999
1/10/2011
5/11/2012
12/17/2005
6/13/2003
12/27/2010
7/31/2002
10/11/2004
12/10/2005
2/8/2006
2/5/2014
3/21/1999
8/8/2005
9/24/2015
11/30/2001
11/16/2011
4/2/2003
1/9/2004
2/8/1999
7/25/2009
8/8/2003
1/31/2002
5/3/2008
2/23/2015
9/30/2010
12/1/2005
10/9/2011
4/12/2008
Checking Account
Checking Account
Credit Card
Credit Card
Checking Account
CD
Savings Account
Checking Account
Auto Loan
Savings Account
Savings Account
Retirement Plan
Personal Loan
Savings Account
Auto Loan
Auto Loan
Business Banking
Checking Account
Checking Account
Credit Card
Credit Card
Personal Loan
Auto Loan
Credit Card
Checking Account
Retirement Plan
Home Equity Funding
Personal Loan
Credit Card
Savings Account
Savings Account
Savings Account
Savings Account
Checking Account
Mortgage
Retirement Plan
$27,042,444
$384,603,648
$1,478,320,272
$24,037,728
$420,660,240
$973,527,984
$865,358,208
$383,101,290
$105,165,060
$630,990,360
$829,301,616
$486,763,992
$1,502,358
$667,046,952
$153,240,516
$336,528,192
$301,973,958
$108,169,776
$441,693,252
$234,367,848
$58,591,962
$27,042,444
$387,608,364
$27,042,444
$437,186,178
$6,009,432
$1,230,431,202
$40,563,666
$99,155,628
$581,412,546
$279,438,588
$126,198,072
$306,481,032
$79,624,974
$216,339,552
$216,339,552
1271
1272
1273
1275
1277
1278
1281
1284
1285
1286
1287
1292
1294
1301
1303
1309
1314
1315
1317
1318
1321
1323
1326
1328
1330
1331
1332
1337
1340
1342
1344
1345
1346
1347
1349
1350
9/14/2005
12/16/2012
12/7/2002
6/21/2001
5/22/2003
5/3/2001
7/25/2010
2/16/2004
5/31/2003
12/27/2012
3/11/2004
9/3/2005
10/23/2016
9/21/2008
2/15/2003
5/25/2005
10/15/2000
11/5/2010
4/8/2008
3/3/2002
2/21/2014
9/21/1999
8/6/2001
5/9/2011
3/19/2017
3/21/2013
12/2/2006
8/15/2007
12/25/2009
10/18/2015
10/14/2013
5/25/2004
12/3/2006
6/24/2004
1/14/2017
11/14/2005
Credit Card
Savings Account
Credit Card
Personal Loan
Savings Account
Credit Card
Credit Card
CD
Checking Account
Checking Account
Business Banking
Auto Loan
Savings Account
Checking Account
Savings Account
CD
Home Equity Funding
Auto Loan
Checking Account
Student Loan
Checking Account
Retirement Plan
Retirement Plan
Home Equity Funding
Credit Card
Checking Account
Student Loan
Savings Account
Savings Account
Home Equity Funding
Personal Loan
Checking Account
CD
Savings Account
Savings Account
Savings Account
$630,990,360
$310,988,106
$12,018,864
$270,424,440
$558,877,176
$180,282,960
$351,551,772
$252,396,144
$180,282,960
$450,707,400
$283,945,662
$336,528,192
$292,959,810
$202,818,330
$892,400,652
$739,160,136
$333,523,476
$811,273,320
$585,919,620
$180,282,960
$135,212,220
$743,667,210
$1,370,150,496
$919,443,096
$540,848,880
$180,282,960
$636,999,792
$105,165,060
$1,153,810,944
$103,662,702
$392,115,438
$144,226,368
$1,045,641,168
$667,046,952
$757,188,432
$90,141,480
1352
1353
1360
1366
1367
1375
1377
1379
1381
1385
1386
1392
1393
1394
1400
1405
1406
1407
1408
1409
1413
1414
1416
1420
1422
1425
1426
1429
1430
1432
1434
1437
1440
1442
1444
1448
4/2/2005
4/11/2007
3/30/2005
11/10/2005
6/18/2006
2/14/2004
4/12/2006
11/12/2011
5/7/2015
3/24/2013
3/10/2010
8/31/2008
4/14/2008
11/30/2009
8/4/2007
2/18/2003
6/1/2003
4/11/2015
2/13/2011
2/5/2015
1/30/2010
4/27/2009
6/30/2001
9/13/1999
4/29/2003
9/26/2009
7/10/2000
9/23/2001
2/11/2005
5/19/2000
6/9/2013
12/4/2011
12/1/2006
4/24/2016
11/20/2006
6/15/2010
Home Equity Funding
Credit Card
Home Equity Funding
Retirement Plan
Checking Account
Checking Account
Checking Account
Checking Account
Auto Loan
Checking Account
Credit Card
Checking Account
Checking Account
Mortgage
Home Equity Funding
CD
Savings Account
Health Insurance
CD
Checking Account
Mortgage
Credit Card
Mortgage
Savings Account
Credit Card
Checking Account
Mortgage
Checking Account
Home Equity Funding
Checking Account
Credit Card
Retirement Plan
Business Banking
Personal Loan
Checking Account
Auto Loan
$396,622,512
$36,056,592
$567,891,324
$1,135,782,648
$162,254,664
$745,169,568
$793,245,024
$685,075,248
$36,056,592
$279,438,588
$745,169,568
$139,719,294
$297,466,884
$432,679,104
$45,070,740
$351,551,772
$396,622,512
$40,563,666
$865,358,208
$297,466,884
$829,301,616
$662,539,878
$144,226,368
$207,325,404
$1,802,829,600
$106,667,418
$919,443,096
$991,556,280
$207,325,404
$36,056,592
$153,240,516
$189,297,108
$229,860,774
$473,242,770
$54,084,888
$9,014,148
1449
1450
1451
1454
1456
1458
1459
1462
1467
1472
1473
1476
1481
1483
1484
1485
1489
1490
1492
1494
1499
1500
1501
1505
1509
1513
1516
1517
1518
1521
1522
1523
1526
1535
1536
1538
5/15/2007
12/25/2006
1/31/2000
10/6/2008
4/20/2015
10/21/2005
3/17/2002
2/5/1999
12/25/1999
5/23/2009
5/11/2010
8/7/2000
12/2/2006
5/23/2011
5/11/2017
12/10/2006
7/4/2014
3/29/2009
2/23/2003
1/24/2012
7/5/2001
1/1/2009
7/6/2016
12/28/2013
1/20/2014
9/28/2011
3/14/2017
6/22/2009
1/29/2015
10/6/2016
6/11/2009
10/30/2002
8/29/2008
4/22/2009
6/16/1999
1/28/2014
Checking Account
Auto Loan
Credit Card
Checking Account
Savings Account
Checking Account
Health Insurance
Mortgage
Mortgage
Home Equity Funding
Mortgage
Checking Account
Credit Card
Auto Loan
Mortgage
Credit Card
Credit Card
Auto Loan
Retirement Plan
Health Insurance
Savings Account
Business Banking
Auto Loan
Credit Card
Savings Account
Credit Card
Checking Account
Savings Account
Savings Account
Savings Account
Savings Account
Health Insurance
Auto Loan
Checking Account
Auto Loan
Checking Account
$63,099,036
$820,287,468
$342,537,624
$301,973,958
$90,141,480
$225,353,700
$252,396,144
$468,735,696
$333,523,476
$1,325,079,756
$157,747,590
$144,226,368
$67,606,110
$99,155,628
$139,719,294
$55,587,246
$297,466,884
$228,358,416
$594,933,768
$378,594,216
$174,273,528
$1,201,886,400
$51,080,172
$76,620,258
$162,254,664
$4,507,074
$15,023,580
$18,028,296
$1,099,726,056
$676,061,100
$6,009,432
$130,705,146
$878,879,430
$811,273,320
$811,273,320
$297,466,884
1539
1542
1545
1547
1551
1552
1553
1554
1557
1558
1559
1561
1563
1564
1569
1570
1571
1575
1577
1579
1580
1582
1583
1587
1589
1594
1604
1605
1608
1609
1610
1611
1612
1615
1616
1619
5/30/2007
3/31/2003
1/1/2009
2/28/2017
8/17/2015
6/17/2005
5/6/2007
7/19/2001
7/25/2016
4/22/2016
1/28/2012
12/15/2015
1/7/2000
1/29/1999
5/15/2013
3/23/2001
9/25/2004
3/29/2009
7/22/2001
12/2/1999
1/24/2008
3/16/2011
10/4/1999
3/2/2004
8/20/2007
4/15/2015
5/3/2004
10/15/2003
1/17/2015
3/5/2006
6/30/2000
6/13/2003
11/5/2010
9/23/2014
6/21/2011
1/23/2017
CD
Checking Account
CD
Checking Account
Personal Loan
Savings Account
Savings Account
CD
Savings Account
Checking Account
Retirement Plan
Mortgage
Health Insurance
Credit Card
Credit Card
Savings Account
CD
Credit Card
Credit Card
Credit Card
Credit Card
Savings Account
Checking Account
Checking Account
Savings Account
Auto Loan
Savings Account
Retirement Plan
Health Insurance
Student Loan
Health Insurance
Health Insurance
Checking Account
Business Banking
Personal Loan
Auto Loan
$534,839,448
$108,169,776
$60,094,320
$378,594,216
$18,028,296
$36,056,592
$171,268,812
$39,061,308
$30,047,160
$288,452,736
$1,243,952,424
$252,396,144
$270,424,440
$129,202,788
$847,329,912
$85,634,406
$76,620,258
$162,254,664
$829,301,616
$211,832,478
$1,162,825,092
$249,391,428
$1,149,303,870
$72,113,184
$486,763,992
$24,037,728
$351,551,772
$387,608,364
$667,046,952
$419,157,882
$414,650,808
$1,027,612,872
$603,947,916
$594,933,768
$1,298,037,312
$93,146,196
1623
1624
1626
1627
1628
1632
1637
1640
1642
1643
1651
1653
1668
1681
1684
1688
1692
1694
1701
1704
1707
1708
1709
1710
1719
1721
1727
1728
1730
1732
1736
1737
1740
1743
1746
1751
10/4/2008
8/28/2009
8/8/2012
3/11/2005
12/4/2012
6/28/2016
12/19/2000
3/6/2010
12/7/2002
3/21/2014
2/29/2008
3/27/2004
5/23/2002
11/16/2014
12/19/2007
2/6/2006
11/13/2000
2/12/2017
1/18/2017
4/24/2015
3/11/2005
1/20/2014
9/12/2011
5/2/2016
12/22/2005
4/4/2008
8/24/2015
6/24/2006
9/24/2007
2/26/2017
5/20/2006
8/12/2007
11/21/2006
8/20/2008
3/5/2003
11/20/2010
Personal Loan
Savings Account
Health Insurance
Credit Card
Health Insurance
Retirement Plan
Health Insurance
Home Equity Funding
Retirement Plan
Home Equity Funding
Auto Loan
Checking Account
Auto Loan
Savings Account
Savings Account
Savings Account
Checking Account
Student Loan
Savings Account
Checking Account
Auto Loan
Savings Account
Checking Account
Business Banking
Auto Loan
Checking Account
Auto Loan
Savings Account
Savings Account
Personal Loan
Savings Account
Health Insurance
Mortgage
Checking Account
Mortgage
Savings Account
$24,037,728
$441,693,252
$534,839,448
$1,730,716,416
$99,155,628
$63,099,036
$1,162,825,092
$54,084,888
$27,042,444
$432,679,104
$54,084,888
$1,149,303,870
$24,037,728
$739,160,136
$1,640,574,936
$21,033,012
$1,388,178,792
$297,466,884
$1,243,952,424
$297,466,884
$1,243,952,424
$9,014,148
$1,257,473,646
$40,563,666
$1,099,726,056
$1,045,641,168
$105,165,060
$865,358,208
$1,162,825,092
$180,282,960
$288,452,736
$384,603,648
$600,943,200
$238,874,922
$333,523,476
$1,135,782,648
1753
1754
1757
1758
1759
1760
1764
1765
1772
1782
1785
1787
1792
1793
1794
1795
1797
1798
1799
1803
1805
1810
1812
1814
1816
1820
1822
1827
1828
1829
1831
1832
1836
1837
1840
1844
2/6/2004
10/16/2004
9/20/2004
1/7/2003
6/29/2002
7/1/2016
1/2/2006
12/27/2011
4/19/2014
5/24/2017
1/19/1999
5/8/2012
11/14/2015
8/29/1999
6/29/2015
5/13/2015
5/25/2017
6/6/2003
12/18/2007
9/18/1999
8/20/2013
12/31/2004
9/5/2013
4/4/2003
1/20/2001
11/6/2003
7/14/2002
7/16/2010
7/18/2012
4/6/2002
9/3/2012
6/10/2002
8/11/2006
10/19/2012
4/25/2001
7/26/2001
Health Insurance
Checking Account
Mortgage
Checking Account
Auto Loan
Business Banking
Auto Loan
Credit Card
Mortgage
Savings Account
CD
Savings Account
Business Banking
Student Loan
Checking Account
Credit Card
Credit Card
CD
Mortgage
Savings Account
CD
Mortgage
Mortgage
CD
Personal Loan
Checking Account
Health Insurance
Mortgage
Checking Account
Savings Account
Business Banking
Home Equity Funding
Health Insurance
Credit Card
Savings Account
Savings Account
$396,622,512
$630,990,360
$100,657,986
$106,667,418
$282,443,304
$378,594,216
$84,132,048
$444,697,968
$838,315,764
$324,509,328
$36,056,592
$365,072,994
$216,339,552
$829,301,616
$81,127,332
$310,988,106
$581,412,546
$793,245,024
$27,042,444
$1,000,570,428
$148,733,442
$157,747,590
$100,657,986
$229,860,774
$955,499,688
$621,976,212
$582,914,904
$90,141,480
$16,525,938
$991,556,280
$247,889,070
$847,329,912
$878,879,430
$1,370,150,496
$342,537,624
$18,028,296
1850
1853
1856
1857
1859
1860
1867
1870
1874
1876
1878
1880
1881
1882
1884
1885
1888
1889
1892
1893
1894
1895
1897
1898
1899
1903
1905
1906
1910
1911
1918
1920
1923
1924
1930
1935
2/25/2006
6/30/2003
8/16/2010
8/25/2012
6/16/2000
12/17/2015
9/2/2002
3/11/2002
1/25/2008
8/5/2008
10/5/2003
8/24/2003
12/10/2006
12/11/2013
2/26/2007
7/11/2005
5/4/2016
11/9/2000
5/19/2017
4/21/2009
10/24/2001
11/6/2008
6/6/2003
12/5/2016
10/17/2006
11/19/2014
11/29/2009
8/6/2002
10/10/2016
5/30/2007
10/6/2007
5/26/2017
6/20/2005
7/29/2004
12/31/2001
1/9/2010
Home Equity Funding
Mortgage
Checking Account
Credit Card
Auto Loan
Mortgage
Savings Account
Retirement Plan
Credit Card
Checking Account
Mortgage
Auto Loan
Savings Account
Mortgage
Credit Card
Savings Account
Personal Loan
Savings Account
Auto Loan
Checking Account
Credit Card
Checking Account
CD
Savings Account
Savings Account
Auto Loan
Auto Loan
Checking Account
Checking Account
Credit Card
Checking Account
Home Equity Funding
Mortgage
Savings Account
Retirement Plan
Health Insurance
$585,919,620
$274,931,514
$120,188,640
$216,339,552
$1,081,697,760
$1,041,134,094
$356,058,846
$378,594,216
$297,466,884
$130,705,146
$384,603,648
$932,964,318
$378,594,216
$36,056,592
$739,160,136
$283,945,662
$540,848,880
$54,084,888
$51,080,172
$739,160,136
$865,358,208
$378,594,216
$126,198,072
$477,749,844
$141,221,652
$4,507,074
$274,931,514
$820,287,468
$229,860,774
$16,525,938
$396,622,512
$22,535,370
$54,084,888
$1,153,810,944
$528,830,016
$283,945,662
1936
1938
1939
1940
1945
1947
1948
1959
1960
1963
1964
1973
1975
1978
1979
1980
1983
1984
1986
1995
1996
1999
12/19/2010
12/4/2007
5/10/2014
3/22/2016
2/12/2002
5/25/2011
10/30/2008
8/4/2005
9/1/2012
7/5/2011
3/19/2015
3/1/2002
3/30/2001
2/27/1999
7/11/2013
3/8/2000
2/13/2013
12/9/2004
5/16/2012
8/15/2001
9/23/2001
11/22/2003
Personal Loan
CD
Savings Account
Savings Account
Savings Account
Business Banking
Savings Account
Checking Account
Credit Card
Savings Account
Health Insurance
Business Banking
Mortgage
Checking Account
Student Loan
Savings Account
Home Equity Funding
Checking Account
Savings Account
Savings Account
Home Equity Funding
Home Equity Funding
$946,485,540
$1,027,612,872
$87,136,764
$1,325,079,756
$54,084,888
$504,792,288
$829,301,616
$419,157,882
$81,127,332
$480,754,560
$202,818,330
$564,886,608
$333,523,476
$1,189,867,536
$256,903,218
$54,084,888
$234,367,848
$82,629,690
$84,132,048
$378,594,216
$63,099,036
$955,499,688
Section 2: Category Keys and Summary Tables (Complete Summary Tables for Graphing)
Account type
Savings Account
Checking Account
Retirement Plan
Health Insurance
Mortgage
Auto Loan
CD
#
Credit Card
Business Banking
Student Loan
Personal Loan
Home Equity Funding
TOTAL
Section 3: Data Analysis (Complete Descriptive Statistics)
Descriptive Statistics
Mean
Median
Mode
Std Deviation
Total assets
Assets per member
Variance
Kurtosis
Skewness
Max
Min
Range
Sum
Count
Number
General
Number
Number
Number
Year Ending
12/31/2017
Years of
Mature Yr
10
Directors
2
3
4
4
4
2
1
1
3
2
3
2
3
1
1
3
2
2
2
3
2
2
2
4
4
4
Member count
13500
13500
6000
13500
2250
14625
9563
1688
9563
16500
21938
5250
5625
10125
375
18000
3375
7875
4875
563
2250
20250
12375
9000
36000
22500
Mature
Data
4
3
4
3
1
3
3
3
1
4
3
1
1
3
1
2
1
3
1
1
3
3
3
3
3
3
Number
Number
Gender of Marital status of CEO
2
1
1
2
1
1
1
2
1
2
2
1
1
2
1
1
1
1
1
1
1
1
1
1
1
2
2
2
1
1
1
1
1
1
1
1
1
2
1
1
2
1
1
2
1
1
1
2
1
1
1
2
Text
General
State
FL
NE
SD
LA
SD
SD
SD
SD
CT
SD
NE
SD
CT
CT
NE
SD
NE
LA
SD
LA
CT
FL
SD
NE
SD
NE
Region
2
3
2
2
3
2
1
3
1
1
4
4
2
2
2
1
1
3
1
4
1
4
4
2
2
1
3
1
3
3
1
3
3
2
4
1
3375
27000
4500
11250
13500
3750
2250
16875
5625
938
12750
20250
10125
375
21375
3938
5625
16875
5625
36000
8438
13500
15750
7125
4500
375
10125
563
1688
28688
3563
38250
9000
6750
7500
5625
3
3
3
3
3
1
3
3
3
1
1
3
3
1
3
3
3
3
2
3
3
3
3
1
1
1
1
1
1
3
1
4
1
1
1
3
1
2
1
2
1
1
1
1
1
1
1
1
1
1
1
1
1
2
1
2
1
1
1
1
1
1
1
1
1
1
1
1
1
2
1
1
2
2
2
1
1
1
2
1
1
2
2
1
1
1
2
2
1
2
1
2
1
1
1
2
1
1
1
2
2
2
2
2
2
2
1
2
NE
FL
TX
CT
TX
SD
TX
FL
SD
CT
TX
NE
FL
LA
TX
NE
SD
TX
SD
FL
SD
NE
CT
CT
FL
NE
LA
FL
SD
TX
FL
FL
FL
FL
CT
FL
1
4
4
2
2
3
2
1
2
4
4
4
1
3
2
4
1
4
3
1
1
3
1
2
1
1
3
3
2
3
2
1
4
1
4
2
375
51000
4500
4500
9000
13500
7875
7313
4875
24750
33750
11250
1875
4500
18000
28500
7313
2250
3938
8438
10688
21938
7875
9000
4500
10500
16875
30375
6375
13500
5625
1125
18000
563
10500
14625
1
4
3
1
4
3
3
3
1
3
3
1
1
2
3
2
3
1
1
3
3
3
3
3
3
4
3
3
1
3
3
1
3
1
1
3
2
1
1
1
1
1
1
1
1
2
1
1
2
1
1
1
2
1
1
1
1
1
2
2
2
1
1
2
1
2
2
2
1
1
2
1
2
2
2
2
2
2
1
2
2
2
1
1
1
1
1
1
1
1
2
1
1
2
2
2
1
1
1
1
2
2
2
2
1
2
1
1
NE
TX
LA
FL
CT
SD
NE
LA
FL
SD
NE
TX
CT
SD
TX
FL
FL
NE
TX
FL
LA
SD
NE
CT
SD
NE
LA
FL
FL
LA
TX
CT
NE
TX
TX
LA
4
3
1
2
3
2
2
3
2
2
3
4
3
3
3
4
2
3
2
1
3
3
4
3
3
4
2
2
4
1
1
1
4
4
2
2
31500
10125
5250
6000
9000
5250
14625
18000
9750
20250
1688
11250
9563
30375
10125
22500
12375
8438
7875
1500
4500
30375
39000
10125
11813
27000
15000
750
15750
5063
6750
2250
29250
24750
16875
2625
3
1
2
2
4
2
3
2
2
3
3
1
1
3
3
3
3
3
3
4
1
3
4
3
3
3
4
1
3
3
3
3
3
3
3
1
1
1
2
1
1
1
1
1
1
1
2
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
2
1
1
1
2
2
1
2
2
2
1
1
2
1
1
1
2
2
1
2
2
2
2
1
1
2
2
1
1
1
1
1
1
2
1
1
1
1
1
2
2
2
LA
CT
NE
SD
FL
CT
FL
CT
LA
LA
CT
FL
LA
SD
LA
NE
TX
FL
LA
SD
TX
SD
CT
TX
TX
FL
TX
LA
CT
TX
CT
NE
NE
FL
LA
CT
4
1
2
3
2
4
4
2
4
2
4
3
1
2
1
1
4
3
3
3
4
3
2
3
4
1
3
4
2
1
1
3
3
3
3
2
9000
3375
18000
21938
18000
10500
40500
10125
13500
10125
13500
7313
7313
19125
9000
1688
1500
10688
11250
21938
6750
25313
11250
4500
33750
2438
21938
20250
14625
6188
5625
32063
3375
30375
6750
11250
3
3
3
3
3
2
3
3
3
3
2
1
3
3
3
3
2
1
4
3
3
3
3
1
3
1
3
3
3
3
3
3
3
3
1
3
1
1
1
1
1
1
1
1
1
1
1
1
2
1
1
1
1
1
2
1
1
1
1
1
1
2
1
1
1
1
1
1
1
2
1
1
2
2
2
2
2
1
2
1
1
1
1
2
1
1
2
2
2
2
2
1
2
2
1
1
1
1
1
1
1
2
2
1
2
1
1
2
SD
FL
TX
TX
FL
NE
CT
TX
LA
CT
TX
SD
TX
FL
LA
NE
SD
TX
SD
LA
TX
SD
SD
CT
CT
TX
CT
NE
TX
TX
LA
LA
CT
TX
FL
NE
3
2
2
1
2
2
1
2
2
3
1
3
3
1
3
1
2
2
2
3
1
4
2
3
1
3
2
4
4
3
1
1
3
2
3
3
10688
6750
20250
4500
3375
5250
7875
13500
5625
11813
6188
2813
10688
3375
3938
938
19125
375
13500
1688
11250
13500
4125
5625
1688
5063
1875
11250
33750
6750
3375
563
27000
15750
27000
1688
1
3
3
3
3
1
3
3
3
3
3
1
1
1
1
1
3
1
3
3
4
2
1
1
3
1
1
3
3
3
1
3
3
3
4
3
1
1
2
1
1
2
1
1
1
1
1
1
1
1
1
2
1
1
1
1
1
1
2
1
1
2
2
1
1
1
1
1
1
1
2
1
2
1
1
1
2
1
2
1
2
2
1
2
1
2
2
2
1
2
1
2
2
1
1
2
1
2
2
1
2
1
2
1
1
2
2
1
SD
NE
LA
CT
SD
CT
LA
CT
TX
NE
LA
NE
CT
LA
LA
LA
LA
TX
NE
CT
CT
SD
TX
SD
FL
TX
FL
TX
NE
SD
LA
NE
LA
CT
FL
LA
1
3
1
4
3
1
3
1
2
4
4
1
1
1
4
3
1
3
4
1
3
2
3
2
4
2
3
1
4
1
4
4
2
2
4
1
2250
14625
3375
27000
1125
563
9563
3375
3000
31500
36000
7313
563
938
24750
1125
10125
9000
15750
563
30375
3375
30375
25500
3750
12750
13500
8438
27000
563
20250
33750
6750
21375
2250
375
4
2
2
3
1
3
1
1
2
3
3
3
3
1
3
1
3
4
3
3
3
1
3
4
1
2
3
3
3
3
3
3
3
3
3
1
1
1
1
1
1
1
1
1
2
1
1
2
2
2
1
1
1
1
1
1
1
1
2
1
1
2
2
1
1
2
1
2
2
1
2
1
2
1
2
1
2
1
2
2
1
1
1
1
1
1
2
2
2
2
1
2
2
1
1
2
2
1
2
2
2
2
1
1
2
1
2
2
NE
LA
LA
TX
NE
LA
CT
TX
SD
SD
FL
TX
TX
FL
LA
NE
TX
TX
LA
CT
LA
LA
TX
LA
TX
LA
NE
FL
TX
TX
NE
CT
LA
FL
NE
NE
4
2
4
4
3
3
3
1
3
3
3
3
1
4
4
2
4
1
4
1
4
1
3
1
3
3
1
4
3
4
2
1
4
1
2
4
27000
13500
9000
4500
9000
563
30375
1313
5063
5063
6750
20250
5625
29250
33750
3375
36000
10125
40500
5625
38250
2063
6750
10688
13500
36000
3000
29250
15188
31500
15750
1500
1500
2813
1125
6750
4
3
1
3
2
1
3
1
3
1
1
3
2
3
3
1
3
3
3
3
3
1
3
3
4
4
1
3
3
3
3
4
2
3
3
3
1
1
1
1
1
1
1
1
1
1
1
2
1
1
1
1
1
2
1
1
1
1
1
2
1
1
1
1
1
1
1
1
1
1
2
1
2
1
2
2
1
1
1
2
1
1
2
2
2
2
2
1
2
2
2
1
2
1
2
1
2
1
1
1
1
2
2
2
1
1
2
2
FL
NE
FL
NE
NE
NE
NE
FL
SD
CT
CT
NE
TX
NE
NE
TX
NE
TX
CT
CT
SD
CT
LA
LA
TX
LA
TX
FL
NE
CT
FL
FL
TX
TX
TX
TX
1
2
1
1
1
1
2
4
3
1
2
1
4
4
2
1
3
4
1
3
1
1
1
1
4
2
1
3
1
4
1
4
3
4
1
4
8438
11250
1875
563
750
1125
2625
12750
1688
6188
18000
6188
4500
15750
10125
7313
30375
42750
1313
18563
5625
6750
188
2250
22500
3375
9000
9563
1688
18000
7313
42750
15188
33750
9000
27000
3
3
1
1
2
3
1
1
3
3
3
3
1
3
3
3
3
3
1
3
3
3
1
3
3
3
4
1
1
3
3
3
3
3
3
3
1
1
1
2
1
1
1
1
1
1
2
2
1
1
1
1
1
1
1
1
1
1
1
1
1
2
1
1
1
2
1
1
2
1
1
1
1
2
2
2
1
1
1
1
2
2
2
2
1
1
1
2
1
2
2
1
2
2
1
2
1
2
2
1
1
2
1
1
1
2
1
2
NE
FL
LA
SD
TX
NE
SD
CT
SD
LA
LA
CT
TX
SD
TX
LA
SD
SD
NE
FL
LA
NE
TX
TX
NE
NE
NE
TX
TX
NE
SD
TX
CT
SD
NE
NE
3
3
2
1
2
3
1
4
3
1
4
3
3
1
3
3
4
2
1
4
4
3
2
2
1
3
1
2
1
1
2
1
3
4
2
1
6750
2813
6750
1688
9000
3375
2813
11250
1688
9000
7500
23625
16875
7500
16875
16875
24750
7875
3375
15750
10500
21938
16875
21375
1500
2813
6188
1125
9000
3188
4500
5625
1688
20250
16500
10688
2
1
1
3
4
3
3
1
3
3
1
3
3
4
2
3
3
3
1
3
2
3
3
3
1
1
3
3
3
1
3
3
3
3
4
3
2
1
1
1
2
1
1
2
1
1
1
1
1
2
1
1
2
1
2
1
1
1
1
2
2
2
2
1
1
2
1
2
1
1
2
1
1
1
1
2
2
2
1
1
1
2
2
2
1
2
1
1
2
2
2
2
2
2
1
1
1
1
1
1
2
2
1
2
1
2
2
2
SD
TX
NE
NE
SD
TX
LA
TX
SD
SD
NE
NE
NE
FL
CT
TX
SD
FL
TX
LA
SD
FL
FL
SD
TX
CT
LA
SD
TX
TX
FL
FL
NE
TX
FL
NE
3
3
2
3
1
4
4
1
1
4
3
4
1
4
3
4
4
1
4
4
2
4
28688
8438
3000
21938
7875
22500
9000
7875
1688
3000
5063
13500
10125
4500
4500
18000
18000
563
1500
24750
14625
24750
3
3
1
3
3
3
3
3
3
1
3
1
3
3
1
3
3
1
1
3
3
3
# of Directors
1
2
3
4
#
%
#
%
Small
Medium
Large
Mega
TOTAL
Gender
1
1
1
1
1
1
1
1
1
2
1
1
1
1
1
1
1
1
1
1
1
1
1
1
2
1
1
2
2
2
2
2
1
2
1
1
2
2
2
1
1
1
2
1
Savings account Summary
Yr
Saving deposits
2001
$54,256
2002
$61,050
2003
$77,564
2004
$77,680
2005
$85,022
2006
$143,775
2007
$229,236
2008
$469,052
LA
LA
NE
SD
LA
CT
NE
CT
FL
NE
CT
NE
TX
FL
LA
SD
SD
SD
SD
TX
TX
FL
1
2
Male
Female
Number of Data Anayst on staff
#
%
1
2
3
4
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
$754,091
$1,333,581
$2,758,925
$6,661,460
$9,521,546
$25,364,593
$78,569,990
$125,000,045
TOTAL
1
2
State
CT
FL
LA
NE
SD
TX
Years of service
Marital Status of CEOs
#
Married
Single
TOTAL
Regions Summary
Region
#
N-West
South
West
North
Central
East
TOTAL
Directors
Member count
%
%
Frequency Table by Total Assets
Bin Range
Labels
1000
Up to 1000
6000
1000 – 6000
11000
6000-11000
16000
21000
26000
31000
36000
41000
46000
51000
56000
61000
66000
71000
76000
81000
86000
91000
96000
16000-21000
21000-26000
26000-31000
31000-36000
36000-41000
41000-46000
46000-51000
51000-56000
56000-51000
51000-66000
66000-71000
71000-76000
76000-81000
81000-86000
86000-91000
91000-96000
96000+
In Section 1 on the Data tab, complete each blank column of the spreadsheet to arrive at the desired
calculations. Use Excel formulas to demonstrate that you can perform the calculations. Remember, a cell
address is the combination of a column and a row. For example, C11 refers to Column C, Row 11 in a
spreadsheet.
Reminder: Occasionally in Excel, you will create an unintentional circular reference. This means that
within a formula in a cell, you directly or indirectly referred back to the cell. For example, while entering
a formula in A3, you enter =A1+A2+A3. This is not correct and will result in an error. Excel allows you to
remove or allow these references.
Hint: Another helpful feature in Excel is Paste Special. Mastering this feature allows you to copy and
paste all elements of a cell, or just select elements like the formula, the value, or the formatting.
Ready to Begin?
1. To calculate assets per member, you will divide the total assets by the member count using cell
referencing. In cell E11, enter =D11/H11, which will calculate the assets per member by dividing
D11, total assets for the first listed credit union, by H11, which contains the member count for
that credit union. Once you have the answer displayed, you will then copy this formula down
the column. Click the cell with the formula to be copied, move your cursor to the bottom right of
the cell until it becomes a plus sign, then click and drag to the bottom of the table. This will copy
the formula and Excel will increment the references so that you only need to type the formula
once.
2. In Column F, calculate the number of years each credit union has been open by creating a
formula that incorporates the date in cell F9 and demonstrates your understanding of relative
and absolute cells in Excel. You will need a formula that can compute absolute values to
determine years of service. You could do this longhand, but it would take you a long time. So, try
the YEARFRAC formula, which computes the number of years (and even rounds for you). Once
you start the formula in Excel, the element will appear to guide you. You need to know the
ending date (F9) and the opening date (B11). The formula looks like this: =YEARFRAC($F$9,B11),
and the $ will repeat the formula calculation down the column if you grab the edge of the cell
and drag it to the bottom of the column, as above.
3. To determine if a credit union is mature, use an IF statement in Column I to flag with a “Yes” any
credit union in business for 10 years or more. Here is how an IF statement works: =IF(X is
greater (or less than) Y, “Answer”, IF not, “Answer”). Expressed as a formula, the IF statement
would look like this: =IF(F11>=10,”Yes”,”No”). You can drag this formula down the column or
highlight the starting cell, hold down the shift key, scroll down to cell 382, and release, and the
whole column should compute properly.
4. Using the VLOOKUP function, use the Region Key located at F417:G423 to fill in the cells in
Column N to identify the region in which the employee is located based on the state listed in
Column M. (If this function is new to you, hang in there—this one is worth it.)
There are some video resources available, one listed below, that address common challenges in this
Excel assignment. Do not be confused if you see a data set that is different from yours—the principles
are the same! Remember, if you have any questions, ask.
Source: Used with permission from Microsoft.
You will devise a formula that will match the state to a region (in position 2). We will use the $ function
to enable a repeat of the formula down the column. =VLOOKUP(M11,$F$417:$G$423,2,FALSE
A. Calculate the statistics. You can perform these calculations in one step by highlighting the
adjacent columns of data in D10:H382. Place the output on a new sheet in the workbook. Label
the tab “Excel Summary Stats.”
B. Compare your calculations from the data analysis feature to the results you got in the previous
step, using individual functions.
In this step, things get interesting! You will expand your analysis by employing descriptive statistics,
or summary statistics, using Excel formulas. Now you will calculate mean, median, and mode for the
categories of data and derive the deviation, variance, dispersion, and distribution. In Section 3 of the
data sheet, use the appropriate Excel function to complete the table, calculating summary statistics of
Total Assets, Assets per Member, Years of Service, Directors, and Member Count. Use the summary
statistic Excel functions of =AVERAGE, =MEDIAN, = MODE, = STDEV.S, =VAR.S, =KURT, = SKEW, = MIN,
=MAX, =SUM, and =COUNT to derive these statistics for the three data categories. Standard error and
range should also be calculated.
Your data set in Tab 1: DATA should be built when you have completed this step
Create the following graphs in your workbook on a separate tab named “Graphs & Charts”:
A. Create separate pie charts that show the percentage breakdown by (a) gender, (b) number of
data analyst on staff, and (c) marital status. Explore pie chart formats.
B. Create separate bar charts that show the (a) number of directors, and (b) number of credit
unions per state.
C. Create a line graph for the savings account deposits.
Source: Used with permission from Microsoft.
D. Create a histogram that shows the assets per member in incremental assets of $5,000. Show
how many credit unions have assets per member in the following ranges: < $1,000; $1,000– $6,000; $6,000–$11,000; etc., up to $91,000–$96,000, and > $96,000. This process involves
calculating each assets per member, creating what is called a frequency distribution table and
histogram. Histograms seem hard to create and understand, but mastering how to visualize the
frequency of events is very helpful in analysis!
Note: Your Excel data tab has the upper limit and labels already identified in Section 3. Complete the
table and histogram by engaging the Data Analysis Toolpak. Place the output on a new worksheet and
label it “Histogram.
Create a new tab titled “Tab 5: Sorted Data.” When you’re finished, you’ll be ready to conduct your
quantitative analysis.
Here you will use the SUBTOTAL function in Excel to summarize the following:
a. number of members per region
b. total assets per region
A. Right-click the page label with the data.
B. Click Move or Copy.
C. Check the box Create a Copy, then click OK. This will create a duplicate of the page. Relabel the
page “SORT.”
D. Delete all content below the table in Section 1, except the section used as reference for the
VLOOKUP. If you delete this referenced section, you will get an error in your region column.
Source: Used with permission from Microsoft.
E. Click Row 10 (with the table heading), then select the Data tab and click Filter (with the funnel
icon). This will create a dropbox option for each column heading.
F. Sort the data by region by clicking the drop arrow on the region column heading, and then sort
A to Z.
You’re now ready to complete your analysis of the data to share with your boss and upload your
workbook for review! In this step, your hard work bears fruit. What does it all mean? Your boss tasked
you with providing an analysis of where he should focus his efforts in the next fiscal year. Can you make
any projections of how company demographics might affect the viability of the industry? What are the
most numerous product lines? What do the sales numbers tell you?
Please write a 250- to 300-word essay that includes the following:
•
a one-paragraph narrative summary of your findings, describing patterns of interest
•
an explanation of the potential relevance of such patterns
•
a description of how you would investigate further to determine if your results could be
perceived as good or bad for the company
You may find it helpful to create your essay in a Microsoft Word document and copy and paste it into
the text box on the Data Analysis Essay tab.
When you have completed your essay, review the order of tabs:
•
Tab 1: Data Analysis Essay
•
Tab 2: Data Sheet (given)
•
Tab 3: Summary Stats
•
Tab 4: Charts
•
Tab 5: Histogram
•
Tab 6: Sorted Data

Purchase answer to see full
attachment

Order Solution Now