Security Valuation Project Estimate American Express
apply solution in Excel on the file attached
there are two sheets in file
Problems: 1. Calculate the weekly return on JBL by dividing the adjusted close in any particular week by the adjusted closing price the previous week and subtracting 1.
2. Calculate the weekly return on the SPY the same way. Paste the returns on the S&P and the returns on JBL into a single spreadsheet. (To do so, use the “Paste-Special-Values” sequence in Excel).
3. Calculate the correlation coefficient between JBL returns and SPY returns.
4. You will run a regression in Excel using the returns on JBL as the dependent, or Y variable, and returns on the SPY as the independent, or X variable. You can use the data analysis function under the tools menu (you may have to first click “Add ins” under the tools menu and then check the box labeled “Analysis Toolpak”). Click “Tools-Data Analysis Regression” to set up the regression. Type in the cell range containing JBL returns for the input Y range, and type in the cell range containing the SPY returns for the input X range. Leave all the other boxes unchecked. You have just estimated JBL’s beta, which is the coefficient of the X variable in the regression output.
5. Estimate JBL’s required rate of return using a risk-free rate of 0.5% and a market risk premium of 1.5%.
6. Estimate JBL’s dividend growth rate (g) using the formula g = ROE × b, where b is the retention ratio. Remember that b = 1- Dividend Payout Ratio. Use the value of “Dvd Payout Ratio” for 2018Y in the “11) Profitability” tab under “5) Ratios.” Convert the dividend payout ratio to a decimal. Use the ROE value in the same location, and convert it to a decimal.
7. Find the most recent annual dividend paid by JBL as of the most recent date in the date range and assume that the dividend growth rate will remain constant. What would be JBL’s intrinsic stock price according to the Constant Dividend Growth Model?
8. Answer the following questions by comparing your computed stock price from Problem 7 to JBL’s closing price as of the most recent date in the raw data. Is JBL’s current stock price overvalued or undervalued? Why? If you already hold JBL in your portfolio, should you sell it or keep holding it? Why? If you do not already hold JBL, should you buy it or not? Why?
