PLEASE SOLVE USING EXCEL. PLEASE ONLY USE EQUATIONS IN EXCEL FOR EACH CELL.

SHOW EQUATION IN EACH CELL DOWN BELOW PLEASE AND THANK YOU!

The following table contains information about four different companies, including the estimated next years EPS, payout ratio, shareholders required rate of return, and return on equity (ROE):

	FIRM A	FIRM B	FIRM C	FIRM D
EPS	$0.80	$1.15	$1.47	$1.85
PAYOUT RATIO	30%	50%	60%	40%
Required Rate of Return	12%	11%	12%	14%
ROE	16.00%	15.00%	14.00%	16.00%

Calculate each companys future earnings growth rate.

Using the earnings model, what is the value of the stock? Calculate the value without growth and PVGO separately.

\begin{tabular}{l} \hline EPS \\ \hline Payout Ratio \\ \hline Required rate of Return \\ \hline ROE \end{tabular} \begin{tabular}{r|rrr} \multicolumn{1}{|c}{ Firm A } & \multicolumn{1}{c}{ Firm B } & \multicolumn{1}{c}{ Firm C } & \multicolumn{1}{c}{ Firm D } \\ \hline$0.80 & $1.15 & $1.47 & $1.85 \\ \hline 30% & 50% & 60% & 40% \\ \hline 12% & 11% & 12% & 14% \\ \hline 16.00% & 15.00% & 14.00% & 16.00% \\ \hline \end{tabular} Growth Rate Value without Growth Value of Growth Opportunities Value of Stock \begin{tabular}{l} \hline EPS \\ \hline Payout Ratio \\ \hline Required rate of Return \\ \hline ROE \end{tabular} \begin{tabular}{r|rrr} \multicolumn{1}{|c}{ Firm A } & \multicolumn{1}{c}{ Firm B } & \multicolumn{1}{c}{ Firm C } & \multicolumn{1}{c}{ Firm D } \\ \hline$0.80 & $1.15 & $1.47 & $1.85 \\ \hline 30% & 50% & 60% & 40% \\ \hline 12% & 11% & 12% & 14% \\ \hline 16.00% & 15.00% & 14.00% & 16.00% \\ \hline \end{tabular} Growth Rate Value without Growth Value of Growth Opportunities Value of Stock