Homework answers / question archive / Your company is: The Coca Cola Company (KO) Semester Project Instructions: Stock Valuation Please read very carefully

Your company is: The Coca Cola Company (KO) Semester Project Instructions: Stock Valuation Please read very carefully

Finance

Share With

Your company is: The Coca Cola Company (KO) Semester Project Instructions: Stock Valuation Please read very carefully. The project consists of i) an individual report and ii) a group oral presentation. The company you’re assigned at the top of this page is your company. The entire project is an individual assignment, and you may not collaborate on the report, except for the Canvas discussion. The estimated number of hours to be spent on this project is at least 20-25 hours in total. So please do not wait until the last minute. I will frequently send out reminders so that you can complete each stage in a timely manner. By the end of the course, you have to prepare a professional report of analysis on this company. You will be analyzing and dissecting this company’s business, finance, operations, and risk in order to come up with a valuation. IMPORTANT: Please save your work as frequently as possible. A sudden crash or shutdown will not be a valid excuse for extension. Requirements: i) Professionalism: To become a “professional” rather than an “amateur,” we can start by emulating the looks of professionals. Make your report look good, look like something done by a professional. In the business world, the looks are just as important as the content. People won’t bother to look at unattractive, messy reports, no matter how excellent the content might be. ii) All reports must be completed in Microsoft Word: 8-10 pages long, single-spaced, Times New Roman, and 12 pts. As for other things, go out of your way and be creative. But don’t overdo it. Sometimes, the simplicity is the virtue. Tables, charts, graphs are all welcome if they serve appropriate purposes and needs. The appropriate use of italics, bold, and underlines is also permitted. You may also use color. iii) You may have a cover page but it won’t count towards the 8-10 page length requirement of the report. iv) Math equations and formulas can be typed with the math applications embedded in Microsoft Word. Different versions of Word support different equation tools. For help, please consult the Google search. v) You’re required to show all your calculations to prove that you did your own work. However, if they make the report look messy, please make the appendix section at the very end of the report where you can show me all your calculation (tables can be imported from Excel and be modified to look nice). Use your judgement to decide whether showing the calculation is critical as part of the report or is more appropriate in the appendix. The appendix does not count towards the designated length of the report. For the appendix section, you may hand-write the formulas (you will have to scan) if you’re not familiar with typing math equations on Word. vi) You can utilize all the resources and tools available outside of class, including the web documents, Excel, Powerpoint, Words, etc. vii) Please submit your report in pdf file (please do not attach the Word document or Excel spreadsheets as your final submission. You may submit spreadsheets and Word for Project Progress only.). Part I (15 Points). Company Description (<=1 page) We have to command a thorough understanding of the firm’s nature of business in order to come up with the right valuation. First, please download the annual 10-K filings for the company. The document can get as long as 100 pages. It is a comprehensive financial report that includes explanations, details, and commentaries on the company’s performance for the year and is audited by an independent auditor. While there is no need to read every single page (because other financial sources already have a nice summary of many aspects on the 10K), there are some important parts worth a thorough read, or at least a skim, to successfully understand the company. Find the Table of Contents in the 10-K, and quickly get a big picture of how the 10-K is structured. I recommend that you read the overview of the firm businesses, which is provided by the company’s management itself. You’re welcome to supplement your understanding of the firm with other resources out there besides the 10-K. Whatever resources you decide to use, be sure to cite your sources and include the following areas, at the least, in describing the company: i) A brief history, the management, the location(s) of the headquarter and businesses, the fiscal year, etc. ii) Business, strategy, products, services, etc. iii) Competition, risk factors. Part II (50 Points). Financial Ratios (<=2 pages) Financial ratios are helpful for analyzing the company’s financial condition and performance. We will evaluate the company for the year 2021 and 2020. Data is already provided on Canvas. On a blank sheet, you can choose a cell, simply type in the equal sign ‘=’, and select the cells that contain the relevant numbers for computation. Please consult any basic Excel tutorial online for help. i) Calculate the liquidity, leverage, turnover, profitability, and market value ratios for the most recent two years (check the fiscal year and month.). In total, you should calculate 20 (ratios) x 2 (years) = 40 ratios. Please also provide the formulas and the numbers you use to calculate each ratio in the appendix. ***Note*** To calculate EPS, P/E, P/S, and ME/BE, you will need the number of shares outstanding for that year. On the income statements on Morningstar, you will see the number of weighted average shares outstanding in two versions, basic and diluted. Please use the basic shares. ii) Please provide sufficient interpretations of the ratios and explain their change (or no change) from the year before, in 3-5 paragraphs for all the ratios. If there’s anything unusual or notable, please explain. Please be cautious about the signs on financial statement figures, especially on the income and cash flow statement. Some companies choose to report expense items as negative numbers because they get subtracted from the revenue to obtain the net income. When calculating the ratios, use your judgement to adjust their signs appropriately. iii) In the appendix, please show the formulas (with actual numbers used) for all 20 ratios for just one year. iv) After calculating all the ratios, please decompose the company’s profitability over the two years using the Dupont analysis. Explain the composition of the profitability for your company. Has each component of Dupont increased or decreased from the previous year? How has that affected the company’s overall profitability? Is there room for improvements? *Some financial ratios are already available on lots of sources including the actual 10K filings. For the purpose of learning, do not use those ratios. They may be different from what you calculate. Part III (40 Points): Cost of Equity (2-3 pages) In Part III, we are going to calculate the company’s cost of equity in two different ways, first using the dividend growth model and then the CAPM method. Please review this content from the workbook before starting. First, we will start with the dividend growth model. i) Use the year-end price in 2021 (the last price that is available in that year) as the current price (We are going to pretend that we’re at the year-end of 2021. This is because we don’t have 2022 data in full. We’re still in the middle of it.). Find the company’s annual dividends during the 5-year period 01/01/2017-12/31/2021 (already provided on Canvas). Make sure they are sorted in the ascending order of dates. Plot the dividends with the dates on the horizontal axis and the dividends on the vertical axis. Describe the plot and check whether the dividends have been paid in a constant growth pattern. ii) Calculate the dividend growth rate as discussed in class. Use the most recent annual dividend (the sum of all quarterly dividends in year 2021) as the current dividend (?! and multiply it by 1+g to calculate ?". iii) Calculate the cost of equity using the constant growth model and the dividend growth rate calculated above. Alternatively, we can use the CAPM method to compute the cost of equity. iv) Data for the company’s monthly historical prices during the 5-year period 01/01/2017- 12/31/2021 and the S&P500 ETF (ticker: SPY) 1 monthly historical prices during the same period are already provided (originally from yahoo finance). Using the adjusted closing price for both series, calculate the monthly returns for the company and the S&P500. Show a snapshot of your work in this part (5-10 lines, not the entire series) in the appendix. 1 SPY is an exchange-traded fund (ETF) that tracks S&P500 index very closely. It’s a good but imperfect proxy for the market portfolio. v) Calculate the variance of the market returns, covariance between market and the company. Then, calculate the company’s beta. vi) Use the CAPM formula to calculate the cost of equity. Assume that the riskless rate of 2% and the average market return of 8%. We can compare the costs of equity obtained using two different approaches and determine what to do with them. vii) In many cases, we use the CAPM cost of equity. However, we may also take the average of the two values (cost of equities from (iv) and (vii)). What is the averaged cost of equity? Part IV (30 Points): Equity Valuation (2-3 pages) We are going to value the stock of the company using the methods learned in class and compare it to the market price (the adjusted closing price on the last trading day of 2021). Please review the entire content on equity valuation before proceeding. i) Use the averaged cost of equity from Part III (viii) as the company’s cost of equity for the following: Please come up with the valuation of the stock in each of the following three scenarios: 1. Zero-growth dividends: Assume that annual dividends in the future will stay the same and not grow larger than the sum of all quarterly dividends in 2021. 2. Constant-growth dividends: please estimate the price of the stock for each of the following possible growth rates of the dividends: 1%, 2%, 3%, 4%, and 5%. Which constant growth rate yields the valuation that is closest to the market price? 3. Non-constant growth dividends: Assume that dividends will grow at the rate calculated in Part III (iii) for the next two years and then come down to a lower growth rate, thereafter. Estimate the price using the following second-stage growth rate: 2%. In which scenario is the price you calculated closest to the market price? Which scenario do you think is most applicable for the company? Part V (15 Points): Discussion, Conclusion, and Citations (<=1 page) Provide a 2-3 paragraph concluding remarks on the report. What are your overall thoughts on the company and this project? Try to ask yourself the following questions: what did I learn about the company’s fundamental value? What else do I like to know about this company? Do I want to invest in this company or recommend others to invest? Would I like to work for this company? What do I want to see happening in this company as a potential prospective investor, employee, or analyst? What are some external conditions (in terms of the economy, market, business cycle, industry, etc) that would help or jeopardize the company’s stock performance? This project includes the year 2020, so our analysis should probably weigh the effect of the COVID-19 pandemic on the company’s cost of capital and valuation. How do you think the pandemic may have or not have affected the company? Can you find any news articles relating the pandemic and the company (or its industry or related other companies)? What do you predict for the company’s cost of equity and stock valuation for the year 2022 (no right or wrong answer, here. Your thoughts backed by reasoning count.)? On a separate page at the very end of the report, please cite all the outside resources you have used to complete your report (For APA-style reference rules: https://owl.english.purdue.edu/owl/resource/560/10/) LASTLY: The report will be graded in two aspects: qualitative and quantitative. On the rubric, you will see that 80 out of 160 points have to do with getting numerical values correctly (the highlighted items). You will have to enter these numerical answers (not all of them) on Canvas “Project quantitative input” such that Canvas can check whether your numbers are right. 90 out of 160 points have to do with the qualitative aspect of your project, the remaining part of the rubric. And as a peer grader, you will be grading the qualitative portion only, since Canvas will grade the quantitative part for us.