I need help with this assignment, along with the excel file I need to write a memo answering the questions in the word doc.

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 125404 Matha Dauphin 163061 Jeta Derose 287009 Raymundo Derrow 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 F F 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 6/17/1990 6/30/1983 6/4/1952 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 4-Jan-13 16-Jul-11 8-Apr-90 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 144,264 149,571 295,888 111,518 80,008 157,148 201,252 81,672 114,107 180,848 43,313 62,842 102,294 89,211 220,417 39,288 225,010 151,570 155,823 119,779 160,727 130,926 184,676 230,611 130,594 52,547 175,748 300,997 31,636 125,818 46,762 33,117 309,525 134,070 277,756 211,906 246,956 96,819 132,560 95,922 29,434 237,545 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 314134 Corey Hulme 217730 Elvin Hunger 413765 Yolande Huntzinger 238807 Claud Innes 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 M M F M 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 5/23/1977 10/30/1976 7/31/1957 4/25/1961 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 23-Nov-00 13-Oct-98 11-Apr-83 7-Jun-84 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 41,013 35,538 204,165 41,172 133,641 82,318 151,561 44,845 390,826 76,100 140,571 87,129 63,598 32,787 56,345 253,725 59,439 401,180 147,320 31,741 43,820 44,555 54,127 409,099 367,074 36,483 54,123 148,198 159,342 121,602 93,189 365,578 92,067 85,751 193,912 303,644 135,743 150,421 34,601 105,183 29,626 40,501 149,212 152,437 148,534 72,361 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 406879 Lamont Minter 255387 Rogelio Mitschke 183719 Deangelo Mleczko 420651 Freddy Monica 370452 Micah Monroe 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 M M M M M 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 4/23/1976 8/10/1988 8/20/1963 9/10/1977 5/3/1974 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 5-Aug-01 16-Sep-13 14-Aug-90 25-Jul-99 16-Jul-99 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 470,148 220,953 94,092 83,173 210,783 99,923 95,096 445,487 35,394 42,276 158,655 196,026 219,053 53,494 36,761 148,741 215,376 88,854 169,599 96,752 264,813 40,031 124,617 176,787 83,989 327,200 208,951 42,551 90,521 366,589 133,744 59,687 254,068 84,356 52,137 43,015 228,158 68,396 191,298 329,968 31,422 89,848 150,068 206,643 68,499 147,710 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 118518 Mack Swayne 328325 Skye Tadlock 445387 Antony Tarr 384224 Eboni Tellez 287820 Camille Teston 300781 Thersa Tiedemann 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 M F M F F F 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 12/23/1991 9/16/1971 4/5/1954 7/10/1984 7/10/1955 8/10/1989 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 3-Apr-12 7-Jul-98 13-Aug-80 5-Jan-06 4-Mar-85 28-Feb-12 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 242,569 328,194 162,418 38,715 41,289 94,443 138,008 148,837 69,294 125,347 47,339 78,169 198,894 92,649 166,065 163,594 170,771 143,948 74,531 422,813 103,821 180,059 52,958 296,725 157,553 75,394 138,146 32,860 213,300 39,820 30,132 100,975 42,226 109,053 181,790 187,447 71,277 245,407 29,744 31,313 46,438 361,884 415,769 35,046 341,841 49,503 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 F M M M M M M M F M F M M M F M F M M M 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 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 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 108,085 231,133 276,785 38,425 61,346 165,840 35,588 237,616 188,961 217,969 30,853 108,208 156,925 129,886 275,654 111,951 87,138 65,193 109,198 254,964 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 62 63 63 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 9 8 9 Anticipated Life Span 87 80 85 81 78 78 82 81 79 79 84 80 80 87 81 81 78 87 80 89 85 82 82 75 79 75 84 87 87 92 82 86 76 84 77 76 84 79 81 88 89 81 Forecasted Retirement Salary 244,873.24 154,058.13 320,003.24 114,863.54 291,007.28 340,145.17 217,653.80 106,563.44 117,530.21 186,273.44 123,408.44 115,842.96 359,531.90 175,219.69 287,594.19 192,003.71 238,713.11 263,697.56 175,380.16 287,160.43 384,853.81 382,145.28 373,551.76 283,622.44 458,594.72 96,866.51 240,777.03 325,528.19 109,778.93 284,633.35 122,288.85 148,029.94 497,229.86 363,241.97 286,088.68 375,755.78 528,353.86 221,080.67 221,524.16 356,936.22 126,834.46 244,671.35 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 60 62 60 61 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 8 5 7 6 88 80 78 81 80 81 86 84 82 80 76 83 82 89 82 86 84 88 84 89 76 84 80 82 85 89 81 78 84 81 76 77 85 87 82 76 81 84 82 88 88 81 82 79 86 78 151,777.09 182,986.21 306,090.47 127,024.52 235,888.21 187,729.80 374,698.84 132,602.12 480,666.68 315,688.31 167,849.13 247,091.70 179,872.85 178,668.63 107,923.64 406,983.53 244,908.29 525,603.10 471,974.36 128,144.80 91,838.64 131,938.62 150,231.71 503,140.17 533,732.85 102,763.44 94,481.45 232,633.76 172,328.81 154,041.78 198,309.70 532,421.77 353,408.07 237,500.92 245,641.92 460,911.45 157,363.34 177,764.93 77,600.68 276,824.03 142,897.50 200,161.64 324,482.79 339,873.04 170,422.44 91,664.75 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 62 66 65 60 63 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 6 8 3 7 10 79 80 83 84 76 82 75 77 84 78 84 82 76 82 82 87 80 81 76 78 85 84 78 84 87 81 86 84 80 86 84 82 77 81 83 81 85 83 78 80 86 80 82 75 81 84 498,780.01 288,293.55 334,312.53 308,411.14 217,106.49 122,892.69 97,948.88 458,851.61 158,193.57 210,693.51 532,829.77 348,721.14 232,393.33 108,587.47 187,218.64 181,052.70 221,837.28 239,390.27 249,764.68 333,826.65 569,801.81 206,971.91 365,899.09 411,257.30 134,268.35 414,487.17 254,342.76 127,353.61 299,713.64 560,249.21 301,874.01 100,099.39 261,690.04 275,452.60 133,340.16 166,038.29 246,752.47 243,230.64 272,088.52 535,692.09 145,743.14 212,642.48 549,527.49 327,566.08 165,188.70 321,518.46 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 60 63 61 61 62 61 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 3 4 8 8 4 8 79 78 82 80 78 84 89 83 85 82 87 78 85 84 79 76 80 88 80 80 77 84 89 83 76 80 85 88 78 78 91 79 77 84 84 77 76 79 85 86 77 86 80 87 81 88 360,845.39 415,746.34 283,452.21 167,611.45 168,257.02 129,387.59 167,988.11 175,892.70 175,095.80 408,290.49 132,541.11 123,204.18 296,412.14 229,806.02 171,046.95 168,501.82 175,894.13 387,683.34 231,333.49 462,019.18 120,356.99 319,138.73 107,658.18 515,713.91 162,279.59 207,670.90 270,102.24 66,418.60 254,691.35 121,105.28 136,275.84 235,261.38 129,508.34 271,250.37 410,627.89 315,797.41 87,661.72 354,700.68 70,513.84 145,401.29 183,627.96 560,650.12 428,242.07 139,248.65 392,216.00 197,898.36 60 65 62 60 61 65 63 63 61 66 60 65 62 61 66 62 62 64 61 61 5 10 7 10 3 8 8 8 6 6 10 5 9 9 8 5 3 9 10 10 82 82 79 84 77 82 82 80 86 78 92 79 83 83 87 79 81 81 82 82 182,412.82 275,984.89 386,975.90 156,366.34 239,284.44 357,299.59 136,642.05 292,237.71 258,878.53 224,508.07 138,405.43 377,716.17 389,033.05 432,739.15 440,797.58 211,306.76 269,332.44 113,610.26 166,155.57 270,491.31 The company invests plan assets in a diversified equites fund with JP Morgan Chase. The fund performance for the past te years is listed below. Assume there were no accumulated gains or losses prior to 2005. All contributons are made at the en of the calendar year, so returns generated by the fund are based on the beginning balance of each period. Beginning Balance 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 $ $ $ $ $ $ $ $ $ $ 55,520,201.33 59,779,381.54 70,588,612.96 76,399,564.30 51,195,320.08 56,398,513.34 65,185,130.49 67,896,130.66 79,031,686.58 96,563,281.91 Return on Plan Assets Contributions Ending Balance $ 2,681,625.72 $ 1,577,554.49 $ 59,779,381.54 $ 9,166,619.91 $ 1,642,611.51 $ 70,588,612.96 $ 3,758,164.91 $ 2,052,786.44 $ 76,399,564.30 $ (26,859,599.95) $ 1,655,355.73 $ 51,195,320.08 $ 4,235,336.12 $ 967,857.14 $ 56,398,513.34 $ 7,652,561.45 $ 1,134,055.71 $ 65,185,130.49 $ 1,256,203.34 $ 1,454,796.83 $ 67,896,130.66 $ 9,788,470.45 $ 1,347,085.47 $ 79,031,686.58 $ 15,958,824.08 $ 1,572,771.25 $ 96,563,281.91 $ 11,959,482.28 $ 2,133,423.47 $ 110,656,187.66 Expected Return Percentage 9.94% 10.15% 7.99% 11.80% 11.40% 10.02% 11.15% 10.72% 10.32% 9.20% Accumulated performance gain (loss) as of 12/31/2013 Loss amortization as of 12/31/2013 Gain (loss) during 2014 Accumulated gain (loss) as of 12/31/2014 performance for the past ten ibutons are made at the end ach period. Gain (Loss) $ (2,839,026.07) $ 3,098,244.39 $ (1,884,898.77) $ (35,874,761.62) $ (1,600,930.37) $ 1,998,679.63 $ (6,011,938.71) $ 2,510,938.59 $ 7,805,810.47 $ 3,075,660.35 $ (32,797,882.45) $ 2,412,795.05 $ 3,075,660.35 $ (27,309,427.06) ACCT 4113, Summer 2016, Prof. Siva Nathan Excel Project 30 points Due Date: Sunday, July 24, 2016, 11:59pm, via Dropbox in D2L/Brightspace Data Analytics and Critical Thinking Project based on Chapter 17 - Pensions PantherTec offers a defined benefit pension plan for its employees. The company's pension formula pays 2% per year of service time of the employee's final year's salary, with a maximum benefit of 75% of final salary. To illustrate, an employee earning $200,000 and retiring after 20 years would receive an $80,000 annual benefit (2% x 20 years x $200,000). Required: Write a project summary addressing each of the following requirements, citing accompanying tables and figures as necessary. Your final deliverable should include the following: (i) a report (Word or PDF file) with responses to each of the points listed below under \"Analyses\" and \"Critical Thinking.\" (ii) an Excel file with supporting analysis. Two files (an Excel file and a Word/PDF file) should be uploaded onto the D2L Dropbox by the due date. The assignment should be done individually by each student in the class. You are allowed to discuss the assignment with other students in the class. Accompanying this project is an Excel spreadsheet with two tabs. The first, \"Employee Information,\" lists the 200 employees of PantherTec, along with information which may or may not be needed to compute pension obligations. The second tab, \"Pension Plan Asset Performance,\" outlines how PantherTec's plan assets have performed over the last several periods, as well as expected returns on plan assets over that period. There are no prior service costs or other accumulated losses on the projected benefit obligation. Analyses: 1. Compute the projected benefit obligation (PBO) as of 12/31/2014 using the \"Employee Information\" tab assuming the company's actuary recommends a 6% discount rate. For simplicity, assume that all retirements occur on December 31 of the retirement year, and partial years worked provide no benefit. In other words, if a 40 year-old employee begins working on 7/1/2014 and is expected to retire at 65, then assume that his retirement date is 12/31/2039 and that he will accrue 25 years of service (2015 through 2039). Computations should be performed in Excel. 2. Compute Pension Expense for 2014, separately identifying each component. Assume that no actuarial assumptions changed between 2013 and 2014 (i.e., same projected ending salaries, benefit periods, etc.). 3. Describe the funding status for the plan. Is the plan over- or under-funded? Critical Thinking: 4. Compare differences between current and projected salaries as well as the PBO for male vs. female employees. Comment on possible reasons for those differences. It may be useful to use a Pivot Table for your analysis. 5. Assume PantherTec is considering investing in an employee health and wellbeing program. This program will involve an onsite fitness center and complementary dietary and fitness consulting services. Discuss how this program could affect pension costs. Keeping in mind that managers of companies have both social responsibilities to employees and fiduciary responsibilities to shareholders, what considerations should the manager take into account when making this decision? 6. Comment on the trend in plan asset returns. Have managers been accurate in their expected rates of return? When pension plan assets consistently deviate from expected returns, managers may either adjust the riskiness of the plan's investment portfolio, or adjust the expected rate of return. Discuss the ethical dilemma managers face when confronted with those two choices