Financial Ratio Analysis Template for Cooperatives.

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