70.2K

Verified Solution

Link Copied!

Next, from the stock price data in the spreadsheet, copy your firms closing stock prices into the template file in the column Stock price (Column B). Also, from the spreadsheet, copy the closing data for the index you are using into the column Index value (Column C). The worksheet will automatically calculate the stock and index monthly returns and will calculate the monthly standard deviation, and coefficient of variation below the columns of returns.

To calculate the beta for the firm you must use the regression function of EXCEL. After copying the new data into the spreadsheet, click on Tools and then on Data analysis. If you do not see Data Analysis under tools, you must add- in Data Analysis (you can find instructions under Help). A list of analysis alternative should appear. Go down the list and click on Regression (this will highlight Regression) and then click on OK. A window should appear for the regression analysis technique. If the regression does not automatically include your data, you will need to list the stock returns as the y variable (Column D), the index returns as the x variable (Column E), and cell a72 as the output range. Alternately, and a preferred method, you choose a separate Excel sheet as the output range. If using this method, name your new tabs based on the regression such as SP500 vs WGO. Before printing the worksheet, change the title line at the top of the worksheet to indicate the name of the firm you are analyzing and the index used in this calculation. To print the worksheet, click on File, then click on Print, and then click on OK. The print settings have been preset to ensure that all of the information will print, but this is sometimes lost in the downloading process.

must create worksheet (and calculate beta) for your company with each of the three indices you choose to use (select from S&P 500, NYSE Composite, NASDAQ COMPOSITE, Russell 1000, Russell 3000, and NYSE World Leaders). Also calculate a beta for the NASDAQ Composite relative to the S&P500 by using the template and putting the NASDAQ values in under Stock price and the S&P500 values in under Index value. Ultimately, you will create four regressions (your company versus three indices, and NASDAQ versus S&P 500).

Key Financials sFor the fiscal period ending 12 month 12months 12months 12months 12months 12months 12months Sept 30 2016A Sept 28. 2018 A Oct 2 2020 A Sept 29. 2019 A Sept 30 2021 E Sept 30 2022 Sept 30 2023 E 12 months Sept 29 2017 A USD 14,604.4 1.3% USD 14,415.8 0.6% USD 15,789.6 8.1% USD 16,227.3 2.8% USD 12,829.6 (20.9%) USD 12,772.38 (0.45%) USD 15,797.21 23.68% USD 16,758.62 6.09% 1,531.4 10.6% 1,228.1 8.5% 752.3 5.2% 288.2 1,609.0 11.0% 1,280.6 8.8% 795.2 5.4% 374.2 Currency Total Revenue Growth over prior year Gross profit Margin % EBITDA Margin % EBIT Margin % Earnings from Cont Ops Margin % Net Income Margin% Diluted EPS Excl extra items Growth over prior year 1,791.7 11.3% 1,408.3 8.9% 842.6 5.3% 568.4 1,706.7 10.5% 1,314.8 8.1% 755.9 4.7% 448.5 1,393.6 10.9% 474.1 3.7% (82.6) (0.6%) (461.4) 8.55% 806.59 6.32% 366.5 2.87% 12.15% 1,439.51 9.11% 990.51 6.27% 13.00% 1,556.02 9.28% 1,107.84 6.61% 2.0% 287.8 2.0% 1.16 2.6% 373.9 2.6% 1.49 3.6% 567.9 3.6% 2.24 2.8% 448.5 2.8% 1.78 (3.6%) (461.5) (3.6%) (1.83) (54.34) (0.43%) (0.21) 445.23 2.82% 1.77 540.09 3.22% 2.12 20.8% 28.4% 50.3% (20.5%) NM 20.03% Of USD) Latest Capitalization (Millions Currency USD Share price $36.55 Shares Out 253.9 Market 9,281.1 Capitalization -Cash &short. 2,509.2 term investments +Total Debt 9,808.8 Drofit 10.0 + Pref Equity +Total Minority Interest 16,590.6 2,736.0 10.0 = Total Enterprise Value (TEV) Book value of common equity +Pref equity + Total Minority Interest +Total Debt = Total Capital Total Liability includes Total Debt, Minority Interest and Pref. Equity. 9,808.8 12,554,8 Net Liability includes Total Liability, net of Net Liability includes Total Liability, net of Cash and Short Term Investments TEV includes Market Cap and Net Liability. Total Capital includes Common Equity and Total Liability Valuation multiples based on Current Capitalization For fiscal period ending 12 months 12 months 12 months 12 months 12 months Sept 27-2019 Oct 2 A 2020 A 1.0 x 1.3 x Sept 30 Sept 30 Sept 30- 2021 E 2022 E 2023 E 1.30 x 1.95 x 0.99 x TEV/Total revenue TEV/EBITDA TEV/EBIT P/Diluted EPS before extra P/BV Price/Tang BV 12.6 x 21.9 x 20.5 x 15.8 x NM NM 20.57 x 45.27 x 11.53 x 16.75 x 20.70 x 10.66 x 14.98 x 17.25 x 3.96 x 3.63 x 3.22 x 2.7 x NM 3.4 x NM

Answer & Explanation Solved by verified expert

See Answer