Question

Part 1 : Retirement Planning Analysis – A case study (100 points)

Wow….Planning for retirement can be so HARD to understand!

Read the following articles:

Adjusting Expectations

http://www.fa-mag.com/news/adjusting-expectations-13473.html

Drifting Off Track

http://www.fa-mag.com/news/drifting-off-track-13452.html

You are CPA's and your largest client asks you for assistance with retirement planning providing you with the following data – make sure your numbers are correct – their future depends upon it.

Case Data:

a) They will begin investing a monthly amount on 1/1/14 at age (a) 25, (b) 45, Yrs Old.

b) They will retire at age (a) 55, (b) 70, on 1/1/XX of that year.

c) They will stop contributing upon retirement and begin withdrawing a monthly amount of $3,500. (Their net nest egg will continue to earn interest)

d) They will earn an annual rate of: (a) 2%, (b) 8%.

e) They will invest 10% of their gross annual salary of $60k on an equal monthly basis.

f) When will their retirement money run out in each scenario if it does not last until they are 85? (Provide both the Number of Total Months and also Years and Month)

g) How much will they leave to their beneficiaries, should they die at age 85 on 1/1/XX of that year, in each scenario (Include this short comment within your Excel file)?

h) What advice would you give to yourself and your family members regarding retirement investing? (Include this short comment within your Excel file)

Provide your client with the following: (This is what you hand in to me)

A) An Excel-based, multi-sheet, data file with the first sheet being a tabular summary with one additional WS Tab for each of the scenarios providing all monthly calculation tables (Note: I provided you with an example of how to “start” this project…use this and you will be in great shape!).

B) Link all amounts/figures shown in this Summary to the WS pages from which they originate.

NOTE 1: Make sure that you are utilizing the power of excel's calculations and formulas at every stage in the table. You will be graded on your ability to use excel.

NOTE 2: Utilize one (1) Excel file with multiple sheets for each of the many scenarios (name each sheet appropriately), with the first sheet (Sheet 1) being the "Final Analysis" table appropriately named as such, followed by the first scenario tabbed (25, 55, 2%), then (25, 55, 8%), (25, 70, 2%) and so on and so forth.

NOTE 3: This is a planning tool…format and formulate it as such…as in any research case start with the main details as inputs for all spreadsheet calculations to utilize. This should provide a dynamic, not static, planning tool.

NOTE 4: Place a quotation from each article above plus one additional quote that you find on your own, regarding the importance of starting your retirement planning early (remember to utilize proper citations).