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?
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
We are here to help you