Pension Data Analytics AssignmentThe Excel data file contains two worksheets of data. Data 1...

Question

Pension Data Analytics AssignmentThe Excel data file contains two worksheets of data.  Data 1 contains pension data for public pension systems across different states from 2001 to 2017. Data 2 has capital income data by each state.  Please analyze the data and answer 4 questions below by using functions such as sum, average, if, and pivot table. You must present your answers in a Word file. You should copy and paste tables or graphs from your analysis in Excel into your deliverable file to support your answers. 1) Present a table with averages of the following items for each state: Total Pension Assets, Total Pension Liabilities, Funding Ratios, and Funding Status. · Discuss Illinois’ funding status. How is Illinois compared to other states? 2) Present a table with averages of the following items for each pension plan in Illinois: Total Pension Assets, Total Pension Liabilities, Funding Ratios, and Funding Status (use filter).· How many pension systems does Illinois have? · Which system is the healthiest? 3) Present a table with averages for Total Contribution Made and Total Benefits Paid for each year from 2001 to 2017.· Use graphics (e.g., bar charts, lines etc.) to demonstrate a pattern· Do you identify a trend across the years? Please explain 4) The Income table in Data 2 provides per capital income data by state. Do you think the wealth of the state affects the health of pension system? Please explain data1-Pension Data    ppd_id    Fisal Year    Plan Names    StateAev    StateName    Total Assets    Total Liabilities    Funding Ratio    Funding status (UndeOver Funded)    Total Contribution     Returns     Total Benefits paid    Retirement Age    Service Benefit Age    Total Membership    Total Payroll    Pension Expenses     3    2001    State of Alaska Public Employees’ Retirement System    AK    Alaska    5,579,440    5,528,026    1.01    -51,414    136,141    232,924        65    16    54,902    1,208,700    272432    3    2002    State of Alaska Public Employees’ Retirement System    AK    Alaska    4,611,170    6,133,182    0.75    1,522,012    138,943    195,432        65    17    56,159    1,245,054    292637    3    2003    State of Alaska Public Employees’ Retirement System    AK    Alaska    4,607,673    6,330,541    0.73    1,722,868    148,690    168,595        65    17    58,337    1,300,041    320987    3    2004    State of Alaska Public Employees’ Retirement System    AK    Alaska    4,709,592    6,711,507    0.70    2,001,915    157,468    170,048        65    18    59,149    1,305,670    343455    3    2005    State of Alaska Public Employees’ Retirement System    AK    Alaska    4,658,413    7,087,191    0.66    2,428,778    182,165    164,860        65    18    60,538    1,404,043    371195    3    2006    State of Alaska Public Employees’ Retirement System    AK    Alaska    6,331,065    8,094,043    0.78    1,762,978    234,909    171,344        65    18    62,191    1,590,693    393578    3    2007    State of Alaska Public Employees’ Retirement System    AK    Alaska    6,739,004    8,662,324    0.78    1,923,320    278,562    212,759        66    19    60,757    1,605,819    422783    3    2008    State of Alaska Public Employees’ Retirement System    AK    Alaska    7,210,772    9,154,282    0.79    1,943,510    271,704    411,168        66    19    59,559    1,577,846    460041    3    2009    State of Alaska Public Employees’ Retirement System    AK    Alaska    6,108,528    9,702,086    0.63    3,593,558    311,555    288,657        33    19    59,146    1,585,490    485151    3    2010    State of Alaska Public Employees’ Retirement System    AK    Alaska    6,469,832    10,371,672    0.62    3,901,840    309,683    98,797        67    20    58,932    1,586,697    514744    3    2011    State of Alaska Public Employees’ Retirement System    AK    Alaska    6,762,149    10,919,047    0.62    4,156,898    320,532    135,470        67    20    58,166    1,559,938    543944    3    2012    State of Alaska Public Employees’ Retirement System    AK    Alaska    6,530,421    11,428,944    0.57    4,898,523    426,310    152,813        67    20    57,567    1,522,399    577626    3    2013    State of Alaska Public Employees’ Retirement System    AK    Alaska    6,510,749    11,945,881    0.55    5,435,132    454,871    118,528        68    17    57,050    1,534,665    617367    3    2014    State of Alaska Public Employees’ Retirement System    AK    Alaska    7,731,438    12,947,759    0.60    5,216,321    489,563    132,304                56,576    1,405,198    660140    3    2015    State of Alaska Public Employees’ Retirement System    AK    Alaska    8,931,160    13,337,929    0.67    4,406,769    1,326,172    150,734                56,109    1,412,237    704095    3    2016    State of Alaska Public Employees’ Retirement System    AK    Alaska    9,056,662    13,633,033    0.66    4,576,371    419,970    152,212                55,618    1,322,925    749418    3    2017    State of Alaska Public Employees’ Retirement System    AK    Alaska    9,229,703    13,832,130    0.67    4,602,427    452,109    147,941                55,028    1,247,884    784713    4    2001    State of Alaska Teachers’ Retirement System    AK    Alaska    3,468,310    3,651,488    0.95    183,178    92,204    135,117    217,271    63    31    17,915    496,188    215586    4    2002    State of Alaska Teachers’ Retirement System    AK    Alaska    2,699,445    3,959,958    0.68    1,260,513    91,885    112,193    237,095    63    31    18,277    509,437    227158    4    2003    State of Alaska Teachers’ Retirement System    AK    Alaska    2,694,785    4,190,970    0.64    1,496,185    94,675    93,833    255,193    64    32    18,893    532,630    249493    4    2004    State of Alaska Teachers’ Retirement System    AK    Alaska    2,647,777    4,216,480    0.63    1,568,703    99,997    92,263    267,556    64    32    19,119    522,421    260480    4    2005    State of Alaska Teachers’ Retirement System    AK    Alaska    2,640,642    4,334,585    0.61    1,693,943    109,421    86,301    265,746    64    30    19,502    561,038    274101    4    2006    State of Alaska Teachers’ Retirement System    AK    Alaska    3,296,934    4,859,336    0.68    1,562,402    133,414    95,930    278,528    65    31    19,891    574,409    285517    4    2007    State of Alaska Teachers’ Retirement System    AK    Alaska    3,441,867    5,043,448    0.68    1,601,581    144,121    112,566    305,032    65    33    19,631    554,245    297996    4    2008    State of Alaska Teachers’ Retirement System    AK    Alaska    3,670,086    5,231,654    0.70    1,561,568    196,671    184,446    318,490    66    33    19,430    549,148    313119    4    2009    State of Alaska Teachers’ Retirement System    AK    Alaska    3,115,719    5,463,987    0.57    2,348,268    185,077    127,846    331,136    66    34    19,365    557,026    325307    4    2010    State of Alaska Teachers’ Retirement System    AK    Alaska    3,259,868    6,006,981    0.54    2,747,113    190,829    50,419    342,621    67    34    19,270    564,887    338860    4    2011    State of Alaska Teachers’ Retirement System    AK    Alaska    3,345,949    6,196,104    0.54    2,850,155    197,494    68,683    360,703    67    34    19,171    584,068    348795    4    2012    State of Alaska Teachers’ Retirement System    AK    Alaska    3,194,994    6,399,777    0.50    3,204,783    247,596    76,940    378,425    68    35    19,014    561,971    366686    4    2013    State of Alaska Teachers’ Retirement System    AK    Alaska    3,170,313    6,589,533    0.48    3,419,240    284,518    59,113    396,160            18,963    550,044    385922    4    2014    State of Alaska Teachers’ Retirement System    AK    Alaska    3,771,139    6,921,362    0.55    3,150,223    294,185    65,502    412,835            18,885    514,035    402161    4    2015    State of Alaska Teachers’ Retirement System    AK    Alaska    5,422,651    7,051,724    0.77    1,629,073    1,744,580    86,637    433,863            18,810    490,667    421334    4    2016    State of Alaska Teachers’ Retirement System    AK    Alaska    5,428,687    7,159,788    0.76    1,731,101    166,721    92,261    446,084    70    37    18,724    473,734    440230    4    2017    State of Alaska Teachers’ Retirement System    AK    Alaska    5,476,835    7,217,525    0.76    1,740,690    193,212    88,762    455,499    70    36768    18,631    449,629    451312    184    2001    Anchorage Police and Fire Retirement System    AK    Alaska                    0    11,277    35            825         XXXXXXXXXX    184    2002    Anchorage Police and Fire Retirement System    AK    Alaska                    0    9,203    36            840         XXXXXXXXXX    184    2003    Anchorage Police and Fire Retirement System    AK    Alaska    374,593    361,878    1.04    -12,715    4,711    6,782    37            840         XXXXXXXXXX    184    2004    Anchorage Police and Fire Retirement System    AK    Alaska    395,039    370,296    1.07    -24,743    19    6,789    37            841         XXXXXXXXXX    184    2005    Anchorage Police and Fire Retirement System    AK    Alaska    395,248    371,119    1.07    -24,129    16    7,955    38            818         XXXXXXXXXX    184    2006    Anchorage Police and Fire Retirement System    AK    Alaska    419,854    374,596    1.12    -45,258    0    8,632    39            816         XXXXXXXXXX    184    2007    Anchorage Police and Fire Retirement System    AK    Alaska    413,804    368,990    1.12    -44,814    0    9,429    39            814    9,523     XXXXXXXXXX    184    2008    Anchorage Police and Fire Retirement System    AK    Alaska    277,585    372,540    0.75    94,955    0    9,067    40            812    7,761     XXXXXXXXXX    184    2009    Anchorage Police and Fire Retirement System    AK    Alaska    300,006    378,668    0.79    78,662    0    7,014    40            818    7,639     XXXXXXXXXX    184    2010    Anchorage Police and Fire Retirement System    AK    Alaska    323,354    380,000    0.85    56,646    12,399    5,762    40            816    7,240     XXXXXXXXXX    184    2011    Anchorage Police and Fire Retirement System    AK    Alaska    300,855    392,583    0.77    91,728    8,980    5,567                752    6,751     XXXXXXXXXX    184    2012    Anchorage Police and Fire Retirement System    AK    Alaska    316,569    388,695    0.81    72,126    6,619    4,990                754    6,418     XXXXXXXXXX    184    2013    Anchorage Police and Fire Retirement System    AK    Alaska    349,736    393,780    0.89    44,044    10,357    5,531                0    4,936     XXXXXXXXXX    184    2014    Anchorage Police and Fire Retirement System    AK    Alaska    344,475    400,520    0.86    56,045    9,004    6,426                0    3,671     XXXXXXXXXX    184    2015    Anchorage Police and Fire Retirement System    AK    Alaska                    6,081    5,801                740    2,199     XXXXXXXXXX    184    2016    Anchorage Police and Fire Retirement System    AK    Alaska                    7,221    5,601                733    2,169     XXXXXXXXXX    184    2017    Anchorage Police and Fire Retirement System    AK    Alaska                        0                        0    1    2001    Employees’ Retirement System of Alabama    AL    Alabama    8,028,471    8,010,123    1.00    -18,348    254,721    343,517    378,196            115,040    2,408,543    405603    1    2002    Employees’ Retirement System of Alabama    AL    Alabama    8,100,846    8,493,469    0.95    392,623    275,108    297,845    411,557            118,858    2,547,775    431048    1    2003    Employees’ Retirement System of Alabama    AL    Alabama    8,312,500    9,124,279    0.91    811,779    316,502    271,698    454,751            121,563    2,677,025    457046    1    2004    Employees’ Retirement System of Alabama    AL    Alabama    8,563,945    9,546,478    0.90    982,533    339,261    268,876    497,945            122,194    2,702,393    492233    1    2005    Employees’ Retirement System of Alabama    AL    Alabama    8,935,358    10,634,976    0.84    1,699,618    356,159    295,631    539,462            126,055    2,982,122    528255    1    2006    Employees’ Retirement System of Alabama    AL    Alabama    9,287,531    11,457,564    0.81    2,170,033    428,876    329,459    615,908            129,396    3,070,146    595128    1    2007    Employees’ Retirement System of Alabama    AL    Alabama    9,770,897    12,370,342    0.79    2,599,445    461,394    347,272    650,344            133,346    3,389,156    658635    1    2008    Employees’ Retirement System of Alabama    AL    Alabama    9,905,766    13,078,687    0.76    3,172,921    520,993    339,578    690,035            136,530    3,553,330    700412    1    2009    Employees’ Retirement System of Alabama    AL    Alabama    9,928,104    13,756,176    0.72    3,828,072    661,420    285,022    734,600            138,175    3,620,243    742239    1    2010    Employees’ Retirement System of Alabama    AL    Alabama    9,739,331    14,284,119    0.68    4,544,788    574,656    268,667    778,328        20    138,621    3,619,670    773606    1    2011    Employees’ Retirement System of Alabama    AL    Alabama    9,456,158    14,366,796    0.66    4,910,638    590,707    307,152    827,274    69    21    137,887    3,540,681    833943    1    2012    Employees’ Retirement System of Alabama    AL    Alabama    9,116,551    13,884,995    0.66    4,768,444    534,390    310,748    864,958    69    21    137,820    3,252,003    903987    1    2013    Employees’ Retirement System of Alabama    AL    Alabama    9,546,459    14,536,600    0.66    4,990,141    562,465    310,200    898,212    69    22    137,801    3,400,596    955681    1    2014    Employees’ Retirement System of Alabama    AL    Alabama    10,134,581    15,138,294    0.67    5,003,713    617,197    298,049    930,223    69    22    138,967    3,444,341    1011714    1    2015    Employees’ Retirement System of Alabama    AL    Alabama    10,589,258    15,723,720    0.67    5,134,462    640,186    311,516    966,983            140,718    3,488,017    1086780    1    2016    Employees’ Retirement System of Alabama    AL    Alabama    11,082,280    16,728,009    0.66    5,645,729    673,115    297,369    1,008,381            143,483    3,572,891    1100976    1    2017    Employees’ Retirement System of Alabama    AL    Alabama    11,690,952    17,250,835    0.68    5,559,883    660,116    302,295    1,041,042            146,356    3,584,700    1116882    2    2001    Teachers' Retirement System of Alabama    AL    Alabama    17,475,298    17,238,616    1.01    -236,682    510,498    769,739    818,745            194,316    4,305,080    854284    2    2002    Teachers' Retirement System of Alabama    AL    Alabama    17,904,881    18,374,174    0.97    469,293    501,396    672,200    886,969            197,813    4,379,183    903220    2    2003    Teachers' Retirement System of Alabama    AL    Alabama    18,110,470    19,357,735    0.94    1,247,265    487,804    592,645    1,027,877            202,130    4,632,611    974567    2    2004    Teachers' Retirement System of Alabama    AL    Alabama    18,704,009    20,886,190    0.90    2,182,181    566,334    580,000    1,144,627            207,336    4,846,677    1033119    2    2005    Teachers' Retirement System of Alabama    AL    Alabama    19,248,207    23,027,338    0.84    3,779,131    610,718    624,397    1,210,649            214,008    5,326,408    1141742    2    2006    Teachers' Retirement System of Alabama    AL    Alabama    19,821,133    23,945,100    0.83    4,123,967    715,650    703,189    1,399,367            222,294    5,458,443    1258729    2    2007    Teachers' Retirement System of Alabama    AL    Alabama    20,650,916    25,971,534    0.80    5,320,618    845,933    719,910    1,465,133            227,498    6,310,616    1447782    2    2008    Teachers' Retirement System of Alabama    AL    Alabama    20,812,477    26,804,117    0.78    5,991,640    1,056,999    705,555    1,531,056            230,416    6,294,341    1538665    2    2009    Teachers' Retirement System of Alabama    AL    Alabama    20,582,348    27,537,400    0.75    6,955,052    1,077,224    580,656    1,601,276            229,821    6,236,922    1571497    2    2010    Teachers' Retirement System of Alabama    AL    Alabama    20,132,779    28,299,523    0.71    8,166,744    1,097,824    559,941    1,679,431        22    233,881    6,183,204    1629451    2    2011    Teachers' Retirement System of Alabama    AL    Alabama    19,430,135    28,776,316    0.68    9,346,181    1,102,840    624,546    1,760,247    70    22    233,575    6,159,562    1740260    2    2012    Teachers' Retirement System of Alabama    AL    Alabama    18,786,008    28,251,367    0.67    9,465,359    1,076,840    634,695    1,818,999    70    22    235,165    6,222,316    1868094    2    2013    Teachers' Retirement System of Alabama    AL    Alabama    19,629,816    29,665,843    0.66    10,036,027    1,105,478    638,766    1,871,441            236,336    6,263,364    1967938    2    2014    Teachers' Retirement System of Alabama    AL    Alabama    20,809,871    30,837,829    0.68    10,027,958    1,220,396    618,861    1,927,712    70    23    237,589    6,335,161    2072613    2    2015    Teachers' Retirement System of Alabama    AL    Alabama    21,740,280    31,844,843    0.68    10,104,563    1,215,589    640,910    1,991,150            241,234    6,350,786    2156126    2    2016    Teachers' Retirement System of Alabama    AL    Alabama    22,645,512    33,143,834    0.68    10,498,322    1,227,882    607,795    2,053,762            245,075    6,430,999    2238719    2    2017    Teachers' Retirement System of Alabama    AL    Alabama    23,887,077    34,688,078    0.69    10,801,001    1,272,333    613,353    2,118,983            248,355    6,698,835    2193279    171    2001    Birmingham Retirement & Relief System    AL    Alabama    773,453    727,361    1.06    -46,093                        6,231    149,422    0    171    2002    Birmingham Retirement & Relief System    AL    Alabama    778,605    758,085    1.03    -20,520                        6,107    151,180    0    171    2003    Birmingham Retirement & Relief System    AL    Alabama    785,646    796,084    0.99    10,437                        6,158    152,242    0    171    2004    Birmingham Retirement & Relief System    AL    Alabama    801,612    838,486    0.96    36,873    21,865        41,316            6,250    158,062    43754    171    2005    Birmingham Retirement & Relief System    AL    Alabama    819,167    875,792    0.94    56,625    27,780        48,145            6,272    158,898    52950    171    2006    Birmingham Retirement & Relief System    AL    Alabama    898,671    946,585    0.95    47,914    22,044        44,698            6,241    162,849    48171    171    2007    Birmingham Retirement & Relief System    AL    Alabama    935,821    992,864    0.94    57,043    22,827        47,953            6,324    167,808    53218    171    2008    Birmingham Retirement & Relief System    AL    Alabama    935,080    1,050,786    0.91    97,706    22,755        52,358            6,451    174,114    57012    171    2009    Birmingham Retirement & Relief System    AL    Alabama    910,769    1,083,256    0.84    172,487    25,278        54,228            6,744    186,523    58241    171    2010    Birmingham Retirement & Relief System    AL    Alabama    913,078    1,113,441    0.82    200,364    25,192        56,035            6,860    193,230    63327    171    2011    Birmingham Retirement & Relief System    AL    Alabama    892,096    1,158,070    0.77    265,974    25,654        64,606            6,856    177,977     XXXXXXXXXX    171    2012    Birmingham Retirement & Relief System    AL    Alabama    878,049    1,181,090    0.74    303,042    25,766        65,666            6,959    181,407    71250    171    2013    Birmingham Retirement & Relief System    AL    Alabama    888,210    1,216,684    0.73    328,475    25,428        67,189            7,018    182,634    73317    171    2014    Birmingham Retirement & Relief System    AL    Alabama    945,245    1,263,665    0.75    318,420    26,075        70,348            7,089    191,300    76926    171    2015    Birmingham Retirement & Relief System    AL    Alabama    993,857    1,319,288    0.75    325,431    26,740        71,870            7,146    196,808    77559    171    2016    Birmingham Retirement & Relief System    AL    Alabama    1,016,438    1,346,038    0.76    329,600    30,257        75,490            7,290    200,205    83439    171    2017    Birmingham Retirement & Relief System    AL    Alabama    1,044,789    1,422,976    0.73    378,187    30,629                    7,392    203,985    88319    7    2001    Arkansas Public Employees Retirement System    AR    Arkansas    4,342,000    4,111,000    1.06    -231,000    101,227    116

