Excel Project: Projecting Financial Statements Assignment Instructions

COMPANY: COCA-COLA BEVERAGE CO.

The 12^th tab in your Financial Statement Analysis Template must be labeled Common Sized Balance Sheets.

• Set-up like the Historical Balance Sheets (Tab 3), this worksheet should use formulas to calculate the common sized percentages, linking to the Historical Balance Sheets for the calculations. Total Assets should be used as the base for all common sized calculations on the Balance Sheets. Common sized percentages should be calculated for all three historical years presented.
• See Exhibit A for a partial example of this tab.

• The 13^th tab in your Financial Statement Analysis Template must be labeled Common Sized Income Statements.
  • Set-up like the Historical Income Statements (Tab 2), this worksheet should use formulas to calculate the common sized percentages, linking to the Historical Income Statements for the calculations. Revenue or Total Revenue (depending on your company) should be used as the base for all common sized calculations on the Income Statements. Common sized percentages should be calculated for all three historical years presented.
• The 14^th tab in your Financial Statement Analysis Template must be labeled Horizontal Analysis of Balance Sheets.
  • Start by copying and pasting the Historical Balance Sheets from Tab 3 to this worksheet. Then, add dollar and percentage change columns between each of the set of historical years.
  • See Exhibit B for an example of this tab.
• The 15^th tab in your Financial Statement Analysis Template must be labeled Horizontal Analysis of Income Statements.
  • Start by copying and pasting the Historical Income Statements from Tab 2 to this worksheet. Then, add dollar and percentage change columns between each of the set of historical years.
• The 16^th tab in your Financial Statement Analysis Template must be labeled Projected Income Statements. Three years of projected statements should be included, starting with the most recent year after the companys last issued report.
• The 17^th tab in your Financial Statement Analysis Template must be labeled Projected Balance Sheets. Three years of projected statements should be included. Three years of projected statements should be included, starting with the most recent year after the companys last issued report.
• The 18^th tab in your Financial Statement Analysis Template must be labeled Projected Statement of Cash Flows. Three years of projected statements should be included, starting with the most recent year after the companys last issued report.
  • Other information relating to projected tabs:
    • Projections should be based on research performed on your company and its industry. Use historical, trend, ratio data and current and future economic projections as well as information from the companys MD&A to aid in preparing projections.
    • Clearly state your assumptions in a text box at the bottom of each tab. Cite sources for assumptions based on research.
    • The statements should be consistent, reasonable, and accurate. Areas that will be checked for accuracy include:
      • The Balance Sheets should balance for all years presented.
      • Retained earnings should be properly calculated using the prior years balance, net income, and dividends.
      • The Ending Cash Balance on the Statement of Cash Flows should agree to the Cash Balance shown on the Balance Sheet for the same year.
      • Net Income on the Income Statements should match what is reported on the top of the Statement of Cash Flows (if indirect method is used).

The following information is from my work:

Please help!!!

