Financial Ratio Analysis Template for Cooperatives This template is designed to assist managers and board members in calculating and interpretating standard financial ratios. Up to five years of financial statement information can be entered on the income statement and balance sheet worksheets. The ratio worksheet calculates common financial ratios and provides industry benchmarks. A "red light" "green light" analysis illustrates which ratios are unacceptable (red light) or exceeding standards (green light). Calculated ratios close to industry standards do not trigger either a "red light" or a "green light". Other sheets on the template provides graphs of the common ratios. input cell color Input information is entered in the green cells on the income statement and balance sheet pages. Calculation cells are protected. The template can be modified by turning off the protection. Before modifying the template you should save a master copy under a different file name.Cooperative Name Case Study Cooperative Statement of Income and Expenses 2004 2003 2002 2001 2000 Average Sales Grain 5,156,395 $ 5,881,151 $ 3,302,963 $ 4,689,774 $ 4,689,774 $ 4,744,011 $ Farm Supply 3,664,765 $ 3,345,770 $ 2,946,158 $ 3,028,085 $ 3,028,085 $ 3,202,573 $ Total Sales 8,821,160 $ 9,226,921 $ 6,249,121 $ 7,717,859 $ 7,717,859 $ 7,946,584 $ Costs of Goods Sold Grain 4,686,341 $ 5,604,171 $ 2,925,217 $ 4,183,877 $ Costs of Goods Sold Farm Supply 3,202,767 $ 2,956,050 $ 2,616,553 $ 2,697,462 $ Gross Margin Grain 470,054 $ 276,980 $ 377,746 $ 505,897 $ 4,689,774 $ Gross Margin Farm Supply 461,998 $ 389,720 $ 329,605 $ 330,623 $ 3,028,085 $ Gross Margin on Grain and Farm Supply 932,052 $ 666,700 $ 707,351 $ 836,520 $ 7,717,859 $ Other Income Storage and Handling 73,886 $ 56,888 $ 108,413 $ 86,554 $ 86,554 $ Feed milling 33 $ 7,310 $ 45,911 $ 45,911 $ Custom Application 182,641 $ 187,928 $ 215,338 $ 157,839 $ 157,839 $ Petroleum Revenues 28,686 $ 31,687 $ 40,667 $ 39,685 $ 39,685 $ Finance Charge 24,051 $ 21,315 $ 20,588 $ 23,274 $ 23,274 $ Other Income #1 (feed truck) 1,038 $ 1,065 $ 12,670 $ 12,362 $ 12,362 $ Other Income #2 (misc) 2,867 $ 1,215 $ 922 $ 6,290 $ 6,290 $ Gain (loss) on sale of fixed assets 5,170 $ 220 $ (1,387) $ (1,387) $ Total Other Income 313,169 $ 305,301 $ 406,128 $ 370,528 $ 371,915 $ Total Gross Income 1,245,221 $ 972,001 $ 1,113,479 $ 1,207,048 $ 8,089,774 $ Operating Expenses Payroll and Benefits 444,199 $ 431,200 $ 404,860 $ 409,602 $ 409,602 $ Fixed Expenses Depreciation and Amoritzation 156,948 $ 177,105 $ 177,140 $ 177,070 $ 177,070 $ Interest 512,382 $ 499,665 $ 469,767 $ 502,189 $ 502,189 $ Insurance Property Taxes 29,999 $ 31,353 $ 32,990 $ 29,564 $ 29,564 $ Total Fixed Expense 699,329 $ 708,123 $ 679,897 $ 708,823 $ 708,823 $ Utilities Truck Repairs and Supplies Advertising Office Supplies Rent Bad Debt Provision (23,500) $ (26,000) $ (26,000) $ (30,000) $ (30,000) $ Telephone Other Operating Expenses Total Other Expense Total Operating Expenses 1,143,528 $ 1,139,323 $ 1,084,757 $ 1,118,425 $ 1,118,425 $ Local Savings 101,693 $ (167,322) $ 28,722 $ 88,623 $ 6,971,349 $ Patronage Refunds Received 47,769 $ 43,012 $ 45,951 $ 16,508 $ 16,508 $ Other non-local income (expense) 10,233 $ 9,474 $ (127,243) $ (49,985) $ (49,985) $ Savings before Income Tax 159,695 $ (114,836) $ (52,570) $ 55,146 $ 6,937,872 $ Case Study Cooperative Balance Sheet Assets 2004 2003 2002 2001 2000 Current Assets Cash $37,610 $135 $3,214 4736 4736 Accounts Receivable $470,527 $430,237 $293,507 258,789 $ 258,789 $ Allowance for Doubtful -$23,500 -$26,000 -$26,000 (30,000) $ (30,000) $ Notes Receivable $10,030 $29,344 $12,534 8,578 $ 8,578 $ Sales Contracts Recievable $351,879 $21,992 $532,527 46,296 $ 46,296 $ Accrued Storage $42,756 $18,635 $56,947 57,757 $ 57,757 $ Other Receivable $148,701 $21,608 $43,643 202,827 $ 202,827 $ Grain Inventory $1,255,418 $97,753 $328,782 257,389 $ 257,389 $ Farm Supply Inventory $382,982 $341,789 $301,624 366,829 $ 366,829 $ Prepaid income taxes $3,800 $3,800 $2,800 6,880 $ 6,880 $ Prepaid Expenses $76,588 $24,512 $39,659 104,498 $ 104,498 $ Total Current Assets $2,756,791 $963,805 $1,589,237 1,284,579 $ 1,284,579 $ Long-term Assets Property Plant and Equipment at Cost $3,427,920 $3,400,704 $3,373,115 3,265,158 $ 31,590,419 $ Accumulated Depreciation $2,699,029 $2,542,081 $2,367,424 2,192,564 $ 17,916,299 $ Net Property Plant and Equipment 728,891 $ 858,623 $ 1,005,691 $ 1,072,594 $ 13,674,120 $ Other Assets $315,299 $317,115 $300,362 197,935 $ 16,171 $ Total Local Assets 3,800,981 $ 2,139,543 $ 2,895,290 $ 2,555,108 $ 14,974,870 $ Investments in Cooperatives $209,926 $172,911 $680,238 650,949 $ 12,833,530 $ Total Assets 4,010,907 $ 2,312,454 $ 3,575,528 $ 3,206,057 $ 27,808,400 $ Case Study Cooperative Balance Sheet Liabilities and Members Equity 2004 2003 2002 2001 2000 Current Liabilities Notes Payable 1,863,369 $ 319,892 $ 744,723 $ 474,336 $ 474,336 $ Current Portion of Long Term Note 97,776 $ 97,776 $ 97,776 $ 78,180 $ 78,180 $ Secured Operating Loan Accounts Payable 186,839 $ 148,539 $ 196,748 $ 72,764 $ 72,764 $ Trade Grain 37,162 $ 85,725 $ 85,725 $ 20,437 $ 20,437 $ Accrued Payables and Taxes 94,218 $ 17,295 $ 20,539 $ 20,170 $ 20,170 $ Patron Refunds Payable Total Current Liabilities 2,242,202 $ 583,502 $ 1,096,948 $ 751,612 $ 751,612 $ Deferred Taxes and Liabilities Long Term Debt, Excluding Current Portion 306,322 $ 404,098 $ 501,874 $ 403,650 $ 403,650 $ Secured Note 4,000 $ 4,000 $ 4,000 $ 8,641 $ 8,641 $ Total Long Term Debt 310,322 $ 408,098 $ 505,874 $ 412,291 $ 8,641 $ Total Liabilities 2,552,524 $ 991,600 $ 1,602,822 $ 1,163,903 $ 760,253 $ Members Equities Common Stock 68,800 $ 1,074,700 $ 1,102,400 $ 1,118,200 $ 1,118,200 $ Allocated Patranage 789,382 $ 233,713 $ 229,174 $ 209,815 $ 209,815 $ Unallocated Reserve 600,201 $ 12,441 $ 641,132 $ 693,702 $ 693,702 $ Patronage dividends payable 20,437 $ 20,437 $ Total Members Equity 1,458,383 $ 1,320,854 $ 1,972,706 $ 2,042,154 $ 2,021,717 $ Total Liability and Equity 4,010,907 $ 2,312,454 $ 3,575,528 $ 3,206,057 $ 2,781,970 $ RATIO ANALYSIS FOR: Case Study Cooperative Red Flag Green Flag Analysis to the right 2004 2003 2002 2001 2000 STANDARD CALCULATION Profitability Ratios Sales Trend -4.40% 47.65% -19.03% 0.00% -2.88% > 5% (Current Sales-Prior Sales)/Prior Sales Gross Margin 10.57% 7.23% 11.32% 10.84% 100.00% > 8% Gross Margin/Sales Local Savings Margin 1.15% -1.81% 0.46% 1.15% 90.33% > 1.5% Local Savings/Sales Local Savings to Local Assets 2.68% -7.82% 0.99% 3.47% 46.55% > 8% local savings/local assets Return on Local Equity 8.15% -14.58% 2.22% 6.37% -64.48% > 10% local savings/members equity Expense Ratios Personnel Cost/Gross Margin 35.67% 44.36% 36.36% 33.93% 5.06% < 35% for grain coops, 45% for Farm Supply Fixed Expenses/Gross Margin 56.16% 72.85% 61.06% 58.72% 8.76% < 25-30 Other Expense /Gross Margin 0.00% 0.00% 0.00% 0.00% 0.00% <25 Operating Expense to Sales 12.96% 12.35% 17.36% 14.49% 14.49% < 10% Bad Debt/Sales -0.64% -0.78% -0.88% -0.99% -0.99% < .25% Efficiency Ratios Total Asset Turnover 2.20 3.99 1.75 2.41 0.28 > 4 to 5 Sales/Total Assets Total Assets/Sales 45.47% 25.06% 57.22% 41.54% 360.31% < 20% Farm supply inventory turnover 9.57 9.79 9.77 8.25 8.25 > 7 to 10 Average Farm Supply Sales Outstanding 44.94 45.01 34.87 29.91 29.91 < 30 to 45 days Accounts Rec./Daily Farm Supply Sales Accounts over 60 days < 20% over 60 days Fixed Assets/Total Assets 18.17% 37.13% 28.13% 33.46% 49.17% < 33% Liquidity Ratios Working Capital 514,589 $ 380,303 $ 492,289 $ 532,967 $ 532,967 $ 500,000 $ Current Ratio 1.23 1.65 1.45 1.71 1.71 > 1.5 Farm Supply, 2.0 Grain Coops Working Capital to Sales 5.83% 4.12% 7.88% 6.91% 6.91% > 7% 1.5% grain sales + 2.5% of Farm supply sales Debt Ratios Debt/Assets 7.74% 17.65% 14.15% 12.86% 0.03% < 50% L.T. Debt/Total Assets Debt/Equity 21.28% 30.90% 25.64% 20.19% 0.43% < 50% L.T. Debt/Total Equity Debt/Local Equity 24.86% 35.55% 39.14% 29.64% -0.08% <30% to 40% L.T. Debt/Net Worth-Investment in Coops Ownership Ratio ME/TA 36.36% 57.12% 55.17% 63.70% 7.27% > 50-60% Total Equity/Total Assets Local Equity/Total Equity 85.61% 86.91% 65.52% 68.12% -534.78% > 80% Working capital 514589 380303 492289 532967 532967 1.5% grain sales + 2.5% of Farm supply salesWorking Capital $514,589 $380,303 $492,289 $532,967 $532,967 $500,000 $-$100,000 $200,000 $300,000 $400,000 $500,000 $600,000 2004 2003 2002 2001 2000 STANDARD Working CapitalExpense Ratio -10.00% 0.00% 10.00% 20.00% 30.00% 40.00% 50.00% 60.00% 70.00% 80.00% 2004 2003 2002 2001 Year Percentages Personnel Cost/Gross Margin Fixed Expenses/Gross Margin Operating Expense to Sales Bad Debt/Sales Other Expense /Gross MarginProfitability -5.00% 0.00% 5.00% 10.00% 15.00% 20.00% 25.00% 30.00% 35.00% 40.00% 45.00% 50.00% 55.00% 60.00% 65.00% 70.00% 75.00% 2004 2003 2002 2001 Year Percentages Personnel Cost/Gross Margin Fixed Expenses/Gross Margin Other Expense /Gross Margin Operating Expense to Sales Bad Debt/SalesEfficiency 0.00 5.00 10.00 15.00 20.00 25.00 30.00 35.00 40.00 45.00 50.00 2004 2003 2002 2001 Year Percentages Total Asset Turnover Total Assets/Sales Farm supply inventory turnover Average Farm Supply Sales Outstanding Fixed Assets/Total AssetsInventories 0.428571