Akshay Kumar · Accepted Answer

Pension Data Analytics Assignment
The Excel data file contains two worksheets of data.  Data 1 contains pension data for public pension systems across different states from 2001 to 2017. Data 2 has capital income data by each state.  Please analyze the data and answer 4 questions below by using functions such as sum, average, if, and pivot table. You must present your answers in a Word file. You should copy and paste tables or graphs from your analysis in Excel into your deliverable file to support your answers. 
1) Present a table with averages of the following items for each state: Total Pension Assets, Total Pension Liabilities, Funding Ratios, and Funding Status. 
· Discuss Illinois’ funding status. How is Illinois compared to other states? 
	State
	Average of Total Assets
	Average of Total Liabilities
	Average of Funding Ratio
	Average of Funding status (Under/Over Funded)
	AK
	3,842,062.83
	5,757,998.74
	0.74
	1,915,936.35
	AL
	10,152,368.45
	13,433,840.22
	0.80
	3,281,118.82
	AR
	8,390,572.56
	10,171,151.52
	0.82
	2,015,151.52
	AZ
	7,325,286.23
	9,474,386.46
	0.78
	2,149,323.41
	CA
	35,432,885.14
	43,545,835.28
	0.85
	8,112,950.26
	CO
	8,673,356.15
	12,575,350.24
	0.78
	3,901,993.96
	CT
	6,265,086.48
	11,113,112.00
	0.75
	4,848,019.15
	DC
	2,243,078.26
	2,566,514.13
	1.03
	(102,530.04)
	DE
	3,545,638.18
	3,701,380.10
	0.92
	155,741.96
	FL
	41,909,111.69
	44,402,214.65
	0.84
	2,493,309.79
	GA
	17,285,128.13
	20,172,376.15
	0.76
	2,892,219.60
	HI
	11,513,048.24
	17,971,995.00
	0.66
	6,458,958.53
	IA
	11,790,770.39
	13,921,277.55
	0.85
	2,130,507.22
	ID
	10,296,729.41
	11,559,723.53
	0.89
	1,278,900.00
	IL
	14,433,541.33
	26,084,608.57
	0.59
	11,651,067.17
	IN
	10,242,946.16
	16,275,341.58
	0.67
	6,032,395.42
	KS
	6,896,214.79
	10,114,889.92
	0.86
	3,218,675.07
	KY
	6,997,418.97
	11,315,036.87
	0.68
	4,317,617.89
	LA
	4,226,394.16
	6,410,442.77
	0.76
	2,184,033.35
	MA
	14,966,034.08
	22,180,428.30
	0.68
	7,216,394.20
	MD
	10,300,617.17
	13,564,876.65
	0.81
	3,264,259.50
	ME
	5,204,697.76
	6,638,621.12
	0.88
	1,433,923.35
	MI
	13,026,549.01
	17,498,595.86
	0.80
	4,462,511.13
	MN
	7,351,969.53
	8,901,430.46
	0.83
	1,549,779.93
	MO
	6,501,231.76
	7,934,081.30
	0.81
	1,433,744.52
	MS
	20,191,279.94
	30,252,302.35
	0.69
	10,061,022.41
	MT
	3,526,790.81
	4,653,162.11
	0.77
	1,126,371.30
	NC
	23,835,239.53
	24,082,669.81
	0.97
	247,430.26
	ND
	1,693,098.50
	2,302,123.19
	0.78
	609,027.61
	NE
	2,838,529.12
	3,489,681.63
	0.73
	650,972.52
	NH
	5,239,449.16
	8,411,802.74
	0.65
	3,172,353.58
	NJ
	27,969,871.25
	39,301,675.78
	0.75
	11,271,522.34
	NM
	10,490,671.88
	13,990,687.32
	0.77
	3,500,018.38
	NV
	12,189,322.24
	16,455,774.83
	0.74
	4,266,453.49
	NY
	49,427,237.60
	55,620,940.03
	0.85
	6,193,694.38
	OH
	29,361,246.15
	38,607,447.28
	0.77
	9,257,667.70
	OK
	5,179,124.74
	7,869,283.94
	0.78
	2,690,162.53
	OR
	50,819,976.47
	55,863,129.41
	0.93
	5,043,152.94
	PA
	18,977,939.54
	25,703,601.05
	0.71
	6,725,663.98
	RI
	2,586,248.72
	4,343,610.92
	0.62
	1,755,542.18
	SC
	13,675,674.71
	19,942,919.44
	0.74
	6,267,244.74
	SD
	7,272,299.49
	7,455,230.37
	0.97
	182,930.88
	TN
	11,502,165.52
	12,170,115.58
	0.93
	667,950.05
	TX
	22,211,558.04
	26,057,780.53
	0.85
	3,846,238.16
	UT
	8,964,305.31
	10,249,083.25
	0.87
	1,273,287.94
	VA
	18,295,176.89
	23,445,517.55
	0.81
	5,150,340.67
	VT
	1,392,734.49
	1,839,633.91
	0.80
	446,899.42
	WA
	6,918,690.00
	6,396,700.00
	1.14
	(515,532.10)
	WI
	28,552,321.10
	28,682,251.26
	0.98
	129,930.22
	WV
	4,070,478.74
	6,646,907.09
	0.64
	2,544,634.24
	WY
	5,640,674.99
	6,670,582.57
	0.86
	1,029,907.58
	Grand Total
	15,513,054.77
	19,661,782.65
	0.80
	4,098,152.34
