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 company$$s 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 it is likely that the growth will transition from $9\%$ down to $4\%$ gradually, rather than instantaneously. If you believe that the transition will take five years, what is the value of the stock? Use the three$-$stage and H$-$Model valuation methods.

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.

PLEASE SOLVE BY EXCEL AND UPLOAD WITH EXCEL FORMULA. THANK YOU