BC El Cap Climbing Company Sensitivity to Market Fluctuations Questions

User Generated

Natvrl3

Business Finance

Brooklyn College

Description

Part 4: Risky Business

Lastly, just for fun, El Cap Climbing Company (ECCC) is looking at determining its sensitivity to market fluctuations.

Since ECCC isn’t publically traded and can’t look at its own stock history, it must evaluate its competitors. Black Diamond Equipment is its closest competitor, but the company doesn’t have enough trading volume to make any sound conclusions. Leah identifies Callaway Golf Company (ELY) as ECCC’s closest publicly-traded competitor. Even though ELY sells golf equipment, it too is a specialized company selling high-tech sports equipment.

Finding Beta with CAPM

Note: This information is also in your textbook.

CAPM is one of the most thoroughly researched models in financial economics. When beta is estimated in practice, a variation of CAPM, called the market model, is often used. To derive the market model, start with the CAPM:

E(Ri) = Rf + β[E(RM) − Rf]

Since CAPM is an equation, you can subtract the risk-free rate from both sides, which gives you:

E(Ri) − Rf = β[E(RM) − Rf]

This equation is deterministic—that is, exact. In a regression, you'll realize that there’s some indeterminate error. You need to formally recognize this in the equation by adding epsilon, which represents this error:

E(Ri) − Rf = β [E(RM) − Rf] + ε

Finally, think of the above equation in a regression. Since there’s no intercept in the equation, the intercept is zero. However, when you estimate the regression equation, you can add an intercept term, which is called alpha here:

E(Ri) − Rf = αi + β[E(RM) − Rf] + ε

The intercept term is known as Jensen’s alpha, and it represents the “excess” return. If CAPM holds exactly, this intercept should be zero. Think of alpha in terms of the SML: If the alpha is positive, the stock plots above the SML; if the alpha is negative, the stock plots below the SML. You’ll first create a scatter plot and then perform a regression analysis for ELY stock and the mutual fund. Then, use those results to compare and analyze the results.

A. Scatter Plotting and Regression Analysis

Use the following steps to create the scatter plot:

  1. Go to the Part 4 Stock Data tab in your Excel spreadsheet. Highlight column K–M headings, then hold down the Ctrl button and select cells K-3 through M-62.
  2. Go to the Insert tab, click on Scatter Chart, and select the first style.
    An image of a dialogue box with five types of scatter charts and two types of bubble charts
  3. Move the chart to the side of the data, and increase the size of the chart. Click on the Chart Title and change it to Risk Premium Analysis.
  4. In the Design tab, click on Quick Layout. Select the layout that gives you the y formulas and the R2.
    An image of a dialogue box of quick layout with 11 layouts.
  5. Move the y formulas and the R2 to the bottom right-hand corner of the chart.

Now, use the following steps to create a regression analysis for ELY and for the Mutual Fund:

  1. First, check to see that you have the ability to run the analysis. Go to the Data tab in Excel, and look for the Data Analysis feature shown in the following image.
    An image of the Excel sheet menu bar with the data analysis selection under data tab.

If you don’t see Data Analysis, you might need to add Analysis Toolpak in Excel. For instructions on how to load the Analysis Toolpak into your version of Excel, visit https://support.office.com/en-us/article/Load-the-Analysis-ToolPak-6a63e598-cd6d-42e3-9317-6b40ba1a66b4. Contact Microsoft Support if you have any issues with this add-in. Once you've completed all these steps, you can continue with your project.

  1. Click on Data Analysis. A dialog box with a list of analysis tools will open. Select Regression from the list and click OK.
    An image of data analysis dialogue box with regression option selected in it.
  2. Next, another dialog box opens for you to select your Inputs and Output for the regression. Select the input data ranges by highlighting S&P Risk Premium numbers (x-axes range) and the number for the asset you’re comparing as the y-axes range. Output to a new worksheet (do not type a name in the text box). Select the checkboxes for Labels, Confidence Level, and Residuals. Click OK.
    An image of Regression dialogue box.
  3. Your regression analysis will open in a new worksheet. Rename the worksheet based on the premium being compared to the S&P premium, for example: “ELY Regression Analysis.”

B. Answer the following questions:

  1. In this regression, Rt is the return on the stock, and Rft is the risk-free rate for the same period. RMt is the return on a stock market index, such as the S&P 500 index. αi is the regression intercept, and βi is the slope (and the stock’s estimated beta). εt represents the residuals for the regression. The intercept, αi, is often called Jensen’s alpha. What does it measure? If an asset has a positive Jensen’s alpha, where would it plot with respect to the SML?
    RtRft = αi + βi [RMt - Rft] + εt
  2. Is the alpha of either ELY or the mutual fund significantly more or less than zero? (Hint: The alpha is the intercept.)
  3. How do you interpret the beta for the stock and the mutual fund? (Hint: The beta is next to the coefficient.)
  4. Which of the two regression estimates has the highest R-squared? Is this what you would have expected? Use the scatterplot to explain why.

