E33 1 2 3 Excel Case #2 - XYZ Company Answer Schedule 4 5 Market value of equity (market cap) 6 Growth rate for equity 7 8 Annual dividend Market price of stock (per share) 9 Beta of stock 10 Risk free rate 11 Market rate of return 12 DGM cost of equity (stock) 13 CAPM cost of equity (stock) 14 Average cost of equity 15 Market value of debt 16 Average tax rate 17 Pre tax cost of debt (bonds) After tax cost of debt (bonds) Total market value of equity and debt 18 19 20 Weight for equity 21 Weight for debt 22 Weighted average cost of capital 23 24 Total points 25 3.60% 10.25% 35 36 0 Format billion currency, 2 decimal places percentage, 2 decimal places currency, 2 decimal places currency, 2 decimal places. number, 2 decimal places percentage, 2 decimal places percentage, 2 decimal places percentage, 2 decimal places percentage, 2 decimal places percentage, 2 decimal places billion currency, 2 decimal places percentage, 2 decimal places percentage, 2 decimal places percentage, 2 decimal places billion currency, 2 decimal places number, 4 decimal places number, 4 decimal places percentage, 2 decimal places 26 27 Save your file with this name format Excel Case2 FirstinitialLastname sectionnumber Example: Excel Case 2 JDoe 03W 28 23 30 Submit by posting to Blackboard 31 (due date and time is Tuesday, November 28th, 11:30pm) 32 33 34 G Points available 0.50 175 0.50 0.50 0.50 125 125 1.00 0.50 1.00 50 1.25 0.50 1.25 1.25 3.50 20.00 Remarks H use 12/31/22 data provided on Data tab-use value in BILLIONS use dividend growth for most recent six years as I have segregated on the Data tab use 12/31/22 data provided on Data tab use the most recent year on the Data tab use 12/31/22 data provided on Data tab simple average of two methods use bond schedule for 12/31/22 - use value in BILLIONS use average for most recent six calendar years = there are two acceptable ways to calculate this that result in two slightly different answers use bond schedule for 12/31/22 use bond schedule for 12/31/22 use value in BILLIONS two possible answers use value in BILLIONS use value in BILLIONS 0 Net Points Earned