Coca-Cola Consolidated Income Statement For the years ended Dec 31, 2021, 2020, 2019 USD ($) shares in Millions, $ in Millions 1,049 Net Operating Revenues Cost of goods sold Gross Profit Selling, general and administrative expenses Other operating charges Operating Income Interest income Interest expense Equity income (loss) net Other income (loss) net Income Before Income Taxes Income taxes Consolidated Net Income Less: Net income (loss) attributable to noncontrolling interests Net Income Attributable to Shareowners of The Coca-Cola Company Basic Net Income Per Sharel Diluted Net Income Per Share1 Average Shares Outstanding Basic Effect of dilutive securities Average Shares Outstanding Diluted For the Years ended December 31, 2021 2020 2019 $ 38,655 $ 33,014 $ 37,266 15,357 13,433 14,619 23,298 19,581 22,647 12,144 9,731 12,103 846 853 458 10,308 8,997 10,086 276 370 563 1,597 1,437 946 1,438 978 2,000 841 34 12,425 9,749 10,786 2,621 1,981 1,801 9,804 7,768 8,985 33 21 65 $9,771 $ 7,747 $ 8,920 $ 2.26 $ 1.80 $ 2.09 $ 2.25 $ 1.79 $2.07 4,315 4,295 4,276 25 28 38 4,340 4,323 4,314 Coca-Cola Consolidated Balance Sheets As of Dec. 31 2021, 2020, 2019 Sin Millions Dec 31, 2021 Dec 31, 2020 Dec 31, 2019 ASSETS: Current Assets Cash and cash equivalents Short-term investments Total Cash, Cash Equivalents and short-Term Investments $9,684 1,242 $ 10,926 $ 6,795 1,771 $ 8,566 $6,480 1,467 $ 7,947 $ 1,699 3,512 3,414 2,994 $ 22,545 $ 2,348 3,144 3,266 1,916 $ 19,240 $ 3,228 3,971 3,379 1,836 $ 20,411 Marketable securities Trade accounts receivable, less allowances of $516 and $526, respectively Inventories Prepaid expenses and other current assets Total Current Assets Equity method investments Other investments Other noncurrent assets Deferred income tax assets Property, plant and equipment net Trademarks with indefinite lives Goodwill Other intangible assets Total Assets $ 17,596 818 6,731 2,129 9,920 14,465 19,363 785 $ 94,354 $ 19,273 812 6,184 2,460 10,777 10,395 17,506 649 $ 87,296 $ 19,025 854 6,075 2,412 10,838 9,266 16,764 736 $ 86,381 LIABILITIES Current Liabilities Accounts payable and accrued expenses Loans and notes payable Current maturities of long-term debt Accrued income taxes Total Current Liabilities $ 14,619 3,307 1,336 686 $ 19,950 $ 11,145 2,183 485 $ 11,312 10,994 4,253 414 $ 26,973 788 $ 14,601 Long-term debt Other noncurrent liabilities Deferred income tax liabilities Total Liabilities $ 38,116 8,607 2,821 $ 69,494 $ 40,125 9,453 1,833 $ 66,012 $ 27,516 8,510 2,284 $ 65,283 Shareowner's Equity The Coca-Cola Company Shareowners' Equity Common stock, 50.25 par value; authorized 11,200 shares; issued 7,040 shares Capital surplus Reinvested earnings Accumulated other comprehensive income (loss) Treasury stock, at cost 2,715 and 2,738 shares, respectively Equity Attributable to shareowners of The Coca-Cola Company Equity attributable to noncontrolling interests $ 1,760 18, 116 69,094 (14,330) (51,641) 22,999 1,361 $ 1,760 17,601 66,555 (14,601) (52,016) 19,299 1,985 $ 1,760 17,154 65,855 (13,544) (52,244) 18,981 2,117 Total Equity Total Liabilities and Equity $ 24,860 $ 94,354 $ 21,284 $ 87,296 $ 21,098 $ 86,381 Coca-Cola Cash Flow Statements 12 Months Ended 2021, 2020, 2019 $s in millions 2019 $ 8,985 1,365 201 (280) OPERATING ACTIVITIES Consolidated Net Income Depreciation and amortization Stock-based compensation expense Deferred income taxes Equity (income) loss - net of dividends Foreign currency adjustments Significant (gains) losses - net Other operating charges Other items Net change in operating assets and liabilities Net Cash Provided by Operating Activities 12 Months Ended 2021 2020 $ 9,804 $ 7,768 1,452 1,536 337 126 894 (18) (615) (511) 86 (88) (1,365) (914) 506 556 201 699 1,325 690 $ 12,625 $9,844 (421) 91 (467) 127 504 366 $ 10,471 INVESTING ACTIVITIES Purchases of investments Proceeds from disposals of investments Acquisitions of businesses, equity method investments and nonmarketable securities Proceeds from disposals of businesses, equity method investments and nonmarketable securities Purchases of property, plant and equipment Proceeds from disposals of property, plant and equipment Other investing activities Net Cash Provided by (Used in) Investing Activities $ (6,030) 7,059 (4,766) 2,180 (1,367) 108 51 $ (2,765) $ (13,583) 13,835 (1,052) 189 (1,177) 189 122 $ (1,477) $(4,704) 6,973 (5,542) 429 (2,054) 978 (56) $ (3,976) FINANCING ACTIVITIES Issuances of debt Payments of debt Issuances of stock Purchases of stock for treasury Dividends Other financing activities Net Cash Provided by (Used in) Financing Activities Effect of Exchange Rate Changes on Cash, Cash Equivalents, Restricted Cash and Restricted Cash Equivalents $ 13,094 (12,866) 702 (111) (7,252) (353) (6,786) $ (159) $ 26,934 (28,796) 647 (118) (7,047) 310 (8,070) $ 76 $ 23,009 (24,850) 1,012 (1,103) (6,845) (227) (9,004) $ (72) CASH AND CASH EQUIVALENTS Net increase (decrease) in cash, cash equivalents, restricted cash and restricted cash equivalents during the year Cash, cash equivalents, restricted cash and restricted cash equivalents at beginning of year Cash, Cash Equivalents, Restricted Cash and Restricted Cash Equivalents at End of Year Restricted Cash and Restricted Cash Equivalents Cash and cash equivalents $ 2,915 7,110 10,025 341 $ 9,684 $ 373 6,737 7,110 315 $ 6,795 $ (2,581) 9,318 6,737 257 $ 6,480 Coco Cola Historical Ratios For the years ended December 31, 2021, 2020, 2019 Competitor Ratio Analysis Monster Beverage Industry Dr. Pepper Corp Average 2021 2020 2019 PepsiCo 1.13 1.32 0.76 0.83 0.47 4.85 2.05 0.96 1.09 0.63 0.66 0.33 4.24 1.74 0.45 0.49 0.5 0.44 0.26 0 0.23 1.86 2.01 0.53 0 2.03 1.30 2.54 2.23 1.02 0.90 1.53 1.89 0 0 4.74 4.92 5.99 33.01 6.88 10.65 16.85 1.24 4.11 4.33 6.73 5.63 5.05 2.79 3.3 9.70 8.28 9.35 0.25 0.79 1.45 Liquidity Ratios o Current Ratio o Quick Ratio Leverage Ratios o Debt to Total Assets Ratio o Debt Equity Ratio o Long-Term Debt to Equity o Times Interest Earned Ratio Activity Ratios o Inventory Turnover o Fixed Assets Turnover o Total Assets Turnover o Accounts Receivable Turnover o Average Collection Period Profitability Ratios o Gross Profit Margin o Operating Profit Margin o Net Profit Margin o Return on Total Assets (ROA) o Return on Stockholders' Equity (ROE) o Earnings Per Share (EPS) (in millions) o Price Earnings Ratio 0.43 0.38 0.43 0.86 0.74 0.61 0.23 11.09 11.03 10.51 9.39 9.16 6.90 9.05 91.44 75.12 63.47 25.70 16.32 59.06 33.69 56.8 58.45 60.67 53.35 55.83 59.23 56.14 27.25 27.06 26.68 14.04 21.35 35.51 23.63 0.60 0.61 9.59 11.40 30.65 17.21 0.59 8.90 10.80 10.40 8.31 2.66 22.73 11.23 42.59 47.55 5.56 26.81 34.73 $2.25 36.50 $1.79 29.47 27.31 $2.64 $2.07 $0.93 3.02 $5.49 31.43 26.00 24.88 23.39 32.17 29.00