Suppose you are a recent business student graduate who is planning to offer personal financial advice to clients. In preparation for this business venture, you developed the following financial planning model.

Cells B3 through B8 are active cells that can be changed to reflect your client's actual data as far as age, salary, portfolio, investment rate, salary growth, and portfolio growth rates. Rows 11 and beyond will automatically recalculate based on the data entered on cells B3 through B8.

Once you developed the above model, you began to think of these questions:

1. Are salary growth rates truly expected to remain flat, that is, the same salary rate increase every year?
2. Is it reasonable to expect a flat portfolio growth rate increase each year? Financial markets vary widely over time, so you are now thinking that a flat growth rate is not reasonable.

Questions to answer:

1. Recreate the financial planning model exactly as presented in the scenario using Excel AND extend the solution to year 20. What would be your ending balance at age 50 assuming the variables given in Cells B3 through B8?
2. Based on your answer to question 1, what should your annual investment rate need to be if your goal were to reach $350,000 at the end of year 20? HINT: Use Goal Seekin Excel (Review Appendix A if needed)
3. Redesign the financial planning model to incorporate the random variability of the annual salary growth rate and the annual portfolio growth rate into the simulation model. Run the simulation 1 time. What would be your ending balance at age 50?
4. Based on your answer to question 3, what should your annual investment rate need to be if your goal were to reach approximately $400,000 at the end of year 20? HINT: Use Goal Seek.
5. Discuss how the financial planning model you developed can be used as a template to develop a financial plan for anyone.

Case Study Suppose you are a recent business student graduate who is planning to offer personal financial advice to clients. In preparation for this business venture, you developed the following financial planning model. 30 Lesson 11 Basic Scenario: Date Age Current Salary Current Portfolio Annual Investment Rate Salary Growth Rate Portfolio Growth Rate $60,000 $25,000 5% 4% 896 - N Year Beginning Balance $25,000 $30,120 $35,774 $42,011 $48,881 $56.442 $64.753 $73,881 $83,897 $94,879 O A Salary $60,000 $62.400 $64.896 $67.492 $70,192 $72,999 $75,919 $78,956 $82,114 $85,399 New Investment $3,000 $3,120 $3 245 $3,375 $3.510 $3,650 $3,796 $3.948 $4,106 $4,270 Earnings Ending Balance $2,120 $30.120 $2,534 $35,774 $2.992 $42,011 $3,496 $48,881 $4,051 $56,442 $4.661 $64.753 $5,332 $73,881 $6,068 $83,897 $6,876 $94,879 $7.761 $106.910 O O Case Study Suppose you are a recent business student graduate who is planning to offer personal financial advice to clients. In preparation for this business venture, you developed the following financial planning model. 30 Lesson 11 Basic Scenario: Date Age Current Salary Current Portfolio Annual Investment Rate Salary Growth Rate Portfolio Growth Rate $60,000 $25,000 5% 4% 896 - N Year Beginning Balance $25,000 $30,120 $35,774 $42,011 $48,881 $56.442 $64.753 $73,881 $83,897 $94,879 O A Salary $60,000 $62.400 $64.896 $67.492 $70,192 $72,999 $75,919 $78,956 $82,114 $85,399 New Investment $3,000 $3,120 $3 245 $3,375 $3.510 $3,650 $3,796 $3.948 $4,106 $4,270 Earnings Ending Balance $2,120 $30.120 $2,534 $35,774 $2.992 $42,011 $3,496 $48,881 $4,051 $56,442 $4.661 $64.753 $5,332 $73,881 $6,068 $83,897 $6,876 $94,879 $7.761 $106.910 O O