Question: Now calculate the beta for each stock using Excel's Slope or Linest function to use estimate the slope of the linear regression line, which

Now calculate the beta for each stock using Excel's Slope or Linest

Now calculate the beta for each stock using Excel's Slope or Linest function to use estimate the slope of the linear regression line, which is beta. Use the S&P 500 index as the market portfolio in finding beta. 5. Now use the CAPM/SML Equation to estimate each stock's required return. Use 8.5% as the required market return and the recent 10-year Treasury rate of 0.85% as the risk- free rate. Would you recommend buying BEST BUY and/or Home Depot if your APR average for each stock from #2 is your expected return? Explain your answer. 6. Construct a graph of the Security Market Line given by the parameters in #5. Point out the SML returns you calculated in #5 on your SML. Also, find and plot BEST BUY's and Home Depot's expected returns from the information given below gathered from their recent Yahoo Finance stock quotes against each stock's beta on your SML graph. Would you recommend buying BEST BUY and/or Home Depot based on this information? Explain your answers. Note: you are evaluating each stock individually, not in comparison in one another. Stock Current Price Estimated Price 1 year from now Annual Expected Dividend (D1) BEST BUY $116.00 Home Depot $280.00 $118.59 $2.20 $6.00 $302.72 7. Now, assume that Home Depot's current price is in equilibrium and is a constant growth stock. What is Home Depot's expected constant growth rate based on your CAPM return? Use price and dividend data from #6. 8. Use the variable dividend growth valuation model to value BEST BUY. Here are the projections. Year 1 dividend 2.40 Dividends are projected to grow 12% annually in years 2 through 4 After year 4, dividends have a projected constant growth rate of 10% Using your Best Buy CAPM required return from # 5, what is your valuation of BEST BUY's common stock today? Would you recommend buying BEST BUY's stock today and why? Use current price in #6 to help aid your recommendation.

Step by Step Solution

★★★★★

3.42 Rating (142 Votes )

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

Calculations and Analysis 5 Beta and Required Returns Beta excel SLOPEB2B11A2A11 This formula uses the SLOPE function to calculate the beta for each s... View full answer

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Accounting Questions!

1. If a project schedule needs to be adjusted, what trade-offs might have to occur? 2. Who can initiate a change to a project schedule? Why would they do so? When would they do so? Give examples....

Bella sells vacuums and shampooers. The vacuums each sell for $60 with variable costs of $20, and the shampooers each sell for $100 with variable costs of $60. Thefixed costs for the store are...

Assume that you recently graduated with a major in finance and that you just landed a job as a financial planner with Barney Smith Inc., a large financial services corporation. Your first assignment...

Question: Calculate the beta for each stock using Excels Slope or Linest function to use estimate the slope of the linear regression line, which is beta. Use the S&P 500 index as the market portfolio...

4. Now calculate the beta for each stock using Excel's Slope or Linest function to use estimate the slope of the linear regression line, which is beta. Use the S&P 500 index as the market portfolio...

Assignment Overview This project consists of determining the current weighted average cost of capital for a publicly traded firm. Please read the information below. The Problem As new analysts in the...

Date Open High Low Close Volume Adj Close Monthly Required Return Annual Return Average Annual Return SD of Returns 2/1/2016 1936.939941 1962.959961 1810.099976 1932.22998 4881887000 1932.22998...

Part D ) Graph Insert a Scatter Plot to plot the stock returns ( y - axis ) versus the market returns ( x - axis ) . To do so , select the Insert Tab and then select Charts, and finally select the...

7. EXTRA CREDIT: Calculate the BETA for Stock Y using Excel SLOPE function. Note Y variable inputs includ. Stock Y" Use the CAPM equation to find the expected return on Stock A: Given: f=8%, rm =...

Please do in excel and show cell references/formulas! Problem 12-27 The spreadsheet below has data on Microsoft and the S&P 500 from 1986 to 2006. Complete the steps below using cell references to...

An airplane propeller is 2.08 m in length (from tip to tip) with mass 117 kg and is rotating at 2400 rpm {rev/min} about an axis through its center you can model the propeller as a slender rod. (a)...

Oil, with = 890 kg/m3 and = 0.07 kg/ms, flows through a horizontal pipe 15 m long. The power delivered to the flow is 1 hp. (a) What is the appropriate pipe diameter if the flow is at the laminar...

Which fraud is usually prevented when supervisors monitor hours worked? Payroll calculations are inaccurate. Fictitious employees added to the nave oll. Employee paid for hours not worked. Checks are...

A long, hollow conducting pipe of radius R carries a uniform current I along the pipe, as shown in Fig. 26.46. Use Amperes law to find the magnetic field strength (a) inside and (b) outside the pipe....

Which of the following is not necessary for a special purpose local government to be viewed as a primary government for reporting purposes? a. It must have a separately elected governing body. b. It...

(a) Neglecting activity coefficients and ion pairing, find the pH and concentrations of species in 1.00 L of solution containing 0.040 mol 2-aminobenzoic acid (a neutral molecule, HA), 0.020 mol...

Neglecting activity coefficients and ion pairing, find the pH and concentrations of species in 1.00 L of solution containing 0.010 mol hydroxybenzene (HA), 0.030 mol dimethylamine (B), and 0.015 mol...

A Harris Corporation/University of Florida study was undertaken to determine whether a manufacturing process performed at a remote location can be established locally. Test devices (pilots) were set...

The average age of menopause is about _____ years.Question 44 options:a)45b)60c)51d)40

Ugh Inc.'s net income for the most recent year was $17,382. The tax rate was 34 percent. The firm paid $3,953 in total interest expense and deducted $4,283 in depreciation expense. What was the cash...

You've observed the following returns on Crash-n-Bum Computer's stock over the past five years: 7 percent, -13 percent, 21 percent, 34 percent, and 15 percent. a. What was the arithmetic average...

What are the four primary disadvantages of the sole proprietorship and partnership forms of business organization? What benefits are there to these types of business organization as opposed to the...

The project described in tire following table and precedence network has just begun. It is scheduled to be completed in 11 weeks. a. if you were the manager of tills project, would you be concerned?...

List the steps in risk management planning.

Explain briefly how Delteks (Welcom) EPM software suite assists in project management.