Segment 1: Project Information
➢ Individual Project
➢ Weightage – 30%
➢ Submission format – A written report in Word + Excel
➢ Complete the Excel sheets using relevant formulas to show your calculations.
➢ Formatting guidelines for the written document – APA format, 12-point Times New Roman font, use double-spacing
➢ Length of the written document – 8–12 pages
➢ Total points – 150
Topics Covered
Financial statements and ratio analysis, cost of debt, cost of common equity (CAPM model), capital structure, and WACC calculation
Segment 2: Project Introduction
The project on company analysis is designed to give you an opportunity to practically apply the concepts covered during the course in a “real-world” scenario as well as to familiarize you with the business research resources available to you. After you have completed the project, you should be comfortable researching any publicly listed company in which you may want to invest, where you may want to seek employment, or with which you may want to do business.
● Company Selection
Choose a company that has been publicly traded for at least 3 years, pays dividends, and has debt. (Do not select a bank or an insurance company.)
You may also refer to blogs such as the one mentioned here to search for companies that meet the criteria. Please note that the selected company will dictate the entire project. Thus, please ensure you select the company carefully.
Report
Your written report should be 8–12 pages long (typed and double-spaced; tables and graphs can be submitted as appendices). Given below is a breakdown of the analysis report that you need to create as part of the graded project. Each individual question has pointers to guide you in your analysis.
The report should have the following structure:
● Title Page
❖ Your name
❖ The chosen company’s name
❖ The ticker symbol (The abbreviated name of the company displayed along with stocks. For example, MSFT is the ticker symbol for Microsoft.)
❖ The stock exchange where your chosen company trades
❖ Recent stock price. You may use the stock price on the day you started working on this report (Ensure to provide a date)
● Company and Industry Description
❖ Click on this link to find the annual report for the company of your choice: Link 1
❖ Provide an introduction, a description of the company (explain the company’s history and outlook in the description), and a description of the industry in which it operates. You can find all this information in the company’s annual report.
❖ Provide a summary of any recent major news events related to the company. List at least two major events from the recent past. Please remember to cite all your news article sources.
● Financial Statements and Ratio Analysis
a. Use “Yahoo Finance” to obtain the latest financial statements of the company you initially selected. Please follow these steps to obtain the financials:
i. Go to https://finance.yahoo.com/.
ii. Select “Industries” after hovering the cursor over the three dots to the extreme right.
iii. On the Industry tab, search for your company using its ticker symbol in the search bar.
iv. Select the “Financials” tab to view the financial statements of your chosen company.
(Note: These financial statements will be used for your ratio calculations and company analyses.)
❖ Study the company’s balance sheet, the income statement, and the statement of cash flows for the latest financial year and list only two key highlights from these statements, which you think are noteworthy observations about these statements. Justify your observations.
❖ Using the Financial statements calculate the key financial ratios for your chosen company using the data from Yahoo Finance. (Use the Excel template on Financial Ratio to collate the necessary data from Yahoo Finance and calculate the ratios). (Note: In case Yahoo Finance does not have the required ratios for your chosen company, please use a different online source and ensure you mention it. Ensure to use credible sources.)
❖ Select any 2 financial ratios and identify their trends. What can you infer from the trends? Explain in brief. (Note: The most important part of this section is your interpretation and analysis of the ratios rather than the ratios themselves.)
❖ Perform a trend analysis for the same two ratios for a comparable company from the same industry. Comment on which company is performing better with respect to these ratios. (Note: For a trend analysis of the comparable source, search these ratios for at least three recent years, using Yahoo Finance, to determine the upward, downward, or random trends.)
Note: This company, which you select for comparison, will remain the same for the rest of the comparisons to follow.
● Cost of Debt
❖ Based on the ratios you obtained for the company chosen initially, use the debt management ratios and the liquidity ratios for the current year to analyze the company’s creditworthiness. Also, comment on the bond rating and the resulting cost of debt capital for the company in your analysis.
❖ Cost of debt: Use Yield to Maturity on existing bonds as the cost of debt.
○ Steps to find cost of debt:
i. Visit https://finra-markets.morningstar.com/BondCenter/Default.jsp?part=3
ii. In the ‘Quick search’ section type your company’s ticker symbol in the ‘Symbol’ search box and press enter.
iii. Scroll down to ‘Accept’ the agreement
iv. You will find a list of bonds issued by your firm. Select any bond that has 10-15 years of maturity period (in the ‘Maturity’ column, the year should be a date which is beyond 2033.)
v. The value in the ‘Yield’ column in the end will be your bonds yield to maturity/ cost to debt value.
❖ Click on this link to find a list of the traded bonds and the bond-rating for the company:
https://finra-markets.morningstar.com/BondCenter/Default.jsp?part=3
● Cost of Common Equity
❖ CAPM Model
○ This section should include the chosen company’s Beta and the resulting cost of common equity using the CAPM model.
○ Calculate the Beta and the expected return for the stock of your chosen company using the Excel file – “Excel template to calculate Beta”
i. Here are the steps to get the data:
ii. Go to https://finance.yahoo.com/.
iii. Search for your company by typing its name in the search bar.
iv. Select “Historical data” on the left-side menu.
v. In Historical data, set “Time period” to “5Y.”
vi. Set the frequency to “Monthly.”
vii. Click on Apply.
viii. Click down on the top left side of the table.
ix. Use the “Close*” value to populate your Excel sheet.
x. Repeat the same process with the ticker symbol ( SPY ) inYahoo Finance for SPDR S&P 500 ETF Trust – (SPY)
○ Use the Excel workbook to calculate the Beta and Required rate of return.
○ Watch the Beta calculation video to recap how Beta is calculated.
○ Compare the Beta of your chosen company to the Beta of the comparable firm selected earlier. Comment on the Beta’s. (You can use the Beta for the comparable company directly from Yahoo finance and need not calculate it.)
○ Based on the Beta of the two stocks, which stock would you select, considering you are risk averse? Explain your choice.
❖ Dividends
○ How much common stock dividend does your chosen company pay and how often do they pay it? You can get this information from Yahoo Finance (Under Historical data)
○ Does the comparable company pay dividends? Which company has a better dividend payout and why?
○ Has the chosen company significantly changed its dividend payments over the past five years? If so, what are the factors that could explain the shift? You may consult the company’s annual report to see if there is any discussion on and/or explanation for these changes.
● Capital Structure
❖ Provide a pie chart (only for the most recent year) showing the breakdown of the company’s capital into different components, e.g., long-term debt, preferred stock, and common equity capital. Include each component’s weight and value in the pie chart. You may use the data from the financial statements Excel sheet, which you compiled for your firm’s financial ratios. (Hint: Add the data to an Excel workbook sheet labeled ‘Capital Structure’ and use the Excel workbook to create the pie chart.)
❖ Has the chosen company changed its capital structure significantly over the past five years? If so, what are the factors that could explain the shift? List at least two reasons for the shift. (Hint: You may consult the company’s annual report to see if there is any discussion on and/or explanation for these changes.
● WACC Calculation
❖ Use the “WACC calculation Excel worksheet” to calculate the WACC for the past financial year.
○ Cost of debt: Use Yield to Maturity on existing bonds. (Note: You will find this in the chosen company’s annual report.)
○ For the steps, refer to the Cost of Debt section.
○ Cost of equity: Use the CAPM model.
i. CAPM → Required Return = Risk-Free rate + Beta (Return on Market – Risk-Free Rate)
ii. Cost of Equity → Required return on the stock.
○ Find the tax rate for your chosen company from its annual report.
○ Use your capital structure weights and then find the WACC.
● Forecasting
❖ Discuss recent developments (new projects, new developments, new ventures, or mergers and acquisitions done by the chosen company that will impact its value) and the issues facing your chosen company regarding the developments in terms of lawsuits, government sanctions, changes in laws and regulations, now and in the foreseeable future. Talk about the company’s prospects. Your discussion above should address both the positive and negative aspects of the company’s current financial condition and outlook.
● Conclusion
❖ Provide a conclusion with recommendations (buy, sell, hold) based on your analysis. Provide two strong reasons to support your judgment. (Hint: When concluding, consider all the aspects that you calculated above and present an informed decision. Explain your conclusion based on your answers to the above questions.)