f. Estimate Bartman's and Reynolds's betas by running regressions of their returns against the index's returns. Round your answers to four decimal places.Bartman's beta: fill in the blank 30Reynolds's beta: fill in the blank 31Are these betas consistent with your graph?These betas consistent with the scatter diagrams.g. Assume that the risk-free rate on long-term Treasury bonds is 4.5%. Assume also that the average annual return on the Winslow 5000 is not a good estimate of the market's required return—it is too high. So use 9% as the expected return on the market. Use the SML equation to calculate the two companies' required returns. Round your answers to two decimal places.Bartman's required return: fill in the blank 33 %Reynolds's required return: fill in the blank 34 %h. If you formed a portfolio that consisted of 50% Bartman and 50% Reynolds, what would the portfolio's beta and required return be? Round your answer for the portfolio's beta to four decimal places and for the portfolio's required return to two decimal places.Portfolio's beta: fill in the blank 35Portfolio's required return: fill in the blank 36 %i. Suppose an investor wants to include Bartman Industries's stock in his portfolio. Stocks A, B, and C are currently in the portfolio, and their betas are 0.829, 0.969, and 1.486, respectively. Calculate the new portfolio's required return if it consists of 30% of Bartman, 10% of Stock A, 40% of Stock B, and 20% of Stock C. Round your answer to two decimal places.fill in the blank 37 % Please help in solving with excel. I am having trouble getting the rest of the problem. for d. Assume the risk-free rate during this time was 2% and round to four decimal places. ### Financial Analysis of Bartman Industries, Reynolds Inc., and Winslow 5000#### a. Return Data**Annual Returns (%)** - **2019**: - Bartman Industries: -4.47% - Reynolds Inc.: 14.21% - Winslow 5000: 1.02%- **2018**: - Bartman Industries: 60.27% - Reynolds Inc.: -12.19% - Winslow 5000: 31.84%- **2017**: - Bartman Industries: 4.66% - Reynolds Inc.: -1.76% - Winslow 5000: 16.44%- **2016**: - Bartman Industries: 58.94% - Reynolds Inc.: 12.38% - Winslow 5000: 21.15%- **Average Returns**: - Bartman Industries: 28.80% - Reynolds Inc.: 2.01% - Winslow 5000: 19.85%#### b. Standard Deviations of Returns- **Bartman Industries**: 30.03%- **Reynolds Inc.**: 11.11%- **Winslow 5000**: 12.17%#### c. Coefficients of Variation- **Bartman Industries**: 1.04- **Reynolds Inc.**: 5.54- **Winslow 5000**: 0.61#### d. Sharpe Ratios*Assuming a risk-free rate of 2%:*- **Bartman Industries**: 0.9586- **Reynolds Inc.**: 0.18- **Winslow 5000**: Not provided#### e. Scatter DiagramThe correct graph depicting Bartman's and Reynolds’s returns on the vertical axis and the Winslow 5000 Index's returns on the horizontal axis is **graph B**. # Excel Activity: Evaluating Risk and ReturnBartman Industries's and Reynolds Inc.'s stock prices and dividends, along with the Winslow 5000 Index, are shown here for the period 2015–2020. The Winslow 5000 data are adjusted to include dividends.| Year | Bartman Industries Stock Price | Dividend | Reynolds Inc. Stock Price | Dividend | Winslow 5000 Includes Dividends ||------|-------------------------------|----------|---------------------------|----------|-------------------------------|| 2020 | $17.25 | $1.13 | $49.25 | $2.85 | $11,119.99 || 2019 | 14.75 | 1.06 | 53.50 | 2.75 | 8,631.61 || 2018 | 16.55 | 1.00 | 49.25 | 2.60 | 8,544.66 || 2017 | 10.95 | 0.95 | 59.05 | 2.45 | 6,481.17 || 2016 | 11.37 | 0.90 | 62.60 | 2.30 | 5,566.25 || 2015 | 7.72 | 0.85 | 57.75 | 2.10 | 4,594.61 |*The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round intermediate calculations. Use a minus sign to enter negative values, if any.*### [Download spreadsheet Evaluating Risk and Return-75da4d.xlsx](#)### Instructions:a. Use the data to calculate annual rates of return for Bartman, Reynolds, and the Winslow 5000 Index. Then calculate each entity's average return over the 5-year period. *(Hint: Remember, returns are calculated by subtracting the beginning price from the ending price to get the capital gain or loss, adding the dividend to the capital gain or loss, and dividing the result by the beginning price. Assume that dividends are already included in the index. Also, you cannot calculate the rate of return for 201

