STEP #1: Template for Income Statement, Balance Sheet & Ratios.
YOU ARE EXPECTED TO BUILD THIS SPREADSHEET FROM SCRATCH. IF IT COMES TO MY ATTENTION THAT YOU HAVE USED A SOFTWARE PROGRAM (OR ANY OTHER MEANS) TO CONVERT MY PDF DOCUMENT INTO AN EXCEL DOCUMENT, YOU WILL RECEIVE A ZERO FOR THE ENTIRE ASSIGNMENT. IT IS JUST NOT WORTH IT.
The first significant milestone for this project is to build an excel worksheet EXACTLY like this. Step guidance as follows:
1. Choose your S$P 500 company.
2. Then select 2 key competitors of that company.
3. My recommendation is to use Yahoo Finance. Has tons of information on each company. Just click on COMPETITORS link to find out key competitors.
4. Download Income Statement Data and Balance Sheet Data into a new Excel w/s.
5. Start building the framework.
6. Complete Income Statement section first.
7. Complete Balance Sheet section.
8. Use Quad Graphics Data FIRST. Replicate my EXACT w/s.
9. Once you have replicated all my equations, now drop in your company data.
10. Once you have completed one company, just copy w/s into 2 new tabs.
11. Lather, rinse, repeat :-)
12. Excluding your IS and BS data input, your worksheet should be 100% equations. I will be checking for that with your final submission.
STEP #2: Complete Company Analysis (Part 2). [PDF DOCS ONLY].
Step Guidance as follows:
1. First, review the sections that are required for Part 2.
2. There are 4 sections: Stock Analysis, WACC Analysis, Beta Analysis, and Cash Flow Analysis.
3. A GOOD RULE OF THUMB is to FIRST build your financial equations by replicating my data in the worksheet.
4. KEEP IN MIND: You will need to complete this worksheet for all 2 companies. Data from this worksheet will need to be transferred to Balance Scorecard.
Stock Analysis
1. Retrieve monthly share prices by going to Yahoo HISTORICAL PRICES.
2. Calculate the Standard Deviation using excel formula.
3. Remember, the bigger the standard deviation, the riskier the stock (asset).
WACC Analysis
1. Go to your Income Statement and Balance Sheet for the data you need in this section.
2. If your company does NOT have interest expense (or long term debt) data, calculate the arithmetic average of your 2 competitor companies, and use that.
3. You may have to do some work to calculate the DIV GROWTH rate. If you cannot find this stat through research, like stock analyst opinions and recommendations, you will have to calculate it using historical dividend data. Do the following: Go to Historical Prices on YF banner. Change the setting to MONTHLY prices. Make sure you back at least 4 years. In addition to share prices, YF provides the dividend paid. Group the last 4 dividends to make your current year. Do that again for the next 4 dividends. Then do it one more time. Now calculate the dividend growth rate for each year. Use these rates for your dividend growth rate assumption.
4. PLEASE PROVIDE YOUR DIV DATA in the right section of the worksheet with AVG calculations for me to review.
BETA Analysis
1. Go to YF Historical Prices section.
2. Set FREQUENCY to MONTHLY.
3. Type in your first company. Copy and paste share prices for 12 months into your excel worksheet.
4. Do this again for your other 2 companies.
5. Now use the same screen to retrieve S&P historical prices. The S&P 500 is like a security. It is an index. Just type in S&P 500 and you will get the index prices.
6. Now replicate the monthly % change logic that I have in my worksheet.
7. The BETA calculation is the % change of the stock (num) divided by the % change of the S&P 500 (denom).
8. The final step is to calculate the 12 month arithmetic average.
9. Transfer that data point to your Balance Scorecard.
CASH FLOW Analysis
1. This data will come from the Cash Flow Statement from YF. Last report in the banner.
2. Keep numbers in the THOUSANDS.
3. Replicate my financial logic that I have in my worksheet.
4. Make sure your totals (per section) agree to the totals on YF. You do this by plugging the OTHER line item (in each section).
5. You will need to include SHARES OUTSTANDING for each year.
6. If you cannot easily find the # of shares outstanding, do a little reverse engineering. Since EPS = REV / Shares Outstanding, then just take REV / EPS to back into the # of shares outstanding for each year.
7. Remember, the WTD average #s that you transfer to the Balance Scorecard should be weighted by RECENCY. ((cur.yr x 3)+(mid.yr x2)+(base.yr x
STEP 3: Transfer Your Metrics to the Balanced Scorecard.
Attached Files:
Step Guidance as follows:
1. Download the Balanced Scorecard template.
2. Manually transfer your metrics from the Project Templates to the BS scorecard.
3. Follow all directions on the template.
4. After your metrics have been transferred, now RATE each scorecard COMPONENT based on its value to the business.
5. When you have completed, theoretically, the company with the HIGHEST score is financially the strongest.
6. You will use this BS during your presentation in Week 6.
7. Your presentation should be focused on WHY a particular company scored the way it did.
8. You must be prepared to field questions from the teacher and class.
STEP #4: Equity Research Report
Report requirements follow:
1. GOAL. The final step of the class project is to produce an EQUITY RESEARCH REPORT / COMPARISON. Equity reports are generally produced for individual companies. However, you will be preparing a COMPARISON of your two companies. Pretend you are a Financial Advisor. Your client informed you that he wants to invest $1MM of his retirement in ONE of the two companies you are researching. Your goal is to prepare a robust, well researched report that concludes with a recommendation of one company.
2. FORMAT. APA format. 5 pages of content (minimum) 10 pages maximum. Page 6 and 7 are the cover page and sources page. Abstract page is not required.
3. GRAMMAR. Grammar, spelling, punctuation, highlights, and business clarity/brevity all count.
4. SECTIONS OF PAPER. You should have the following sections in your paper: Basic Information, Analyst Opinion and Summary, Key Highlights of Both Companies, Snapshot of the Industry, Financial Ratio Analysis, Valuation Analysis, and Investment Risk Factors.
5. HOW TO REFERENCE. Do not cut and paste your Benchmark, Page 2 or your Balanced Scorecard into this report. You can reference and include "key data".
6. INCLUDE PDF DOCS. Please include PDF documents of your Benchmarks, Page 2 and Balanced Scorecard at the end of the report so that I can easily reference. Each PDF document should be ONE page and readable. That means you will have to get good at managing the size of columns and rows for each page. When in doubt, just go to my PDF examples. When you print each report, it prints nicely on ONE page. While it is small, it is still professional and readable. Remember, the inclusion of these PDF documents do not count towards your report page count.
7. REFERENCES. Please use the website references below to learn how to write great equity research reports.
8. YOUR CLASS PRESENTATION. After you have done ALL this work, you should be able to stand up in front of the class and explain your Investment Recommendation. You are expected to give a 3 minute (timed) presentation. You can have notes in front of you -- but no projector slides. My goal with this requirement is to teach you to credibly discuss and present key financial aspects of companies. And be prepared for questions from the class and myself.
WEBSITES ON HOW TO WRITE AN EQUITY RESEARCH REPORT
https://www.financewalk.com/equity-research-report-writing-format/
https://www.educba.com/write-equity-research-report/
https://www.cfainstitute.org/community/challenge/Documents/rc_equity_research_report_essentials.pdf
Attachment:- Project.zip