My professor wants an Excel projected EPS using a table. How is this achieved?

However, I was only able to calculate it using a financial calculator. Please use a step-by-step explanation process.

Please explain how to arrive at the EPS and calculate it using both Excel and a financial calculator & math. Also in general how would you best analyze a Value Line Investment Survey? Any tips?

Any help would be much appreciated. Thanks!

Question 10:

What is the Fiscal 2019 EPS (Earnings per share) forecast for this company? (Hint: Use Value Line Investment Survey for Boston Beer Co. dated 04-19-2019. Report as $x.xx per share).

The answer that I arrived at using a financial calculator & math:

Projected 2019 EPS is $8.65

Financial information --

BOSTON BEER CO. NYSE-SAM 373.88 RATIO J Median: 27.0 PERATIO OL G6'L VALUE TIMELINESS 2 Rind 12719 91.2 134.4 2028 196 0 SAFETY 145.3 128.7 160 4 230.9 Target Price Range TECHNICAL 2 Radiated - 850 BETA 75 1101 -MOM 18-Month Target Price Range 2012-24 PROJECTIONS Price Any 1 Total Insinutional Decisions * TOT. RETURN 1214 isaded 541 15.21 200 24.05 28. 10 29.13 34.62 29.85 4417 57.98 155. 65 131 543 74.26 95 59 102 10 121.25 Sales per sh 6. 17 7.55 M.AS "Cash Flow" per sh 1.2T LES 1.45 217 152 518 7 25 9.55 12. 15 Earnings perch ^ MY Divids Deal'd per sh 1.19 God Copy Spending par gh 6.16 9.85 1215 12 18.89 14.16 311 38 44 64.19 76.10 Back Tolue par sh 1419 14.25 13.40 12 67 1290 1200 12.M0 Common Ss Outsig 12.50 21.5 21.1 1.16 1.28 CAPITAL STRUCTURE i of $ 26/19 475.1 17.2% LT Debi None 2042 259 513 Pad Depreciation [mill 87.3 927 150.0 Net Profit [Em!) Leases, Uncapitalized: Annual rentals $20 2 mill 131 Working Copi Only Pld Stock None MY Long Term Debt (4mill] Common Block 12,084,005 shs. |Includes 2 817 983 shares of Class B common 180% 203% 27.5% 243% 213% 218% 21.3% 195% 186% 20.1% 15:5% 16.5% Return on Total Capi stock jas of October 25, 2019. 180% MARKET CAP: $4 5 billion (UM Gas 140% CURRENT POSITION 23.3% 218% 21.3% 19.6% 186% 20.1% 15.5% 16.5% Retained to Comeg 2017 1027 round varieties of craft-brewed boors, hard cider, and solemrs. The barrels of its products a pear. Has 1,435 employees. Off. and dir. 18.3 12.8 company brows its core beers in breweries located in MA, PA, and can 21.4% of common stock (4/19 Proxy). Chairman C. James Accis Payable 71.0 flavored malt beverages under the Thered Tow name, hard cider Design Cantor Place, Buta 8:50, Boston, MA (2110. Telaphone: Cher Current Limb Boston Beer is benefiting from some on a mixed note. We expect continued ANNUAL RATES momentum. The company posted strong momentum at the Truly business. Invest- alchange (er shy 10 22-74 results in the third quarter. Both sales of $378.5 million and earnings of $3.65 a Adams may well help those brands gener- share easily topped our calls as well as ate improvement over the long haul. Hows Cividands 2018's marks. Management attributed the ever, the increased investment behind Book Value performance to strength in the Truly and these brands was probably a drag on prof- Cal- QUARTERLY SALES IS TILA Twisted Tea brands. Elsewhere, a Wall its. Thus, we estimate $2764 million on andar MaxPer Jun.Par Sep. Per Dec.Per Street analyst upgraded his outlook for the top line and $1 52 a share on the bot- 2015 198.3 244.8 213.4 219.4 SAM, which prompted some investor sup- tom line in the December quarter. 2017 151.7 247 9 247.1 206.3 863.0 the port after the rating was made public. We estimate share net of $12.15 for These shares have since pulled back 2020. The first full year with the Dogfish 2013 The company is integrating Dogfish Head business under its umbrella ought to 505 Head into its operations. The marriage be a good one. Indeed, sales will likely be EARNINGS PER SHARE of the two revered beer brands is on track. bolstered by better beer trends and the andar MaxPer Jun.Par Sep.Por Dec.Per Going forward, there are ample cost- strength of Truly Earnings should post a 53 2.05 1 75 saving opportunities by combining the two sizable year over-year advance thanks to operations. The addition of the Dogfish cost saving efforts, synergies, and a more 3.21 1 68 743 lineup of ales and lagers should rejuvenate normalized marketing budget. What's 2019 202 the beer unit that has struggled due to soft more, the long-term picture is bright. This 2021 2 20 3. 10 12. 15 demand for Samuel Adams products. The Truly brand is worth highlight- well-run company will likely continue to QUARTERLY DIVIDENDS PAID find new growth avenues. War.31 Jun 30 Sep 20 Dec.11 ing. The spiked seltzer lineup continues to This stock is timely. Still, this issue of- register breakneck growth. Looking ahead. fors unattractive gains potential over 18- 2017 NO CASH DIVIDENDS it will probably continue to be a growth month and 2022-2024 time frames at the BEING PAID driver, given demand trends for this al- coholic beverage category. recent quotation. Therefore, this equity is best suited for near-term accounts. 2021 The company likely finished the year Richard J. Gallagher January 17, 2020 int Saturday of the calendar your. Excludes Price Growth Persistence Earnings Predictably To subscribe call 1-80D-VALUELINEBOSTON BEER CO PRICE 373.88 RATIO 35.7( Median: 27.0 P/E RATIO 1.95 YLD LINE TIMELINESS 2 Raised 7/26/19 High 54.2 115.5 299.0 325.0 202.8 Target Price Range Low: 17.5 22.7 71.0 195.0 145.3 2022 | 2023 2024 SAFETY Lowered 1/24/14 LEGENDS 640 TECHNICAL 2 Raised 1/17/20 BETA .75 (1.00 = Market) Options: Yes 480 Shaded area Indicates recession 400 18-Month Target Price Range 320 240 Low-High Midpoint (% to Mid) 200 $ 198-$474 $336 (-10%) 160 2022-24 PROJECTIONS 120 Ann'l Total Price Gair 80 High 590 Low (+5%) % TOT. RETURN 12/19 Institutional Decisions 102019 202019 302019 TOCK 56.9 23.6 to Buy 120 122 143 shares 3 yr. 122.5 26.5 to Sell Hid's(000) 8673 8852 8988 traded 5 yr. 30.5 42.2 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 VALUE LINE PUB. LLC 22-24 15.4 15.21 17.07 20.00 24.05 28.10 29.13 34.62 39.85 57.98 69.09 75.25 73.29 74.26 86.59 166.65 1.31 2.38 2.31 3.37 5.04 5.43 6.17 7.55 9.63 14.85 18.65 "Cash Flow" per sh .70 86 1.07 1.27 1.85 1.45 2.17 3.52 3.73 4.39 5.18 6.69 7.25 6.82 6.37 7.83 9.55 12.15 Earnings per sh AB 16.35 Nil Div'ds Decl'd per sh 13 .32 1.00 .63 1.80 4.20 1.19 5.12 7.90 11.61 5.82 4.04 2.84 8.35 5.00 Cap'l Spending per sh 6.65 4.63 5.49 6.16 7.61 9.41 9.88 12.36 23.70 36.11 36.44 76.80 Book Value per sh 13.50 13.96 14.25 12.90 12.00 12.00 Common Shs Outst'g C 21.5 24.3 21.9 23.7 21.1 27.5 14.8 18.3 24.0 24.7 36.7 35.0 34.0 25.2 24.4 32.3 35.7 Avg Ann'l P/E Ratio 30.0 1.23 1.28 1.17 1.28 1.12 1.66 .99 1.16 1.51 2.06 1.84 1.71 1.23 1.74 1.95 Relative P/E Ratio 1.65 Avg Ann'l Div'd Yield Nil CAPITAL STRUCTURE as of 9/28/19 415.1 513.0 580.2 863.0 995.6 1225 1455 Sales ($mill) A 17.2% 21.3% 23.9% 20.0% 19.0% 20.3% 20.8% 19.3% 18.0% 19.5% Operating Margin Total Debt None 18.8 35.1 42.9 49.6 52.0 59.0 72.0 Depreciation ($mill) 100 LT Debt None 16.9 17.4 20.2 31.1 50.1 51.1 59.5 87.3 92.7 120.0 150.0 Net Profit ($mill) 205 Leases, Uncapitalized: Annual rentals $26.2 mill. 42.8% 38.2% 36.3% 32.3% 20.3% 27.0% 27.0% Income Tax Rate 7.5% 10.8% 10.2% 9.6% 9.3% 9.7% 10.4% Net Profit Margin No Defined Benefit Pension Plan 39.2 39.8 58.6 73.4 59.9 97.3 112.4 99.7 66.6 115 230 Working Cap'l ($mill) 685 Nil Long-Term Debt (Smill) Pfd Stock None Common Stock 12,034,005 shs. 165.6 184.7 245.1 302.1 436.1 446.6 423.5 460.3 770 920 Shr. Equity ($mill 1470 (Includes 2,817,983 shares of Class B common 18.0% 30.3% 27.6% 24.3% 23.3% 19.5% 18.6% 16.5% Return on Total Cap'l stock.)as of October 25, 2019. 18.0% 30.3% 27.6% 23.3% 20.8% 21.3% 18.6% 20.1% 16.5% Return on Shr. Equity MARKET CAP: $4.5 billion (Mid Cap) 18.0% 30.3% 2 20.1% 15.5% 16.5% Retained to Com Eq 14.0%CAPITAL STRUCTURE as of 9/28/19 Total Debt None LT Debt None Leases, Uncapitalized: Annual rentals $26.2 mill. No Defined Benefit Pension Plan Pfd Stock None Common Stock 12,034,005 shs. (Includes 2,817,983 shares of Class B common stock.)as of October 25, 2019. MARKET CAP: $4.5 billion (Mid Cap) CURRENT POSITION 2017 2018 9/28/19 $MILL.) Cash Assets 65.6 108.4 27.1 B Receivables Inventory (FIFO) 33.8 34.1 68.7 50.7 70.2 92.6 Other 18.3 18.9 21.0 Current Assets 168.4 231.6 209.4 Accts Payable 38.1 47.1 71.0 Debt Due Other 63.7 73.4 95.5 Current Liab. 101.8 120.5 166.5 ANNUAL RATES Past Past Est'd '16-'18 of change (per sh) 10 Yrs. 5 Yrs. to '22-24 Sales "Cash Flow" 12.5% 10.5% 13.5% 18.5% 12.5% 14.0% Earnings 16.5% 9.5% 15.0% Dividends Nil Book Value 15.5% 14.5% 22.0% Cal- QUARTERLY SALES ($ mill.) A Full endar Mar.Per Jun.Per Sep.Per Dec.Per Year 2016 188.8 244.8 253.4 219.4 906.4 2017 161.7 247.9 247.1 206.3 863.0 2018 190.4 273.1 306.9 225.2 995.6 2019 251.7 318.4 378.5 276.4 1225 2020 300 400 450 305 1455 Cal- EARNINGS PER SHARE A B Full endar Mar.Per Jun.Per Sep.Per Dec.Per Year 2016 .53 2.06 2.48 1.75 6.82 2017 45 2.35 2.78 85 6.37 2018 .78 1.98 3.21 1.86 7.83 2019 2.02 2.36 3.65 1.52 9.55 2020 2.20 3.10 4.65 2.20 12.15 Cal- QUARTERLY DIVIDENDS PAID Full endar Mar.31 Jun.30 Sep.30 Dec.31 Year 2016 2017 NO CASH DIVIDENDS 2018 BEING PAID 2019