G1 fx Assets Cerreat avtet: Cish ndeashequvent? Ariminere itvention: Qbipicuterised Tetar curent assets Neb evitest assets Mocutalridomitiation Homet int ang Lle difela Total noeverient assets Total avists \begin{tabular}{|c|c|c|c|c|c|} \hline & 6.563 & 5:287 & +222 & 4,469 & 3490 \\ \hline thes & 1414 & & tsor & 1278 & 1287 \\ \hline 190 & 155 & 206 & 22 & 1967 & \\ \hline 395 & 302 & 89 & 440 & tojo & 90 \\ \hline 10,685 & 10.239 & 8.761 & 0,097 & 8,734 & 7,457 \\ \hline & 5721 & 5.740 & 5.425 & 4.709 & 4325 \\ \hline[2,n] & [2.5ti) & {[2.427]} & (2,24) & (2,125) & (2.411) \\ \hline 4002 & 42 & 4.682 & 1,382 & & 435 \\ \hline 34 & & 606 & 78 & 1058 & 1006 \\ \hline 104 & bet & 396 & 778 & 828 & 734 \\ \hline 10,891 & 9,112 & 3,257 & 9,040 & 8,308 & 8,974 \\ \hline 24,576 & 19.351 & theon & 17,07 & 17.542 & 15,431 \\ \hline \end{tabular} in tinationt ti. Gout semind 1. Toral surrent liabaises Noin cariemt linabilities: Loghtrem stel Total hatalies \begin{tabular}{|c|c|c|c|c|c|} \hline 500 & 60 & 300 & 743 & 500 & 531 \\ \hline 1428 & 1,52 & 102 & & 106 & 920. \\ \hline 641 & 6se. & 521 & 724 & 222 & 70 \\ \hline 2869 & 2,390 & 2,123 & 2.474 & 2,258 & 2,264 \\ \hline 724 & 6.241 & 53% & (3i) & 2,577 & 2970 \\ \hline at & 98 & 20 & 42 & 7 & i) \\ \hline (44:) & tac & teor & 1500 & 13x & 6 \\ \hline 8.774 & 7,724 & c.9an & 5,563 & 5,047 & 3,694 \\ \hline 11.343 & 10,04 & 3.111 & 8.143 & 7,205 & 5,954 \\ \hline \end{tabular} Stockholders" rquit, \begin{tabular}{|c|c|c|c|c|c|c|} \hline Stockholders & & 4 & & x2e2 & +8 & \\ \hline Connon ploy, & 10 & 041 & 174 & 574 & 174 & (74) \\ \hline Adsonal Painc & 2691 & & 210 & 1950 & & 1674 \\ \hline & 45,11 & 4205 & 29.50 & 37906 & & \\ \hline Firang pod & & & & 22009 & 205 & 55213 \\ \hline & [157] & De0 & {[24]} & (197) & (3) & (526) \\ \hline Total sockholders" twai, & 18333 & 9.207 & 0,307 & 0,374 & 10.337 & \\ \hline \end{tabular} Total shockholders" +quis 7) Totul tiablaties and stoctholders' equ. in \begin{tabular}{|c|c|c|c|c|c|c|} \hline & & & & & 14=12 & 201712 \\ \hline & 4,264 & & & & 4.,3e: & 12370 \\ \hline & & 502 & 520 & 5.901 & 5247 & \\ \hline Gioss profit (Giasess margin) & 12,376 & 3x63 & 3,164 & 10.271 & 3,614 & 8.240 \\ \hline & 20 & limx2 & 223 & 20007 & & \\ \hline \begin{tabular}{l} EBron \\ Depro unt rimint \end{tabular} & 10,057 & 7,139 & 6,341 & 7,401 & 6.397 & 5.604 \\ \hline & 347 & 352? & cos07 & 1y & ing & 405 \\ \hline Operatang incrame (EBII) & 2,103 & 6.207 & 5,893 & 6,611 & 5,450 & 4,993 \\ \hline & & 150 & 100 & 15 & N & 6 \\ \hline & 10,313 & 6,017 & 5,124 & & 5,313 & 4.33e \\ \hline & & 422 & & 250 & (1594) & \\ \hline Nitt incont & 7,163 & 5.5ns & 3.0H & 5.500 & 3,648 & 2,035 \\ \hline \end{tabular} Net incons. Instructions Answer Schedule Financials Data Bond Sched Ready \begin{tabular}{|c|c|c|c|} \hline \begin{tabular}{l} xrz compary \\ rahoo finance data \\ 12/31/22 (rear end) \end{tabular} & & & \\ \hline & 4 & & \\ \hline Current stock price & & & \\ \hline Marbit Cap Nalue) & & 5.176 .41 & billion. \\ \hline Beta & & 135 & \\ \hline Pe ancio. & & 22.82 & \\ \hline ES. & & 826 & \\ \hline Annus dividend & & 421 & \\ \hline Iof shars outstanding & & 936.000 & miltion \\ \hline cument price. & is 18447 & & \\ \hline outstanding Sharns & 236,000,000 & & \\ \hline marivet value: & $176,407,920,000 & & \\ \hline & or & & \\ \hline & 17641 & fillion & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|} \hline Date & \begin{tabular}{l} Quartenly \\ Oividends \end{tabular} & \begin{tabular}{r} Annual \\ Dividends \end{tabular} & \begin{tabular}{r} Anmual \\ Growth hlates \end{tabular} & \begin{tabular}{l} Averase \\ Growth mate \end{tabular} \\ \hline 10/28/22 & 115 & & & \\ \hline 07/22/22 & 102 & & & \\ \hline 04/29/22 & 102 & E & & \\ \hline 01/28/22 & 1.02 & & & \\ \hline 10/29/21 & 102 & +2 & & \\ \hline 07/23/21 & 0.90 & =8 & & \\ \hline 04/30/21 & 0.90 & 8x2 & & \\ \hline 01/29/21 & 090 & + & & \\ \hline 10/29/20 & 0.90 & & = & \\ \hline 07/30/20 & 0n & 24 & & \\ \hline 05/01/20 & on & & & - \\ \hline 01/30/20 & 077 & =1 & & \\ \hline 10/30/19 & 077 & & & \\ \hline 07/30/19 & 0.62 & = & & \\ \hline 05/01/19 & 062 & & & \\ \hline os/30/9 & 0,02 & =1 & & \\ \hline 10/30/1 & 062 & 5 & & \\ \hline oypo/ta & 050 & & 5 & \\ \hline 05/04/13 & \begin{tabular}{l} 0.50 \\ 050 \end{tabular} & & 3 & \\ \hline10/701/80/13 & \begin{tabular}{l} 0.50 \\ 0.50 \end{tabular} & & & \\ \hline & on & & & \\ \hline 04/27/17 & 0.3s & SE: & & \\ \hline 01/23/17 & 0.35 & & & \\ \hline 11/03/16 & 0.38 & +2 & & \\ \hline \begin{tabular}{l} 07/23/16 \\ 04/23/16 \end{tabular} & \begin{tabular}{l} 0.34 \\ 0.34 \end{tabular} & & & \\ \hline 01/2816 & 0.14 & & & D. \\ \hline \end{tabular}