SchemitaBFIN312CaseStudyProject_ExcelCalculations1PEP.xlsx

Calculations

Case Study Project Financial Ratio Calculation Spreadsheet

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

Financial Ratios Formula PepsiCo, Inc The Coca-Cola Company

2020 2019 2018 2020 2019 2018 <= Change the years to the most recent years on Yahoo Finance financial statements ΔNWC Current year NWC - Previous year NWC 2,445,000 (2,391,000) blank 11,201,000 (7,973,000) Blank (Ch 03) EBIT (Find this on Income Statement) 10,197,000 10,447,000 10,477,000 11,186,000 11,732,000 9,269,000 OCF EBIT + Depreciation - Taxes 10,851,000 10,920,000 10,741,000 14,898,000 NI (Find this on Income Statement) 7,120,000 7,314,000 7,747,000 8,920,000 CR Total Current Assets/Total Current Liabilities 0.98 0.86 1.32 0.76 QR (Current Assets - Inventory)/Current Liabilities 0.81 0.70 0.84 1.09 0.63 0.95 D/E ratio Total Liabilities/Total Shareholders Equity 586% 428% 310% 309% ICR EBIT / Interest expenses 9.04 9.20 7.78 12.40 ROE Net Income/Total Shareholders' Equity 52.54% 49.19% 36.40% 42.28% PM Net Income/Total Sales Revenue 10.12% 10.89% 23.47% 23.94% MV (Total market value) (Find this on Yahoo Finance summary page. Multiply MV in $billion by 1,000,000) 200,129,000 23,334,800 BV (Total book value) Total shareholders' equity 13,552,000 14,868,000 14,602,000 21,284,000 21,098,000 19,058,000 M/B Total Market Value/Total Shareholders’ Equity 14.77 1.10 Current share price (in $) (Find this on Yahoo Finance summary page) 145.39 54.26 No. of shares outstanding Total Market Value/Current Share Price 1,376,498 430,055 Past share prices (in $) (Find year-end share prices on the chart, by setting 5 years, in Yahoo Finance summary page) 104.16 48.26 EPS NI/# of shares outstanding 5.17 18.01 P/E Share price/EPS 28.11 3.01 Beta (Find this on Yahoo Finance summary page) 0.6100 0.6100 (Ch 13) E(Rj) with CAPM Rf + (beta)(E(Rm) - Rf) 6% 6% DY Dividend/Share Price 2.96% 3.10% (Ch 08) Analyst earnings estimate (Find average earnings estimate for next year on Yahoo Finance) 6.58 2.35 Earnigns growth rate (EPS1 - EPS0)/EPS0 P1 with P/E ratio EPS * (1 + earnigns growth rate) * EPS P1 with CAPM Curent share price + (1 + E(Rj)) A/R period 365/Accounts receivable turnover 52.47 51.56 56.71 (Ch 18) Inventory period 365/Inventory turnover 43.10 39.16 90.28 76.71 A/P period 365/Accounts payable turnover 96.80 92.22 99.46 78.67 Operating cycle A/R period + Inventory period 95.57 90.72 146.99 76.71 Cash cycle Oprating cycle - A/P period (1.23) (1.50) 47.53 (1.96) Accounts Receivable (Find this on Balance Sheet) 6,892,000 6,447,000 6,079,000 3,144,000 3,971,000 3,396,000 <= Find these figures on Yahoo Finance and update the figures in the cells here by typing in. Inventory (Find this on Balance Sheet) 4,172,000 3,338,000 3,128,000 3,266,000 3,379,000 2,766,000 Total Current Assets (Find this on Balance Sheet) 23,001,000 17,645,000 21,893,000 19,240,000 20,411,000 30,634,000 Total Assets (Find this on Balance Sheet) 92,918,000 78,547,000 77,648,000 87,296,000 86,381,000 83,216,000 Accounts Payable (Find this on Balance Sheet) 8,853,000 8,013,000 7,213,000 3,517,000 3,804,000 2,498,000 Total Current Liabilities (Find this on Balance Sheet) 23,372,000 20,461,000 22,318,000 14,601,000 26,973,000 29,223,000 Total Liabilities (Find this on Balance Sheet) 79,366,000 63,679,000 63,046,000 66,012,000 65,283,000 64,158,000 Total Shareholders' Equity (Find this on Balance Sheet) 13,552,000 14,868,000 14,602,000 21,284,000 21,098,000 19,058,000 Sales Revenue (Find this on Income Statement) 70,372,000 67,161,100 64,661,000 33,014,000 37,266,000 31,856,000 Cost of Goods sold (Cost of revenue) (Find this on Income Statement) 31,797,000 30,132,000 29,381,000 13,433,000 14,619,000 11,770,000 Interest expenses (Find this on Income Statement) 1,128,000 1,135,000 1,525,000 1,437,000 946,000 919,000 Income taxes (Find this on Income Statement) 1,894,000 1,959,000 1,981,000 1,801,000 Depreciation (Find this on Statement of Cash Flows) 2,548,000 2,432,000 1,536,000 1,365,000 Dividend (Find this on Yahoo Finance summary page) 4.3 1.68 Risk-free rate (Use rates as given in the cells right) Expected market return (Use rates as given in the cells right) Net Working Capital Total current assets - Total current liabilities -371,000 -2,816,000 -425,000 4,639,000 -6,562,000 1,411,000 Average Accounts Receivables (Current A/R + Last A/R)/2 10,115,500 9,486,500 5,129,500 A/R Turnover Sales revenue/Average A/R 6.96 7.08 6.44 Average Inventory (Current inventory + Last inventory)/2 3,755,000 3,233,000 3,322,500 3,072,500 Inventory Turnover COGs/Average invenotry 8.47 9.32 4.04 4.76 Average Accounts Payable Current A/P + Last A/P)/2 8,433,000 7,613,000 3,660,500 3,151,000 A/P Turnover COGS/Average A/P 3.77 3.96 3.67 4.64