null

Calculations

Case Study Project Financial Ratio Calculation Spreadsheet

All numbers in thousands (except for share price, EPS, and Divdend per share)

Financial Ratios Formula Coca-Cola Company Pepsi Company

2020 2019 2018 2020 2019 2018 <= Change the years to the most recent years on Yahoo financial statements ΔNWC Current year NWC - Previous year NWC (5, 499, 000) (1, 090, 000)(6,308, 000) 2,445,000 (2,570,200) (Ch 03) EBIT (Find this on Income Statement) 11, 186,000 11,732,000 9,269,000 3,460,100 5,461,400 6,224,700 OCF EBIT + Depreciation - Taxes 9,844,000 10,471,000 7,627,000 4,114,100 5,934,400 11,993,700 NI (Find this on Income Statement) 7,747,000 8,920,000 6,434,000 1,940,000 3,161,700 3,537,300 CR Total Current Assets/Total Current Liabilities 1.32 0.76 1.05 0.98 0.86 0.99 QR (Current Assets - Inventory)/Current Liabilities 0.96 0.56 0.66 0.70 0.60 0.70 D/E ratio Total Liabilities/Total Shareholders Equity 201% 203% 232% 3% 2% 2% ICR EBIT / Interest expenses 10.70 11.20 6.90 9.60 11.40 8.50 ROE Net Income/Total Shareholders' Equity 40.50% 49.60% 37.80% 50.40% 49.90% 97.90% PM Net Income/Total Sales Revenue 59.30% 60.80% 61.90% 54.90% 55.40% 54.60% MV (Total market value) (Find this on Yahoo summary page. Multiply MV in $billion by 1,000,000) 242, 144, 000 239, 890, 000 202, 080, 000 204,940,000 190,100,000 155,660,000 BV (Total book value) Total shareholders' equity 21, 284,000 21,098,000 19,058,000 13,454,000 14,786,000 14,518,000 M/B Total Market Value/Total Shareholders’ Equity 10.99 10.77 9.86 14.87 12.23 9.91 Current share price (in $) (Find this on Yahoo summary page) 56.16 55.35% 47.35% 146.06 130.79% 102.68% No. of shares outstanding Total Market Value/Current Share Price 4,323,000 4,314,000 4,299,000 1,392,000 1,407,000 1,425,000 Past share prices (in $) (Find year-end share prices on the chart, by setting 5 years, in Yahoo summary page) 87.6 91.88 89.82 76.27 88.34 90.66 EPS NI/# of shares outstanding 1.79 2.07 1.50 5.12 5.20 8.78 P/E Share price/EPS 28.30 30.60 74.30 28.53 25.19 11.67 Beta (Find this on Yahoo summary page) 0.6100 0.62 0.62 0.6200 0.61 0.62 (Ch 13) E(Rj) with CAPM Rf + (beta)(E(Rm) - Rf) 2% 2.7 3 3% 2.8 3.2 DY Dividend/Share Price 3.20% 2.90% 3.20% 2.80% 2.80% 3.20% (Ch 08) Analyst earnings estimate (Find average earnings estimate for next year on Yahoo ) 2.18 3.14 2.78 7.46 6.75 6.23% Earnigns growth rate (EPS1 - EPS0)/EPS0 -11.41% 8.65% -5.28% 4.78% 3.87% 1.79% P1 with P/E ratio EPS * (1 + earnigns growth rate) * EPS 3.50 2.50% 4.40% 2.00 13.00% 44.00% P1 with CAPM Curent share price + (1 + E(Rj)) 33.00 44.00% 44.00% 54.00 55.00% 66.00% A/R period 365/Accounts receivable turnover 34.76 38.91 38.91% 27.00 33.00 22.00% (Ch 18) Inventory period 365/Inventory turnover 90.35 81.11 79.35% 77.00 78.00 88.00% A/P period 365/Accounts payable turnover 95.55 95.05 77.50% 77.00 72.00 75.00% Operating cycle A/R period + Inventory period 124.00 123.00 125.00% 104.00 110.00 115.00% Cash cycle Oprating cycle - A/P period 28.00 28.00 48.00% 27.00 44.00 32.00% - 0 Accounts Receivable (Find this on Balance Sheet) 23,001,000 19,240,000 20,411,000 30,634,000 17,645,000 21,893,000 <= Find these figures on Yahoo and update the figures in the cells here by typing in. Inventory (Find this on Balance Sheet) 3,266,000 12,205,000 12,625,000 3,817,000 4,209,700 4,934,700 Total Current Assets (Find this on Balance Sheet) 19,240,000 20,411,000 30,634,000 23,001,000 17,645,000 21,893,000 Total Assets (Find this on Balance Sheet) 87,296,000 86,381,000 83,216,000 92,918,000 78,547,000 77,648,000 0 Accounts Payable (Find this on Balance Sheet) 42,793,000 42,763,000 43,555,000 7,552,900 8,816,000 9,240,800 Total Current Liabilities (Find this on Balance Sheet) 14,601,000 26,973,000 29,223,000 23,372,000 20,461,000 22,138,800 Total Liabilities (Find this on Balance Sheet) 66,012,000 65,283,000 64,158,000 79,366,000 63,679,000 63,046,000 0 Total Shareholders' Equity (Find this on Balance Sheet) 21,284,000 21,098,000 19,058,000 14,786,000 14,518,000 14,518,000 0 Sales Revenue (Find this on Income Statement) 33,014,000 37,266,000 31,856,000 70,372,000 67,161,000 64,661 Cost of Goods sold (Cost of revenue) (Find this on Income Statement) 13,433,000 14,619,000 11,770,000 31,797,000 30,132,000 29,831 Interest expenses (Find this on Income Statement) 1,437,000 946,000 919,000 1,128,000 1,135,000 1,525,000 Income taxes (Find this on Income Statement) 1,981,000 1,801,000 1,319,000 1,894,000 1,959,000 (3,370,000) 0 Depreciation (Find this on Statement of Cash Flows) 1,536,000 1,365,000 1,623,000 2,548,000 2,432,000 2,399,000 Dividend (Find this on Yahoo summary page) 0.41 0.4 0.39 1.03 0.955 0.9275 Risk-free rate (Use rates as given in the cells right) 7% 7 7 6% 4.31 4.61 Expected market return (Use rates as given in the cells right) 20% 20.26 10.74 13% 13.31 16.71 Net Working Capital Total current assets - Total current liabilities -3,280,000 -2,219,000 -1,129,000 -371,000 -2,816,000 -245,800 Average Accounts Receivables (Current A/R + Last A/R)/2 3,144,000 3,971,000 3,685,000 6,691,000 6,691,000 5,978,000 A/R Turnover Sales revenue/Average A/R 10.51 9.39 9 8.05 7.80 8 Average Inventory (Current inventory + Last inventory)/2 3,322,000 3,225,000 2,863,000 3,755,000 3,233,000 3,038,000 Inventory Turnover COGs/Average invenotry 4.00 4.50 5 7.60 9.03 9 [[[[[[[[[[[[ Average Accounts Payable Current A/P + Last A/P)/2 3.517,000 3,804,000 2,719,000 8,184,450 9,028,400 7,213,000 A/P Turnover COGS/Average A/P 9.62 3.84 8 3.89 3.34 3 0