Estimate Walmart's common stock price, using the Gordon Constant Growth Model in excel to determine whether Walmart appears to be correctly valued, overvalued, or undervalued based on Walmart's current stock price and model result.

Attached is a document of the information I think is needed for the excel spread sheet of the Gordon Constant Growth Model. Please help me putting it in excel with the correct formulas and calculations

Dividend Discount Model (DDM) Difficulty: Intermediate In discounted cash flow (DCF) valuation techniques the value of the stock is estimated based upon present value of some measure of cash flow. Dividends are the cleanest and most straightforward measure of cash flow because these are clearly cash flows that go directly to the investor. Intrinsic Stock Value (Valuation Summary) Required Rate of Return ( r ) Dividend Growth Rate ( g ) Intrinsic Stock Value (Valuation Summary) WalMart Stores Inc., dividends per share (DPS) forecast USD $ Year Value DPS or Terminal value (TV ) t t Calculation Present value at 5.00% 0 DPS 0 2.00 1 DPS 1 2.25 = 2.00 (1 + 12.52%) 2.14 2 DPS 2 2.48 = 2.25 (1 + 10.00%) 2.25 3 DPS 3 2.66 = 2.48 (1 + 7.48%) 2.30 4 DPS 4 2.79 = 2.66 (1 + 4.96%) 2.30 1 5 DPS 2.86 = 2.79 (1 + 2.44%) 5 Terminal value (TV ) 5 114.51 5 = 2.86 (1 + 2.44%) (5.00% - 2.44%) Intrinsic value of Walmart's common stock (per share) $80.05 Required Rate of Return (r) Assumptions R 1 Expected rate of return on market portfolio Required rate of return on Walmart's common stock 2.66% F E(R ) 2 M Systematic risk () of Walmart's common stock 3 r 13.05% WMT 0.23 WMT 5.00% Unweighted average of bid yields on all outstanding fixedcoupon U.S. Treasury bonds neither due or callable in less than 10 years (risk free rate of return proxy). 1 Calculations 2 3 See Details rWMT = RF + WMT [E(RM) - RF] = 2.66% + 0.23 [13.05% - 2.66%] = 5.00% 89.71 $100.93 Current share price Rate of return on LT Treasury Composite 2.24 Dividend Growth Rate (g) Dividend growth rate (g) implied by PRAT model Average Jan 31, 2017 Jan 31, 2016 Jan 31, 2015 Jan 31, 2014 Jan 31, 2013 Jan 31, 2012 Selected Financial Data (USD $ in millions) Cash dividends declared 6,216 6,294 6,185 6,139 5,361 5,048 13,643 14,694 16,363 16,022 16,999 15,699 Net sales 481,317 478,614 482,229 473,076 466,114 443,854 Total assets 198,825 199,581 203,706 204,751 203,105 193,406 77,798 80,546 81,394 76,255 76,343 71,315 0.54 0.57 0.62 0.62 0.68 0.68 2.83% 3.07% 3.39% 3.39% 3.65% 3.54% 2.42 2.40 2.37 2.31 2.29 2.29 2.56 2.48 2.50 2.69 2.66 2.71 Consolidated net income attributable to Walmart Total Walmart shareholders' equity Ratios Retention rate Profit margin 1 2 Asset turnover 3 Financial leverage 4 Averages Retention rate Profit margin Asset turnover 0.62 3.31% 2.35 Financial leverage Dividend growth rate (g) 2.60 5 12.52% WalMart Stores Inc., PRAT model 2017 Calculations 1 2 3 4 5 Retention rate = (Consolidated net income attributable to Walmart - Cash dividends declared) Consolidated net income attributable to Walmart = (13,643 - 6,216) 13,643 = 0.54 Profit margin = 100 Consolidated net income attributable to Walmart Net sales = 100 13,643 481,317 = 2.83% Asset turnover = Net sales Total assets = 481,317 198,825 = 2.42 Financial leverage = Total assets Total Walmart shareholders' equity = 198,825 77,798 = 2.56 g = Retention rate Profit margin Asset turnover Financial leverage = 0.62 3.31% 2.35 2.60 = 12.52% Top Dividend growth rate (g) implied by Gordon growth model g = 100 (P0 r - D0) (P0 + D0) = 100 ($80.05 5.00% - $2.00) ($80.05 + $2.00) = 2.44% where: P0 = current price of share of Walmart's common stock D0 = last year dividends per share of Walmart's common stock r = required rate of return on Walmart's common stock Top Dividend growth rate (g) forecast WalMart Stores Inc., Hmodel Year Value g t 1 g 12.52% 2 g 10.00% 3 g 7.48% 4 g 4.96% 5 and thereafter g 2.44% 1 2 3 4 5 where: g1 is implied by PRAT model g5 is implied by Gordon growth model g2, g3 and g4 are calculated using linear interpoltion between g1 and g5 Calculations g2 = g1 + (g5 - g1) (2 - 1) (5 - 1) = 12.52% + (2.44% - 12.52%) (2 - 1) (5 - 1) = 10.00% g3 = g1 + (g5 - g1) (3 - 1) (5 - 1) = 12.52% + (2.44% - 12.52%) (3 - 1) (5 - 1) = 7.48% g4 = g1 + (g5 - g1) (4 - 1) (5 - 1) = 12.52% + (2.44% - 12.52%) (4 - 1) (5 - 1) = 4.96%