$2.($explain for excel file$)$ As a broker at Churnem & Burnem Securities, you recommend stocks to your clients. After gathering data on Furniture Factory, you have found that its dividend has been growing at a rate of $4\%$ per year to the current $($D$0)$ $$1\mathrm{.}35$ per share. The stock is now selling for $$24\mathrm{.}50$ per share, and you believe that an appropriate rate of return for this stock is $11\%$ per year.

a$.$ If you expect that the dividend will continue to grow at a $4\%$ rate into the foreseeable future, what is the highest price at which you would recommend purchasing this stock to your clients?

b$.$ Suppose now that you believe that the companys new product line will cause much higher growth in the near future. Your new estimate is for a three$-$year period of $9\%$ annual growth to be followed by a return to the historical $4\%$ growth rate. Under these new assumptions, what is the value using the two$-$stage dividend growth model?

c$.$ You now realize that, after a three$-$year period of $9\%$ annual growth, it is likely to have another three$-$year period of $6\%$ annual growth before a return to the historical $4\%$ growth rate. Then what is the value of the stock?

d$.$ For each of the answers from above, create an IF statement that shows whether the stock is undervalued, overvalued, or fairly valued.

e$.$ Create a data table showing the stock value using the above models for long$-$run growth rates between $0\%$ and $15\%$ in $1\%$ increments. Use an XY Scatter chart to visualize the results.