please solve using the excel sheet
2. 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 (D0)$1.35 per share. The stock is now selling for $24.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 twostage 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 longrun growth rates between 0% and 15% in 1% increments. Use an XY Scatter chart to visualize the results. \begin{tabular}{|c|c|c|c|c|c|c|} \hline \multicolumn{7}{|c|}{ Single-Stage Growth Data } \\ \hline Stock Price & 24.50 & & & & z2 & \\ \hline De & 1.35 & 4 & & & & \\ \hline Growth Rate & 4% & & & & & \\ \hline Required Return & 11% & = & & & & \\ \hline \multicolumn{7}{|c|}{ Two-Stage Growth Data } \\ \hline \multicolumn{7}{|c|}{ Stock Price } \\ \hline \multicolumn{7}{|l|}{D6} \\ \hline \multicolumn{7}{|l|}{ Growth Rate 1} \\ \hline Growth Rate 2 & & & & & & \\ \hline \multicolumn{7}{|l|}{ Rapid Growth Time } \\ \hline \multicolumn{7}{|l|}{ Required Growth Time } \\ \hline \multicolumn{7}{|l|}{ Required Return } \\ \hline & & & & & & \\ \hline \multicolumn{7}{|c|}{ Three-Stage Growth Data } \\ \hline \multicolumn{7}{|l|}{ Stock Price } \\ \hline \multicolumn{7}{|l|}{De} \\ \hline 19 Growth Rate I & & & & & & \\ \hline 20 Growth Rate 2 & & Long-Run Growth Rate & 1-Stage & 2-Stage & 3. Stage & H.Model. \\ \hline 21 Rapid Growth Time & 5 & & & & & \\ \hline 22 Transition Period & & & & & & \\ \hline 23 Required Return & & & & & & \\ \hline \end{tabular} Three-Stage Growth Data Stock Price D0 Growth Rate 1 Growth Rate 2 Rapid Growth Time Long-Run Growth Rate 1-Stage Transition Period Required Return Valuation Single-Stage Two-Stage Three-Space H-Model
