Question

Case Problem- 1: TRI-STATE CORPORATION

Managerial Report

Play the role of Tom Gifford and develop a simulation model for financial planning. Write a report for Tom’s boss and, at a minimum, include the following:

1. Without considering the random variability in growth rates, extend the worksheet in Figure 12.18 to 30 years. Confirm that by using the constant annual salary growthrate and the constant annual portfolio growth rate, Tom can expect to have a 30-year portfolio of $627,937. What would Tom’s annual investment rate have to increase to in order for his portfolio to reach a 30-year, $1 million goal?

2. Incorporate the random variability of the annual salary growth rate and the annual portfolio growth rate into a simulation model. Assume that Tom is willing to use theannual investment rate that predicted a 30-year, $1 million portfolio in part 1. Show how to simulate Tom’s 30-year financial plan. Use results from the simulation

model to comment on the uncertainty associated with Tom reaching the 30-year, $1 million goal. Discuss the advantages of repeating the simulation numerous times.

3. What recommendations do you have for employees with a current profile similar to Tom’s after seeing the impact of the uncertainty in the annual salary growth rate and the annual portfolio growth rate?

4. Assume that Tom is willing to consider working 35 years instead of 30 years. What is your assessment of this strategy if Tom’s goal is to have a portfolio worth $1 million?

5. Discuss how the financial planning model developed for Tom Gifford can be used as a template to develop a financial plan for any of the company’s employees.

- (Refer to Case problem 1 – TRI-STATE CORPORATION; Page 585; An Introduction to Management Science: Quantitative Approaches to Decision Making, Revised Thirteenth Edition. David R. Anderson, Dennis J. Sweeney,)

Answer 1

Information given about TS Corporation:

• The Human resource department of a TS corporation asked T to develop financial planning model.

• T is an employee in the TS Corporation and plans to earn $1 million at the time of retirement.

• T is currently earning $34,000 as a gross salary.

• T assumes percentage increase in salary is 5% per year.

• T plans to do new investment of 4% of his salary every year.

• T expects to have a portfolio of $627,937 after 30 years.

• T showed his investment analysis to his boss KR.

• KR raised several critics such as why is the percentage increase of salary constant every year.

• KR suggested the annual portfolio of growth rate for a normal probability distribution with a mean of 10% and a standard deviation of 5%.

• KR suggested assuming an annual growth rate of a salary from 0 % to 10%.

1.Calculate the annual investment rate to meet T’s goal:

The company gave a spreadsheet of T’s calculations showing his assumptions of increase in salary.

Now, calculate the annual investment rate to meet T’s portfolio using the spreadsheet:

Using a 4% annual interest rate:

Continued up to 30 years.

Hence, the portfolio earning using 4% annual interest rate is $633,450.12, and it is proved that it does not meet the desired portfolio of $1 million

Using 5% annual interest rate:

Continued up to 30 years.

Hence, the portfolio earning using 5% annual interest rate is $728,558.57 and proved that it does not meet the desired portfolio of $1 million.

Using 6% annual interest rate:

Continued up to 30 years.

Hence, the portfolio earning using 6% annual interest rate is $823,667.01 and proved that it does not meet the desired portfolio of $1 million.

Using 7% annual interest rate:

Continued up to 30 years.

Hence, the portfolio earning using 7% annual interest rate is $918,775.46 and proved that it does not meet the desired portfolio of $1 million.

Using 8% annual interest rate:

Continued up to 30 years.

Hence, the portfolio earning using 8% annual interest rate is $1,013,883.91 and proved that it meets the desired portfolio of $1 million.

2.Calculate the random variability of the annual salary growth rate and annual portfolio rate using simulation model:

Prepare spreadsheet model:

Continued up to 30 years.

Justification:

• The 30 years simulated results will vary significantly.

• The uncertainty is connected with annual salary growth rate and the annual portfolio growth rate will show $1,000,000 even though the investment rate is 8%.

• Repeating simulation process will provide an objective basis for abolishing the probability of reaching $1,000,000.

• The probability of reaching $1,000,000 was estimated to be 0.48 using thousand simulation trails.

3. The recommendation given for employees :

• The simulation model results provide additional strategies that are considered to attain a high probability of reaching $1 million goals.

• Increasing annual investment rate more than 8% is beneficial to reach the desired goal.

4.Assessment for changing 35 years working instead of 30 years:

Using 4% annual interest rate:

Continued up to 30 years.

Justification:

• Changing the working years as 35 in the company is a good idea.

• The results show that within 35 years with 4% annual salary growth rate, the employee can reach the desired goal.

• The results represent the long-term advantages of investing.

5.Financial planning developed by T is used as template:

• The financial planning developed by T is used as template for the employee since it contains all calculations of the results.

• The assumptions in cells D3:D8 will be different for each employee and rows.

• Through varying inputs and projecting the ultimate portfolio value, the staff is able to make investment.

Please please Upvote if you like the answer.