Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Play the role of Tom Gifford, and develop a simulation model for financial planning. Write down the function for calculating the end-of-year balance as a
Play the role of Tom Gifford, and develop a simulation model for financial planning. Write down the function for calculating the end-of-year balance as a function of Toms annual salary (S), current portfolio (P), investment contributions rate (c), and annual portfolio growth rate (r). Calculate the value for this function using Toms initial estimates for these parameters. According to Kates comments, which of these parameters is uncertain?
Please answer the above question showing functions in excel. Thank you!
Case Problem: Four Corners What will your investment portfolio be worth in a year? The Human Resources Department at Four Corners Corporation was asked to develop a financial planning model that would help employees address this question. Tom Gifford was asked to lead this effort and decided to begin by developing a financial plan for himself. Tom has a degree in business and, at the age of 40, is making $85,000 per year. Through contributions to his company's retirement program and the receipt of a small inheritance, Tom has accumulated a portfolio valued at $50,000. Tom plans to make an investment contribution of 6% of his salary in the beginning of the year. After some research on historical stock market performance, Tom decided that a 10% annual portfolio growth rate within the current year is reasonable. Therefore, Tom's portfolio growth earnings in a year will be based on his current portfolio plus the additional salary contribution that he is making. Tom thinks that he can develop a spreadsheet that employees can use to calculate their end of year balances based on these assumptions. This is a single-period problem and we are not considering portfolio growth over multiple years. When Tom pitched this idea to his boss, Kate Krystkowiak, she voiced a criticism around the assumption of a fixed annual portfolio growth rate. She pointed out that the constant and known annual portfolio growth rate was unrealistic and that the actual growth rate is very hard to predict. She further suggested that a simulation model for the portfolio projection might allow Tom to account for the random variability in the portfolio growth rate. Four Corners' accountants suggested that the annual portfolio growth rate could be approximated by a normal probability distribution with a mean of 10% and a standard deviation of 5%. With this information, Tom set off to design his spreadsheet so that it could be used by the company's employees for financial planning. Case Problem: Four Corners What will your investment portfolio be worth in a year? The Human Resources Department at Four Corners Corporation was asked to develop a financial planning model that would help employees address this question. Tom Gifford was asked to lead this effort and decided to begin by developing a financial plan for himself. Tom has a degree in business and, at the age of 40, is making $85,000 per year. Through contributions to his company's retirement program and the receipt of a small inheritance, Tom has accumulated a portfolio valued at $50,000. Tom plans to make an investment contribution of 6% of his salary in the beginning of the year. After some research on historical stock market performance, Tom decided that a 10% annual portfolio growth rate within the current year is reasonable. Therefore, Tom's portfolio growth earnings in a year will be based on his current portfolio plus the additional salary contribution that he is making. Tom thinks that he can develop a spreadsheet that employees can use to calculate their end of year balances based on these assumptions. This is a single-period problem and we are not considering portfolio growth over multiple years. When Tom pitched this idea to his boss, Kate Krystkowiak, she voiced a criticism around the assumption of a fixed annual portfolio growth rate. She pointed out that the constant and known annual portfolio growth rate was unrealistic and that the actual growth rate is very hard to predict. She further suggested that a simulation model for the portfolio projection might allow Tom to account for the random variability in the portfolio growth rate. Four Corners' accountants suggested that the annual portfolio growth rate could be approximated by a normal probability distribution with a mean of 10% and a standard deviation of 5%. With this information, Tom set off to design his spreadsheet so that it could be used by the company's employees for financial planningStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started