1. Prepare Excel spreadsheets for the following: The Coca-Cola Company and Subsidiaries Consolidated Statements of Income, Consolidated Balance Sheets, and Consolidated Statements of Cash Flows. (The financial statements are located in Appendix C of the textbook or e-book.) (Omit footnotes or any reference to the Notes to Consolidated Financial Statements.)
2. Your spreadsheets should include formulas for the following: (not hard-keyed numbers)
a. Income Statement
i. Gross Profit
ii. Income from Continuing Operations before Income Taxes
iii. Net Income from Continuing Operations
iv. Consolidated Net Income
v. Net Income Attributable to Shareowners of the Coca-Cola Company
vi. Basic net income per share from continuing operations
vii. Basic net income per share from discontinued operations
viii. Basic Net Income Per Share
ix. Diluted net income per share from continuing operations
x. Diluted net income per share from discontinued operations
xi. Diluted Net Income Per Share
b. Balance Sheet
i. Total Cash, Cash Equivalents and Short-Term Investments
ii. Total Current Assets
iii. Total Assets
iv. Total Current Liabilities
v. Total Liabilities (You will need to insert a row in the appropriate place.)
vi. Equity Attributable to Shareowners of the Coca-Cola Company
vii. Total Equity
viii. Total Liabilities and Equity
c. Statement of Cash Flows
i. Consolidated net income (Link to Income Statement)
ii. (Income) loss from discontinued operations (Link to Income Statement)
iii. Net income from continuing operations (Link to Income Statement)
iv. Net cash provided by operating activities
v. Net cash provided by (used in) investing activities
vi. Net cash provided by (used in) financing activities
vii. Net cash provided by (used in) discontinued operations
viii. Net increase (decrease) during the year
ix. Balance at beginning of year (2017 only) (Link to Balance Sheet)
x. Balance at end of year (DO NOT link to Balance Sheet. This number should match the amount on the Balance Sheet. If it does not, then there's an error in your spreadsheet that you will need to find and correct)
3. Each financial statement should be on a separate worksheet that is named to indicate the appropriate financial statement.
4. On a separate worksheet within the same workbook, compute all of the financial ratios from Appendix 5A, Illustration 5A.1, for 2017.
a. To compute the "average", you will take the beginning balance plus the ending balance and divide by 2. This is true for any account, e.g. current liabilities, net accounts receivable, inventory, etc.
b. Use "Net Operating Revenues" for Net Sales.
c. Use "Consolidated Net Income" for Net Income.
d. Coca-Cola has no preferred stock; therefore, there are no preferred dividends.
e. Use "Total Equity" for Common Stockholder's Equity.
f. Use "Average Shares Outstanding – Basic" for Weighted-Average Common Shares Outstanding and for Outstanding Shares.
g. Find the market price per share of Coca-Cola stock on the day that you do this assignment. Indicate the date and source of your market price.
h. Modify your formula to ensure that your Payout ratio is a positive number.
i. Capital expenditures are included in the statement of cash flows as "Purchases of property, plant and equipment."