Fianane

Summer 2021

FIN 430 Research Project Segment 1: Stock Selection and Data Analysis

Objectives:

This segment focuses on the stock selection and data analysis. You will start with choosing stocks based on the financial statement analysis and your economic outlook. You will then download stock price data for the companies that you choose, calculate returns and then calculate several summary statistics. The findings from this segment will be used in the next segment; therefore, it is very important that the results for this segment are correct.

To-do list

1. Each student chooses 5 companies that meet the following requirements:  Publicly traded on U.S. domestic exchanges (NYSE, NASDAQ, AMEX).  Each company has at least 5 years (60 months) of documented pricing records.  Each company is capitalized at least $1 million dollars for the past 5 years.  Cover at least three different industries.

2. Collect annual financial statement data and monthly stock price and return data of the past 5 years (60 months) for the selected companies.  From 01/2016 to 12/2020.  Access annual financial statement data via WRDS/Computstat.  Access monthly index and stock price/return data via WRDS/CRSP.  Access 3-month T-bill rate via FRED.  See Appendix 1 for step-by-step instruction below.

3. Calculate and tabulate the following financial ratios and statistics for 12/2019.  Financial ratios  P/E ratio, book-to-market (BM) ratio, market capitalization, net income.  Return statistics  Historical average return, average excess return, standard deviation,

skewness, kurtosis, CAPM beta, CAPM alpha.  Identify industry membership using 2-digit SIC.  See Appendix 2 for detailed definition of each ratio and statistics below.

4. Each student submits one report.  Brief introduction of each selected company.  Discussion of the financial ratios and return statistics.  Single-space, 12 font size, maximum 10 pages including cover page, tables and

figures, reference, and everything.  See Appendix 3 for recommended outline.

Summer 2021

Appendix 1 Step-by-step instruction for data collection. 1. WRDS/Compustat

a. Login https://wrds-www.wharton.upenn.edu/ with username: fin430summer2021, password: fin430project.

b. Click “Get Data”  click “Compustat - Capital IQ”  click “North America - Daily”  click “Fundamentals Annual”.

c. Under “Step 1: Choose your date range.”, select “data date” and set it from 2016-01 to 2020-12.

d. Under “Step 2: Apply your company codes.”, select “TIC”  Type in the company ticker (e.g., IBM) in the first search bar right under “Select an option for entering company codes”.

e. Under “Screening variables”, unselect “Output” for all. i. Check “C” for Consolidation Level. ii. Check “INDL” for industry format. iii. Check “STD” for Data Format. iv. Check “D” for Population Source. v. Check “USD” for Currency.

vi. Check both “Active” and “Inactive”. f. Under “Step 3: Choose variable types.”, check “Data Items” for Select Variable

Types Under “How does this work”, select the follow items (you can find the items by typing in the search bar):

i. AT – Assets - Total ii. NI – Net Income (Loss) iii. CSHO – Common Shares Outstanding iv. PRCC_C – Price Close – Annual – Calendar v. SEQ – Stockholders’ Equity – Total

vi. SICH – Standard Industry Classification – Historical g. Under “Step 4: Select query output.” Select “Excel spreadsheet (*.xlsx)”, keep

“Compression Type” as None, keep “Date Format” as YYMMDDn8. h. Click “Submit Query” and wait till the download link is ready.

2. WRDS/CRSP

a. Login https://wrds-www.wharton.upenn.edu/ with username: fin430summer2021, password: fin430project.

b. Click “Get Data”  click “CRSP”  click “Stock/Security Files”  click “Monthly Stock File”.

c. Under “Step 1: Choose your date range.”, set the date range as from 2016-01 to 2020-12.

d. Under “Step 2: Apply your company codes.”, select “TICKER” and type in the correct company ticker (e.g., IBM) in the first search bar right under “Select an option for entering company codes”.

e. Under “Step 3: Query Variables.”, select the following items: i. Ticker ii. Exchange code

Summer 2021

iii. Price iv. Holding Period Return

f. Under “Step 4: Select query output.”, select “Excel spreadsheet (*.xlsx)”, keep “Compression Type” as None, and keep Date Format as “YYMMDDn8.”.

g. Click “Submit Query” and wait till the download link is ready.

Note: to get index returns, similar to step b, Click “Get Data”  click “CRSP”  click “Stock/Security Files”  click “Stock Market Indexes”  set the Date rang as from 2016-01 to 2020-12.  set data frequency as “Monthly”  select “Return on S&P Composite Index”  click “Submit Query”.

3. FRED

a. Go to https://fred.stlouisfed.org/series/DTB3 b. Set date range to 2016-01-01 - 2020-12-31. c. Click “EDIT GRAPH”  Set “Units:” as Percent, set “Modify frequency:” to

monthly, and “Aggregation method:” to End of Period  click the cross (x). d. Click “Download”  select “Excel (data)”.

Note: the 3-month T-bill rate needs to be adjusted by dividing the number by 1,200 to get the monthly rate.

Appendix 2 Variable Definition

1. Financial ratios (mark ‘n/a’ if any of the variables has missing value) a. P/E ratio = PRCC_C

NI/CSHO

b. BM ratio = 𝑆𝑆𝑆𝑆𝑆𝑆 PRCC_C ×CSHO

c. Market capitalization = PRCC_C × CSHO d. Net income = 𝑁𝑁𝑁𝑁

𝐴𝐴𝐴𝐴

2. Return statistics (Excel functions)

a. Historical average return: =average(monthly stock return) b. Historical excess return: =average(monthly stock return – T-bill rate) c. Standard deviation = stdev.s(monthly stock return) d. Kurtosis = kurt(monthly stock return) e. Skewness = skew(monthly stock return) f. CAPM beta = slope(monthly stock return – T-bill rate, S&P500 return) g. CAPM alpha = intercept(monthly stock return – T-bill rate, S&P 500 return)

Appendix 3 Recommended report outline and rubric

Cover page

Part 1. Company Descriptions

Summer 2021

• For each company, provide a brief introduction about its primary business and the industry it operates in. You can group companies by their industry.

Part 2. Statistical Analysis

• Tabulate the financial ratios and return statistics. • Discuss the financial ratios and return statistics (e.g., comparing companies that are in the

same industry, similar age, proximate location, etc. or predicting future performance based on the financial ratios and return statistics.)

Part 3. Summary

Briefly summarize the report, its major findings and provide a brief discussion of the next step in your analysis.

Rubric: for each part, I will grade based on the following part criteria: completeness 5pt, informativeness 5pt, comprehensiveness 5pt, language 5pt. The highest grade will be 20 pts.

Note: Please make sure that your report is well written. Consider yourself as an investment advisor educating clients about these stocks. Grammatical errors, spelling error, missing or unreferenced tables and citations, and other violations (far too numerous to list here) will be reasons for returning the ungraded report for further work.

Example of Tabulating Your Results

Financial Ratios Return Statistics Ticker SIC Market Cap P/E BM NI … Mean Stdev …

Panel A: Five-year Average IBM GOOG … … Panel B: Last Year IBM GOOG …