How do you calculate this? And can you calculate it on excel?

- o x Sam Youngblood 8 Case Studies (2) - Saved File Home Insert Draw Formulas Data Review View B I U D A A 3 3 akp q 12: Et l 47 Sensitivity a J K L M N O AB Chapter 9 Stock Valuation at Ragan Engines Input area: Shares owned by each sibling Ragan EPS Dividend to each sibling Ragan ROE Ragan required return 150,000 5.35 320,000 21% 18% EPS 1.19 $ DPS 0.19 0.55 0.57 $ Blue Ribband Motors Corp. Bon Voyage Marine, Inc. Nautilus Marine Engines Industry average Stock price 16.32 13.94 23.97 ROE 10.00% 12.00% N/A 1.26 12.00% 17.00% 16.00% (0.27) Nautilus EPS w/o write-off 2.07 Output area: Case Studies Chapter 8 + Chapter 9 Chapter 10 Chapter 11 Chapter 13 0 9 :23 - o x Sam Youngblood 8 Case Studies (2) - Saved File Home Insert Draw Formulas Data Review View B I U CA A A = 3 gbe sq 23 E 21 AL 28 B Sensiti A B D E F G H I J K L M N O 1) Total earnings Payout ratio Retention ratio Growth rate Total dividends next year Total equity value Value per share Screenshot now Screenshot taken Show in folder 2) Industry EPS Industry payout ratio Industry retention ratio Industry growth rate Year Total dividends Stock value in Year 5 COPY TO CLIPBOARD Case Studies Chapter 8 Chapter 9 Chapter 10 Chapter 11 Chapter 13 0 9 :23 - o x Sam Youngblood 8 Case Studies (2) - Saved File Home Insert Draw Formulas Data Review View B I U CA A A 3 be sq 23 E 21 AL 49 B Sensitiv AB TUISI SLOCK value wuay D E F G H I J K L M N O Value per share 3) Industry PE Ragan PE (original assumption) Ragan PE (revised assumption) Stock price implied by industry PE 4) Total earnings Cash cow value Percentage not attributable to growth opportunities Percentage attributable to growth opportunities C 5) ROE Case Studies Chapter 8 Chapter 9 Chapter 10 Chapter 11 Chapter 13 0 9 :23