User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Please see attached answers requested. The scatter plot is in the excel sheet 'Part 4 Stock Data' tab. The word file contains all the answers with the image of the scatter plot.

Question 1:
"In this regression, Rt is the return on the stock, and Rft is the risk-free rate for the same period. RMt is
the return on a stock market index, such as the S&P 500 index. αi is the regression intercept, and βi is
the slope (and the stock’s estimated beta). εt represents the residuals for the regression. The intercept,
αi, is often called Jensen’s alpha. What does it measure? If an asset has a positive Jensen’s alpha, where
would it plot with respect to the SML?
Rt − Rft = αi + βi [RMt - Rft] + εt"
Answer:
The left-hand side of the equation (Rt-Rft) gives us the premium return that the stock gives for choosing
this risky asset.
Of the right-hand side,
[RMt - Rft] gives the return premium that market gives for choosing risky asset.
βi [RMt - Rft] multiplying it with βi brings the return premium offered by market to return
premium that should be offered by the stock.
Εt takes into account any errors
The intercept αi or Jensen's alpha is a measure showing the excess return that stock is offering
compared to the return that it should offer as per CAPM.

If an asset has positive Jensen's alpha, it would plot on Y-axis with respect to the SML.
Question 2:
Is the alpha of either ELY or the mutual fund significantly more or less than zero? (Hint: The alpha is the
intercept.)
Answer:
The t-stat for both Ely and Mutual fund vary a lot.
t-stat
Mutual fund
22.15873553
ELY
0.208319942
It depicts that t-stat of Mutual fund holds the null hypothesis true that the correlation between
Mutual fund and S&P500 is true. This mean the return variation would be less leading to alpha value
of 0.

For ELY, it can be said that the S&P500 and ELY return vary a lot and hence, alpha will be significantly
more or less than 0.
Question 3:
How do you interpret the beta for the stock and the mutual fund? (Hint: The beta is next to the
coefficient.)
Answer:
Standard error
Mutual fund
ELY

0.00007996
0.01031561

The beta coefficient reflects the degree of change in one variable for every 1-unit of change in another
variable.
Comparatively, β of ELY is higher indicating it varies in larger proportion compared to change in S&P500.
The β of Mutual fund indicates its result are similar to S&P500.

Question 4: Which of the two regression estimates has the highest R-squared? Is this what you would
have expected? Use the scatterplot to explain why.
Answer:

Of the two regression estimates, R-squared value of Mutual fund (0.9996) is higher than ELY (0.1992).
As the R-squared value for Mutual fund is closer to 1, it indicates that it is a better fit of the regression
line.
In other words, the variation in y IE Mutual Fund (VINIX) is very well described by variation in x IE S&P
500 leading to a value closer to 1.
It is evident from the scatter plot too. The squared error is too small in case of Mutual fund.


Cost of Goods Sold
Cash
Depreciation
Interest Expense
Selling and Admin Exp
Accounts Payable
Net Fixed Assets
Sales
Accounts Receivable
Notes Payable
Long-Term Debt
Inventory
New Equity

2015
235,942
36,542
61,056
13,877
40,952
32,194
269,369
482,155

2016
297,915
51,940
69,011
15,905
58,569
33,999
328,185
587,715

24,120

24,089

24,866
142,148

26,972
161,000

32,766
0

58,798
16,000

I. INCOME STATEMENT

Sales
Cost of goods sold
Gross profit
Selling and admin expenses
Depreciation
Earning before interest and taxes
Interest expense
Taxable Income
Tax (30%)
Net income

Sales
Cost of goods sold
Gross profit
Selling and admin expenses
Depreciation
Earning before interest and taxes
Interest expense
Taxable Income
Tax (30%)
Net income

2018 and 2019 Balance sheets

Assets

Cash
Inventory
Accounts receivable
Net current assets
Net fixed assets

Total assets

I. INCOME STATEMENT
El Cap Climbing Company
2018 Income Statement
$

$
$
$
$
$

482,155
235,942
246,213
40,952
61,506

$
$
$
$
$

143,755
13,877
129,878
38,963.40
90,914.60

El Cap Climbing Company
2019 Income Statement
$

II. BALANCE SHEET
El Cap Climbing Company
2018 and 2019 Balance sheets

$
$
$
$
$

587,715
297,915
289,800
58,569
69,011

$
$
$
$
$

162,220
15,905
146,315
43,894.50
102,420.50

Liabilities and Equity

$
$
$
$
$

$