On an average basis, Illinois Funding Status is that it is Underfunded.  Its Average Total Liabilities are 26.1 million whereas the Average total assets are 14.4 million.  It is underfunded by 11.6 million. As compared to other status, Illinois Funding Status is worse. 
The average funding status of the all the status is that it is underfunded by only 4.1 million which is lower than average underfunding of Illinois. The average underfunding of Illinois is highest as compared to average of all states
2) Present a table with averages of the following items for each pension plan in Illinois: Total Pension Assets, Total Pension Liabilities, Funding Ratios, and Funding Status (use filter).
· How many pension systems does Illinois have? 
· Which system is the healthiest? 
	State
	Average of Total Assets
	Average of Total Liabilities
	Average of Funding Ratio
	Average of Funding status (Under/Over Funded)
	Illinois
	14,433,541.33
	26,084,608.57
	0.59
	11,651,067.17
Illinois has 8 pension systems which are as follows:
· Public School Teachers' Pension and Retirement Fund of Chicago
· Illinois Municipal Retirement Fund
· State Employees' Retirement System of Illinois
· Teachers' Retirement System of The State of Illinois
· State Universities Retirement System of Illinois
· Chicago Municipal Employees' Annuity Benefit Fund
· Chicago Policemen's Annuity Benefit Fund
· Cook County Employees' Annuity Benefit Fund
The system which is healthiest is Illinois Municipal Retirement Fund because it has the higher funding ratio. Average Total assets of this system is 25.2 million and Average Total liabilities of the system is 28.1 million. Average Funding Ratio of this system is 0.91 which is more than the funding ratio of all other systems.

Pension Data Analytics Assignment The Excel data file contains two worksheets of data. Data 1 contains pension data for public pension systems across different states from 2001 to 2017. Data 2 has...

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment