I have a pension project heavy excel and a good amount of analysis if someone can help me out that would be great.

Discount Rate Annual Award Year EMPID Employee Name 252160 Birdie Ahlers 205188 Francesco Alers 252579 Deneen Alonzo 397185 Loyd Alt 156175 Ignacio Arter 118099 Garland Audete 310475 Lynete Back 307248 Marcelino Baldon 431615 Randolph Balser 336022 Daniel Bastarache 448195 Garret Baten 279704 Keenan Baze 176833 Gabriel Bea 355450 Catna Beason 351791 Dong Beaty 163872 Vince Bhat 294706 Jospeh Blanco 162642 Bete Bolanos 334792 Dean Bopp 193413 Kiesha Braithwaite 446198 Hermina Brasch 362755 Arnulfo Brissete 403652 Waylon Britan 355869 Elijah Brownson 411768 Clemente Bunde 170758 Ruben Bussey 248501 Shalon Carlson 172755 Rosalva Carmen 169528 Kristan Chappell 274048 Moon Claw 262273 Ethan Clukey 272818 Berenice Cogan 321020 Zane Connelly 404071 Tamela Cordes 331133 Steve Cordray 224616 Brandon Cregan 390299 Argentna Curran 207185 Eliseo Cutshall 438501 Devon Dalpiaz 5.0% 2.5% 2014 Gender F M F M M M F M M M M M M F M M M F M F F M M M M M F F F F M F M F M M F M M DOB 6/11/1965 9/29/1952 3/10/1949 1/4/1949 11/2/1989 8/16/1975 2/17/1950 12/8/1957 9/30/1949 11/25/1952 5/5/1976 11/25/1966 12/21/1987 1/26/1972 5/29/1957 9/27/1990 4/1/1950 1/28/1968 9/20/1952 11/25/1972 9/7/1973 4/7/1984 11/14/1974 10/21/1956 11/26/1991 9/2/1966 7/19/1960 9/13/1953 5/22/1983 4/30/1975 8/10/1974 5/15/1985 4/26/1967 11/8/1980 9/25/1949 5/16/1968 7/28/1971 9/20/1973 4/21/1965 Date of Salary as of Employment 12/31/2014 13-Nov-03 1-Aug-76 14-May-84 21-Dec-72 1-Feb-11 1-Nov-05 6-Aug-72 26-Feb-97 22-May-73 10-Jun-86 23-Jun-98 4-Oct-98 13-Mar-12 6-Oct-96 22-Jan-82 5-Apr-12 10-Nov-77 23-Dec-94 8-Feb-92 16-Oct-13 7-Jul-98 28-Nov-07 27-Nov-98 26-Nov-78 21-Feb-14 24-Jul-90 4-Jun-89 11-Jun-77 21-Dec-05 21-Jun-98 23-Aug-97 24-Apr-07 19-Apr-89 25-Apr-09 7-Dec-71 23-Oct-95 21-Jul-12 6-Jul-96 13-Jun-87 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 181,800 155,400 194,000 68,300 41,900 70,700 169,800 213,200 137,700 134,400 155,600 64,000 66,400 104,200 211,500 84,000 131,000 61,600 115,200 65,400 60,400 61,800 122,400 123,200 66,500 113,600 177,800 72,900 74,300 56,800 107,400 82,800 164,800 55,600 135,800 133,800 93,100 120,200 97,700 125404 Matha Dauphin 163061 Jeta Derose 287009 Raymundo Derrow 135098 Shawana Devivo 246504 Andy Dively 197072 Noble Dotson 190186 Tory Dowdell 363566 Elijah Eakle 338019 Julio Edmisten 165869 Natalie Elsen 308478 Dillon Elser 132290 Heath Fava 293895 Jerrod Ferree 186527 Benedict Forck 135517 Amparo Frantz 111632 Jorge Freitag 341678 Lawanna French 177644 Cheryl Frost 234729 Una Galante 286590 Raina Gass 259465 Delphia Gaxiola 362336 Clif Giesen 383413 Karly Goldschmidt 136328 Jasper Gowans 190605 Jefery Grasso 126215 Hosea Guertn 441309 Carol Guillermo 348983 Gia Guillory 424729 Nickole Gunning 348564 Danial Hadley 369641 Todd Hallowell 266351 Joane Halsted 417424 Delbert Hanway 427537 Ezequiel Hartline 289817 Dexter Helmuth 225035 Sena Hemmings 439312 Marqueta Heng 280123 Allen Hesson 169947 Rolando Hilden 322250 Elisha Hippler 138325 Elnora Hofert 227843 Joi Holland 121326 Eugenie Hooser 404882 Ayesha Horak 148870 Jae Houge F F M F M M M M M F M M M M F M F F F F F M F M M M M F F M M F M M M F F M M M F F F F M 6/17/1990 6/30/1983 6/4/1952 9/20/1982 2/19/1990 3/28/1965 4/3/1977 4/9/1966 9/21/1972 8/24/1978 12/26/1976 8/6/1961 3/8/1992 2/1/1957 5/24/1977 11/27/1983 8/23/1991 3/10/1964 11/2/1961 2/18/1991 12/22/1961 6/2/1989 4/2/1981 6/4/1969 9/2/1977 1/21/1975 2/7/1955 5/13/1960 11/2/1974 3/24/1962 2/27/1966 6/7/1952 6/6/1962 5/4/1968 4/15/1975 9/10/1992 5/15/1978 2/16/1957 6/11/1967 5/27/1957 4/4/1957 6/19/1971 6/14/1976 4/20/1989 8/17/1992 4-Jan-13 16-Jul-11 8-Apr-90 21-Aug-05 25-Sep-12 25-Jan-87 12-Dec-08 17-Oct-92 24-Oct-12 11-Apr-00 11-Aug-04 14-Sep-83 26-Nov-13 9-Sep-79 27-Mar-00 3-Jun-05 27-Jan-12 9-May-87 2-Oct-85 14-Sep-13 3-Aug-92 17-Jan-11 18-May-12 24-Sep-96 15-Dec-06 10-May-02 28-Jan-77 22-Nov-82 6-Sep-96 5-Jan-86 14-Jun-88 2-Nov-74 11-Mar-84 21-Aug-90 4-Apr-06 16-Oct-14 2-Mar-01 25-Dec-79 23-May-89 16-Oct-79 8-Oct-79 19-Jul-94 6-May-13 2-Jun-14 3-Oct-14 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 49,600 102,400 232,400 65,200 104,400 81,500 49,100 198,000 133,600 114,600 68,200 215,800 91,800 73,200 48,000 76,400 66,200 91,400 89,100 41,600 140,400 55,700 44,800 96,800 70,100 60,200 127,900 107,600 136,600 53,200 162,800 165,600 69,200 138,200 45,300 76,800 92,400 128,400 108,400 77,100 63,100 147,100 62,000 67,000 84,800 314134 Corey Hulme 217730 Elvin Hunger 413765 Yolande Huntzinger 238807 Claud Innes 335211 Dean Jasmin 314553 Pierre Jordan 111213 Craig Koepp 198302 Sal Kovacs 397996 Gayle Krell 176414 Coleman Krueger 324247 Buck Kush 203958 Dalton Lamere 389880 Jacki Lank 273237 Francisco Laplaca 393107 Hilton Laurie 149289 Augustus Lawton 425540 Alden Learned 382994 Britaney Ledet 265932 Nathanial Leer 349794 Tomi Lefevre 372449 Nicky Lefew 238388 Royce Leib 432426 Jaime Leicht 358677 Timothy Leland 391110 Eura Lessman 260276 Arthur Logan 317361 Don Lozier 218960 Mitch Mace 282931 Laurice Macintosh 124985 Edgar Manry 434423 Kum Marable 145211 Asa Maring 241615 Ken Marsch 150100 Elli Martneau 293476 Vito Mathieson 280934 Art Maybee 418654 Elliot Mccasland 315364 Quincy Mccaw 444968 Rosy Mcdougal 296703 Kirk Mickelsen 342097 Helga Mifin 184530 Clint Mikula 417843 Curt Milliner 212074 Marcel Millis 204377 Maira Millsaps M M F M M M M M M M M M F M M M M F M F M M M M F M M M F M F M M F M M M M F M F M M M F 5/23/1977 10/30/1976 7/31/1957 4/25/1961 6/2/1950 1/3/1958 9/27/1991 5/4/1987 12/16/1953 12/6/1955 4/23/1954 9/13/1948 11/23/1987 5/23/1993 3/6/1986 4/15/1968 11/30/1951 4/10/1970 7/14/1990 3/30/1959 7/17/1954 7/3/1979 2/5/1964 9/23/1990 7/22/1977 4/19/1992 8/9/1982 4/2/1979 4/7/1959 7/20/1959 4/26/1957 7/24/1979 10/15/1988 12/24/1970 8/1/1975 10/31/1966 8/10/1949 1/25/1987 7/11/1974 10/1/1985 4/1/1949 9/30/1990 3/3/1961 4/24/1975 7/26/1989 23-Nov-00 13-Oct-98 11-Apr-83 7-Jun-84 21-Sep-72 2-Feb-80 5-Jun-14 12-Jan-14 19-Aug-95 12-Nov-77 20-Jul-79 9-Jan-70 8-Mar-13 12-Apr-12 8-May-08 12-Dec-90 26-Apr-87 2-Jul-92 25-Apr-12 20-Dec-87 15-Mar-76 9-Oct-04 9-Oct-94 17-Sep-13 19-Mar-05 13-Jul-14 15-Jun-04 12-Aug-04 14-Sep-85 23-Mar-05 8-Feb-93 19-May-01 28-May-13 9-Jun-92 13-Sep-97 15-Jul-88 22-Nov-71 16-Dec-09 3-Aug-96 16-May-12 18-Mar-73 25-Feb-12 21-Jan-02 8-Mar-97 20-Nov-12 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 143,100 158,800 126,400 183,200 153,200 126,400 52,700 104,900 61,700 117,600 78,000 177,200 68,500 39,100 117,200 87,800 80,600 160,400 62,000 167,500 106,000 78,000 172,000 61,800 51,400 91,400 109,200 84,000 195,700 53,900 99,800 100,200 110,400 156,000 130,900 152,400 135,800 89,700 121,800 103,600 149,200 90,000 148,100 93,900 50,300 406879 Lamont Minter 255387 Rogelio Mitschke 183719 Deangelo Mleczko 420651 Freddy Monica 370452 Micah Monroe 410957 Rex Morace 342908 Bret Moran 376527 Hobert Morocco 218149 Jeremiah Muir 197491 Maynard Nair 329136 Margorie Neufeld 410538 Lucinda Nicholes 344905 Della Oaks 399993 Temeka Pangburn 133101 Jerrold Paulson 155756 Gwenn Pechacek 179641 Kareem Penny 356680 Chantelle Perry 191416 Leonard Prieto 386221 Ricardo Pulido 424310 Ward Puryear 225846 Coleman Rankin 231502 Denese Rentro 253390 Merlin Ribeiro 114440 Emerson Rickel 112443 Asa Rodd 141984 Carmelita Rosborough 214071 Danyelle Russi 303589 Sarita Salmons 211263 Dalton Salomone 183300 Sang Sancho 156986 Senaida Santana 379335 Sonia Schimmel 276045 Hugo Schlenker 143214 Jordan Schoenfeld 131871 Laurena Selke 300362 Truman Sessions 301592 Geraldo Sharman 158983 Rosella Slaubaugh 119329 Timmy Song 396766 Kirby Soria 232732 Toney Springer 376108 Russ Stall 269159 Tania Stephens 231921 Dede Sugg M M M M M M M M M M F F F F M F M F M M M M F M M M F F F M M F F M M F M M F M M M M F F 4/23/1976 8/10/1988 8/20/1963 9/10/1977 5/3/1974 4/12/1963 7/11/1961 2/24/1967 6/7/1988 3/16/1990 7/16/1962 1/11/1954 9/12/1958 6/2/1977 10/28/1986 12/23/1976 11/16/1959 5/11/1959 9/1/1974 3/22/1950 4/9/1952 8/4/1949 7/11/1978 6/14/1982 10/15/1953 1/15/1954 9/21/1966 6/13/1970 5/22/1968 12/9/1949 10/17/1980 2/17/1967 7/29/1965 2/14/1956 8/16/1978 8/31/1985 1/9/1975 12/4/1983 12/28/1979 12/9/1979 11/21/1968 12/5/1956 3/15/1959 5/26/1967 4/25/1989 5-Aug-01 16-Sep-13 14-Aug-90 25-Jul-99 16-Jul-99 13-Apr-85 21-Feb-83 23-Dec-97 2-Nov-12 16-Nov-12 3-Jan-84 9-Apr-76 19-Jan-81 13-Feb-03 24-Oct-14 18-Jul-02 7-Sep-84 10-Oct-82 22-Mar-97 20-Mar-80 15-Apr-80 25-Nov-73 18-Apr-00 15-Apr-04 22-Nov-90 19-Aug-77 15-May-88 3-Jan-02 20-Jan-92 7-Jun-83 7-Sep-02 6-Mar-05 18-Oct-10 21-Jul-90 10-Oct-01 8-Oct-12 25-Apr-12 28-May-07 8-Feb-01 17-Dec-01 25-Dec-90 22-Mar-90 5-Sep-87 1-May-93 17-Mar-13 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 142,400 71,200 81,800 135,800 95,000 141,200 76,200 98,600 111,200 76,400 134,600 83,800 165,000 89,100 90,800 111,000 72,500 146,600 95,400 97,600 189,400 157,200 127,400 64,800 101,300 213,000 181,600 162,200 168,400 89,600 128,600 123,500 100,700 108,200 110,200 114,200 56,200 44,100 56,000 129,000 147,200 128,600 125,000 52,700 79,500 118518 Mack Swayne 328325 Skye Tadlock 445387 Antony Tarr 384224 Eboni Tellez 287820 Camille Teston 300781 Thersa Tiedemann 142403 Jennifer Tindle 369222 Bryant Tiner 239618 Emile Toledo 220957 Scot Urbano 431196 Denis Valade 307667 Alberto Valazquez 365563 Dwain Vanderwal 245693 Tyron Vella 210844 Crissy Vigil 451854 Huey Wedding 245274 Amada Weisman 259046 Clyde Wemple 438082 Thomas Winders 321439 Erwin Windley 377338 Regine Witzel 267162 Christan Wolfgram 327906 Adrienne Wooten 152097 Pierre Yadon 128212 Carlos Ying 200299 Emmit Yingst M F M F F F F M M M M M M M F M F M M M F M F M M M 12/23/1991 9/16/1971 4/5/1954 7/10/1984 7/10/1955 8/10/1989 4/10/1967 6/3/1955 4/29/1962 1/6/1991 7/26/1992 5/20/1972 9/23/1983 10/17/1958 6/3/1962 9/3/1949 1/19/1989 2/13/1987 4/1/1980 6/14/1990 9/13/1961 1/28/1971 7/7/1982 11/15/1964 8/25/1964 5/21/1955 3-Apr-12 7-Jul-98 13-Aug-80 5-Jan-06 4-Mar-85 28-Feb-12 5-Nov-12 10-Oct-77 17-Feb-87 20-Oct-14 12-Nov-14 15-Sep-94 27-Nov-14 22-Dec-80 8-Jun-84 4-Jul-72 26-May-11 27-Jan-14 17-Jan-02 8-Jun-12 25-Jun-83 7-Jul-93 2-Jan-12 18-Sep-86 17-Aug-08 17-Sep-85 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 74,200 141,200 131,200 41,600 173,400 98,200 39,100 99,900 137,000 45,800 38,600 77,800 106,400 109,600 82,200 115,700 58,400 64,400 84,800 82,800 189,900 139,400 82,400 210,000 94,400 161,700 Anticipated Retirement Age 63 63 66 67 63 62 65 66 66 63 64 64 65 60 66 65 66 61 66 66 66 62 61 65 62 63 63 62 60 61 63 65 62 63 66 64 65 64 64 Health Score 9 8 7 9 4 4 4 9 7 7 10 8 6 8 9 7 6 9 8 7 6 8 8 3 5 3 4 7 9 10 8 5 4 4 5 4 6 5 9 Anticipated Life Span 87 81 85 82 77 77 82 82 80 80 83 81 79 86 82 80 79 87 81 85 84 81 81 76 78 76 82 85 87 88 81 83 77 82 78 77 84 78 82 Forecasted Retirement Salary 214,000.00 159,400.00 201,400.00 71,900.00 76,100.00 100,600.00 179,000.00 233,400.00 141,300.00 138,600.00 231,000.00 94,400.00 161,400.00 196,000.00 220,100.00 229,550.00 141,200.00 91,600.00 122,000.00 138,600.00 161,650.00 138,600.00 305,350.00 136,000.00 281,000.00 193,100.00 283,400.00 82,100.00 149,700.00 131,600.00 148,800.00 181,800.00 218,050.00 158,550.00 144,400.00 166,200.00 142,600.00 182,300.00 121,700.00 62 63 63 62 66 61 66 65 64 62 66 60 63 63 66 60 64 65 66 62 61 61 63 64 65 66 66 64 61 65 61 63 60 66 66 61 64 65 60 62 61 61 65 63 64 9 8 9 6 6 6 7 8 7 7 10 10 6 4 3 8 10 5 9 6 8 10 9 4 10 6 10 7 8 9 6 5 9 4 3 5 8 10 4 9 5 4 6 9 7 87 86 82 84 79 79 80 81 80 85 83 83 79 77 81 81 88 83 87 84 86 83 87 77 83 79 83 85 86 82 79 83 82 77 76 83 86 83 77 82 83 82 84 87 80 240,800.00 272,000.00 236,200.00 108,700.00 278,700.00 104,900.00 217,250.00 233,700.00 183,100.00 212,100.00 100,400.00 229,000.00 239,400.00 84,600.00 120,500.00 212,700.00 176,900.00 265,650.00 132,650.00 131,300.00 152,400.00 256,500.00 83,800.00 107,250.00 137,300.00 180,350.00 134,700.00 136,600.00 187,000.00 98,700.00 179,700.00 167,000.00 95,200.00 163,200.00 154,650.00 323,050.00 193,200.00 132,600.00 253,250.00 92,100.00 71,900.00 220,900.00 93,050.00 262,700.00 246,500.00 60 62 60 61 66 66 61 66 62 66 61 67 64 64 66 64 65 61 66 60 61 64 61 61 66 66 64 61 65 63 62 63 61 60 63 60 66 61 61 64 66 60 63 62 62 8 5 7 6 7 8 9 10 4 10 3 5 5 4 10 10 4 4 8 8 8 7 4 4 6 10 4 10 7 9 10 10 6 5 10 10 5 7 4 7 8 9 6 6 5 81 78 85 79 80 81 82 83 77 83 76 78 83 77 83 83 77 82 81 86 81 80 77 77 84 83 77 83 85 82 88 83 79 83 83 83 78 80 82 80 86 82 79 79 83 187,950.00 317,600.00 135,400.00 199,600.00 165,400.00 157,400.00 126,800.00 277,050.00 70,500.00 128,200.00 82,800.00 184,400.00 118,450.00 140,150.00 311,000.00 130,100.00 91,600.00 236,050.00 251,000.00 179,500.00 113,400.00 270,800.00 204,450.00 313,500.00 107,950.00 115,600.00 218,000.00 160,700.00 220,700.00 61,100.00 109,800.00 209,400.00 154,150.00 310,400.00 169,300.00 171,600.00 142,200.00 196,800.00 181,650.00 236,600.00 152,800.00 335,800.00 189,100.00 122,650.00 185,350.00 62 66 65 60 63 63 61 64 65 61 61 66 60 60 64 62 66 65 66 65 63 66 65 63 64 66 64 61 62 66 63 66 62 64 64 64 64 60 60 60 62 65 66 66 62 6 8 3 7 10 7 6 10 6 4 4 10 5 6 8 9 6 9 10 7 4 8 7 6 8 5 7 10 3 4 6 7 9 6 4 10 5 3 5 10 5 4 7 6 8 79 81 76 80 83 80 79 83 79 77 82 88 83 84 81 87 79 87 83 80 77 81 85 79 81 78 85 88 81 77 79 85 87 79 77 88 78 76 83 83 78 77 80 84 86 224,000.00 177,200.00 132,200.00 188,700.00 180,100.00 183,800.00 93,800.00 247,050.00 200,900.00 143,000.00 148,000.00 102,800.00 172,800.00 183,400.00 195,200.00 265,000.00 96,150.00 182,600.00 152,600.00 105,600.00 197,800.00 159,600.00 205,700.00 153,150.00 107,900.00 220,600.00 330,400.00 207,250.00 237,200.00 95,600.00 195,300.00 194,750.00 160,500.00 113,400.00 326,400.00 226,200.00 148,700.00 131,100.00 123,500.00 202,750.00 192,800.00 136,000.00 156,900.00 97,350.00 162,750.00 60 63 61 61 62 61 60 65 62 60 61 65 63 63 61 66 60 65 62 61 66 62 62 64 61 61 3 4 8 8 4 8 5 10 7 10 3 8 8 8 6 6 10 5 9 9 8 5 3 9 10 10 76 82 81 86 82 86 83 83 80 83 76 81 81 81 84 79 88 78 82 82 86 78 81 82 83 83 174,100.00 176,200.00 138,000.00 147,000.00 184,400.00 172,000.00 101,500.00 118,900.00 162,000.00 93,900.00 108,800.00 158,300.00 136,800.00 127,600.00 90,000.00 121,300.00 131,900.00 258,200.00 213,600.00 251,250.00 339,300.00 268,850.00 212,900.00 247,800.00 129,050.00 163,100.00 The company invests plan assets in a diversified equites fund with JP Morgan Chase. The fund performance for the past te is listed below. Assume there were no accumulated gains or losses prior to 2005. All contributons are made at the end of t calendar year, so returns generated by the fund are based on the beginning balance of each period. Year-End 12/31/2005 12/31/2006 12/31/2007 12/31/2008 12/31/2009 12/31/2010 12/31/2011 12/31/2012 12/31/2013 12/31/2014 on Plan Beginning Balance Return Assets $ $ $ $ $ $ $ $ $ $ 23,312,120.80 26,009,522.19 31,547,460.10 35,220,971.43 25,101,943.33 27,961,717.12 32,546,897.40 34,573,567.21 40,427,244.07 50,411,512.26 Contributions Ending Balance $ 1,119,846.90 $ 1,577,554.49 $ $ 3,895,326.41 $ 1,642,611.51 $ $ 1,620,724.89 $ 2,052,786.44 $ $ (11,774,383.83) $ 1,655,355.73 $ $ 1,891,916.66 $ 967,857.14 $ $ 3,451,124.56 $ 1,134,055.71 $ $ 571,872.98 $ 1,454,796.83 $ $ 4,506,591.40 $ 1,347,085.47 $ $ 8,411,496.94 $ 1,572,771.25 $ $ 6,599,258.33 $ 2,133,423.47 $ Expected Return Percentage Pre-2005 Gain (Loss): 26,009,522.19 9.94% 31,547,460.10 10.15% 35,220,971.43 7.99% 25,101,943.33 11.80% 27,961,717.12 11.40% 32,546,897.40 10.02% 34,573,567.21 11.15% 40,427,244.07 10.72% 50,411,512.26 10.32% 59,144,194.06 9.20% Accumulated gain (loss) as of 12/31/2014 ormance for the past ten years re made at the end of the Gain (Loss) $ (215,421.00) $ (1,198,194.07) $ 1,255,025.63 $ (901,272.91) $ (15,930,464.48) $ (969,704.88) $ 647,996.70 $ (3,057,106.08) $ 800,780.26 $ 4,240,968.82 $ 1,961,399.20 $ 2,401,212.44 Loss amortzaton as of 1/1/2014 $ (10,964,780.37) Intermediate\tFinancial\tAccounting\tIII\t-\t4113 Analytics\tand\tCritical\tThinking\tProject PantherTec\tsponsors\ta\tdefined\tbenefit\tpension\tplan\tfor\tits\temployees. The\tcompany's\tpension formula\tpays\t2.5%\tper\tyear\tof\tservice\ttime\tof\tthe\temployee's\tmaximum\tsalary,\twith\ta maximum\tbenefit\tof\t60%\tof\tfinal\tsalary.\tTo\tillustrate,\tan\temployee\tearning\ta\tmaximum\tsalary\tof $200,000\tand\tretiring\tafter\t20\tyears\twould\treceive\ta\t$100,000\tannual\tbenefit\t(2.5%\tx\t20\tyears\tx $200,000). Accompanying\tthis\tproject\tis\tan\tExcel\tspreadsheet\twith\ttwo\ttabs. The\tfirst,\t\"Employee Information,\"\tlists\tthe\t200\temployees\tof\tPantherTec,\talong\twith\tinformation\twhich\tmay\tor\tmay not\tbe\tneeded\tto\tcompute\tpension\tobligations. The\tsecond\ttab,\t\"Pension\tPlan\tAsset Performance,\"\toutlines\thow\tPantherTec's\tplan\tassets\thave\tperformed\tover\tthe\tlast\tseveral periods,\texpected\treturns\ton\tplan\tassets\tover\tthat\tperiod,\tand\tinformation\ton\tprevious amortizations.\tThere\tare\tno\tprior\tservice\tcosts\tor\tother\taccumulated\tlosses\ton\tthe\tprojected benefit\tobligation. Required: Write\ta\tproject\tsummary\taddressing\teach\tof\tthe\tfollowing\trequirements,\tciting accompanying\ttables\tand\tfigures\tas\tnecessary. Your\tfinal\tdeliverable\tshould\tinclude\ta\ttyped report\twith\tresponses\tto\teach\tof\tthe\tfollowing\tpoints\tas\twell\tas\tan\tExcel\tfile\twith\tsupporting analysis.\tResponses\tshould\tbe\tsuccinct,\tbut\tcomplete. Analyses: 1. Compute\tthe\tprojected\tbenefit\tobligation\t(PBO)\tas\tof\t12/31/2014\tusing\tthe\t\"Employee Information\"\ttab\tassuming\tthe\tcompany's\tactuary\trecommends\ta\t5%\tdiscount\trate.\tFor simplicity,\tassume\tthat\tall\tretirements\toccur\ton\tDecember\t31\tof\tthe\tretirement\tyear, and\tpartial\tyears\tworked\tprovide\tno\tbenefit.\tIn\tother\twords,\tif\ta\t40\tyear-old\temployee begins\tworking\ton\t7/1/2014\tand\tis\texpected\tto\tretire\tat\t65,\tthen\tassume\tthat\this retirement\tdate\tis\t12/31/2039\tand\tthat\the\twill\taccrue\t25\tyears\tof\tservice\t(2015\tthrough 2039). Computations\tshould\tbe\tperformed\tin\tExcel. 2. Compute\tPension\tExpense\tfor\t2014,\tseparately\tidentifying\teach\tcomponent.\tAssume that\tno\tactuarial\tassumptions\tchanged\tbetween\t2013\tand\t2014\t(i.e.,\tsame\tprojected maximum\tsalaries,\tbenefit\tperiods,\tetc.). 3. Describe\tthe\tfunding\tstatus\tfor\tthe\tplan.\tIs\tthe\tplan\tover-\tor\tunder-funded? 4. PantherTec\tis\tconsidering\ta\tpension\tplan\tamendment\tthat\twould\talter\tits\tplan\tto\tpay\tan annual\tbenefit\tof\t3.0%\tinstead\tof\t2.5%.\tIn\taddition,\tthe\tcompany's\tactuary\trecently suggested\tthat\tthe\tcompany\tmay\tneed\tto\treduce\tits\tdiscount\trate\tfrom\t5%\tto\t3.5%\tin 2015\tin\tlight\tof\thistorically\tlow\tinterest\trates.\tTreating\teach\tpotential\tchange independently,\tcompute\tthe\teffect\tof\tthe\tchanges\ton\tthe\tcompany's\tPBO\tas\tof 12/31/2014\tand\tcomment\ton\thow\teach\twould\tbe\trecorded,\tshould\tthe\tchanges\tbe enacted. Critical\tThinking: 5. Compare\tpension\tand\tsalary\tinformation\tmale\tvs.\tfemale\temployees. Comment\ton possible\treasons\tfor\tthose\tdifferences.\tCan\tyou\tinfer\tanything\tabout\texpectations\tfor male\tvs.\tfemale\temployees\tfrom\tthe\tdata\tprovided?\tIt\tmay\tbe\tuseful\tto\tuse\ta\tPivot Table\tfor\tyour\tanalysis. 6. Assume\tPantherTec\tis\tconsidering\tinvesting\tin\tan\temployee\thealth\tand\twellbeing program. This\tprogram\twill\tinvolve\tan\tonsite\tfitness\tcenter\tand\tcomplementary\tdietary and\tfitness\tconsulting\tservices.\tDiscuss\thow\tthis\tprogram\tcould\taffect\tpension\tcosts.\tCan you\tassess\twhether\tthese\tconcerns\tappear\tto\tbe\tvalid\t(hint:\tconsider\tusing\t\"CORREL()\")? Keeping\tin\tmind\tthat\tmanagers\tof\tcompanies\thave\tboth\tsocial\tresponsibilities\tto employees\tand\tfiduciary\tresponsibilities\tto\tshareholders,\twhat\tconsiderations\tshould the\tmanager\ttake\tinto\taccount\twhen\tmaking\tthis\tdecision? 7. Comment\ton\tthe\ttrend\tin\tplan\tasset\treturns. Have\tmanagers\tbeen\taccurate\tin\ttheir expected\trates\tof\treturn? When\tpension\tplan\tassets\tconsistently\tdeviate\tfrom\texpected returns,\tmanagers\tmay\teither\tadjust\tthe\triskiness\tof\tthe\tplan's\tinvestment\tportfolio,\tor adjust\tthe\texpected\trate\tof\treturn. Discuss\tthe\tethical\tdilemma\tmanagers\tface\twhen confronted\twith\tthose\ttwo\tchoices