Given: Bartman Industries Reynolds Inc Winslow 5000 Year Stock Price Dividend Stock Price Dividend Includes dividends 2020 17.25 1.13 $49.25 $2.85 $11,119.99 2019 14.75 1.06 $53.50 $2.75 $8,631.61 2018 16.55 1 $49.25 $2.60 $8,544.66 2017 10.95 0.95 $59.05 $2.45 $6,481.17 2016 11.37 0.9 $62.60 $2.30 $5,566.25 2015 7.72 0.85 $57.75 $2.10 $4,594.61Calculate the Sharpe ratio, beta and required return as follows: Formulas:

Estimate Bartman's and Reynolds's betas by running regressions of their returns against the index's returns. Round your answers to four decimal places. Bartman's beta: fill in the blank 30 Reynolds's beta: fill in the blank 31 Are these betas consistent with your graph? These betas consistent with the scatter diagrams. g. Assume that the risk-free rate on long-term Treasury bonds is 4.5%. Assume also that the average annual return on the Winslow 5000 is not a good estimate of the market's required return—it is too high. So use 9% as the expected return on the market. Use the SML equation to calculate the two companies' required returns. Round your answers to two decimal places. Bartman's required return: fill in the blank 33 % Reynolds's required return: fill in the blank 34 % h. If you formed a portfolio that consisted of 50% Bartman and 50% Reynolds, what would the portfolio's beta and required return be? Round your answer for the portfolio's beta to four decimal places and for the portfolio's required return to two decimal places. Portfolio's beta: fill in the blank 35 Portfolio's required return: fill in the blank 36 % i. Suppose an investor wants to include Bartman Industries's stock in his portfolio. Stocks A, B, and C are currently in the portfolio, and their betas are 0.829, 0.969, and 1.486, respectively. Calculate the new portfolio's required return if it consists of 30% of Bartman, 10% of Stock A, 40% of Stock B, and 20% of Stock C. Round your answer to two decimal places. fill in the blank 37 % Please help in solving with excel. I am having trouble getting the rest of the problem. for d. Assume the risk-free rate during this time was 2% and round to four decimal places.

Essentials Of Investments

11th Edition

ISBN:9781260013924

Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.

Publisher:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.

Chapter1: Investments: Background And Issues

Section: Chapter Questions

Problem 1PS

See similar textbooks

Related questions

Question

100%

f. Estimate Bartman's and Reynolds's betas by running regressions of their returns against the index's returns. Round your answers to four decimal places.

Bartman's beta: fill in the blank 30

Reynolds's beta: fill in the blank 31

Are these betas consistent with your graph?

These betas

consistent with the scatter diagrams.

g. Assume that the risk-free rate on long-term Treasury bonds is 4.5%. Assume also that the average annual return on the Winslow 5000 is not a good estimate of the market's required return—it is too high. So use 9% as the expected return on the market. Use the SML equation to calculate the two companies' required returns. Round your answers to two decimal places.

Bartman's required return: fill in the blank 33 %

Reynolds's required return: fill in the blank 34 %

h. If you formed a portfolio that consisted of 50% Bartman and 50% Reynolds, what would the portfolio's beta and required return be? Round your answer for the portfolio's beta to four decimal places and for the portfolio's required return to two decimal places.

