Hello, I would like to know how to create this model in excel. Please explain formulas.

Homework assignment Build the following three VBA functions DiV1 1. GGM (Gordon Growth Model, Inputs: Divi, R, 9) Stock Value, Po = = R - g 2. CATCHUP (Inputs: p1, p2, r1, r2) Years to catchup = (1+r1 3. PStdev (Portfolio Standard Deviation, Expandable) (Pieri, N as inputs) Economy Probability High Tech (i) (P) Return (R) HT= .1x-27+2x-.07+.4x.15+2x.30+. 17.45 = 12.4% PStdevo) = Recession 10% -27% For=0.124 12=(r; f)2 P Below 20% -7% r nr)?xP Average Average 40% 15% [(-0.27 - 0.124)*(0,1)+(-0.07 0.124)(0.2))? where Above 20% HighTech = +(0.15-0.124) (0.4) + (0.30-0.124) (0.2) = !-1 Piri Average +(0.45-0.124) (0.1) Boom 10% OH = 0.040144 = 20.04% 30% Homework assignment This is how your completed model should look like 2 GGM 3 $ Dividend one year from now (Div1) R (cost of equity) g (growth rate of dividends) 2.00 10% 4% Stock Value Today (PO) Stock Value Today (PO) (Your Function) $ $ 33.33 =C2/(C3-C4) 33.33 EGGM(C2,C3,C4) 7 9 CATCHUP 10 $ $ 11 pl (sales of WMT) p2 (sales of AMZN) r1 (growth rate of WMT sales) 12 (growth rate of AMZN sales) 514,405 280,522 2.90% 20.45% Years to Catchup Years to catchup 3.85 = N(C10/09)/LN((1+C11)/(1+C12)) 3.85 =CATCHUP(09,C10,011,C12) $ 16 17 Portfolio STDEV Probability 10% 20% 40% 20% 10% Value -27.00% -7.00% 15.00% 30.00% 45.00% Expected Value (r hat) Portfolio Standard Deviation (Calculation) Portfolio Standard Deviation (Your Function) 12.40% =B18*C18+B19*C19+B20*20+B21*C21+B22*C22 20.04% =SQRT (B18*($C$24-C18)^2)+(B19*($C$24-C19)^2)+(B20*($C$24-C20)^2)+(B21*($C$24-C21)^2)+(B22*($C$24-C22)^2)) 20.04% =PStdev(B17:B21,C17:021) Homework assignment Build the following three VBA functions DiV1 1. GGM (Gordon Growth Model, Inputs: Divi, R, 9) Stock Value, Po = = R - g 2. CATCHUP (Inputs: p1, p2, r1, r2) Years to catchup = (1+r1 3. PStdev (Portfolio Standard Deviation, Expandable) (Pieri, N as inputs) Economy Probability High Tech (i) (P) Return (R) HT= .1x-27+2x-.07+.4x.15+2x.30+. 17.45 = 12.4% PStdevo) = Recession 10% -27% For=0.124 12=(r; f)2 P Below 20% -7% r nr)?xP Average Average 40% 15% [(-0.27 - 0.124)*(0,1)+(-0.07 0.124)(0.2))? where Above 20% HighTech = +(0.15-0.124) (0.4) + (0.30-0.124) (0.2) = !-1 Piri Average +(0.45-0.124) (0.1) Boom 10% OH = 0.040144 = 20.04% 30% Homework assignment This is how your completed model should look like 2 GGM 3 $ Dividend one year from now (Div1) R (cost of equity) g (growth rate of dividends) 2.00 10% 4% Stock Value Today (PO) Stock Value Today (PO) (Your Function) $ $ 33.33 =C2/(C3-C4) 33.33 EGGM(C2,C3,C4) 7 9 CATCHUP 10 $ $ 11 pl (sales of WMT) p2 (sales of AMZN) r1 (growth rate of WMT sales) 12 (growth rate of AMZN sales) 514,405 280,522 2.90% 20.45% Years to Catchup Years to catchup 3.85 = N(C10/09)/LN((1+C11)/(1+C12)) 3.85 =CATCHUP(09,C10,011,C12) $ 16 17 Portfolio STDEV Probability 10% 20% 40% 20% 10% Value -27.00% -7.00% 15.00% 30.00% 45.00% Expected Value (r hat) Portfolio Standard Deviation (Calculation) Portfolio Standard Deviation (Your Function) 12.40% =B18*C18+B19*C19+B20*20+B21*C21+B22*C22 20.04% =SQRT (B18*($C$24-C18)^2)+(B19*($C$24-C19)^2)+(B20*($C$24-C20)^2)+(B21*($C$24-C21)^2)+(B22*($C$24-C22)^2)) 20.04% =PStdev(B17:B21,C17:021)