Financial Ratio Spreadsheet Assignment

• Download the file containing the financial statements and ratios for Kroger Company 2006-2016.
• Copy and paste the financial ratio labels from column 1 in the ?Ratios? tab into a new worksheet page.
• Write formulas to calculate each of the ratios for FYE Jan 2016 using the balance sheet and income statement data from the first two pages of the spreadsheet.
  • Check your results against the numbers provided for the ratio outcomes in the Kroger spreadsheet.
• Once you have finished the formulas for the first year, copy and paste that column over to cover the time period extending back to FYE Jan 2006.
• Delete the page containing the Kroger ratios that came with the spreadsheet, keeping the page with your formulas.

Balance Sheets Kroger Company TICKER: SIC: GICS: ASSETS Cash & Equivalents Net Receivables Inventories Prepaid Expenses Other Current Assets Total Current Assets Gross Plant, Property & Equipment Accumulated Depreciation Net Plant, Property & Equipment Investments at Equity Other Investments Intangibles Deferred Charges Other Assets TOTAL ASSETS LIABILITIES Long Term Debt Due In One Year Notes Payable Accounts Payable Taxes Payable Accrued Expenses Other Current Liabilities Total Current Liabilities Long Term Debt Deferred Taxes Investment Tax Credit Other Liabilities TOTAL LIABILITIES Redeemable Noncontrolling Int. EQUITY Preferred Stock - Redeemable Preferred Stock - Nonredeemable Total Preferred Stock Common Stock Capital Surplus Retained Earnings Less: Treasury Stock Common Equity Stockholder's Equity - Parent Nonredeemable Noncontrolling Int. TOTAL EQUITY TOTAL LIABILITIES & EQUITY COMMON SHARES OUTSTANDING ANNUAL BALANCE SHEET KR 5411 30101030 Jan-16 Jan-15 Jan-14 Jan-13 Jan-12 Jan-11 Jan-10 Jan-09 Jan-08 Jan-07 Jan-06 $ 1,200.00 $ 1,734.00 $ 6,168.00 @CF $ 790.00 -----------------$ 9,892.00 $ 37,667.00 $ 18,048.00 -----------------$ 19,619.00 $ $ 273.00 $ 3,777.00 $ $ 336.00 -----------------$ 33,897.00 $ 1,256.00 $ 1,266.00 $ 5,688.00 @CF $ 701.00 -----------------$ 8,911.00 $ 34,540.00 $ 16,628.00 -----------------$ 17,912.00 $ $ 231.00 $ 3,061.00 $ $ 441.00 -----------------$ 30,556.00 $ 1,359.00 $ 1,116.00 $ 5,651.00 @CF $ 704.00 -----------------$ 8,830.00 $ 32,379.00 $ 15,486.00 -----------------$ 16,893.00 $ $ 51.00 $ 2,837.00 $ $ 670.00 -----------------$ 29,281.00 $ 1,193.00 $ 1,051.00 $ 5,146.00 @CF $ 569.00 -----------------$ 7,959.00 $ 29,370.00 $ 14,495.00 -----------------$ 14,875.00 $ $ 44.00 $ 1,234.00 $ $ 540.00 -----------------$ 24,652.00 $ 974.00 $ 949.00 $ 5,114.00 @CF $ 288.00 -----------------$ 7,325.00 $ 28,071.00 $ 13,607.00 -----------------$ 14,464.00 $ $ 50.00 $ 1,138.00 $ $ 499.00 -----------------$ 23,476.00 $ 1,491.00 $ 845.00 $ 4,966.00 @CF $ 319.00 -----------------$ 7,621.00 $ 26,716.00 $ 12,569.00 -----------------$ 14,147.00 $ $ 69.00 $ 1,140.00 $ $ 528.00 -----------------$ 23,505.00 $ 1,078.00 $ 909.00 $ 4,902.00 @CF $ 561.00 -----------------$ 7,450.00 $ 25,673.00 $ 11,744.00 -----------------$ 13,929.00 $ $ 68.00 $ 1,158.00 $ $ 488.00 -----------------$ 23,093.00 $ 894.00 $ 944.00 $ 4,859.00 @CF $ 509.00 -----------------$ 7,206.00 $ 23,900.00 $ 10,739.00 -----------------$ 13,161.00 $ $ 67.00 $ 2,271.00 $ $ 506.00 -----------------$ 23,211.00 $ 918.00 $ 786.00 $ 4,855.00 @CF $ 555.00 -----------------$ 7,114.00 $ 22,436.00 $ 9,938.00 -----------------$ 12,498.00 $ $ 75.00 $ 2,234.00 $ $ 378.00 -----------------$ 22,299.00 $ 803.00 $ 778.00 $ 4,609.00 @CF $ 565.00 -----------------$ 6,755.00 $ 20,982.00 $ 9,203.00 -----------------$ 11,779.00 $ $ 152.00 $ 2,268.00 $ $ 261.00 -----------------$ 21,215.00 $ 210.00 $ 686.00 $ 4,486.00 @CF $ 1,084.00 $ 1,380.00 $ 990.00 $ 5,728.00 $ $ 1,426.00 $ 3,447.00 -----------------$ 12,971.00 $ 9,709.00 $ 1,752.00 $ $ 2,667.00 -----------------$ 27,099.00 $ - $ 610.00 $ 1,275.00 $ 5,052.00 $ $ 1,291.00 $ 3,175.00 -----------------$ 11,403.00 $ 9,771.00 $ 1,209.00 $ $ 2,731.00 -----------------$ 25,114.00 $ - $ 407.00 $ 1,250.00 $ 4,881.00 $ $ 1,150.00 $ 3,017.00 -----------------$ 10,705.00 $ 9,653.00 $ 1,381.00 $ $ 2,147.00 -----------------$ 23,886.00 $ - $ 2,734.00 $ $ 4,524.00 $ $ 977.00 $ 2,822.00 -----------------$ 11,057.00 $ 6,145.00 $ 800.00 $ $ 2,436.00 -----------------$ 20,438.00 $ - $ 1,315.00 $ $ 4,329.00 $ $ 1,056.00 $ 2,405.00 -----------------$ 9,105.00 $ 6,850.00 $ 647.00 $ $ 2,908.00 -----------------$ 19,510.00 $ - $ 588.00 $ $ 4,227.00 $ $ 888.00 $ 2,367.00 -----------------$ 8,070.00 $ 7,304.00 $ 750.00 $ $ 2,083.00 -----------------$ 18,207.00 $ - $ 579.00 $ $ 3,890.00 $ $ 786.00 $ 2,459.00 -----------------$ 7,714.00 $ 7,477.00 $ 568.00 $ $ 2,428.00 -----------------$ 18,187.00 $ - $ 558.00 $ $ 3,822.00 $ $ 828.00 $ 2,421.00 -----------------$ 7,629.00 $ 7,505.00 $ 384.00 $ $ 2,422.00 -----------------$ 17,940.00 $ 95.00 $ 1,592.00 $ $ 4,050.00 $ $ 815.00 $ 2,232.00 -----------------$ 8,689.00 $ 6,529.00 $ 367.00 $ $ 1,800.00 -----------------$ 17,385.00 $ - $ 906.00 $ $ 3,804.00 $ $ 796.00 $ 2,075.00 -----------------$ 7,581.00 $ 6,154.00 $ 722.00 $ $ 1,835.00 -----------------$ 16,292.00 $ - $ $ $ -----------------$ $ 1,918.00 $ 2,980.00 $ 13,331.00 $ 11,409.00 -----------------$ 6,820.00 -----------------$ 6,820.00 $ (22.00) -----------------$ 6,798.00 -----------------$ 33,897.00 967.00 $ $ -----------------$ $ 959.00 $ 3,707.00 $ 11,555.00 $ 10,809.00 -----------------$ 5,412.00 -----------------$ 5,412.00 $ 30.00 -----------------$ 5,442.00 -----------------$ 30,556.00 974.00 $ $ -----------------$ $ 959.00 $ 3,549.00 $ 10,517.00 $ 9,641.00 -----------------$ 5,384.00 -----------------$ 5,384.00 $ 11.00 -----------------$ 5,395.00 -----------------$ 29,281.00 1,016.00 $ $ -----------------$ $ 959.00 $ 3,451.00 $ 9,034.00 $ 9,237.00 -----------------$ 4,207.00 -----------------$ 4,207.00 $ 7.00 -----------------$ 4,214.00 -----------------$ 24,652.00 1,028.00 $ $ -----------------$ $ 959.00 $ 3,427.00 $ 7,727.00 $ 8,132.00 -----------------$ 3,981.00 -----------------$ 3,981.00 $ (15.00) -----------------$ 3,966.00 -----------------$ 23,476.00 1,122.00 $ $ -----------------$ $ 959.00 $ 3,394.00 $ 7,675.00 $ 6,732.00 -----------------$ 5,296.00 -----------------$ 5,296.00 $ 2.00 -----------------$ 5,298.00 -----------------$ 23,505.00 1,240.00 $ $ -----------------$ $ 958.00 $ 3,361.00 $ 6,751.00 $ 6,238.00 -----------------$ 4,832.00 -----------------$ 4,832.00 $ 74.00 -----------------$ 4,906.00 -----------------$ 23,093.00 1,284.00 $ $ -----------------$ $ 955.00 $ 3,266.00 $ 6,994.00 $ 6,039.00 -----------------$ 5,176.00 -----------------$ 5,176.00 @NA -----------------$ 5,176.00 -----------------$ 23,211.00 1,298.00 $ $ -----------------$ $ 947.00 $ 3,031.00 $ 6,358.00 $ 5,422.00 -----------------$ 4,914.00 -----------------$ 4,914.00 @NA -----------------$ 4,914.00 -----------------$ 22,299.00 1,326.00 $ $ -----------------$ $ 937.00 $ 2,755.00 $ 5,242.00 $ 4,011.00 -----------------$ 4,923.00 -----------------$ 4,923.00 @NA -----------------$ 4,923.00 -----------------$ 21,215.00 1,410.00 $ 6,466.00 $ 19,786.00 $ 8,421.00 $ 11,365.00 ### $ 119.00 $ 2,295.00 ### $ 237.00 $ 20,482.00 $ $ $ $ $ $ $ 554.00 ### 3,550.00 ### 742.00 1,869.00 6,715.00 6,678.00 843.00 ### 1,856.00 $ 16,092.00 ### ### ### $ $ $ $ ### 927.00 2,536.00 4,330.00 3,403.00 $ 4,390.00 $ 4,390.00 @NA $ 4,390.00 $ 20,482.00 1,446.00 Income Statements Kroger Company TICKER: SIC: GICS: Sales Cost of Goods Sold Gross Profit Selling, General, & Administrative Expense Operating Income Before Deprec. Depreciation, Depletion, & Amortization Operating Profit Interest Expense Non-Operating Income/Expense Special Items Pretax Income Total Income Taxes Minority Interest Noncontrolling Interest - Inc Acc -----------------Income Before Extraordinary Items & Discontinued Operations Preferred Dividends -----------------Available for Common Savings Due to Common Stock Equivalents -----------------Adjusted Available for Common Extraordinary Items Discontinued Operations -----------------Adjusted Net Income Income to Company Incl Extraordinary Items & Disc Ops Earnings Per Share Basic Excluding Extra Items & Disc Op Earnings Per Share Basic Including Extra Items & Disc Op Earnings Per Share DilutedExcluding Extra Items & Disc Op Earning Per Share Diluted Including Extra Items & Disc Op EPS Basic from Operations EPS Diluted from Ops Dividends Per Share Com Shares for Diluted EPS Com Shares for Basic Eps ANNUAL INCOME STATEMENT KR 5411 30101030 Jan-16 $ 109,830.00 $ 85,496.00 -----------------$ 24,334.00 Jan-15 $ 108,465.00 $ 85,512.00 -----------------$ 22,953.00 Jan-14 $ 98,375.00 $ 78,138.00 -----------------$ 20,237.00 Jan-13 $ 96,751.00 $ 76,858.00 -----------------$ 19,893.00 Jan-12 $ 90,374.00 $ 71,494.00 -----------------$ 18,880.00 Jan-11 $ 82,189.00 $ 63,927.00 -----------------$ 18,262.00 Jan-10 $ 76,733.00 $ 58,958.00 -----------------$ 17,775.00 Jan-09 $ 76,000.00 $ 58,564.00 -----------------$ 17,436.00 Jan-08 $ 70,235.00 $ 53,779.00 -----------------$ 16,456.00 Jan-07 Jan-06 $ 66,111.00 $ 60,553.00 $ 50,115.00 $ 45,565.00 -----------------$ 15,996.00 $ 14,988.00 $ 18,669.00 $ 17,781.00 $ 15,809.00 $ 15,592.00 $ 15,011.00 $ 14,462.00 $ 14,046.00 $ 13,518.00 $ 12,799.00 $ 12,488.00 $ 11,688.00 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------$ 5,665.00 $ 5,172.00 $ 4,428.00 $ 4,301.00 $ 3,869.00 $ 3,800.00 $ 3,729.00 $ 3,918.00 $ 3,657.00 $ 3,508.00 $ 3,300.00 $ 2,089.00 -----------------$ 3,576.00 $ 482.00 $ $ -----------------$ 3,094.00 $ 1,045.00 -----------------$ 2,049.00 $ 10.00 ------------------ $ 1,948.00 -----------------$ 3,224.00 $ 488.00 $ $ (87.00) -----------------$ 2,649.00 $ 902.00 -----------------$ 1,747.00 $ 19.00 ------------------ $ 1,703.00 -----------------$ 2,725.00 $ 443.00 $ @CF -----------------$ 2,282.00 $ 751.00 -----------------$ 1,531.00 $ 12.00 ------------------ $ 1,652.00 -----------------$ 2,649.00 $ 462.00 $ $ 115.00 -----------------$ 2,302.00 $ 794.00 -----------------$ 1,508.00 $ 11.00 ------------------ $ 1,638.00 -----------------$ 2,231.00 $ 435.00 $ $ (953.00) -----------------$ 843.00 $ 247.00 -----------------$ 596.00 $ (6.00) ------------------ $ 1,600.00 -----------------$ 2,200.00 $ 448.00 $ $ (18.00) -----------------$ 1,734.00 $ 601.00 -----------------$ 1,133.00 $ 17.00 ------------------ $ 1,525.00 -----------------$ 2,204.00 $ 502.00 $ $ (1,113.00) -----------------$ 589.00 $ 532.00 -----------------$ 57.00 $ (13.00) ------------------ $ 1,442.00 -----------------$ 2,476.00 $ 485.00 $ $ (25.00) -----------------$ 1,966.00 $ 717.00 -----------------@NA @CF ------------------ $ 1,356.00 -----------------$ 2,301.00 $ 474.00 $ $ -----------------$ 1,827.00 $ 646.00 -----------------@NA $ ------------------ $ 1,272.00 -----------------$ 2,236.00 $ 488.00 $ @CF -----------------$ 1,748.00 $ 633.00 -----------------@NA $ ------------------ $ 2,039.00 $ -----------------$ 2,039.00 $ 1,728.00 $ -----------------$ 1,728.00 $ 1,519.00 $ -----------------$ 1,519.00 $ 1,497.00 $ -----------------$ 1,497.00 $ 602.00 $ -----------------$ 602.00 $ 1,116.00 $ -----------------$ 1,116.00 $ 70.00 $ -----------------$ 70.00 $ 1,249.00 $ -----------------$ 1,249.00 $ 1,181.00 $ -----------------$ 1,181.00 $ 1,115.00 $ $ -----------------$ 1,115.00 $ 958.00 ### $ (18.00) -----------------$ 2,021.00 $ $ -----------------$ 2,021.00 $ (17.00) -----------------$ 1,711.00 $ $ -----------------$ 1,711.00 $ (12.00) -----------------$ 1,507.00 $ $ -----------------$ 1,507.00 $ (12.00) -----------------$ 1,485.00 $ $ -----------------$ 1,485.00 $ (4.00) -----------------$ 598.00 $ $ -----------------$ 598.00 $ (7.00) -----------------$ 1,109.00 $ $ -----------------$ 1,109.00 $ (1.00) -----------------$ 69.00 $ $ -----------------$ 69.00 $ -----------------$ 1,249.00 $ $ -----------------$ 1,249.00 $ -----------------$ 1,181.00 $ $ -----------------$ 1,181.00 $ -----------------$ 1,115.00 $ $ $ -----------------$ 1,115.00 $ ### @NA @NA 1,265.00 $ $ 2,035.00 510.00 ### @CF $ 1,525.00 $ 567.00 -----------------@NA ### 958.00 958.00 ### ### 958.00 $ 2,049.00 $ 1,747.00 $ 1,531.00 $ 1,508.00 $ 596.00 $ 1,133.00 $ $ 2.09 $ 1.75 $ 1.47 $ 1.39 $ 0.50 $ 0.88 $ 0.05 $ 0.96 $ 0.86 $ 0.78 $ 0.66 $ 2.09 $ 1.75 $ 1.47 $ 1.39 $ 0.50 $ 0.88 $ 0.05 $ 0.96 $ 0.86 $ 0.78 $ 0.66 $ 2.06 $ 1.72 $ 1.45 $ 1.38 $ 0.50 $ 0.87 $ 0.05 $ 0.95 $ 0.85 $ 0.77 $ 0.65 $ $ $ $ 2.06 2.09 2.06 0.40 980.00 966.00 $ $ $ $ 1.72 1.78 1.76 0.34 994.00 980.00 $ $ $ $ 1.45 1.45 1.42 0.31 1,040.00 1,028.00 $ $ $ $ 1.38 1.33 1.32 0.25 1,073.20 1,066.80 $ $ $ $ 0.50 1.01 1.00 0.22 1,186.40 1,179.20 $ $ $ $ 0.87 0.88 0.88 0.19 1,276.60 1,270.40 $ $ $ $ 57.00 @NA $ 0.05 0.86 0.85 0.18 1,300.00 1,294.00 $ $ $ $ 0.95 0.96 0.96 0.17 1,318.00 1,304.00 $ $ $ $ 0.85 0.82 0.81 0.14 1,396.00 1,380.00 $ $ $ $ @NA 0.77 0.80 0.79 0.10 1,446.00 1,430.00 $ $ $ $ 0.65 0.67 0.66 1,462.40 1,449.00 Kroger Company DuPont Analysis Net Profitability Total Asset Turnover Equity Multiplier Return on Equity Jan-16 Financial Analysis Jan-15 Jan-14 Jan-13 Jan-12 1.86% 3.408 5.266 1.59% 3.625 5.522 1.54% 3.648 5.613 1.55% 4.021 5.884 0.67% 3.847 5.071 33.32% 31.89% 31.62% 36.60% 13.00% 22.16% 3.26% 1.86% 6.33% 33.32% 21.16% 2.97% 1.59% 5.78% 31.89% 20.57% 2.77% 1.54% 5.63% 31.62% 20.56% 2.74% 1.55% 6.22% 36.60% 20.89% 2.47% 0.67% 2.56% 13.00% 73.220 4.985 14.422 25.308 5.853 4.812 3.408 91.071 4.008 15.083 24.200 6.233 5.153 3.625 90.794 4.020 14.474 25.218 6.193 5.297 3.648 96.751 3.773 14.982 24.362 6.595 5.892 4.021 100.751 3.623 14.185 25.731 6.317 5.642 3.847 0.763 0.226 0.093 7.419 15.951 22.883 0.781 0.221 0.110 6.607 17.225 21.190 0.825 0.231 0.127 6.151 16.724 21.825 0.720 0.203 0.108 5.734 17.370 21.013 0.805 0.211 0.107 5.129 16.747 21.795 0.799 0.417 3.986 0.822 0.449 4.615 0.816 0.450 4.427 0.829 0.381 4.850 0.831 0.443 4.919 Profitability Analysis Gross Profitability Operating Profitability Net Profitability Return on Assets Return on Equity Efficiency Analysis Receivable Turnover Receivable DSO Inventory Turnover Inventory DCO Net PP&E Turnover Net Fixed Asset Turnover Total Asset Turnover Liquidity Analysis Current Ratio Quick Ratio Cash Ratio Times Interest Earned Accounts Payable Turnover Accounts Payable DCO (DPO) Leverage Ratio Total Debt to Total Assets Long Term Term Debt to Total Assets Total Debt to Total Equity Jan-11 Jan-10 Jan-09 Jan-08 Jan-07 Jan-06 1.36% 3.528 4.567 0.09% 3.314 4.593 1.64% 3.340 4.510 1.68% 3.228 4.424 1.69% 3.171 4.477 1.58% 21.87% 1.39% 24.76% 24.01% 23.95% 22.22% 2.68% 1.36% 4.79% 21.87% 23.16% 2.87% 0.09% 0.30% 1.39% 22.94% 3.26% 1.64% 5.49% 24.76% 23.43% 3.28% 1.68% 5.43% 24.01% 24.20% 3.38% 1.69% 5.35% 23.95% 93.716 3.895 12.956 28.171 5.855 5.214 3.528 82.820 4.407 12.080 30.214 5.665 4.849 3.314 87.861 4.154 12.058 30.271 5.924 4.873 3.340 89.815 4.064 11.365 32.116 5.786 4.738 3.228 90.316 4.041 11.020 33.121 5.713 4.643 3.171 0.944 0.289 0.185 4.911 15.767 23.149 0.966 0.258 0.140 4.390 15.301 23.855 0.945 0.241 0.117 5.105 14.880 24.529 0.819 0.196 0.106 4.854 13.757 26.531 0.891 0.209 0.106 4.582 13.663 26.715 0.963 0.133 0.031 3.990 0.775 0.431 3.437 0.788 0.454 3.707 0.777 0.444 3.484 0.780 0.390 3.538 0.768 0.411 3.309 0.786 0.458 3.666 24.75% 3.36% 1.58%