Portfolio's beta: fill in the blank 35

Portfolio's required return: fill in the blank 36 %

i. Suppose an investor wants to include Bartman Industries's stock in his portfolio. Stocks A, B, and C are currently in the portfolio, and their betas are 0.829, 0.969, and 1.486, respectively. Calculate the new portfolio's required return if it consists of 30% of Bartman, 10% of Stock A, 40% of Stock B, and 20% of Stock C. Round your answer to two decimal places.

fill in the blank 37 %

Please help in solving with excel. I am having trouble getting the rest of the problem.

for d. Assume the risk-free rate during this time was 2% and round to four decimal places.

### Financial Analysis of Bartman Industries, Reynolds Inc., and Winslow 5000

#### a. Return Data

**Annual Returns (%)**
- **2019**:
- Bartman Industries: -4.47%
- Reynolds Inc.: 14.21%
- Winslow 5000: 1.02%

- **2018**:
- Bartman Industries: 60.27%
- Reynolds Inc.: -12.19%
- Winslow 5000: 31.84%

- **2017**:
- Bartman Industries: 4.66%
- Reynolds Inc.: -1.76%
- Winslow 5000: 16.44%

- **2016**:
- Bartman Industries: 58.94%
- Reynolds Inc.: 12.38%
- Winslow 5000: 21.15%

- **Average Returns**:
- Bartman Industries: 28.80%
- Reynolds Inc.: 2.01%
- Winslow 5000: 19.85%

#### b. Standard Deviations of Returns

- **Bartman Industries**: 30.03%
- **Reynolds Inc.**: 11.11%
- **Winslow 5000**: 12.17%

#### c. Coefficients of Variation

- **Bartman Industries**: 1.04
- **Reynolds Inc.**: 5.54
- **Winslow 5000**: 0.61

#### d. Sharpe Ratios

*Assuming a risk-free rate of 2%:*

- **Bartman Industries**: 0.9586
- **Reynolds Inc.**: 0.18
- **Winslow 5000**: Not provided

#### e. Scatter Diagram

The correct graph depicting Bartman's and Reynolds’s returns on the vertical axis and the Winslow 5000 Index's returns on the horizontal axis is **graph B**.

# Excel Activity: Evaluating Risk and Return

Bartman Industries's and Reynolds Inc.'s stock prices and dividends, along with the Winslow 5000 Index, are shown here for the period 2015–2020. The Winslow 5000 data are adjusted to include dividends.

| Year | Bartman Industries Stock Price | Dividend | Reynolds Inc. Stock Price | Dividend | Winslow 5000 Includes Dividends |
|------|-------------------------------|----------|---------------------------|----------|-------------------------------|
| 2020 | $17.25 | $1.13 | $49.25 | $2.85 | $11,119.99 |
| 2019 | 14.75 | 1.06 | 53.50 | 2.75 | 8,631.61 |
| 2018 | 16.55 | 1.00 | 49.25 | 2.60 | 8,544.66 |
| 2017 | 10.95 | 0.95 | 59.05 | 2.45 | 6,481.17 |
| 2016 | 11.37 | 0.90 | 62.60 | 2.30 | 5,566.25 |
| 2015 | 7.72 | 0.85 | 57.75 | 2.10 | 4,594.61 |

*The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round intermediate calculations. Use a minus sign to enter negative values, if any.*

### [Download spreadsheet Evaluating Risk and Return-75da4d.xlsx](#)

### Instructions:

a. Use the data to calculate annual rates of return for Bartman, Reynolds, and the Winslow 5000 Index. Then calculate each entity's average return over the 5-year period. *(Hint: Remember, returns are calculated by subtracting the beginning price from the ending price to get the capital gain or loss, adding the dividend to the capital gain or loss, and dividing the result by the beginning price. Assume that dividends are already included in the index. Also, you cannot calculate the rate of return for 201

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps with 2 images

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, finance and related others by exploring similar questions and additional content below.