Problem $5(20$ Points$)$

How much will an employee's portfolio be worth after working for the company $30$ years more? The Human Resource department at EcoCarnifex Corporation was asked to develop a financial planning model that would help employees address this question. Frank Joseph was asked to lead this effort, and he decided to begin developing a financial plan for himself first. Frank has a degree in business and at the age of $30$ in $2017.$ At the beginning of $2017,$ he is making an annual salary $$35,000$ and has accumulated a portfolio valued at $$16\mathrm{.}000.$

Frank made the following assumptions:

$3.5\%$ annual salary growth st the end of each year is reasonable.

b$.$ He plans to contribute $5\%$ of his monthly salary throughout each year.

c$.10\%$ annual portfolio growth seems reasonable.

$(1)$ Develop an Excel worksheet that calculates and shows the value at the end of each year of Frank's portfolio after he will work for the company $35$ years more $($i$.$e$.,$ at the end of $2051).(5$ Points$)$

$(2)$ If Frank plans to work for the company $30$ years more instead and hopes to accumulate a portfolio valued at $$1\mathrm{.}000\mathrm{.}000$ for his retirement by the end of $2046.$ Can he do it$?$ Why or why not. Please explain in detail. If not. what he should do$?(3$ Points$)$

Frank has presented his findings based upon the above assumptions to his boss, but his boss does not agree with it$.$ Instead, his boss made the following assumptions.

$2.$ The annual salary growth should not be constant. It should vary from $0$ to $9\%$ following a uniform probability distribution.

b$)$ The annual portfolio growth rate should be approximated by a normal probability distribution with a mean of $7\%$ and a standard deviation of $5\%.$

$(3)$ Develop an Excel worksheet with this new information and then use BRisk to perform this simulation $($using $800$ iterations$)$ that calculates and shows the value at the end of each year of Frank's portfolio after he will work for the company $35$ years more $($i$.$e$.,$ at the end of $2051).(5$ Points$)$

$(4)$ Based on $(3),$ can Frank accumulate a portiolio valued at $$1,100,000$ for his retirement by the end of $2046?$ Why or why not? Please explain in detail. $(2$ Points$)$

$(5)$ Attach the simulation graph result at the end of $35$ years more as an output. $(2$ Points$)$

$(6)$ Based upon the graph result, what is the probability that Frank will have at least $$900\mathrm{.}000$ of his portfolio value for his retirement at the end of $2051?(3$ Points$)$