Microsoft Word - Pension Project SS20 Intermediate Financial Accounting III – 4113 Spring 2020 Analytics and Critical Thinking Project PantherTec sponsors a defined benefit pension plan for its...

1 answer below »
There are two files attached. Questions and information needed to solve questions are in those two files.


Microsoft Word - Pension Project SS20 Intermediate Financial Accounting III – 4113 Spring 2020 Analytics and Critical Thinking Project PantherTec sponsors 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 60% 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). 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, expected returns on plan assets over that period, and information on previous amortizations. There are no prior service costs or other accumulated losses on the projected benefit obligation. Required: Perform the following analyses and be prepared for a quiz on your work. 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? 4. PantherTec is considering a pension plan amendment that would alter its plan to pay an annual benefit of 2.75% instead of 2%. In addition, the company’s actuary recently suggested that the company may need to reduce its discount rate from 6% to 5% in 2015 in light of historically low interest rates. Treating each potential change independently, compute the effect of the changes on the company’s PBO as of 12/31/2014 and comment on how each would be recorded, should the changes be enacted. Critical Thinking: 5. Compare pension and salary information male vs. female employees. Comment on possible reasons for those differences. Can you infer anything about expectations for male vs. female employees from the data provided? It may be useful to use a Pivot Table for your analysis. 6. 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. Can you assess whether these concerns appear to be valid (hint: consider using “CORREL()”)? 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? 7. 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.
pension-project-ss20-cptwgjaw.pdf pension-project-c14ygdkh.xlsx
Answered Same DayMar 05, 2021

Answer To: Microsoft Word - Pension Project SS20 Intermediate Financial Accounting III – 4113 Spring 2020...

