Need Help with part 2.

Managerial Report

Play the role of Tom Gifford, and develop a simulation model for financial planning. Write a report for Toms boss that includes each of the following:

1. Without considering the random variability, extend the current worksheet to 20 years. Confirm that by using the constant annual salary growth rate and the constant annual portfolio growth rate, Tom can expect to have a 20-year portfolio of $772,722. What would Toms annual investment rate have to increase to in order for his portfolio to reach a 20-year, $1,000,000 goal?

2. Redesign the spreadsheet model to incorporate the random variability of the annual salary growth rate and the annual portfolio growth rate into a simulation model. (Note: new values of the salary and portfolio growth rates should be generated for each year, not just for each simulation trial.) Assume that Tom is willing to use the annual investment rate that predicted a 20-year, $1,000,000 portfolio in part 1. Simulate Toms 20-year financial plan and use the results to comment on the uncertainty associated with the future value of Toms portfolio. In particular:

a. Use a histogram to describe the probability distribution for the ending balance; report and interpret both the expected value and standard deviation.

b. What is the probability that the value of Toms portfolio will exceed $1,000,000 after 20 years?

3. 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 companys employees.

1 Four Corners 2 3 Age 4 Current Salary 5 Current Portfolio 6 Annual Investment Rate 7 Salary Growth Rate 8 Portfolio Growth Rate 40 $85,000 $50,000 91 3% 596 10% 10 Year Beginning Balance Salary New Investment Earnings Ending Balance Age S63,14441 S78,010 42 S94,790 43 S113,697 44 S134,96645 $158,85746 S185,65747 S215,682 48 S249,28349 S286,846 50 S328,79751 S375,606 52 S427,792 53 S485,929 54 S550,647 55 S622,642 56 S702,685 57 S791,62058 $890,382 59 S19,600 S90,018S1,000,00060 S50,000S85.000 S63,144 S89,250 $78,010 S93,713 S94,790 S98,398 S113,697 S103,318 S134,966 S108,484 S158,857 S113,908 S185,657 $119,604 S215,682 S125,584 S249,283 S131,863 S286,846 S138,456 S328,797 S145,379 5375,606 S152,648 S427,792 S160,280 S485,929 S168,294 S550,647 S176,709 S622,642 S185,544 S702,685 S194,822 S791,620 S204,56:3 S890,382 S214,791 S7,756 S5,388 S8,144 S6,722 S8,551 S8,229 S8,979 S9,928 S9,428 S11,841 S9,899 $13,992 S10,394 S16,405 S10,914 S19,111 S11,460 $22,141 12,033 $25,530 S12,634 $29,316 S13,266 S33,543 13,929 S38,257 S14,626 S43,511 S15,357 $49,361 S16,125 S55,871 S16,931 S63,111 S17,778 S71,157 S18,667 S80,095 12 13 14 15 16 17 18 19 20 21 4 12 13 23 24 25 26 27 28 29 30 31 32 15 17 34