Copy of Lagusay_Smart_Zutter_Problem_5.35_Start Done
xlsx
School
Northern Alberta Institute of Technology *
*We aren’t endorsed by this school
Course
FNCE-331
Subject
Finance
Date
Apr 3, 2024
Type
xlsx
Pages
12
Uploaded by JusticeOryx2071
Spreadsheet Exercise: Problem 5.35
To Do
Create a spreadsheet to model and analyze the distribution of two secu
a.
Estimate the beta values for INTC and PG based on the data from t
b.
Using the CAPM, determine the required rates of return for both IN
c.
Solution
a.
Estimate the beta values for INTC and PG based on the data from t
Katie plans to form a portfolio consisting of two securities, Intel (INTC
amount that she invests in each stock. Katie’s professor suggests that s
r
j
= r
f Katie measures r
f
using the current long-term Treasury bond return of 3
that figure to measure r
m
.
Complete the steps below using cell references to given data or previou
down a column, an absolute cell reference or a mixed cell reference m
type in numerical data into a cell or function. Instead, make a referenc
cases, unless otherwise directed, use the earliest appearance of the dat
Katie has decided that the portfolio will be distributed between INT
and betas of the portfolio. Determine the weighted average required
-20.0%
-15.0%
-10.0%
-5.0%
0.0%
5.0%
10.0%
15.
-10.0%
-5.0%
0.0%
5.0%
10.0%
15.0%
20.0%
25.0%
f(x) = − 0.119451918168832 x + 0.007157233989827
f(x) = − 0.161770919471419 x + 0.011431814805318
Stock Return, %.
Beta for INTC
-0.185
Beta for PG
-0.1195
b.
Using the CAPM, determine the required rates of return for both IN
Risk-free rate
3%
Expected market return
11%
Required rate of return for INTC
2%
Required rate of return for PG
2.0%
c.
Weight of INTC in the portfolio
60%
Weight of PG in the portfolio
40%
Weighted average required rate of return
2%
Katie has decided that the portfolio will be distributed between INT
and betas of the portfolio. Determine the weighted average required
war
where
war
= weighted average required rate of retur
w
i
= weight of security
i
in the portfolio
r
i
= required return of security
i
in the portfol
w
j
= weight of security
j
in the portfolio
r
j
= required return of security
j
in the portfol
wab
where
wab
= weighted average beta for the portfolio
w
i
= weight of security
i
in the portfolio
b
i
= beta for security
i
w
j
= weight of security
j
in the portfolio
b
j
= beta for security j
-15.0%
Column D
Linear (Column D)
Column E
Market Return, %
Weighted average beta
-0.159
2/1/2017
-1.7%
3/1/2017
0.4%
4/1/2017
0.2%
5/1/2017
-0.1%
6/1/2017
-5.9%
7/1/2017
5.1%
8/1/2017
-1.1%
9/1/2017
9.4%
10/1/2017
19.5%
11/1/2017
-1.4%
12/1/2017
3.6%
1/1/2018
4.3%
2/1/2018
2.4%
3/1/2018
6.4%
4/1/2018
-0.9%
5/1/2018
6.9%
6/1/2018
-9.4%
7/1/2018
-3.2%
8/1/2018
0.7%
9/1/2018
-1.8%
10/1/2018
-0.9%
11/1/2018
5.2%
12/1/2018
-4.2%
1/1/2019
0.4%
2/1/2019
9.1%
Requirements
Stock Return, %
Intel (INTC)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
1
2
Note: Compare calculated beta value with that obtained in the equa
3
Note: Compare calculated beta value with that obtained in the equa
4
5
6
7
9
Save the workbook. Close the workbook and then exit Excel. Subm
In cells C15:E29
, insert a Scatter Chart to estimate betas for Intel (
Inserting a Chart
On the Insert tab, in the Charts group, click the arrow next to Insert
Selecting Data Series
Then in Select Data Source window, delete any series created autom
Add new series for the security market line using cells F50:F74
for
Add new series for the stocks from your portfolio using cells F50:F
Trendline Options
Select any point of the security market line and right click on it. Ch
name. Choose Series1.
Select any point of the trendline and right click on it. Choose Form
Select any point of the security market line and right click on it. Ch
name. Choose Series2.
Select any point of the trendline and right click on it. Choose Form
Edit Chart Elements
Add axis titles. Replace Axis Title for the horizontal axis with Mar
In cell D31
, by using cell references and the function SLOPE
, calc
In cell D32
, by using cell references and the function SLOPE
, calc
In cell D38
, by using cell references, calculate the required rate of r
In cell D39
, by using cell references, calculate the required rate of r
In cell D45
, by using cell references, calculate the weighted averag
In cell D46
, by using cell references, calculate the weighted averag
urities in the portfolio:
the table far below using the graphic method and Excel SLOPE function.
NTC and PG.
the table far below using the graphic method and Excel SLOPE function.
C) and Procter& Gamble (PG), and she wonders how the portfolio’s return will depend
she use the capital asset pricing model to define the required returns for the two compan
+ [b
j × (r
m
- r
f
)]
3%. Katie determines that the average return on the S&P 500 Index over the last severa
us calculations. In some cases, a simple cell reference is all you need. To copy/paste a may be preferred. If a specific Excel function is to be used, the directions will specify the
ce to the cell in which the data is found. Make your computations only in the blue cells ta in your formulas, usually the Given Data section
.
TC and PG in a 60% and 40% split, respectively. Hence, a weighted average can be cal
d rate of return and the weighted average beta for the portfolio.
.0%
20.0%
25.0%
NTC and PG.
TC and PG in a 60% and 40% split, respectively. Hence, a weighted average can be cal
d rate of return and the weighted average beta for the portfolio using the following mod
r = (w
i
× r
i
) + (w
j
× r
j
)
rn for the portfolio
lio
lio
b = (w
i
× b
i
) + (w
j
× b
j
)
o
Linear (Column E)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Market Return, %
4.8%
-7.5%
-1.3%
-0.3%
-2.8%
-1.9%
1.6%
-2.7%
-1.1%
-1.2%
4.2%
-3.9%
2.4%
-0.5%
-1.4%
-3.8%
-5.1%
-4.4%
5.0%
-5.7%
2.1%
-2.4%
-6.0%
-10.2%
-8.4%
6.6%
1.0%
4.3%
-8.8%
-0.8%
2.1%
-4.5%
6.7%
-1.3%
3.6%
-6.5%
3.5%
-5.8%
0.3%
-1.3%
6.5%
14.9%
7.5%
-3.9%
-2.7%
18.1%
4.9%
-14.1%
4.5%
-5.5%
Stock Return, %
Procter& Gamble (PG)
ation of the corresponding trendline.
ation of the corresponding trendline.
mit the workbook as directed.
(INTC) and Procter& Gamble (PG). t Scatter (X,Y) or Bubble Chart and choose Scatter Chart. matically. r the X values and cells D50:D74
for the Y values. Do not name the series.
F74
for the X values and cells E50:E74
for the Y values. Do not name the series.
hoose Add Trendline. In the Trendline Options window, select Linear with automatic tr
mat Trendline. Tick on Display Equation on chart.
hoose Add Trendline. In the Trendline Options window, select Linear with automatic tr
mat Trendline. Tick on Display Equation on chart.
rket Return, %
and Axis Title for the vertical axis with Stock Return, %
.
culate the beta value for INTC.
culate the beta value for PG.
return for INTC.
return for PG.
ge required rate of return.
ge beta.
d on the
nies.
al years is 11%, so she uses formula across a row or e use of that function. Do not highlighted below. In all lculated for both the returns
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
lculated for both the returns dels for the calculations:
Points
5
1
1
1
1
1
1
0
rendline rendline
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Related Documents
Related Questions
Jason Jackson is attempting to evaluate two possible portfolios consisting of the same five assets but held in different proportions. He is particularly interested in using beta to compare the risk of the portfolios and, in this regard, has gathered the following data:
LOADING...
.
a. Calculate the betas for portfolios A and B.
b. Compare the risk of each portfolio to the market as well as to each other. Which portfolio is more risky?
Question content area bottom
Part 1
Data table
(Click on the icon here
in order to copy its contents of the data table below into a spreadsheet.)
Portfolio Weights
Asset
Asset Beta
Portfolio A
Portfolio B
1
1.35
17%
29%
2
0.69
26%
8%
3
1.24
10%
22%
4
1.06
11%
20%
5
0.87
36%
21%
Total
100%
100%
a. The beta of portfolio A is
enter your response here.
(Round to three…
arrow_forward
Jm. 157.
arrow_forward
Can you please answer and kindly show detailed human working.
arrow_forward
Can you answer please and kindly show detailed human working.
arrow_forward
Can you please answer and kindly show detailed human working.
arrow_forward
use attachments to answer question
This question relates to Diagrams 6 - 9 from the 9.2 diagrams, each of which shows a set of portfolios plotted on a set of risk/return axes.
Which diagram shows (in red) the set of efficient portfolios in the presence of a risk-free asset?
Select one:
a.
Diagram 6
b.
Diagram 7
c.
Diagram 8
d.
Diagram 9
arrow_forward
Can you accurately answer these, please? show detailed human working out.
It's for financial managment
arrow_forward
I need help please
arrow_forward
Same question in the second picture. Added info box
arrow_forward
Hi there,
I am trying to solve this corporate finace question. I have attached a photo of the data below.
ρ23 is +1.0 and the portfolio includes securities B and C. How do I calculate the feasible set of portfolios for these securities? I would like to know how to calculate this problem as well as how to represent it on a graph using excel
Thank you
arrow_forward
Assume the betas for securities A, B, and C are as shown here. (Click on the icon here
in order to copy its contents of the data table below into a spreadsheet.)
Security
Beta
A
1.58
B
0.65
C
−0.23
If you have a portfolio with
$30,000
invested in each of Investment A, B, and C, what is your portfolio beta?
arrow_forward
Rose Berry is attempting to evaluate two possible portfolios, which consist of the same five assets held in different proportions. She is particularly interested in using beta to compare the risks of the portfolios, so she has gathered the data shown in the following table:
Portfolio weights
Asset
Asset beta
Portfolio A
Portfolio B
1
1.43
15%
20%
2
0.69
40%
5%
3
1.15
10%
35%
4
1.42
5%
20%
5
0.97
30%
20%
Totals
100%
100%
a. Calculate the betas for portfolios A and B.
b. Compare the risks of these portfolios to the market as well as to each other. Which portfolio is more risky?
arrow_forward
As the chief investment officer for a money management firm specializing in taxable individual investors, you are trying to establish a strategic asset allocation for two different clients. You have established that Ms. A has a risk-tolerance factor of 8, while Mr. B has a risk-tolerance factor of 27. The characteristics for four model portfolios follow:
ASSET MIX
Bond
93%
75
32
13
Portfolio
1
2
3
4
Stock
7%
25
GB
87
a. Calculate the expected utility of each prospective portfolio for each of the two clients. Do not round intermediate calculations. Round your answers to two decimal places.
1
2
3
Portfolio
Ms. A
ER
8%
9
10
11
b. Which portfolio represents the optimal strategic allocation for Ms. A? Which portfolio is optimal for Mr. B?
Portfollo-Select-represents the optimal strategic allocation for Ms. A. Portfolio Select is the optimal allocation for Mr. B.
c. For Ms. A, what level of risk tolerance would leave her indifferent between having Portfolio 1 or Portfolio 2 as her strategic…
arrow_forward
9. Jason Jackson is attempting to evaluate two possible portfolios consisting of the same five assets but held in different
proportions. He is particularly interested in using beta to compare the risk of the portfolios and, in this regard, has gathered
the following data: ®.
a. Calculate the betas for portfolios A and B.
b. Compare the risk of each portfolio to the market as well as to each other. Which portfolio is more risky?
Review Only
Click the icon to see the Worked Solution.
a. The beta of portfolio A is
(Round to three decimal places.)
The beta of portfolio B is
(Round to three decimal places.)
b. Portfolio (1)
is slightly less risky than the market (average risk), while portfolio (2).
IS
more risky than the market. Portfolio (3)
given increase or decrease in market risk. (Select from the drop-down menus.)
L's return will move more than portfolio (4)-
's for a
Portfolio (5)-
is the more risky portfolio. (Select from the drop-down menu.)
8: Data Table
(Click on the icon here
in…
arrow_forward
quiz 8-14
arrow_forward
Use attachments to answer questions
This question relates to Diagrams 1 - 4 from the diagrams attached , each of which shows a set of portfolios plotted on a set of risk/return axes.
Which diagram shows (in red) the set of feasible portfolios?
Select one:
a.
Diagram 1
b.
Diagram 2
c.
Diagram 3
d.
Diagram 4
arrow_forward
Sam has her portfolio invested as indicated in the following table.
Stock $'s Invested
Beta
LCAV $150,000
0.50
DFIB $100,000
0.75
GLW $100,000
0.80
DLM
$50,000
1.45
Find the beta of Sam's portfolio.
arrow_forward
Finance
arrow_forward
financial advisor evaluates four stocks for inclusion in an investor's portfolio. A orrelation matrix showing each stock's correlation with the other stocks is shown below Stock ALK CMN BTY DLE ALK 0.40 0.58 1.00 -0.25 BTY 0.40 1.00 0.16 -0.04 CMN -.25 .16 1.00 .37 DLE .58 .04 .37 1.00 f the goal is to reduce the investor's overall portfolio risk, which two stocks should the advisor recommend? a. ALK and DLE b. ALK and CMN c. BTY and DLE BTY and CM
arrow_forward
Show detailed steps to solve the following question.
Consider a portfolio comprised of three securities in the following proportions and with the indicated security beta.
a.) What is the portfolios beta?
b.) Wht is the portfolios expected return?
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you
Related Questions
- Jason Jackson is attempting to evaluate two possible portfolios consisting of the same five assets but held in different proportions. He is particularly interested in using beta to compare the risk of the portfolios and, in this regard, has gathered the following data: LOADING... . a. Calculate the betas for portfolios A and B. b. Compare the risk of each portfolio to the market as well as to each other. Which portfolio is more risky? Question content area bottom Part 1 Data table (Click on the icon here in order to copy its contents of the data table below into a spreadsheet.) Portfolio Weights Asset Asset Beta Portfolio A Portfolio B 1 1.35 17% 29% 2 0.69 26% 8% 3 1.24 10% 22% 4 1.06 11% 20% 5 0.87 36% 21% Total 100% 100% a. The beta of portfolio A is enter your response here. (Round to three…arrow_forwardJm. 157.arrow_forwardCan you please answer and kindly show detailed human working.arrow_forward
- Can you answer please and kindly show detailed human working.arrow_forwardCan you please answer and kindly show detailed human working.arrow_forwarduse attachments to answer question This question relates to Diagrams 6 - 9 from the 9.2 diagrams, each of which shows a set of portfolios plotted on a set of risk/return axes. Which diagram shows (in red) the set of efficient portfolios in the presence of a risk-free asset? Select one: a. Diagram 6 b. Diagram 7 c. Diagram 8 d. Diagram 9arrow_forward
- Hi there, I am trying to solve this corporate finace question. I have attached a photo of the data below. ρ23 is +1.0 and the portfolio includes securities B and C. How do I calculate the feasible set of portfolios for these securities? I would like to know how to calculate this problem as well as how to represent it on a graph using excel Thank youarrow_forwardAssume the betas for securities A, B, and C are as shown here. (Click on the icon here in order to copy its contents of the data table below into a spreadsheet.) Security Beta A 1.58 B 0.65 C −0.23 If you have a portfolio with $30,000 invested in each of Investment A, B, and C, what is your portfolio beta?arrow_forwardRose Berry is attempting to evaluate two possible portfolios, which consist of the same five assets held in different proportions. She is particularly interested in using beta to compare the risks of the portfolios, so she has gathered the data shown in the following table: Portfolio weights Asset Asset beta Portfolio A Portfolio B 1 1.43 15% 20% 2 0.69 40% 5% 3 1.15 10% 35% 4 1.42 5% 20% 5 0.97 30% 20% Totals 100% 100% a. Calculate the betas for portfolios A and B. b. Compare the risks of these portfolios to the market as well as to each other. Which portfolio is more risky?arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you