Air Products (APD)
Make sure to use the company: Air Products (APD) Section 1 Describe the firm and its businesses. Make sure to discuss how the firm generates its revenues and provide details/breakdown of the main products/services that comprise the firm’s revenues as well as the geographical source of these revenues. These annual SEC filings (Report 10-k) will contain details information about the firm’s businesses. Also download the most recent annual report from the firm’s Investor Relations website/webpage. See a sample report (with instructions) for section 1of The Coca-Cola Company. Section 2 Describe the recent stock performance and compare it to the overall stock market and the firm’s main competitor(s). There should be a paragraph or two discussing the performance and some accompanying graphs. Week 2 module contains a video on downloading data from yahoo finance that could be used to calculate returns and generate performance graphs. You may also INITIALLY have yahoo finance directly generate the graphs. You should only choose this option if you can’t figure out how to create the graphs in Excel the first week. But you may need to eventually create the graphs in Excel, so it is best to do it as soon as possible. Sample graph for Coca-Cola (KO) performance versus S&P 500 and Pepsi (generated by yahoo finance) Performance Graph Image from Yahoo Finance.png Sample graph for Coca-Cola (KO) performance versus S&P 500 and Pepsi (generated by Excel using daily stock prices downloaded from yahoo finance) Performance Graph.xlsx Daily and Monthly Returns for S&P 500 for 2000-2023 period: sp500 – daily-monthly 20002023.xlsx Sample Stock Report for Coca-Cola. Use your imagination to create a well-organized and aesthetically pleasing report. Sample Stock Report – KO (Section 2).docx Sample Stock Report for Netflix. Use your imagination to create a well-organized and aesthetically pleasing report. Sample Stock Report – NFLX.docx Section 3 Discuss the performance of your firm and compare both its performance and volatility with those of the S&P 500. Use a shorter period YE2018-YE2023 that is dominated by the pandemic, and another longer period from YE2013 to YE2023. That way, you may compare volatility over the recent five-year period versus a longer period where the impact from the pandemic is more diluted. Display the return and volatility data as described in the Coca-Cola and Netflix examples. Discuss the correlations of returns over the same periods. Note any differences between the two periods and try to explain the reasons for the difference. Compare and comment on the difference in returns and volatility/standard deviation. See the examples for KO and NFLX. DISPLAY THE CORRELATIONS IN A TABLE FORMAT LIKE THE ONE SHOWED IN THE COCA-COLA AND NETFLIX EXAMPLES. Feel free to copy the MS Word from the Netflix sample report and update the data with your assigned firm. See the Excel file below with downloaded monthly data for S&P 500, KO, and PEP. The file below is just an example. You need to use data that goes up to Dec. 31, 2023. The data for S&P500 is already provided to you on the page for Stock Report – Section 2. The data for your own firm should already have been downloaded by you. You also need to download the data for peer firm based on your assigned firm (Pepsi in the case of KO and Disney in the case of NFLX). Correlation Data – 2003-2018.xlsx nflx returns.xlsx Watch the video from Week 2 & 3 on how to download data from yahoo and easily calculate monthly returns. video A sample version of the report including Section 3 is included below. Sample Stock Report – KO (Section 3).docx Sample Stock Report – NFLX (Section 3).docx Section 4 Describe the industry to which the firm belongs and the positioning of the firm within the industry. Discuss the historical performance of the industry under various economic environments. You should include a graph comparing the performance of the Sector versus the S&P 500. You could graph the most recent year and, in another graph, the most recent five years. That way, you can see how the sector performed versus the S&P 500 over different periods. Keep in mind that the sector is a broader classification of the industry. Use the Sector ETFs for the S&P 500 to download the performance of the sector pertinent to your firm. A list of the S&P 500 Sector ETFs is included below. There is also an Excel file with the Sectors from the S&P 500 and what percentage of the index each sector has represented over the years. Finally, a more detailed and recent list including sectors and industries weights (using GICS classification) for the S&P 500 is also included. S&P 500 Sector ETFs.xlsx 500-sector-representation.xlsx gics-500-scorecard.pdf Discuss the current positioning of the industry and its attractiveness, or lack thereof, for potential investors. Use the paradigm of Porter’s Five Forces to discuss how you believe your sector is positioned and how your company is positioned within your sector and the overall economy. For example, discuss the threat of new entrants, bargaining power of customers, etc. You may also do a SWOT analysis of the industry or the firm you are analyzing. Sample versions of the report including Section 4 for Coca-Cola and Netflix are included below. Sample Stock Report – KO (Section 4).docx Sample Stock Report – NFLX (Section 4).docx Section 5 • • Tabulate the firm’s recent Balance Sheet, Income Statement, and Cash Flow Statement. When tabulating the financial statements in your report, be sure to format it such that it is legible and well organized. Each statement should fit in one page by itself. Do not simply cut and paste from some sources with tons of details such that each statement is lengthy and take over more than one page. It should be one page for Income Statement, one page for Balance Sheet and one page for Cash Flow Statement. One easy source is the SEC website. If you click on 10-K “filing” (the tiny button or clickable word filing next to the 10-K listing) you can have access to “Interactive Data” which allows you to download the financial statements in Excel. From there, it is easy to cut and paste into MS Word. Tabulate the firm’s recent main financial ratios. You can use morningstar’s already calculated ratios. Look up your company, then click on ‘Quote’ and then look for the ‘Key ratio’ button. Then click on ‘full key ratios data’ to see historical ratios for your firm. Just pick some main ratios to include in your report: some of the Liquidity ratios, asset utilization ratios, leverage ratios, and profitability ratios. See sample reports posted below. • Compare the firm’s ratios with the recent past, ratio for the firm’s main peer, and if available some ratios for the industry/sector. • Discuss what is driving the trend in the ratios. Sample reports with Section 5 Sample Stock Report – KO (Section 5).docx Sample Stock Report – NFLX (Section 5).docx Section 6.1 Discuss using CAPM to calculate the expected return (cost of equity) for the stock in your report. Calculate two separate Betas: one using recent five-year monthly returns from YE2018 to YE2023 and one using recent ten-year monthly returns from YE2013-YE2023 (if your company does not have historical data going that far, use as much history as available) or the past three years if your company does not have enough historical data. After calculating these two Betas, if your beta is below 1, calculate adjusted Betas by giving a weight of 2/3 for your calculated Betas and 1/3 for the market Beta of 1. These two adjusted Betas will be used to calculate a lower and an upper bound cost of equity. If your beta is above 1 you can skip this step. The goal here is to use an estimate for Beta as conservative as possible. The file below contains more than ten years of monthly data for both the S&P 500 and the 1month risk-free rate that was downloaded from the St. Louis Fed website. In order to run the CAPM regression, you just need to add the monthly return information for your stock. Watch the video below for a demonstration on how to run the regression. CAPM Data sp500 2010-2023.xlsx Calculate cost of equity using CAPM and two separate equity risk premiums: 6% (S&P 500 minus T-bonds) and 8% (S&P 500 minus T-bills). Then use these values and the most recent 3mo T-bill rate and 10-year T-bond as the risk-free rate in the calculation. See a sample Section 6.1 for KO. Feel free to copy verbatim the references for CAPM academic studies, but don’t copy verbatim the paragraph that discusses them. You need to mention them in your own words. You may also copy the equations from the sample report. Also use the equity risk premiums listed above. DO NOT COPY any more of the text in the sample section. Describe your analysis in your own words. There is also a sample Section 6.1 for NFLX and the accompanying Excel file with the data used to perform the regressions. These files are included for your reference and to demonstrate how Beta is calculated with real historical data. Sample Stock Report – KO (Section 6.1).docx CAPM Data sp500 2010-2023 NFLX.xlsx Sample Stock Report – NFLX (Section 6.1).docx Video demonstrating CAPM regression in Excel: video Section 6 (full) Complete Section 6 by continuing the work on the CAPM based cost of equity calculation submitted for Section 6.1. For valuation based on comparables, you should use the main peer firm discussed in the Section 2 of the report, the Sector ETF used in Section 4 of the report and the overall market (S&P 500). The main valuation metrics for these can be obtained in finance.yahoo.com as well as morningstar.com. If using morningstar, after entering the Sector ETF (XLP in the example for the Coca-Cola report), click on ‘Portfolio’. This will show you the stocks in the ETF as well as the various valuation metrics such as P/E, P/Book etc… In order to calculate a value for your firm using the DDM, use the announced next year’s dividend (from finance.yahoo.com), the CAPM rate calculated in Section 6.1 and for the expected dividend growth you can use the average growth over the past five years for one version of the calculation and the formula g=ROE x b for another version of the calculation. When using the formula use the average ROE for past five years and the average ‘b’ (retention ratio) for the past five years. See sample report for Coca-Cola for more information on how to perform these calculations. In order to perform the discounted free cash flow valuation of the firm it is necessary to forecast future free cash flows for a few years. The common practice is to forecast a few years (5 to 10) and then assume a constant growth thereafter. For this section of the report it is okay to make simple assumptions regarding future free cash flows. See the attached Excel file with Financial Statements for Coca-Cola and the free cash flow estimates for the firm. The Income Statement, Balance Sheet, and Cash Flow statements were simply downloaded from morningstar.com and copied into separate worksheets in the Excel file below. For additional ideas on how to estimate Revenue, see the file for NFLX below. Note that the revenue forecast is fairly detailed even though just a few assumptions were necessary (see Excel cells highlighted in blue). These revenues feed the Cash Flow worksheet which also depends on just a few simplified assumptions (also highlighted in blue). The results populate the DCF or Valuation worksheet. KO – Fin Stmts & DCF.xlsx NFLX Revenue Forecast & DCF – Example.xlsx DDM Valuation & Sensitivity Analysis.xlsx Sample Stock Report – KO (Section 6 – full).docx Sample Stock Report – NFLX (Section 6 – Full).docx
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
