Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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.

image text in transcribed

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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Gulf Capital And Islamic Finance The Rise Of The New Global Players

Authors: Aamir A. Rehman

1st Edition

0071621989

More Books

Students also viewed these Finance questions

Question

Where can I find the answers to Problems 6-2 and 6-1A

Answered: 1 week ago

Question

=+ Will it stimulate consumption, as the traditional view holds?

Answered: 1 week ago

Question

=+how will the policy affect the economy?

Answered: 1 week ago