Accounts payable
Notes payable
Current liabilities
Long term debt
Net non-current liabilities
Total liabilities
Old equity
Total equity
362,797 Shareholders equity and liability
36,542
32,766
24,120
93,428
269,369

2019

Loan Amount
Number of Periods
Monthly Interest Rate
Number of Payments

$500,000 Monthly payment:
360
0.42%
Payment

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11

Interest expense
$2,083.33
$2,080.83
$2,078.32
$2,075.79
$2,073.26
$2,070.71
$2,068.16
$2,065.59
$2,063.01
$2,060.43
$2,057.83
$2,055.22
$2,052.60
$2,049.97
$2,047.32
$2,044.67
$2,042.01
$2,039.33
$2,036.64
$2,033.95
$2,031.24
$2,028.52
$2,025.78
$2,023.04
$2,020.29
$2,017.52
$2,014.74
$2,011.95
$2,009.15
$2,006.34
$2,003.52
$2,000.68
$1,997.83
$1,994.97
$1,992.10
$1,989.22
$1,986.32
$1,983.42
$1,980.50
$1,977.57
$1,974.62

Principal
$600.77
$603.28
$605.79
$608.32
$610.85
$613.40
$615.95
$618.52
$621.10
$623.68
$626.28
$628.89
$631.51
$634.14
$636.79
$639.44
$642.10
$644.78
$647.46
$650.16
$652.87
$655.59
$658.32
$661.07
$663.82
$666.59
$669.36
$672.15
$674.95
$677.77
$680.59
$683.43
$686.27
$689.13
$692.00
$694.89
$697.78
$700.69
$703.61
$706.54
$709.49

Balance
$499,399.23
$498,795.95
$498,190.16
$497,581.84
$496,970.99
$496,357.59
$495,741.64
$495,123.12
$494,502.03
$493,878.35
$493,252.06
$492,623.17
$491,991.66
$491,357.52
$490,720.73
$490,081.30
$489,439.19
$488,794.41
$488,146.95
$487,496.79
$486,843.92
$486,188.32
$485,530.00
$484,868.93
$484,205.11
$483,538.53
$482,869.16
$482,197.01
$481,522.05
$480,844.29
$480,163.70
$479,480.27
$478,794.00
$478,104.87
$477,412.86
$476,717.97
$476,020.19
$475,319.50
$474,615.89
$473,909.35
$473,199.86

42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11

$1,971.67
$1,968.70
$1,965.72
$1,962.72
$1,959.72
$1,956.70
$1,953.67
$1,950.62
$1,947.57
$1,944.50
$1,941.42
$1,938.32
$1,935.22
$1,932.10
$1,928.96
$1,925.82
$1,922.66
$1,919.48
$1,916.30
$1,913.10
$1,909.89
$1,906.66
$1,903.42
$1,900.17
$1,896.90
$1,893.62
$1,890.33
$1,887.02
$1,883.70
$1,880.36
$1,877.02
$1,873.65
$1,870.28
$1,866.88
$1,863.48
$1,860.06
$1,856.63
$1,853.18
$1,849.72
$1,846.24
$1,842.75
$1,839.24
$1,835.72
$1,832.19
$1,828.64
$1,825.07
$1,821.49

$712.44
$715.41
$718.39
$721.38
$724.39
$727.41
$730.44
$733.48
$736.54
$739.61
$742.69
$745.78
$748.89
$752.01
$755.15
$758.29
$761.45
$764.62
$767.81
$771.01
$774.22
$777.45
$780.69
$783.94
$787.21
$790.49
$793.78
$797.09
$800.41
$803.74
$807.09
$810.46
$813.83
$817.22
$820.63
$824.05
$827.48
$830.93
$834.39
$837.87
$841.36
$844.87
$848.39
$851.92
$855.47
$859.03
$862.61

$472,487.42
$471,772.01
$471,053.62
$470,332.23
$469,607.84
$468,880.43
$468,149.99
$467,416.51
$466,679.97
$465,940.36
$465,197.67
$464,451.89
$463,703.00
$462,950.98
$462,195.84
$461,437.55
$460,676.10
$459,911.47
$459,143.66
$458,372.65
$457,598.43
$456,820.98
$456,040.29
$455,256.35
$454,469.15
$453,678.66
$452,884.88
$452,087.79
$451,287.38
$450,483.64
$449,676.55
$448,866.09
$448,052.26
$447,235.03
$446,414.40
$445,590.36
$444,762.88
$443,931.95
$443,097.55
$442,259.69
$441,418.33
$440,573.46
$439,725.08
$438,873.16
$438,017.69
$437,158.65
$436,296.04

89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135

$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.11
$2,684.1...


Anonymous
This is great! Exactly what I wanted.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags