Showing Page:
1/4
©2021 Charles R. Sebuharara 1 | P a g e
Your Full Name: ________________________________________ Date: _____________
FINANCIAL ECONOMICS
Summer II, 2021
EQUITY ANALYSIS PROJECT (100 points)
This project involves using economic, financial and statistical tools to analyze real data of two
(2) of the stocks listed below and the market index represented by the S&P500.
Due date: No later than 11:59pm EST on Sunday, August 8, 2021.
Submission: Upload completed work to course site in Brightspace. These will be two separate
but complementary reports to be submitted via separate links, as follows:
(1) One link for the regular MS Word document (using this document as “template”), which will
report your answers to the questions in Section II below.
(2) The second link will be for the regular MS Excel (using the posted Excel template as guide),
which consists of calculations of the stock returns and related statistics (see Section I below).
Format: Type the answers directly into this document next to or below the questions (make
space, as necessary). Preserve the numbering and the order of the questions. Modify the file
name by adding YOUR NAME.
References: Provide full references, citations and/or sources of any idea, data or information that
is not your own.
Sources of Data and Information: Most of the basic data and information needed to complete
this project can be found on the company website, in the company’s annual report (10K) that
must be filed with the U.S. Security and Exchange Commission (SEC) and accessible through
the latter’s EDGAR database, or at finance.yahoo.com. To search companies, the SEC’s EDGAR
database can be accessed at: www.sec.gov/edgar/searchedgar/companysearch.html.)
Evaluation: the total possible score for this project is 100 points, which is allocated as follows:
Section I (Excel): 20 points; Section II: 80 points. The total will be reduced to 10%, as indicated
in the syllabus.
Showing Page:
2/4
©2021 Charles R. Sebuharara 2 | P a g e
SECTION I: STOCK SELECTION, DATA COLLECTION AND CALCULATIONS
In the list below, the company name is followed by its stock ticker, industry and market
capitalization, as of 7/20/2020. Refer to the posted PowerPoint file titled “Collecting Data” for
more details on:
how to download the data from finance.yahoo.com,
how to manipulate the raw data, and
how to compute the required statistics.
The Market Index: S&P 500 Index, ^GSPC
The List of Companies/Stocks:
(1) Boston Beer Company, Inc., SAM (Brewer, ~$7.8B)
(2) Facebook, Inc., FB (Internet, ~$699B)
(3) RE/MAX Holdings, Inc., RMAX (Real estate, ~$579M)
(4) Casey’s General Stores, Inc., CASY (Grocery/Gas, ~$6B)
(5) Kohl’s Corporation, KSS (Dept. stores, ~$3.3B)
(6) Walmart, WMT (Retail, ~$374B)
(7) NVIDIA, NVDA (Semiconductor, ~$254B)
(8) Zoom Video Communications, ZM (Telecom services, ~$75B)
(9) Wells Fargo, WFC (Banking, Financial Services, Insurance, ~$107B)
(10) Tesla, TSLA (Automotive, ~$280B)
Required Computations (details in Excel file):
Select two stocks of your choice from the above list. Then, follow the instructions below.
3 Steps (see details in posted PowerPoint slides Equity Analysis-Section 1-Collecting Data”):
(1) Collect price data from finance.yahoo.com
Monthly adjusted closing prices (Adj Close). Adjusted close means that the price is
adjusted for dividends and splits (recall HPR formula). Sample: August 1, 2016 - July 29,
2021 (61 prices).
(2) Convert price data to return data
(3) Calculate (using Excel formulas) the following statistics:
a. Sample mean (average) of the returns
b. Sample variance and standard deviation of the returns
c. Covariance matrix (include the index and your two selected stocks) of the returns
d. Correlation matrix (include the index and your two selected stocks) of the returns
e. Beta (using Excel regression tool, Beta is the estimate of the coefficient obtained by
regressing the returns of your stock on the returns of the market index, S&P500).
f. Form an equal-weighted (EW) portfolio of the two stocks you have selected (see the
Excel template). Then calculate the mean, the variance, the standard deviation, and the
Beta of the portfolio.
(4) Briefly comment on your results. In particular, how do your two stocks statistically compare
with the portfolio? (One paragraph of no more than 200 words would be sufficient.)
Showing Page:
3/4
©2021 Charles R. Sebuharara 3 | P a g e
SECTION II: BASIC STOCK RESEARCH AND ANALYSIS
Of the two stocks selected in Section I, choose ONE for further research and answering the
questions below, which are required at the minimum for the completeness of the final report.
1. Company Identification
(a) The company full name
(b) When did the company first went public (Initial Public Offering, or IPO date)?
(c) What is the industry of the company’s core business?
(d) What is the company’s core business? (e.g., Car manufacturer? Semiconductor?)
(e) Where is the company’s headquarters located?
2. Company Stock
(a) What is the stock ticker (symbol)?
(b) What is the exchange where the stock shares are traded?
(c) What is the closing price, the number of shares outstanding, and the market capitalization
value of the stock, using the following two checkpoints: 1/2/2020 and 7/24/2020?
(d) What is the current Price-Earnings (P/E) ratio?
(e) What is, if any, the last dollar amount of dividend per share (indicate the payment date
and the period the dividend covered)? What is the dividend yield?
(f) Briefly explain why you picked this stock (no right/wrong answers).
(g) As of 7/29/2021 (or the most recent available provide exact source), what is the
consensus analyst recommendation and price target?
(h) What is the typical bid-ask spread on your stock? [Check the spread on a 5-day period
and report the average, as well as the raw data on spread.] Briefly discuss how important
the spread is for someone investing in your stock.
(i) What is the short interest in your stock? Give the date on which you measure this and
express it in total shares and as a percent of the oat.
(j) Suppose you had invested $10,000 in your stock on 1/2/2020:
- Calculate the holding period return if you sold the stock at the closing price on 7/29/
2021. (Consider any dividend that might have been paid during that period.)
- Calculate the period geometric mean return?
- Calculate the total value of your investment, given your initial investment and the
estimated mean return.
(k) What is the stock’s beta (β)? How does the beta you obtained from other source(s)
compare with your own estimated beta (see your Excel file)? What does the value of beta
tell about the riskiness of the stock, compared to the market index?
(l) What is the expected return for your stock, as predicted by CAPM? You can use external
estimates; but be sure to explain where you got your estimates and defend your
assumptions.
(m) Sensitivity to business cycles: Is your stock “cyclical”, “non-cyclical” or “defensive”?
(n) Size: Is your stock Small" or Large" (or medium) stock?
(o) Style: Is your stock Value" or Growth" (or neutral) stock?
[Note: Size (Small, Medium, Large) refers to the market capitalization of equity. Style
(Value versus Growth) may be determined by using Book/market is the ratio of book
Showing Page:
4/4
©2021 Charles R. Sebuharara 4 | P a g e
value of equity to the market value of equity. Use your own minimal research or best
educated guess to answer (m) and (n)].
(p) In addition to COVID-19, research any other event that occurred between January 2,
2020, and July 29, 2021, which have had effects on your company and company stock
price. Briefly discuss the impact of these two events on the stock price and the
implications for market efficiency. Attach a plot of a line graph of the stock closing price
(monthly) for the period of January 2, 2020 and July 29, 2021 (copy and paste the plot at
the end of this document, using the data you downloaded in Excel from finance.yahoo).
(q) Are there traded options on your stock? If yes, select any one contract for the call option
and any one contract for the put option (If there are no options on your stock, use options
on Apple, AAPL). For each of them, report the following:
Contract name
Date
Strike (or Exercise) price
Last price
Bid price
Ask price
Open interest
Implied volatility
Intrinsic value (you may calculate this using the last price and the strike price). Is the
option “At-The-Money”, “In-The-Money” or “Out-Of-The-Money”?
3. What is your overall evaluation of your stock? Would you recommend the purchase, hold or
sale of the stock? Does your recommendation agree with that of the consensus of other stock
analysts? Explain, briefly, your reasons and any disagreement with the other analysts’
“consensus.”
CONGRATULATIONS FOR YOUR PATIENCE, PERSEVERENCE, HARD WORK, AND
LEARNING! IF YOU ENJOY THIS PROJECT, THEN YOU ARE A STEP CLOSER TO
CLAIMING THE SAME REWARDING SKILLS OF EQUITY ANALYSTS WHO WORK
FOR INVESTMENT BANKS, HEDGE FUNDS, PRIVATE EQUITY FIRMS, MONEY
MANAGER, EVEN THE CORPORATIONS THAT ISSUE STOCKS!

Unformatted Attachment Preview

Your Full Name: ________________________________________ Date: _____________ FINANCIAL ECONOMICS Summer II, 2021 EQUITY ANALYSIS PROJECT (100 points) This project involves using economic, financial and statistical tools to analyze real data of two (2) of the stocks listed below and the market index represented by the S&P500. Due date: No later than 11:59pm EST on Sunday, August 8, 2021. Submission: Upload completed work to course site in Brightspace. These will be two separate but complementary reports to be submitted via separate links, as follows: (1) One link for the regular MS Word document (using this document as “template”), which will report your answers to the questions in Section II below. (2) The second link will be for the regular MS Excel (using the posted Excel template as guide), which consists of calculations of the stock returns and related statistics (see Section I below). Format: Type the answers directly into this document next to or below the questions (make space, as necessary). Preserve the numbering and the order of the questions. Modify the file name by adding YOUR NAME. References: Provide full references, citations and/or sources of any idea, data or information that is not your own. Sources of Data and Information: Most of the basic data and information needed to complete this project can be found on the company website, in the company’s annual report (10K) that must be filed with the U.S. Security and Exchange Commission (SEC) and accessible through the latter’s EDGAR database, or at finance.yahoo.com. To search companies, the SEC’s EDGAR database can be accessed at: www.sec.gov/edgar/searchedgar/companysearch.html.) Evaluation: the total possible score for this project is 100 points, which is allocated as follows: Section I (Excel): 20 points; Section II: 80 points. The total will be reduced to 10%, as indicated in the syllabus. ©2021 Charles R. Sebuharara 1|Page SECTION I: STOCK SELECTION, DATA COLLECTION AND CALCULATIONS In the list below, the company name is followed by its stock ticker, industry and market capitalization, as of 7/20/2020. Refer to the posted PowerPoint file titled “Collecting Data” for more details on: • how to download the data from finance.yahoo.com, • how to manipulate the raw data, and • how to compute the required statistics. The Market Index: S&P 500 Index, ^GSPC The List of Companies/Stocks: (1) Boston Beer Company, Inc., SAM (Brewer, ~$7.8B) (2) Facebook, Inc., FB (Internet, ~$699B) (3) RE/MAX Holdings, Inc., RMAX (Real estate, ~$579M) (4) Casey’s General Stores, Inc., CASY (Grocery/Gas, ~$6B) (5) Kohl’s Corporation, KSS (Dept. stores, ~$3.3B) (6) Walmart, WMT (Retail, ~$374B) (7) NVIDIA, NVDA (Semiconductor, ~$254B) (8) Zoom Video Communications, ZM (Telecom services, ~$75B) (9) Wells Fargo, WFC (Banking, Financial Services, Insurance, ~$107B) (10) Tesla, TSLA (Automotive, ~$280B) Required Computations (details in Excel file): Select two stocks of your choice from the above list. Then, follow the instructions below. 3 Steps (see details in posted PowerPoint slides “Equity Analysis-Section 1-Collecting Data”): (1) Collect price data from finance.yahoo.com Monthly adjusted closing prices (Adj Close). Adjusted close means that the price is adjusted for dividends and splits (recall HPR formula). Sample: August 1, 2016 - July 29, 2021 (61 prices). (2) Convert price data to return data (3) Calculate (using Excel formulas) the following statistics: a. Sample mean (average) of the returns b. Sample variance and standard deviation of the returns c. Covariance matrix (include the index and your two selected stocks) of the returns d. Correlation matrix (include the index and your two selected stocks) of the returns e. Beta (using Excel regression tool, Beta is the estimate of the coefficient obtained by regressing the returns of your stock on the returns of the market index, S&P500). f. Form an equal-weighted (EW) portfolio of the two stocks you have selected (see the Excel template). Then calculate the mean, the variance, the standard deviation, and the Beta of the portfolio. (4) Briefly comment on your results. In particular, how do your two stocks statistically compare with the portfolio? (One paragraph of no more than 200 words would be sufficient.) ©2021 Charles R. Sebuharara 2|Page SECTION II: BASIC STOCK RESEARCH AND ANALYSIS Of the two stocks selected in Section I, choose ONE for further research and answering the questions below, which are required at the minimum for the completeness of the final report. 1. Company Identification (a) The company full name (b) When did the company first went public (Initial Public Offering, or IPO date)? (c) What is the industry of the company’s core business? (d) What is the company’s core business? (e.g., Car manufacturer? Semiconductor?) (e) Where is the company’s headquarters located? 2. Company Stock (a) What is the stock ticker (symbol)? (b) What is the exchange where the stock shares are traded? (c) What is the closing price, the number of shares outstanding, and the market capitalization value of the stock, using the following two checkpoints: 1/2/2020 and 7/24/2020? (d) What is the current Price-Earnings (P/E) ratio? (e) What is, if any, the last dollar amount of dividend per share (indicate the payment date and the period the dividend covered)? What is the dividend yield? (f) Briefly explain why you picked this stock (no right/wrong answers). (g) As of 7/29/2021 (or the most recent available – provide exact source), what is the consensus analyst recommendation and price target? (h) What is the typical bid-ask spread on your stock? [Check the spread on a 5-day period and report the average, as well as the raw data on spread.] Briefly discuss how important the spread is for someone investing in your stock. (i) What is the short interest in your stock? Give the date on which you measure this and express it in total shares and as a percent of the oat. (j) Suppose you had invested $10,000 in your stock on 1/2/2020: - Calculate the holding period return if you sold the stock at the closing price on 7/29/ 2021. (Consider any dividend that might have been paid during that period.) - Calculate the period geometric mean return? - Calculate the total value of your investment, given your initial investment and the estimated mean return. (k) What is the stock’s beta (β)? How does the beta you obtained from other source(s) compare with your own estimated beta (see your Excel file)? What does the value of beta tell about the riskiness of the stock, compared to the market index? (l) What is the expected return for your stock, as predicted by CAPM? You can use external estimates; but be sure to explain where you got your estimates and defend your assumptions. (m) Sensitivity to business cycles: Is your stock “cyclical”, “non-cyclical” or “defensive”? (n) Size: Is your stock “Small" or “Large" (or medium) stock? (o) Style: Is your stock “Value" or “Growth" (or neutral) stock? [Note: Size (Small, Medium, Large) refers to the market capitalization of equity. Style (Value versus Growth) may be determined by using “Book/market” is the ratio of book ©2021 Charles R. Sebuharara 3|Page value of equity to the market value of equity. Use your own minimal research or best educated guess to answer (m) and (n)]. (p) In addition to COVID-19, research any other event that occurred between January 2, 2020, and July 29, 2021, which have had effects on your company and company stock price. Briefly discuss the impact of these two events on the stock price and the implications for market efficiency. Attach a plot of a line graph of the stock closing price (monthly) for the period of January 2, 2020 and July 29, 2021 (copy and paste the plot at the end of this document, using the data you downloaded in Excel from finance.yahoo). (q) Are there traded options on your stock? If yes, select any one contract for the call option and any one contract for the put option (If there are no options on your stock, use options on Apple, AAPL). For each of them, report the following: • Contract name • Date • Strike (or Exercise) price • Last price • Bid price • Ask price • Open interest • Implied volatility • Intrinsic value (you may calculate this using the last price and the strike price). Is the option “At-The-Money”, “In-The-Money” or “Out-Of-The-Money”? 3. What is your overall evaluation of your stock? Would you recommend the purchase, hold or sale of the stock? Does your recommendation agree with that of the consensus of other stock analysts? Explain, briefly, your reasons and any disagreement with the other analysts’ “consensus.” CONGRATULATIONS FOR YOUR PATIENCE, PERSEVERENCE, HARD WORK, AND LEARNING! IF YOU ENJOY THIS PROJECT, THEN YOU ARE A STEP CLOSER TO CLAIMING THE SAME REWARDING SKILLS OF EQUITY ANALYSTS WHO WORK FOR INVESTMENT BANKS, HEDGE FUNDS, PRIVATE EQUITY FIRMS, MONEY MANAGER, EVEN THE CORPORATIONS THAT ISSUE STOCKS! ©2021 Charles R. Sebuharara 4|Page Name: Description: ...
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.
Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4