> O N Tesla Project part 3 Project... docs.google.com Y E E NT G + 88 Ni Share File Edit View Insert Format Data Tools Extensions Help 5 100% % .0 St 123 Arial - 10 + BIA { 144 A C- Y C8 80 fx A B C D E F G H | J K L M N 1 2 ENTER DATA IN YELLOW-CODED CELLS 3 4 FORD (F) 5 NAME OF COMPANY BE VERY CAREFUL WITH THE DECIMAL POINTS LAST YEAR OUTPUT BELOW & IN SUBSEQUENT WORKSHEETS 10 6 2023 Year 7 NUMBER OF SHARES OUTSTAN / Millions of Shares WATCH DECIMAL POINT 8 MARKET PRICE OF STOCK / SH I Dollars Per Share 9 NET INCOME AFTER TAXES Millions of Dollars 10 TOTAL SALES I Millions of Dollars F2 11 BOOK VALUE OF EQUITY Millions of Dollars 12 RISK FREE RATE (10 Yr. T- Bond] 13 345 DIVIDENDS PAID (TOTAL) Annual Percentage Rate Millions of Dollars 14 STOCK REPURCHASES (TOTAL) Millions of Dollars (Find on Statement of Cash Flows) (Find on Statement of Cash Flows) 15 REQUIRED INFORMATION FOR CALCULATION OF REQUIRED R 16 BETA OF STOCK 17 MARKET RISK PREMIUM Expected Market Risk Premium % 18 REQUIRED INFORMATION FOR VARIABLE GRO 19 2020 2021 20 EXPECTED GROWTH RATE (DIV Terminal 2022 2023 1 Terminal 21 22 22 OUTPUTS AND VALUATIONS COMPUTER-GENERATED CALCULATIONS 23 24 EARNINGS PER SHARE 25 LAST CASH DIVIDEND & STOCK I 26 27 28 EXPECTED ANNUAL GROWTH F REQUIRED RATE OF RETURN (k REQUIRED RATE OF RETURN (k Geometric Mean of Annual Rates CAPM Dividend Discount Model + data input stock return stock price constant growth Stock price variable growth Should be Between 5% & 6% The Terminal Growth Rate Should Not Exceed 2% to 3% > O N Tesla Project part 3 File Edit View Insert Format 5 100% C8 80 fx A B 10 10 TOTAL SALES BOOK VALUE OF EQUITY Data Tools Extensions Help % .0 St 123 Arial - 10 + Project... docs.google.com BIA Y E E 144 NT G C- Y @ Share + 88 Ni C D E F G H | J K L M N I Millions of Dollars 11 12 RISK FREE RATE (10 Yr. T- Bond 13 DIVIDENDS PAID (TOTAL) Millions of Dollars Annual Percentage Rate Millions of Dollars 14 STOCK REPURCHASES (TOTAL) Millions of Dollars 15 16 REQUIRED INFORMATION FOR CALCULATION OF REQUIRED R BETA OF STOCK 17 MARKET RISK PREMIUM Expected Market Risk Premium % 18 REQUIRED INFORMATION FOR VARIABLE GRO 19 2020 2021 2022 20 EXPECTED GROWTH RATE (DIV 21 22 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 (Find on Statement of Cash Flows) (Find on Statement of Cash Flows) Terminal 2023 Terminal OUTPUTS AND VALUATIONS COMPUTER-GENERATED CALCULATIONS EARNINGS PER SHARE LAST CASH DIVIDEND & STOCK EXPECTED ANNUAL GROWTH F REQUIRED RATE OF RETURN (k REQUIRED RATE OF RETURN (k REQUIRED RATE OF RETURN (k SUSTAINABLE RATE OF GROWT Geometric Mean of Annual Rates CAPM Dividend Discount Model Average of CAPM and Dividend Discount Model (ROE * (1-Payout Ratio)) COMPUTER-GENERATED CALCULATIONS STOCK VALUE - CONSTANT GR STOCK VALUE - VARIABLE GRO Should be Between 5% & 6% The Terminal Growth Rate Should Not Exceed 2% to 3% + data input stock return stock price constant growth Stock price variable growth > O N Tesla Project part 3 File Edit View Insert Format Data Tools Extensions Help 5 2 100% G1 A fx Company:FORD (F) % .0 St 123 Times... - 10 B C D E F 1 2 3 4 LC 5 a EXPECTED RETURN COMMON STOCK INPUTS 7 LAST DIVIDEND & STOCK REPURCHASE PER SHARE 8 PRICE OF STOCK 9 EXPECTED GROWTH RATE (DIVIDENDS & STOCK REPURCHASES) 10 11 12 13 14 31 15 ESTIMATED REQUIRED RATE OF RETURN (k) 16 17 18 19 20 21 23 24 27 56822222222222 29 OUTPUT + Project... docs.google.com BIA G Company:FORD (F) Y E { E NT 144 G A C- Y Share + 88 Ni H | J K L M N FORMULA k = (D1/P0)+g + data input stock return stock price constant growth Stock price variable growth > O N Tesla Project part 3 File Edit View Insert Format Data Tools Extensions Help 5 2 100% % .0 St 123 Times... - 10 J1 A fx Company:FORD (F) B C D E 1 PRICE OF COMMON STOCK - CONSTANT GROWTH MODEL 2 3 4 LC 5 a INPUTS 7 LAST CASH/STOCK REPURCHASE PAYMENT PER SHARE REQUIRED RATE OF RETURN (ROR) 8 9 EXPECTED ANNUAL GROWTH RATE 10 (Dividends + Stock Repurchases) 11 14 12 13 31 567 15 PRICE OF STOCK 16 17 18 19 20 21 22 23 24 25 26 27 28 29 OUTPUT + Project... docs.google.com Y BIA F G H k must g E { E NT FORMULA 144 J G A C- Y Company:FORD (F) PO=D1/(k-g) Share K L M N + data input stock return stock price - constant growth Stock price variable growth < + 88 Ni > O N Tesla Project part 3 U U Project... docs.google.com Y E E N G Share File Edit View Insert Format Data Tools Extensions Help 5 < 8 9 100% % .0 123 Times... - 10 + BIA { 144 A C- E Y 13 H3 fx A B C D E F G H | K L M N 1 2 PRICE OF STOCK - VARIABLE GROWTH MODEL Company:FORD (F) 3 4 5 6 7 LAST YEAR (e.g. 2018 2019 8 LAST CASH & ST 9 EXPECTED ANN - 10 REQUIRED RATE 11 (ROR or k) 12 231 13 14 15 16 17 18 19 20 21 22 DIVIDENDS 23 FUTURE PRICE 24 25 NON-DISCOUNTED CASH FLOWS 26 27 PRESENT VALUI 28 29 + OUTPUT INPUTS 2020 2021 2022 2023 FORMULA |P0 = Present Value of D Plus Present Value of Future Stock Price 2019 2020 2021 2022 2023 + 88 Ni data input stock return stock price - constant growth Stock price variable growth