Actuarial Outpost > SoA EOM 3 Task 5 (Excel help)
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

 Enter your email to subscribe to DW Simpson weekly actuarial job updates. li.signup { display: block; text-align: center; text-size: .8; padding: 0px; margin: 8px; float: left; } Entry Level Casualty Health Life Pension All Jobs

#1
09-14-2016, 11:00 AM
 koudai8 SOA Join Date: Feb 2015 Posts: 23
EOM 3 Task 5 (Excel help)

Task 5 asks us to assess whether 12/17 contribution plan is appropriate, and if not, provide our own estimate.

I have a couple of questions:

1. the 12/17 refers to the 12% "up to covered earnings" and 17% "in excess of covered earnings", right?

2. When I was trying to find the rate for certain employee so that he/she could have replacement ratio of 70%, I used goal seek by selecting "By changing cells" to be the cells containing 12% and 17%, only the first cell gets changed. For example, if I select 17% first then 12%, only the 17% would change. What am I doing wrong?

3. I tried a different approach using Solver. I selected the target cell and chose "set it to" 0.7, and the cells containing 12% and 17% to "by changing cells". However, the solver returned that it could not find a solution, with all three methods of equation solving. What is wrong in this case?

4. Lastly, both "up to" and "in excess" rates are capped by 15% in total right? In other words, "up to percentage" * covered + "in excess percentage" * (pensionable - covered) must be less than 15% of earned income. Is that correct?

Thanks!
__________________
ASA ERM Mods
ILALPM ILALFM DMAC FAC
#2
09-14-2016, 11:52 AM
 SirCountador Member Non-Actuary Join Date: Aug 2012 Posts: 500

1-yes
2-goal seek only allows 1 cell at a time as you saw. you're looking for scenario manager or solver.
3-is there something wrong?
4-if you're not sure about something just assume and defend the assumption. what you said seems reasonable but i forget the wording they used
#3
09-15-2016, 12:34 AM
 koudai8 SOA Join Date: Feb 2015 Posts: 23

Quote:
 Originally Posted by SirCountador 1-yes 2-goal seek only allows 1 cell at a time as you saw. you're looking for scenario manager or solver. 3-is there something wrong? 4-if you're not sure about something just assume and defend the assumption. what you said seems reasonable but i forget the wording they used
Thank you so much!
__________________
ASA ERM Mods
ILALPM ILALFM DMAC FAC

 Tags eom3