I'm working on this project that's due midnight Friday and I'm lost. I can't figure out if I have to use IF statements or what I'm missing. Can you please assist?

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 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 DOB year 6/11/1965 1965 9/29/1952 1952 3/10/1949 1949 1/4/1949 1949 11/2/1989 1989 8/16/1975 1975 2/17/1950 1950 12/8/1957 1957 9/30/1949 1949 11/25/1952 1952 5/5/1976 1976 11/25/1966 1966 12/21/1987 1987 1/26/1972 1972 5/29/1957 1957 9/27/1990 1990 4/1/1950 1950 1/28/1968 1968 9/20/1952 1952 11/25/1972 1972 9/7/1973 1973 4/7/1984 1984 11/14/1974 1974 10/21/1956 1956 11/26/1991 1991 9/2/1966 1966 7/19/1960 1960 9/13/1953 1953 5/22/1983 1983 4/30/1975 1975 8/10/1974 1974 5/15/1985 1985 4/26/1967 1967 11/8/1980 1980 9/25/1949 1949 5/16/1968 1968 7/28/1971 1971 Date of Employment 13-Nov-03 2003 1-Aug-76 1976 14-May-84 1984 21-Dec-72 1972 1-Feb-11 2011 1-Nov-05 2005 6-Aug-72 1972 26-Feb-97 1997 22-May-73 1973 10-Jun-86 1986 23-Jun-98 1998 4-Oct-98 1998 13-Mar-12 2012 6-Oct-96 1996 22-Jan-82 1982 5-Apr-12 2012 10-Nov-77 1977 23-Dec-94 1994 8-Feb-92 1992 16-Oct-13 2013 7-Jul-98 1998 28-Nov-07 2007 27-Nov-98 1998 26-Nov-78 1978 21-Feb-14 2014 24-Jul-90 1990 4-Jun-89 1989 11-Jun-77 1977 21-Dec-05 2005 21-Jun-98 1998 23-Aug-97 1997 24-Apr-07 2007 19-Apr-89 1989 25-Apr-09 2009 7-Dec-71 1971 23-Oct-95 1995 21-Jul-12 2012 year 207185 Eliseo Cutshall 438501 Devon Dalpiaz 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 M M 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 9/20/1973 1973 4/21/1965 1965 6/17/1990 1990 6/30/1983 1983 6/4/1952 1952 9/20/1982 1982 2/19/1990 1990 3/28/1965 1965 4/3/1977 1977 4/9/1966 1966 9/21/1972 1972 8/24/1978 1978 12/26/1976 1976 8/6/1961 1961 3/8/1992 1992 2/1/1957 1957 5/24/1977 1977 11/27/1983 1983 8/23/1991 1991 3/10/1964 1964 11/2/1961 1961 2/18/1991 1991 12/22/1961 1961 6/2/1989 1989 4/2/1981 1981 6/4/1969 1969 9/2/1977 1977 1/21/1975 1975 2/7/1955 1955 5/13/1960 1960 11/2/1974 1974 3/24/1962 1962 2/27/1966 1966 6/7/1952 1952 6/6/1962 1962 5/4/1968 1968 4/15/1975 1975 9/10/1992 1992 5/15/1978 1978 2/16/1957 1957 6/11/1967 1967 5/27/1957 1957 4/4/1957 1957 6-Jul-96 1996 13-Jun-87 1987 4-Jan-13 2013 16-Jul-11 2011 8-Apr-90 1990 21-Aug-05 2005 25-Sep-12 2012 25-Jan-87 1987 12-Dec-08 2008 17-Oct-92 1992 24-Oct-12 2012 11-Apr-00 2000 11-Aug-04 2004 14-Sep-83 1983 26-Nov-13 2013 9-Sep-79 1979 27-Mar-00 2000 3-Jun-05 2005 27-Jan-12 2012 9-May-87 1987 2-Oct-85 1985 14-Sep-13 2013 3-Aug-92 1992 17-Jan-11 2011 18-May-12 2012 24-Sep-96 1996 15-Dec-06 2006 10-May-02 2002 28-Jan-77 1977 22-Nov-82 1982 6-Sep-96 1996 5-Jan-86 1986 14-Jun-88 1988 2-Nov-74 1974 11-Mar-84 1984 21-Aug-90 1990 4-Apr-06 2006 16-Oct-14 2014 2-Mar-01 2001 25-Dec-79 1979 23-May-89 1989 16-Oct-79 1979 8-Oct-79 1979 227843 Joi Holland 121326 Eugenie Hooser 404882 Ayesha Horak 148870 Jae Houge 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 F F F M 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 6/19/1971 1971 6/14/1976 1976 4/20/1989 1989 8/17/1992 1992 5/23/1977 1977 10/30/1976 1976 7/31/1957 1957 4/25/1961 1961 6/2/1950 1950 1/3/1958 1958 9/27/1991 1991 5/4/1987 1987 12/16/1953 1953 12/6/1955 1955 4/23/1954 1954 9/13/1948 1948 11/23/1987 1987 5/23/1993 1993 3/6/1986 1986 4/15/1968 1968 11/30/1951 1951 4/10/1970 1970 7/14/1990 1990 3/30/1959 1959 7/17/1954 1954 7/3/1979 1979 2/5/1964 1964 9/23/1990 1990 7/22/1977 1977 4/19/1992 1992 8/9/1982 1982 4/2/1979 1979 4/7/1959 1959 7/20/1959 1959 4/26/1957 1957 7/24/1979 1979 10/15/1988 1988 12/24/1970 1970 8/1/1975 1975 10/31/1966 1966 8/10/1949 1949 1/25/1987 1987 7/11/1974 1974 19-Jul-94 1994 6-May-13 2013 2-Jun-14 2014 3-Oct-14 2014 23-Nov-00 2000 13-Oct-98 1998 11-Apr-83 1983 7-Jun-84 1984 21-Sep-72 1972 2-Feb-80 1980 5-Jun-14 2014 12-Jan-14 2014 19-Aug-95 1995 12-Nov-77 1977 20-Jul-79 1979 9-Jan-70 1970 8-Mar-13 2013 12-Apr-12 2012 8-May-08 2008 12-Dec-90 1990 26-Apr-87 1987 2-Jul-92 1992 25-Apr-12 2012 20-Dec-87 1987 15-Mar-76 1976 9-Oct-04 2004 9-Oct-94 1994 17-Sep-13 2013 19-Mar-05 2005 13-Jul-14 2014 15-Jun-04 2004 12-Aug-04 2004 14-Sep-85 1985 23-Mar-05 2005 8-Feb-93 1993 19-May-01 2001 28-May-13 2013 9-Jun-92 1992 13-Sep-97 1997 15-Jul-88 1988 22-Nov-71 1971 16-Dec-09 2009 3-Aug-96 1996 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 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 M F M M M F 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 10/1/1985 1985 4/1/1949 1949 9/30/1990 1990 3/3/1961 1961 4/24/1975 1975 7/26/1989 1989 4/23/1976 1976 8/10/1988 1988 8/20/1963 1963 9/10/1977 1977 5/3/1974 1974 4/12/1963 1963 7/11/1961 1961 2/24/1967 1967 6/7/1988 1988 3/16/1990 1990 7/16/1962 1962 1/11/1954 1954 9/12/1958 1958 6/2/1977 1977 10/28/1986 1986 12/23/1976 1976 11/16/1959 1959 5/11/1959 1959 9/1/1974 1974 3/22/1950 1950 4/9/1952 1952 8/4/1949 1949 7/11/1978 1978 6/14/1982 1982 10/15/1953 1953 1/15/1954 1954 9/21/1966 1966 6/13/1970 1970 5/22/1968 1968 12/9/1949 1949 10/17/1980 1980 2/17/1967 1967 7/29/1965 1965 2/14/1956 1956 8/16/1978 1978 8/31/1985 1985 1/9/1975 1975 16-May-12 2012 18-Mar-73 1973 25-Feb-12 2012 21-Jan-02 2002 8-Mar-97 1997 20-Nov-12 2012 5-Aug-01 2001 16-Sep-13 2013 14-Aug-90 1990 25-Jul-99 1999 16-Jul-99 1999 13-Apr-85 1985 21-Feb-83 1983 23-Dec-97 1997 2-Nov-12 2012 16-Nov-12 2012 3-Jan-84 1984 9-Apr-76 1976 19-Jan-81 1981 13-Feb-03 2003 24-Oct-14 2014 18-Jul-02 2002 7-Sep-84 1984 10-Oct-82 1982 22-Mar-97 1997 20-Mar-80 1980 15-Apr-80 1980 25-Nov-73 1973 18-Apr-00 2000 15-Apr-04 2004 22-Nov-90 1990 19-Aug-77 1977 15-May-88 1988 3-Jan-02 2002 20-Jan-92 1992 7-Jun-83 1983 7-Sep-02 2002 6-Mar-05 2005 18-Oct-10 2010 21-Jul-90 1990 10-Oct-01 2001 8-Oct-12 2012 25-Apr-12 2012 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 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 M M M F F 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/4/1983 1983 12/28/1979 1979 12/9/1979 1979 11/21/1968 1968 12/5/1956 1956 3/15/1959 1959 5/26/1967 1967 4/25/1989 1989 12/23/1991 1991 9/16/1971 1971 4/5/1954 1954 7/10/1984 1984 7/10/1955 1955 8/10/1989 1989 4/10/1967 1967 6/3/1955 1955 4/29/1962 1962 1/6/1991 1991 7/26/1992 1992 5/20/1972 1972 9/23/1983 1983 10/17/1958 1958 6/3/1962 1962 9/3/1949 1949 1/19/1989 1989 2/13/1987 1987 4/1/1980 1980 6/14/1990 1990 9/13/1961 1961 1/28/1971 1971 7/7/1982 1982 11/15/1964 1964 8/25/1964 1964 5/21/1955 1955 28-May-07 2007 8-Feb-01 2001 17-Dec-01 2001 25-Dec-90 1990 22-Mar-90 1990 5-Sep-87 1987 1-May-93 1993 17-Mar-13 2013 3-Apr-12 2012 7-Jul-98 1998 13-Aug-80 1980 5-Jan-06 2006 4-Mar-85 1985 28-Feb-12 2012 5-Nov-12 2012 10-Oct-77 1977 17-Feb-87 1987 20-Oct-14 2014 12-Nov-14 2014 15-Sep-94 1994 27-Nov-14 2014 22-Dec-80 1980 8-Jun-84 1984 4-Jul-72 1972 26-May-11 2011 27-Jan-14 2014 17-Jan-02 2002 8-Jun-12 2012 25-Jun-83 1983 7-Jul-93 1993 2-Jan-12 2012 18-Sep-86 1986 17-Aug-08 2008 17-Sep-85 1985 Salary as of Anticipated 12/31/2014 Retirement Age $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 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 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 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 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 Forecasted Age at Retirement Employment 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 38 24 35 23 22 30 22 40 24 34 22 32 25 24 25 22 27 26 40 41 25 23 24 22 23 24 29 24 22 23 23 22 22 29 22 27 41 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 120,200 97,700 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 64 64 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 5 9 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 78 82 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 182,300.00 121,700.00 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 23 22 23 28 38 23 22 22 31 26 40 22 28 22 21 22 23 22 21 23 24 22 31 22 31 27 29 27 22 22 22 24 22 22 22 22 31 22 23 22 22 22 22 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 147,100 62,000 67,000 84,800 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 61 65 63 64 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 4 6 9 7 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 82 84 87 80 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 220,900.00 93,050.00 262,700.00 246,500.00 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 23 37 25 22 23 22 26 23 22 22 23 27 42 22 25 22 26 19 22 22 36 22 22 28 22 25 30 23 28 22 22 25 26 46 36 22 25 22 22 22 22 22 22 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 103,600 149,200 90,000 148,100 93,900 50,300 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 64 66 60 63 62 62 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 7 8 9 6 6 5 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 80 86 82 79 79 83 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 236,600.00 152,800.00 335,800.00 189,100.00 122,650.00 185,350.00 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 27 24 22 41 22 23 25 25 27 22 25 22 22 30 24 22 22 22 23 26 28 26 25 23 23 30 28 24 22 22 37 23 22 32 24 34 22 38 45 34 23 27 37 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 44,100 56,000 129,000 147,200 128,600 125,000 52,700 79,500 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 60 60 60 62 65 66 66 62 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 5 10 5 4 7 6 8 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 83 83 78 77 80 84 86 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 131,100.00 123,500.00 202,750.00 192,800.00 136,000.00 156,900.00 97,350.00 162,750.00 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 24 22 22 22 34 28 26 24 21 27 26 22 30 23 45 22 25 23 22 22 31 22 22 23 22 27 22 22 22 22 30 22 44 30 2014 Years for retirement payments Years worked until 2014 24 18 19 15 14 15 17 16 14 17 19 17 14 26 16 15 13 26 15 19 18 19 20 11 16 13 19 23 27 27 18 18 15 19 12 13 19 11 38 30 42 3 9 42 17 41 28 16 16 2 18 32 2 37 20 22 1 16 7 16 36 0 24 25 37 9 16 17 7 25 5 43 19 2 Age in 2014 49 62 65 65 25 39 64 57 65 62 38 48 27 42 57 24 64 46 62 42 41 30 40 58 23 48 54 61 31 39 40 29 47 34 65 46 43 Total service years 25 39 31 44 41 32 43 26 42 29 42 32 40 36 41 43 39 35 26 25 41 39 37 43 39 39 34 38 38 38 40 43 40 34 44 37 24 annual benefit 58,850.00 151,430.00 151,050.00 75,495.00 5,707.50 22,635.00 187,950.00 99,195.00 144,832.50 97,020.00 92,400.00 37,760.00 8,070.00 88,200.00 176,080.00 11,477.50 130,610.00 45,800.00 67,100.00 3,465.00 64,660.00 24,255.00 122,140.00 122,400.00 115,860.00 177,125.00 75,942.50 33,682.50 52,640.00 63,240.00 31,815.00 136,281.25 19,818.75 155,230.00 78,945.00 7,130.00 PVA 582,534.96 144,218.90 143,857.00 140,376.16 56,496.49 224,055.72 178,999.82 705,060.20 137,935.58 92,399.91 914,634.34 409,234.20 79,882.02 1,031,021.84 1,251,544.95 113,611.64 242,857.54 475,388.43 237,933.25 34,298.79 911,314.16 240,091.51 1,565,975.26 708,251.69 1,202,587.41 1,258,972.62 72,326.12 333,410.94 521,064.41 625,989.99 314,925.23 1,414,553.04 196,178.67 147,837.95 922,834.68 70,577.30 PVA today 14 18 25 23 19 22 13 18 14 16 16 23 17 23 16 14 15 21 24 18 21 22 25 22 24 13 18 13 17 21 25 17 18 20 22 11 10 22 22 18 17 20 22 18 27 1 3 24 9 2 27 6 22 2 14 10 31 1 35 14 9 2 27 29 1 22 3 2 18 8 12 37 32 18 28 26 40 30 24 8 0 13 35 25 35 35 41 49 24 31 62 32 24 49 37 48 42 36 38 53 22 57 37 31 23 50 53 23 53 25 33 45 37 39 59 54 40 52 48 62 52 46 39 22 36 57 47 57 57 41 42 39 35 25 39 44 39 35 39 24 40 38 38 42 41 43 38 43 42 42 40 30 39 32 37 36 39 44 42 39 41 39 41 38 44 35 39 41 43 38 40 39 82,035.00 82,147.50 6,020.00 20,400.00 141,720.00 24,457.50 13,935.00 70,807.50 32,587.50 128,535.00 9,155.00 74,235.00 25,100.00 177,475.00 5,985.00 74,025.00 42,175.00 47,857.50 8,845.00 179,313.75 96,171.25 3,282.50 83,820.00 19,237.50 4,190.00 48,262.50 27,460.00 54,105.00 124,597.50 109,280.00 84,150.00 69,090.00 116,805.00 167,000.00 71,400.00 97,920.00 30,930.00 62,790.00 116,025.00 158,281.25 80,587.50 62,912.50 812,034.93 852,663.12 59,589.81 201,932.26 134,971.29 375,971.70 137,937.55 627,584.57 322,571.93 1,449,112.59 90,622.05 734,825.54 248,455.86 1,026,936.02 59,243.36 375,727.95 417,475.14 473,724.16 87,553.47 1,861,215.76 903,391.37 32,492.29 541,746.26 190,425.09 64,410.57 583,267.76 271,816.65 535,565.92 720,967.24 843,830.65 1,078,899.77 649,001.75 1,097,215.94 159,047.46 461,473.19 1,220,299.60 306,164.94 621,535.61 749,893.94 1,486,826.00 348,901.97 223,084.58 21 19 24 16 21 16 25 18 14 15 21 17 15 17 15 11 19 13 17 19 12 21 15 26 20 16 16 16 18 17 13 22 20 19 26 20 18 23 20 23 12 19 21 20 1 0 0 14 16 31 30 42 34 0 0 19 37 35 44 1 2 6 24 27 22 2 27 38 10 20 1 9 0 10 10 29 9 21 13 1 22 17 26 43 5 18 43 38 25 22 37 38 57 53 64 56 23 27 61 59 60 66 27 21 28 46 63 44 24 55 60 35 50 24 37 22 32 35 55 55 57 35 26 44 39 48 65 27 40 38 28 38 42 37 40 34 38 44 44 38 39 20 44 36 45 38 45 44 42 29 39 44 32 39 39 31 38 38 44 42 36 39 17 26 41 36 38 41 38 44 39 39 110,450.00 2,326.25 65,782.50 127,040.00 104,935.00 149,700.00 173,670.00 133,790.00 33,487.50 118,585.00 72,450.00 202,840.00 2,961.25 7,007.50 46,650.00 78,060.00 61,830.00 129,827.50 12,550.00 121,162.50 107,730.00 67,700.00 102,225.00 7,837.50 24,288.75 54,500.00 40,175.00 160,007.50 13,747.50 57,645.00 68,055.00 3,853.75 170,720.00 71,952.50 111,540.00 152,865.00 24,600.00 81,742.50 1,291,115.22 23,026.71 651,157.29 1,257,523.23 285,764.24 967,542.54 322,923.73 1,033,090.26 31,892.83 686,176.69 68,999.93 193,180.76 29,312.35 69,364.72 461,771.56 912,489.40 114,967.32 1,463,684.32 124,227.93 524,570.62 102,599.90 670,137.93 849,122.76 77,580.59 240,425.59 539,475.88 397,677.86 1,235,534.71 88,852.98 249,572.87 673,651.95 38,146.88 1,850,224.09 712,231.89 988,606.91 145,585.57 243,506.54 1,048,032.85 16 20 22 16 17 21 17 15 11 20 20 17 18 19 14 16 21 22 23 24 17 25 13 22 17 15 14 15 20 16 17 12 21 27 19 11 16 19 25 15 13 24 14 2 41 2 12 17 2 13 1 24 15 15 29 31 17 2 2 30 38 33 11 0 12 30 32 17 34 34 41 14 10 24 37 26 12 22 31 12 9 4 24 13 2 2 29 65 24 53 39 25 38 26 51 37 40 51 53 47 26 24 52 60 56 37 28 38 55 55 40 64 62 65 36 32 61 60 48 44 46 65 34 47 49 58 36 29 39 37 42 38 22 40 39 37 41 38 38 38 41 39 34 41 39 39 44 37 34 36 36 41 42 43 35 35 42 43 41 27 43 42 29 38 32 41 28 17 30 41 37 27 11,830.00 156,620.00 16,790.00 56,730.00 52,126.25 9,267.50 72,800.00 4,430.00 79,320.00 70,762.50 67,537.50 133,255.00 72,695.00 104,996.25 10,045.00 7,150.00 111,000.00 97,660.00 142,560.00 50,435.00 79,500.00 72,112.50 146,080.00 64,855.00 89,760.00 168,130.00 163,590.00 71,995.00 38,287.50 64,740.00 204,055.00 214,760.00 62,175.00 130,460.00 74,090.00 58,590.00 43,818.75 16,050.00 68,040.00 106,080.00 11,310.00 7,435.00 117,100.91 149,161.76 166,198.17 438,053.74 515,978.98 91,735.65 720,620.99 76,014.77 785,160.12 700,452.51 668,529.40 1,181,072.38 469,843.05 1,183,735.07 99,431.84 70,775.28 788,968.02 495,691.89 505,510.63 499,237.91 786,941.88 598,995.99 1,127,990.32 641,976.30 85,485.63 160,123.65 155,799.84 712,652.59 378,994.18 176,303.21 1,035,719.92 2,327,519.49 700,965.30 1,413,895.47 70,561.83 579,961.32 529,563.62 150,766.80 345,349.95 1,050,047.73 111,953.62 104,788.44 16 23 23 16 12 14 18 24 16 19 20 25 20 25 23 18 18 23 15 16 18 18 23 13 28 13 20 21 20 16 19 18 22 22 7 13 13 24 24 27 21 1 2 16 34 8 29 2 2 37 27 0 0 20 0 34 30 42 3 0 12 2 31 21 2 28 6 29 31 35 35 46 58 55 47 25 23 43 60 30 59 25 47 59 52 23 22 42 31 56 52 65 25 27 34 24 53 43 32 50 50 59 36 38 38 40 31 38 40 38 39 36 35 39 32 38 15 43 37 37 39 43 32 41 39 43 38 38 40 39 44 40 32 42 17 31 22,942.50 40,137.50 65,893.75 115,680.00 81,600.00 105,907.50 51,108.75 4,068.75 8,705.00 70,480.00 117,300.00 29,400.00 133,690.00 8,600.00 5,075.00 109,982.50 109,350.00 79,150.00 108,460.00 67,500.00 127,365.00 9,892.50 64,080.00 12,562.50 262,957.50 141,146.25 10,645.00 173,460.00 19,357.50 118,247.50 227,099.55 565,695.52 928,702.56 1,253,713.23 472,167.79 879,711.12 617,665.60 40,275.09 86,167.66 878,336.56 111,714.17 291,020.02 364,071.29 85,128.30 47,672.37 558,237.08 844,371.18 783,477.36 627,589.69 479,777.85 121,299.88 97,922.30 634,304.85 124,351.67 2,470,109.68 1,705,797.60 163,639.73 1,717,018.09 160,791.33 219,870.58 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