question archive Project Worksheet The following assignment is designed to introduce you to some of the FREE financial information that is available on the web
Project Worksheet The following assignment is designed to introduce you to some of the FREE financial information that is available on the web
Subject:AccountingPrice: Bought3
Project Worksheet The following assignment is designed to introduce you to some of the FREE financial information that is available on the web. This can be especially useful for personal investing decisions. For the 15t part of the project, we focus on a single site, Yahoo! Finance. There are, however, various other good sites such as Google Finance. We encourage you to explore these web sites on your own. Use the glossary of terms for any items that are new to you. For the second part of the project, you should use the 10-Q report (quarterly report) of Coca-cola Company and PepsiCo Inc. You can go to the Investor Relations section of their website or you can go to sec.gov to search for their 10-Q filings. Coca-cola Company 10-Q (Click the link below and downloaded the 10-Q reports released on 10/22/20 (Q3) and 07/22/20 (Q2).) https://investors.coca-colacompany.com/filings-reports/all-sec-filings?form type=10-Q&year= PepsiCo Inc. Q2 10-Q: https://pepsico.gcs-web.com/static-files/e81f7c71-9e46-44db-ae56-4b605aa23a61 PepsiCo Inc. Q3 10-Q https://pepsico.gcs-web.com/static-files/6f55087e-5382-4835-88e4-fe89c8da0d4f For the purposes of this exercise, we will be examining Coca-Cola Company and in the second part of the project comparing it to PepsiCo Inc. The fiscal year end of Coca-Cola Company and PepsiCo Inc both are 12/31. In order to use recent comparable information in the second part of the project you will gather financial statement facts from the 3id quarterly report (of fiscal year 2020) of Coca-cola Company ending 9/25/2020 and compare those with the 3"d quarter (of fiscal year 2020) of PepsiCo Inc. ending 9/13/2020. 15 PART OF THE PROJECT First, go to the following web site: https://finance.yahoo.com Type "Coca-Cola Company" in the search box. Then click on "search". Select the ticker symbol "KO" from the menu of choices. (NOTE: The ticker symbol for Coca-Cola Company is KO. The ticker symbol is a letter designation that is assigned to stocks and mutual funds traded on U.S. exchanges. You may use it to "get quotes" instead of typing in the whole company name) This will take you to a page of basic information about Coca-Cola Company's stock. It will show you the current price and the volume of trading. From this menu, you will click on the following links to obtain the necessary information: Internet Financial Statement Analysis and Ratio Project Worksheet 1
Summary . . Profile Financials Historical Data Each of the above links will be discussed in the following pages. You will be asked to go to each of the linked pages and record information in the spaces provided. Summary The earnings per share of a company measures the amount of income earned for each share of stock, which is net income divided by shares of common stock outstanding. Earnings per share are abbreviated as "EPS". The earnings per share (EPS) for Coca-Cola Company for the most recent year shown on this page are (Note: TIM = trailing twelve months (the past 12 months)_ 1.93 The current price of Coca-Cola Company's common stock is? 53.01 (Record the Last Trade price and the date you looked at it) _on Date_11/12/2020 Profile From the Profile Page, you can obtain a brief Business Description. There is also general contact information as well as links to several other items, such as the company web site. From this page, answer the following questions about Coca-Cola Company: 1. What Industry is Coca-cola Company in? Beverages-non Alcoholic 2. How many full-time employees work for Coca-cola Company? 86,200 . Who is the Chief Executive Officer? 4. Who is the Chief Financial Officer? Historical Data From this page, you can obtain daily, weekly, and monthly prices for the company for a range of dates. You can also obtain information on dividends. Select "Time Period" as 9/30/2020-9/30/2020, "Show" as Historical Prices, and "Frequency" as Daily to capture the historical price at the end of last year. Look at the column listing the adjusted closing price (this price is adjusted for stock splits and stock dividends). Internet Financial Statement Analysis and Ratio Project Worksheet 2
2" PART OF THE PROJECT Financials From this page, you can view the recent quarterly or annual financial statements of the company. In order to compare Coca-Cola Company to a close competitor we will use data for the quarter ending 9/25/2020. Open the 10-Q of Coca-Cola Company and PepsiCo Inc. (link provided at the beginning of this project) and click on income statement or balance sheet of Coca-Cola Company as requested below. Coca-Cola Company Income Statement: Page 5/89 on Q2 report; 5/96 on Q3 report. Coca-Cola Company Balance Sheet: Page 7/89 on Q2 report; 7/96 on Q3 report. PepsiCo Inc. Income Statement: Page 4/62 on Q2 report; 4/65 on Q3 report. PepsiCo Inc. Balance Sheet: Page 8/62 on Q2 report; 8/65 on Q3 report. Notice that all numbers are in millions of dollars in the following part of the project. Write them down on your worksheet just as they are. For example, if it says $7,000, just write $7,000 or $7,000 million as your final answer, do not write $7,000,000,000. Questions 1 through 16 pertain to Coca-Cola Company. 1. From the quarterly Income Statement, what was the total revenue for the quarter ending on 9/25/2020? 8,652 million 2. From the quarterly Income Statement, what was the total revenue for the previous quarter ending on 6/26/2020? $7,150 million 3. Now use your answers of 1 and 2 to calculate the % growth in revenue for the quarter (subtract revenues for the quarter ending 9/25/2020 from revenues for the quarter ending 6/26/2020 and divide the result by the revenues for the quarter ending 6/26/2020. Then express the result as a percentage). Revenue Growth (8,652-7,150)/8,652 *100=17.36% Internet Financial Statement Analysis and Ratio Project Worksheet 3
4. From the quarterly Income Statement, what was Consolidated Net Income for the quarter ending on 9/25/2020? 1,740 million 5. From the quarterly Income, Statement, what was the Gross Profit for the quarter ending on 9/25/2020? 6. From the quarterly Income Statement, what was the Cost of Revenue (cost of goods sold) for the quarter ending on 9/25/2020? $3,471 million 7. From the quarterly Balance Sheet, what was the value of inventory as of 9/25/2020? 3,264 million S. From the quarterly Balance Sheet, what was the value of inventory as of 6/26/2020? 3,501 million 9. From the quarterly Balance Sheet, what was the total assets value as of 9/25/2020? $97,184 million 10. From the quarterly Balance Sheet, what was the total liabilities as of 9/25/2020? $76868 million 11. From the quarterly Balance Sheet, what was the total current assets value as of 9/25/2020? $30410 million 12. From the quarterly Balance Sheet, what was the total current liabilities value as of 9/25/2020? 26,891 million Use Quarterly data ending at 9/25/2020 to answer question 13 to 16. 13. The largest current asset category is: Cash Equivalents and Short-Term Investments 14. The largest current liability category is: Accounts payable and accrued expenses Internet Financial Statement Analysis and Ratio Project Worksheet 4
15. The largest non-current asset category is: _ Equity method investments 16. The largest non-current liability category is: Long-term debt Internet Financial Statement Analysis and Ratio Project Worksheet 5
FINANCIAL RATIOS Use the information gathered from Coca-Cola Company's Financials on the previous pages to calculate the following ratios for Coca-Cola Company. Do not try to look these up on the website. Instead you must make the calculation yourself. Inventory Turnover (Cost of Goods Sold / Average Inventory) Average Inventory = (Beginning Inventory + Ending Inventory) / 2 Gross profit % (Gross Profit / Revenues) *100% Debt Ratio (Total Liabilities / Total Asset) Current Ratio (Current Asset / Current Liabilities) Compare Coca-cola Company to its close competitor PepsiCo Inc. Now open up the income statement and balance sheet for PepsiCo Inc.: PepsiCo Inc. Income Statement: Page 4/62 on Q2 report; 4/65 on Q3 report. PepsiCo Inc. Balance Sheet: Page 8/62 on Q2 report; 8/65 on Q3 report. Look up the following income statement information: Total Quarter Revenues (9/05/2020) Total Quarter Revenues (6/13/2020) Quarter Gross Profit (9/05/2020) Look up the following Quarterly balance sheet information for 9/05/2020: Total Assets Internet Financial Statement Analysis and Ratio Project Worksheet 6
Total Liabilities Current Assets Current Liabilities Now use these 7 values (above) to calculate the following ratios for PepsiCo Inc. in order to compare them to Coca-Cola Company. Use the same calculation methods as for Coca- Cola Company. Revenue Growth Gross Profit% Debt Ratio Current Ratio Based on a comparison of these two ratios between Coca-Cola Company and PepsiCo Inc. which company seems to have better liquidity, which has better margin, which has better solvency, and which has better revenue growth? Enter the Company names below. Revenue Growth? Gross Profit? Solvency? (Debt Ratio, low is good) Liquidity? (Current Ratio, high is good) HORIZONTAL ANALYSIS Horizontal analysis is also known as trend analysis. By looking at recent trends, and also taking into disclosures and press releases from the company and outside the company, the financial markets often try to predict the future stock prices. In the next few steps, do a simple 3 years trend analysis. Go to Coca-Cola Company and PepsiCo Inc. 10-K for 2019 and open Income Statement. Coca-Cola Company 10-K (Click the link below and download the 2019 10-K report release on 02/24/20): https://investors.coca-colacompany.com/filings-reports/all-sec-filings?form_type=10- K&year= Internet Financial Statement Analysis and Ratio Project Worksheet 7
Page: 68/214 PepsiCo Inc. 10-K: https://pepsico.gcs-web.com/static-files/c85bda79-43e0-4665- a701-do1f2e5858f7 Page: 76/674 Write down the Revenues. Coca-Cola Company PepsiCo Inc. Dec 31, 2017 Dec 31, 2018 Dec 31, 2019 Write down the Operating Income. Coca-Cola Company PepsiCo Inc. Dec 31, 2017 Dec 31, 2018 Dec 31, 2019 Write down the Consolidated Net Income. Coca-Cola Company PepsiCo Inc. Dec 31, 2017 Dec 31, 2018 Dec 31, 2019 Open an Excel Spreadsheet. Type the Revenues for Coca-Cola Company and PepsiCo Inc. in two separate columns. Title them as Coca-cola Company Revenues and PepsiCo Inc. revenues respectively. Make a simple line chart for each. Title the line charts as Coca-Cola Company Revenues and PepsiCo Inc. revenues respectively. Next, type the Operating Income for Coca-Cola Company and PepsiCo Inc. in two separate columns. Title them as Coca-Cola Company Operating Income and PepsiCo Inc. Operating Income respectively. Make a simple line chart for each. Title the line charts as Coca-Cola Company Operating Income and PepsiCo Inc. Operating Income respectively. Next, type the Consolidated Net Income for Coca-Cola Company and PepsiCo Inc. in two separate columns. Title them as Coca-cola Company Net Income and PepsiCo Inc. Net Income respectively. Make a simple line chart for each. Title them as Coca-Cola Company Net Income and PepsiCo Inc. Net Income respectively. Once all six-line charts are ready, write down a brief analysis on Coca-Cola Company's revenue, operating income and net income trends. Next, write down a brief analysis on PepsiCo Inc.'s revenue, operating income and net income trends. Contrast the two. There is no one correct answer. We will be checking for plagiarism. Internet Financial Statement Analysis and Ratio Project Worksheet 8
OPERATING EXPENSES ANALYSIS In the next few steps, make pie charts to show top 3 operating expenses for Coca-Cola Company and PepsiCo Inc. Coca-cola Company Go to Coca-Cola Company 10-K for 2019 (Click the link below and download the 2019 10-K report release on 02/24/20): https://investors.coca-colacompany.com/filings-reports/all-sec-filings?form type=10- K&year= Open Income Statement (Page 68/214) and we can see that its operating expenses consist of "Selling general and administrative expenses" and "Other operating charges." To look deep into those expenses to find out the top 3 expenses in operating expenses, go to page 50/214 and write down the information of top 3 expenses in the table below in descending order. Calculate their percentage of total operating expense in the third column. Coca-cola Company 2019 Operating Expense analysis (in millions) Expense Name Amount Percentage of Total Operating Expense Other operating expense Excluding top 3 expenses) Total Operating Expense 100% Open an Excel Spreadsheet, type the names of top 3 operating expenses and "Other operating expense (Excluding top 3 expenses)" in the same column, then type their Percentage of Total Operating Expense. Make a "Pie Chart" based on the two columns and title the pie chart as "Coca-Cola Company Operating Expenses". Screenshot or copy the pie chart and paste it at the end of this workbook. Internet Financial Statement Analysis and Ratio Project Worksheet 9
PepsiCo Inc. Go to PepsiCo Inc. 10-K for 2019 https://pepsico.gcs-web.com/static-files/c85bda79-43e0-46b5-a701-dolf2e5858f7 Open Income Statement (Page 76/674) and we can see that its operating expenses consist of "Selling, general and administrative expenses". To look deep into those expense to find out the top 3 expenses in operating expenses, go to page 88/674 and 89/674, find the "Total Marketplace Spending" and write down the information of top 3 expenses in the table below in descending order. Calculate their percentage of total operating expense in the third column. PepsiCo Inc. 2019 Operating Expense analysis (in millions) Expense Name Amount Amount/Total Operating Expense Other operating expense (Excluding top 3 expenses) Total Operating Expense 100% Open an Excel Spreadsheet, type the names of top 3 operating expense and "Other operating expense (Excluding top 3 expenses)" in the same column, then type their Percentage of Total Operating Expense. Make a "Pie Chart" based on the two columns and title the pie chart as this workbook. "PepsiCo Inc. Operating Expenses". Screenshot or copy the pie chart and paste it at the end of Coca-Cola Company vs. PepsiCo. Inc Compare the pie charts and write down brief analysis on operating expenses of Coca-Cola Company and PepsiCo Inc. Contrast the two. There is no one correct answer. We will be checking for plagiarism Internet Financial Statement Analysis and Ratio Project Worksheet 10