Preeta answered on Mar 05 2021
149 Votes
Employee Information
        Discount Rate    6%
        Annual Award    2%
        Year    2014
    EMPID    Employee Name    Gender    DOB    Date of Employment    Salary as of 12/31/2014    Anticipated Retirement Age    Health Score    Anticipated Life Span    Forecasted Retirement Salary    Retirement Date    Years of Service    Annual Award (2% of last drawn salary based on years of service    Years for retirement left from date of calculations    PV of Pension with 6% interest rate    Projected benefit Obligation    Pension to be paid in 2014    Total pension expense 2014    Pension with 2.75% annual award rate    PV of revised pension with 6% interest rate    Total Revised pension obligation with 2.75% annual award rate    PV of revised pension with 5% interest rate    Total Revised pension obligation with 5% discount rate    Correlation
    252160    Birdie Ahlers      F    6/11/65    11/13/03    $ 181,800    63    9    87    214,000.00    6/11/28    24    102,720.00    14    45433.15506108    4,923,637.88    FALSE    0.00    141,240.00    62,470.59    7,240,324.13    51,880.58    5,629,967.43    -0.036260181
    205188    Francesco Alers      M    9/29/52    11/14/03    $ 155,400    63    8    81    200,130.00    9/29/15    11    44,028.60    1    41536.4150943396        FALSE        60,539.33    57,112.57        41,932.00
    252579    Deneen Alonzo      F    3/10/49    11/15/03    $ 194,000    66    7    85    255,680.00    3/10/15    11    56,249.60    1    53065.6603773585        FALSE        77,343.20    72,965.28        53,571.05
    397185    Loyd Alt      M    1/4/49    11/16/03    $ 68,300    67    9    82    133,220.00    1/4/16    12    31,972.80    2    28455.6781772873        FALSE        43,962.60    39,126.56        29,000.27
    156175    Ignacio Arter      M    11/2/89    11/17/03    $ 41,900    63    4    77    76,730.00    11/2/52    48    46,038.00    38    5029.1381813309        FALSE        101,283.60    11,064.10        7,209.80
    118099    Garland Audette      M    8/16/75    11/18/03    $ 70,700    62    4    77    87,830.00    8/16/37    33    52,698.00    23    13796.1920725186        FALSE        79,705.72    20,866.74        17,156.96
    310475    Lynette Back      F    2/17/50    11/19/03    $ 169,800    65    4    82    184,290.00    2/17/15    11    40,543.80    1    38248.8679245283        FALSE        55,747.73    52,592.19        38,613.14
    307248    Marcelino Baldon      M    12/8/57    11/20/03    $ 213,200    66    9    82    245,660.00    12/8/23    20    98,264.00    9    58162.3106203144        FALSE        135,113.00    79,973.18        63,341.85
    431615    Randolph Balser      M    9/30/49    11/21/03    $ 137,700    66    7    80    181,050.00    9/30/15    11    39,831.00    1    37576.4150943396        FALSE        54,767.63    51,667.57        37,934.29
    336022    Daniel Bastarache      M    11/25/52    11/22/03    $ 134,400    63    7    80    181,650.00    11/25/15    12    43,596.00    1    41128.3018867924        FALSE        59,944.50    56,551.42        41,520.00
    448195    Garrett Batten      M    5/5/76    11/23/03    $ 155,600    64    10    83    184,010.00    5/5/40    36    110,406.00    26    24268.3460371821        FALSE        182,169.90    40,042.77        31,050.66
    279704    Keenan Baze      M    11/25/66    11/24/03    $ 64,000    64    8    81    110,410.00    11/25/30    27    59,621.40    16    23469.7425397528        FALSE        81,979.43    32,270.90        27,313.25
    176833    Gabriel Bea      M    12/21/87    11/25/03    $ 66,400    65    6    79    106,270.00    12/21/52    49    63,762.00    38    6965.2875606677        FALSE        143,198.82    15,642.87        9,985.47
    355450    Catina Beason      F    1/26/72    11/26/03    $ 104,200    60    8    86    175,570.00    1/26/32    28    98,319.20    18    34445.5212687905        FALSE        135,188.90    47,362.59        40,853.66
    351791    Dong Beaty      M    5/29/57    11/27/03    $ 211,500    66    9    82    240,690.00    5/29/23    19    91,462.20    9    54136.3356510759        FALSE        125,760.53    74,437.46        58,957.35
    163872    Vince Bhatti      M    9/27/90    11/28/03    $ 84,000    65    7    80    135,270.00    9/27/55    51    81,162.00    41    7444.1011309413        FALSE        189,716.18    17,400.59        10,979.73
    294706    Jospeh Blanco      M    4/1/50    11/29/03    $ 131,000    66    6    79    149,450.00    4/1/16    12    35,868.00    2    31922.3923104308        FALSE        49,318.50    43,893.29        32,533.33
    162642    Bettie Bolanos      F    1/28/68    11/30/03    $ 61,600    61    9    87    63,760.00    1/28/29    25    31,880.00    15    13302.410136249        FALSE        43,835.00    18,290.81        15,334.83
    334792    Dean Bopp      M    9/20/52    12/1/03    $ 115,200    66    8    81    119,250.00    9/20/18    14    33,390.00    4    26448.0074155175        FALSE        45,911.25    36,366.01        27,470.04
    193413    Kiesha Braithwaite      F    11/25/72    12/2/03    $ 65,400    66    7    85    84,000.00    11/25/38    34    50,400.00    24    12447.7188360401        FALSE        78,540.00    19,397.70        15,627.42
    446198    Hermina Brasch      F    9/7/73    12/3/03    $ 60,400    66    6    84    99,100.00    9/7/39    35    59,460.00    25    13854.0985697616        FALSE        95,383.75    22,224.28        17,558.70
    362755    Arnulfo Brissette      M    4/7/84    12/4/03    $ 61,800    62    8    81    85,080.00    4/7/46    42    51,048.00    32    7910.2651857655        FALSE        98,267.40    15,227.26        10,713.25
    403652    Waylon Brittan      M    11/14/74    12/5/03    $ 122,400    61    8    81    179,790.00    11/14/35    31    107,874.00    21    31731.7199133108        FALSE        153,270.98    45,085.49        38,720.55
    355869    Elijah Brownson      M    10/21/56    12/6/03    $ 123,200    65    3    76    160,370.00    10/21/21    17    54,525.80    7    36262.7711659488        FALSE        74,972.98    49,861.31        38,750.47
    411768    Clemente Bunde      M    11/26/91    12/7/03    $ 66,500    62    5    78    124,880.00    11/26/53    49    74,928.00    39    7721.7439254603        FALSE        168,275.80    17,341.75        11,175.36
    170758    Ruben Bussey      M    9/2/66    12/8/03    $ 113,600    63    3    76    188,510.00    9/2/29    25    94,255.00    15    39329.3182996284        FALSE        129,600.63    54,077.81        45,338.27
    248501    Shalon Carlson      F    7/19/60    12/9/03    $ 177,800    63    4    82    228,290.00    7/19/23    19    86,750.20    9    51347.3100909224        FALSE        119,281.53    70,602.55        55,919.95
    172755    Rosalva Carmen      F    9/13/53    12/10/03    $ 72,900    62    7    85    75,930.00    9/13/15    11    16,704.60    1    15759.0566037736        FALSE        22,968.83    21,668.70        15,909.14
    169528    Kristian Chappell      F    5/22/83    12/11/03    $ 74,300    60    9    87    128,480.00    5/22/43    39    77,088.00    29    14227.1098779373        FALSE        137,794.80    25,430.96        18,728.25
    274048    Moon Claw      F    4/30/75    12/12/03    $ 56,800    61    10    88    121,660.00    4/30/36    32    72,996.00    22    20256.762053913        FALSE        107,060.80    29,709.92        24,953.67
    262273    Ethan Clukey      M    8/10/74    12/13/03    $ 107,400    63    8    81    141,570.00    8/10/37    33    84,942.00    23    22237.5829637534        FALSE        128,474.78    33,634.34        27,654.68
    272818    Berenice Cogan     F    5/15/85    12/14/03    $ 82,800    65    5    83    138,120.00    5/15/50    46    82,872.00    36    10171.7732557572        FALSE        174,721.80    21,445.49        14,308.47
    321020    Zane Connelly      M    4/26/67    12/15/03    $ 164,800    62    4    77    224,830.00    4/26/29    25    112,415.00    15    46906.8518025858        FALSE        154,570.63    64,496.92        54,073.54
    404071    Tamela Cordes      F    11/8/80    12/16/03    $ 55,600    63    4    82    78,550.00    11/8/43    39    47,130.00    29    8698.1590980073        FALSE        84,244.88    15,547.96        11,450.06
    331133    Steve Cordray      M    9/25/49    12/17/03    $ 135,800    66    5    78    187,340.00    9/25/15    11    41,214.80    1    38881.8867924528        FALSE        56,670.35    53,462.59        39,252.19
    224616    Brandon Cregan      M    5/16/68    12/18/03    $ 133,800    64    4    77    156,630.00    5/16/32    28    87,712.80    18    30729.6348825577        FALSE        120,605.10    42,253.25        36,446.48
    390299    Argentina Curran      F    7/28/71    12/19/03    $ 93,100    65    6    84    103,690.00    7/28/36    32    62,214.00    22    17264.7020990485        FALSE        91,247.20    25,321.56        21,267.85
    207185    Eliseo Cutshall      M    9/20/73    12/20/03    $ 120,200    64    5    78    154,070.00    9/20/37    33    92,442.00    23    24201.0624230098        FALSE        139,818.53    36,604.11        30,096.46
    438501    Devon Dalpiaz      M    4/21/65    12/21/03    $ 97,700    64    9    82    109,100.00    4/21/29    25    54,550.00    15    22761.8090631237        FALSE        75,006.25    31,297.49        26,239.48
    125404    Matha Dauphin      F    6/17/90    12/22/03    $ 49,600    62    9    87    83,200.00    6/17/52    48    49,920.00    38    5453.2033974551        FALSE        109,824.00    11,997.05        7,817.74
    163061    Jetta Derose      F    6/30/83    12/23/03    $ 102,400    63    8    86    142,690.00    6/30/46    42    85,614.00    32    13266.5225594367        FALSE        164,806.95    25,538.06        17,967.48
    287009    Raymundo Derrow      M    6/4/52    12/24/03    $ 232,400    63    9    82    274,220.00    6/4/15    11    60,328.40    1    56913.5849056604        FALSE        82,951.55    78,256.18        57,455.62
    135098    Shawana Devivo      F    9/20/82    12/25/03    $ 65,200    62    6    84    83,020.00    9/20/44    40    49,812.00    30    8672.7738409205        FALSE        91,322.00    15,900.09        11,525.37
    246504    Andy Dively      M    2/19/90    12/26/03    $ 104,400    66    6    79    161,970.00    2/19/56    52    97,182.00    42    8408.9058792168        FALSE        231,617.10    20,041.23        12,520.89
    197072    Noble Dotson      M    3/28/65    12/27/03    $ 81,500    61    6    79    97,700.00    3/28/26    22    42,988.00    12    21363.7190014481        FALSE        59,108.50    29,375.11        23,937.33
    190186    Tory Dowdell      M    4/3/77    12/28/03    $ 49,100    66    7    80    73,400.00    4/3/43    39    44,040.00    29    8127.8787752226        FALSE        78,721.50    14,528.58        10,699.36
    363566    Elijah Eakle      M    4/9/66    12/29/03    $ 198,000    65    8    81    233,940.00    4/9/31    27    126,327.60    17    46913.5757267489        FALSE        173,700.45    64,506.17        55,116.31
    338019    Julio Edmisten      M    9/21/72    12/30/03    $ 133,600    64    7    80    167,350.00    9/21/36    32    100,410.00    22    27864.2867805551        FALSE        147,268.00    40,867.62        34,325.15
    165869    Natalie Elsen      F    8/24/78    12/31/03    $ 114,600    62    7    85    169,890.00    8/24/40    36    101,934.00    26    22406.1154733812        FALSE        168,191.10    36,970.09        28,667.99
    308478    Dillon Elser      M    12/26/76    1/1/04    $ 68,200    66    10    83    75,100.00    12/26/42    38    45,060.00    28    8815.094247689        FALSE        78,479.50    15,352.96        11,494.52
    132290    Heath Fava      M    8/6/61    1/2/04    $ 215,800    60    10    83    249,730.00    8/6/21    17    84,908.20    7    56468.802414868        FALSE        116,748.78    77,644.60        60,342.67
    293895    Jerrod Ferree      M    3/8/92    1/3/04    $ 91,800    63    6    79    135,360.00    3/8/55    51    81,216.00    41    7449.0539593717        FALSE        189,842.40    17,412.16        10,987.03
    186527    Benedict Forck      M    2/1/57    1/4/04    $ 73,200    63    4    77    130,620.00    2/1/20    16    41,798.40    6    29466.2226535138        FALSE        57,472.80    40,516.06        31,190.61
    135517    Amparo Frantz      F    5/24/77    1/5/04    $ 48,000    66    3    81    99,420.00    5/24/43    39    59,652.00    29    11009.178580826        FALSE        106,627.95    19,678.91        14,492.23
    111632    Jorge Freitag      M    11/27/83    1/6/04    $ 76,400    60    8    81    101,360.00    11/27/43    39    60,816.00    29    11224.0026247488        FALSE        108,708.60    20,062.90        14,775.02
    341678    Lawanna French      F    8/23/91    1/7/04    $ 66,200    64    10    88    110,930.00    8/23/55    51    66,558.00    41    6104.6361976441        FALSE        155,579.33    14,269.59        9,004.07
    177644    Cheryl Frost      F    3/10/64    1/8/04    $ 91,400    65    5    83    143,390.00    3/10/29    25    71,695.00    15    29915.8185294346        FALSE        98,580.63    41,134.25        34,486.52
    234729    Una Galante      F    11/2/61    1/9/04    $ 89,100    66    9    87    147,600.00    11/2/27    23    67,896.00    13    31832.2942541736        FALSE        93,357.00    43,769.40        36,006.70
    286590    Raina Gass      F    2/18/91    1/10/04    $ 41,600    62    6    84    81,140.00    2/18/53    49    48,684.00    39    5017.1548855849        FALSE        109,336.15    11,267.69        7,261.12
    259465    Delphia Gaxiola      F    12/22/61    1/11/04    $ 140,400    61    8    86    210,780.00    12/22/22    18    75,880.80    8    47608.5526673149        FALSE        104,336.10    65,461.76        51,359.11
    362336    Cliff Giesen      M    6/2/89    1/12/04    $ 55,700    61    10    83    63,350.00    6/2/50    46    38,010.00    36    4665.3767430656        FALSE        80,137.75    9,836.17        6,562.71
    383413    Karly Goldschmidt      F    4/2/81    1/13/04    $ 44,800    63    9    87    101,830.00    4/2/44    40    61,098.00    30    10637.7807783779        FALSE        112,013.00    19,502.60        14,136.70
    136328    Jasper Gowans      M    6/4/69    1/14/04    $ 96,800    64    4    77    157,550.00    6/4/33    29    91,379.00    19    30201.9483864109        FALSE        125,646.13    41,527.68        36,161.77
    190605    Jeffery Grasso      M    9/2/77    1/15/04    $ 70,100    65    10    83    140,270.00    9/2/42    38    84,162.00    28    16464.6241028406        FALSE        146,582.15    28,675.89        21,469.19
    126215    Hosea Guertin      M    1/21/75    1/16/04    $ 60,200    66    6    79    93,950.00    1/21/41    37    56,370.00    27    11689.3314360133        FALSE        95,594.13    19,823.16        15,098.61
    441309    Carol Guillermo      M    2/7/55    1/17/04    $ 127,900    66    10    83    164,950.00    2/7/21    17    56,083.00    7    37298.3981032815        FALSE        77,114.13    51,285.30        39,857.14
    348983    Gia Guillory      F    5/13/60    1/18/04    $ 107,600    64    7    85    111,050.00    5/13/24    20    44,420.00    10    24803.8959905695        FALSE        61,077.50    34,105.36        27,270.03
    424729    Nickole Gunning      F    11/2/74    1/19/04    $ 136,600    61    8    86    193,180.00    11/2/35    31    115,908.00    21    34094.9644187851        FALSE        164,685.95    48,443.26        41,604.29
    348564    Danial Hadley      M    3/24/62    1/20/04    $ 53,200    65    9    82    125,500.00    3/24/27    23    57,730.00    13    27066.0767540568        FALSE        79,378.75    37,215.86        30,615.45
    369641    Todd Hallowell      M    2/27/66    1/21/04    $ 162,800    61    6    79    168,470.00    2/27/27    23    77,496.20    13    36333.2426355056        FALSE        106,557.28    49,958.21        41,097.89
    266351    Joane Halsted      F    6/7/52    1/22/04    $ ...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?