Loading...
The URL can be used to link to this page
Your browser does not support the video tag.
Home
My WebLink
About
Financial Ratio Analysis for Alaska Native Villages 2001
Environmental Finance Cente Pim ste Y omy meer ai A guidebook and spreadsheet program to help local decision-makers in rural villages evaluate their water utility's financial condition ENVIRONMENTAL FINANCE CENTER BiG 2882S te A: 4 US ER RUS ty de Y. 1910 UNIVERSITY DRIVE BOISE, IDAHO 83725-1935 (208) 426-1567 HTTP://SSPA.BOISESTATE.EDU/EFC Soy @ = Funded by U.S. Environmental Protection Agency @ Produced for Alaska Department of Community & Economic Development ©@ Developed by the Region 10 Environmental Finance Center at Boise State University EPA een Department of fab, Community and Economic Development Environmental Finance Center Boise State University Copyright © 2001, Environmental Finance Center at Boise State University. All rights reserved. No part of this work covered by copyright may be reproduced in any form or by any means ~ graphic, electronic or mechanical, including photocopying, recording, taping, or storage in an information retrieval system — without the prior, written permission of the copyright owner, The information contained within this document is subject to change without notice. The Environmental Finance Center at Boise State University makes no warranty of any kind with regard to this material, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. The Environmental Finance Center at Boise State University shall not be liable for errors contained herein or for incidental consequential damages in connection with the furnishing, performance, or use of this material. Trademarks Ratio 8 and all other trademarks and logos mentioned and/or displayed are trademarks or registered trademarks of the Environmental Finance Center at Boise State University. Microsoft, Microsoft Excel and Screenbeans clipart are registered trademarks of Microsoft Corporation. QuickBooks is a registered trademark of Intuit, Inc. Printing date: October 2001 RE SE ‘The Environmental Finance Center at Boise State University developed and produced the Ratio 8 guidebook with financial support from the U.S. Environmental Protection Agency’s Region 10 Office of Water, grant ID # 00426084-060565, “Building and Measuring TMF Capacity in Alaskan Native Villages,” and by the Alaska Department of Community and Economic Development. Bill Jarocki managed the project and Jon Cecil wrote the text. Sylvia Dana edited the text and designed the guide. Michael Pea and Michael Keith designed the ratio analysis tool and floppy disk software component. We are grateful to the following individuals for their advice, support and encour- agement, in particular Michael Black, Scott Ruby, Athena Logan, Irene Catalone, Josie Morrow, and Roxanne Auge of the Alaska Department of Community and Economic Development; Janet Fairchild, Paul Morrison, and James Keen of the Regulatory Commission of Alaska, Keven Kleweno of the Alaska Department of Environmental Conservation and Bill Gissell of the U.S. Environmental Protec- tion Agency (Alaska Office); Cheryl Koonooka of the city of Gambell, Andy Durny of the City of Nulato; Vera Lestenkof and Taunya Bourdon of the City of Koyukuk. We also appreciate the work of others who have contributed to the development of ratio analysis, including: e The University of Minnesota’s Fiscal Health Education Program within the Department of Applied Economics e Dr. Kenneth W. Brown’s 10 Point Test developed at Southwest Missouri State University ¢ The International City/County Management Association’s publication on evaluating financial condition e _EPA’s Office of Water © US. Department of Agriculture Rural Utilities Service Water and Wastewater Program’s Financial Analysis Course, particularly the financial analysis piece The Environmental Finance Center welcomes comments and suggestions for improvement to this guidebook. The mention of trade names or commercial products does not constitute endorsement or recommendation for use. Ratio 8 is offered on an “as-is” basis, with no warranty expressed or implied. The Region 10 Environmental Finance Center at Boise State University provides assistance, training and tools to help with the “How to Pay” issues of environmental protection. The Center at Boise State University serves the states of Alaska, Idaho, Oregon, and Washington. For more information about the Center and its services, please contact us at (208) 426-1567 or visit us on the web at http://sspa.boisestate.edu/efc Table of contents <4. Terms to know Ratio 8 What is Ratio 8? .......scccecssseseseesessesesessseseessssessseeessesseneeeneaeeaeseeassneaecaeseesensesesaeseeaesecsesneseseeaeseesenaesetaes Why is financial assessment important? . Meeting your goals ......... What does Ratio 8 do?.... How do communities key in? Can Ratio 8 benefit you?.... Using Ratio 8 Installing & running Ratio 8 Getting started ............. Tips for using Ratio 8 Trend analysis..... QauhRWWWNNN Operating RatiO........sssssssesecsssseseseeesecesssssseesssesessseseeseneaesesesessuscaesesesecaeassesecaraesesesenaeseaeaesesesesataceeeanaeaees 9 Per Capita Revenue Ratio.. Current Ratio. Sales Ratio .... Expense Ratio Quick Ratio.... Debt Ratio..... a Receivables Ratio .......ccscsesssssscsecssseeseeessesseessseesssessesseseesesessesesaesesseseeaesseseeseseeseseeseeaesesseseeaeseeseeseneeaneetes 16 Appendix A: Chart of accounts & ratio worksheets.. 17 Appendix B: Case study ..........000++ Appendix C: Case study exercise... crn WT SSN SA MS ES SE TT Endnotes & RefCrence .......sscsscsssccsssssesssenessseeseesseesscersensseesseeeseesseeessesaeeuseeesneeeseesseesseeesesseeseneesseseeees 32 «)Terms to know... Accounts payable The amount owed to suppliers and other business cteditots for materials, merchandise, insurance, and services acquired. Accounts receivable The amount due from customers for water and/or wastewater services sold to customers, but cash has not yet been collected from the customers. Assets Anything that the utility system owns (recorded at cost). These commonly include cash, accounts receivable, inventories, land, buildings and other assets. Cash Money on deposit in the bank and petty cash. Chart of accounts A listing of the typical transactions of an organization. These transactions ate organized into accounts. In double-entry accounting, each transaction affects at least two accounts. The chart of accounts is usually coded for efficient classification. Current assets Cash and other assets that can be converted into cash during the normal operating cycle of the business, generally a year. Current liabilities The money owed that is due within one year. Fixed assets Items secured for long-term use in business. They are usually not for resale, and are recorded at cost less depreciation. Inventory The amount of finished product, work-in-progress and other raw material and supplies on hand. Liabilities Claims of creditors, i.e. the debts owed. Long-term liabilities Those debts or other obligations that are not due within one year. For example, long-term mortgages on property owned, long-term loans, and long-term purchase contracts. Range A range is an indicator that allows the user to identify if a ratio’s value can be used for analysis. A value outside of the range cannot be used for analysis and may be the result of data entry error. Ratio 8’s ratios typically have a range between 0 and 1 or greater than 1. Ratio A financial ratio is an expression of the relationship between two items selected from various financial records (e.g., balance sheet and income statement). Ratio analysis helps you assess the weak and strong points of your utility financial and managerial system. Total assets Current, fixed assets, and other assets totaled. Total liabilities Total of current and fixed liabilities. Ratio 8 — Environmental Finance Center at Boise State University Page 1 ASRatio Ka What is Ratio 8? Puzzled about your water utility's money “matters? Confused about how to evaluate your utility’s financial condition? Now it can all make sense. Ratio 8 is a simple financial assessment tool to help you analyze your community water system’s financial condition. Using eight ratio formulas, Ratio 8 helps you assess the true costs of fi- nancing your public water system, as well as look for trends and find ways to make improvements. Why is financial assessment important ? Providing safe drinking water is the most necessary en- deavor your community can undertake. To ensure you are providing safe drinking water, your water system needs to have the financial resources to renew and re- place equipment, test water for pollutants, take correc- tive actions and meet other service and financial obliga- tions — to constituents, creditors, consumers, taxpayers and suppliers. To deliver safe water, your water utility needs to have a secure water fund. Safe drinking water provides for a healthy community and envi- A strong water fund keeps the water utility afloat. Awater utility in good financial condition pro- 4 duces safe water. To ensure a secure water fund, your utility should have three primary “capacities”: managerial ability, financial resources and technical expertise. Through the efforts of the Alaska Department of Envi- ronmental Conservation, the state developed technical, managerial and financial (TMF) capacity guidelines to improve Alaska’s public water systems (see Table 1). ¢@ Technical Capacity — Does your water utility provide safe, clean water in compliance with all applicable state and federal regulations, and will it be able to continue meeting these standards in the future? @ Managerial Capacity — Does your water utility have capable and trained staff? e Financial Capacity — Does your water utility have a budget and enough revenues to cover operating costs, repairs and replacements? Systems with good TMF capacity are more likely to provide safe, reliable and affordable drinking water. Using TMF capacity measures, you can track your water system’s financial ability to do the most critical job in your community — provide safe drinking water. Table 1 — SDWA Capacity Development Provisions' Technical Managerial Financial (er Teel a (ore Toliny Capacity Source Water Ownership Revenue Adequacy Accountability Sufficiency Infrastructure Staffing & Credit Worthiness Adequacy Organization Technical Effective External | Fiscal Management Knowledge/ Linkages & Controls Implementation Meeting your goals Helping you achieve the goal of improving your water system’s financial condition, this guidebook and spread- sheet program are designed to help you: e Better understand your water utility’s financial con- dition e Analyze information about true costs of financing your public water utility © Develop integrated information for making long- range decisions about your water utility For practical application, the appendices include a case study, an exercise and information about trend analysis. Ratio 8 — Environmental Finance Center at Boise State University Page 2 What does Ratio 8 do? Ratio 8 is an easy-to-use financial assessment tool, designed to com- pliment any accounting and report- ing system. Ratio 8 can be used by decision- makers to proactively improve fi- nancial capacity. Financial capacity, as part of the TMF strategy, is split into three areas, in- cluding revenue sufficiency, creditworthiness, and fiscal management and controls. Transforming traditional approaches of measuring cred- itworthiness, Ratio 8 focuses on the following financial areas: © Operations © Revenue s © Liability O Sales © Expense @ Assets @ Debt ® Accounts Receivable The eight ratios in Ratio 8 translate into financial indica- tors providing insight about your community water sys- tem’s debt burden, financial operations, socioeconomic conditions, and user fees (See Table 2). In addition to calculating financial ratios, Ratio 8 creates a trend analysis graph for each of the ratios, based on three, five or ten years of data. Trend analysis allows you to watch for positive and negative trends, identifying strengths and weaknesses of your utility’s financial con- dition. How do communities key in? The State of Alaska has collected a large amount o financial data, such as au- @ dits and financial state- ments, mostly from municipalities. Still, a lack of meaningful financial data poses a challenge in evalu- ating the financial condition of Alaska’s communities, including traditional and Indian Reorganization Act councils, unincorporated communities and nonprofit corporations. LA AE AS ATTEN, LTS TA SARE A ARNE ST a ATELY SETAC RAR TARA LAM (TSI AT TS RRR SCT TE Ratio 8 — Environmental Finance Center at Boise State University Table 2 — Financial Capacity Development Risk Matrix Criteria? Financial Capacity REVENUE CREDIT FISCAL MANAGEMENT SUFFICIENCY WORTHINESS & CONTROLS Revenue v. Credit rating Books & Records Expenses Rate structure Access to capital Budgeting & Reporting Billing & Collection | Financial ratios Accounting practices Revenue for depre- | Bonds & Asset valuation ciation & interest Assurances Cost of service Debt to equity ratio | Capital facilities studies planning --- --- Management revenues --- --- Investment strategy Despite the absence of a comprehensive financial data- base, Ratio 8 can help Alaska’s communities begin to analyze their own fiscal capacity over time, paving the way for a water utility to compare itself with other Alaska utilities. Can Ratio 8 benefit you? Ratio 8 helps utility mangers and board members man- age better by improving information. Finding time to consider the long-term financial outlook of a water sys- tem poses a challenge. That’s why Ratio 8 offers a short- cut to getting financial information that leaders need to make good decisions. By analyzing your financial data, you gain insight into the financial stability of your utility. Ratio 8 uses simple ratio calculations and a trend analy- sis tool to help pinpoint potential problems and monitor the financial condition of your village water fund, as well as funds supporting electric utilities, wastewater systems and community refuse. Page 3 To use Ratio 8 you will need a chart of accounts, accurate utility system financial reports, a work- ing knowledge of Micro- soft® Excel™ spread- sheet program, and a personal computer. To begin analyzing your system’s financial condition, you will need to collect data from a chart of accounts — a listing of the typical trans- actions of an organization. These transactions are organ- ized by revenue and expense categories. Many organizations use Intuit® QuickBooks™ to re- cord their chart of accounts; others might use other ac- counting software or keep paper records. The account numbers referenced at the bottom of each ratio page (see Ratio 8 Tools section) coincide with the Ratio 8 standardized chart of accounts and were devel- oped to be used with QuickBooks. QuickBooks, a commercial accounting package, uses a standard chart of accounts prepared when the user first loads the software package into the PC. QuickBooks takes the first- time user through a step-by-step process that allows the user to create a customized chart of accounts, including a balance sheet as well as income, expense, cost of goods sold and non-posted accounts. Then, revenues and expenses can be coded to the appropriate source amount. Note: Possession of QuickBooks accounting software is not a prerequisite to using Ratio 8. However, you need an up-to-date chart of accounts and accurate financial reports. From the chart of accounts, you will collect data to en- ter in the data “Wizard” of the Ratio 8 program. If you do not have access to Excel, you can enter and calculate data by hand using the worksheets provided (see Ap- pendix A). When conducting your financial analysis, think about the following questions as they relate to TMF guidelines. (See Table 2, pg. 3): e Revenue sufficiency — Are the rates high enough to meet your system’s short and long term needs? @ Credit worthiness — Can your organization borrow money when it needs to? e Fiscal management and controls — Does your record keeping system ensure your money is secure and being spent for the right purposes? Installing & running Ratio 8: 0 Insert the Ratio 8 floppy disk into your com- puter’s A: drive. Click “Start” on your Windows taskbar. Click “Programs” and select Microsoft Excel. Click “File” and select “Open.” 6 © © ® Select and open the 3 /" floppy disk. Double- click “Ratio 8” to open the program. Click “Enable Macros.” Click “File” and select “Save As.” Save the program in a file on your hard drive and name it “Ratio8_your organization name.” The “Save As” action will allow you to keep a blank Ratio 8 workbook, saved on the disk as “Ratio 8,” while creating your organization’s own spreadsheet document for your own use. The program is now “installed” on your hard drive, leaving the program on the floppy disk unchanged for future use. 8 Click “Begin” on the cover page of the Ratio 8 Workbook in Excel. Go to the next page & get started! e Ratio 8 — Environmental Finance Center at Boise State University Page 4 Getting started Arial 2 Starting the “Wizard” AL Chart of Accounts Oo Using your chart of accounts, en- 5 Acco ri ter data called for by the Ratio 8 = FB Acet# x aoe vere n | Data Entry Wizard, located on fea tooe 7100 sees asec A a the Excel, Ratio 8 menu bar. 1110 Cash o| Click “Ratio 8” and select “Data iz AR. oI Entry Wizard” as shown in the 1140 Prepaid Expenses The Ratio 8 drop-down menu illustration to the right 1200 Poe. Plant & Equipment helps you easily navigate your a 1210 Land way through the program. Or, J. 1220 Buildings you may prefer to use the tabs_/ ” 1230 Utility System low. 1240 Construction Work in Progress Chart of Accounts PRR ce The “Next” and “Back” but- tons in the Wizard allow you to go back and forth in any order you wish. The first Wizard window to appear, step 1 of 19, asks you to enter a city name, year and population. When you are finished entering the information called for, click the “Next” button at the bottom of the Wizard window. When the second Wizard window appears (see right), enter the data called for, click “Next” and so on. 9 Using the “Wizard” If no data is available for certain items, let a zero stand in. Be aware that insufficient data may affect ratio output. Create charts & graphs Click “Charts” and select ratio (see drop-down menu example > Fee , below). This action will generate 41140 |Pre-paidexpenses | $800] a graphical interpretation of the ea This data was ratio, called an output page (see generated from the right). For printing, see Step 4. Pt \ ghart of accounts wy Macro will automatically calculate ratio Ratio 8 can create up to eight pre-set ratio output pages in graphical format. If your ratio is less, you may need to increase collections. Ratio 8 — Environmental Finance Center at Boise State University Page 5 Save & print Go to “File” and click “Save” to save the entire Ratio 8 workbook (see pg. 4, no. 7 for more information about saving). To print, simply select “Print” in the Ratio 8 drop-down menu. In the “Select sheets to print” win- dow that appears, click inside the check box next to the ratio charts or worksheets you want to print. Then click “OK” (see below). This command will tell your printer to print the selected docu- ment. i Select sheets to print = Li} x zi | ci ci) r ty r c ci x ti cl ci c Sales Ratio worksheet Evaluate Based on data collected over time, Ratio 8 can generate a trend analysis for three, five and ten years of data. A trend analysis illustrates the organization’s fi- nancial strengths and weaknesses. L Sales Ratio chart output page Go to the next page for easy-to-use instructions about conducting a trend analysis Tips -for using Ratio 8- Although it is recommended to enter the data needed to calculate each of the eight ratios, you can use select ratios if you do not have complete financial information to use all of the ratios. Ratio 8 was designed for rural water systems. However, the same financial ratios can be applied to rural wastewater systems, too. It is strongly recommended that you have sepa- rate funds for each utility. Garbage in. Garbage out. The data used by Ratio 8 is only as good as the information col- lected and used to calculate the eight ratios. One of the keys to effective decision-making is access to accurate and timely information such as the financial condition of your rural water utility system. Ratio 8 — Environmental Finance Center at Boise State University Page 6 Trend analysis A trend analysis is a comparison of financial data over time and can help you identify potential problems. Trend analysis answers the following types of questions: 1) Is our utility system financial capacity improving or declining? 2) When did significant changes in financial capacity take place and why? One way to understand financial information is to create a graph. Although Ratio 8 generates a graphical output page for each ratio for individual years, you will need to create your own graph to analyze data over time. The graph below is an example of a three-year history of the Current Ratio (CR) used by a public water system. The years 1999-2001 are compared to determine the relative strength or weakness of utility liquidity, such as cash-on-hand. Here, we want to understand how effec- tively (or not) this particular utility has remained finan- cially secure in meeting its current financial obligations. Ratio 8 and the trend analysis tool To analyze data over three, five or ten years, click “Graphing Analysis” on the Ratio 8 drop-down menu. Then, select the trend you wish to analyze, based on the amount of data entered in Ratio 8. From the example below, we conclude that from 1999 to 2001, this public water system’s Current Ratio im- proved substantially. Liquidity grew from .2 to 1.2 per- cent. The change could be explained by a number of factors, such as increased rate collection while maintaining or reducing current expenses. In general, a trend analysis helps your organization determine changes over time in utility operations. These changes can be compared with other communities similar to your own. To prepare a trend analysis for any one of the eight ratios, follow the instructions in the “Tool box” (above). The trend analysis to the left was generated by clicking “Graphing Analysis” on the Ratio 8 menu and selecting “Three Year Trend.” When you select the “Graphing Analysis” tool, Ratio 8 opens an entirely new workbook in Excel. Select the ratio you want to conduct a trend analysis by clicking on the ratio tabs at the t bottom of the “Trend Analysis Graph” window. Ratio 8 — Environmental Finance Center at Boise State University Page 7 Ratio 8 Tools The following eight ratio Pages will define each ratio and describe how you might use the ratio to look for trends and make decisions for the financial future of your utility. Ratio pages include the ratio formula and the Chart of Accounts code numbers from which the ratio is derived. Ratio 8 — Environmental Finance Center at Boise State University Page 8 (1 JOperations > Operating Ratio (OR) What is the Operating Ratio? 2 -) ? Se ee ic ™@ The Operating Ratio measures the amount of operating revenue versus the Pi Pp g P ro co total amount of operating expenses for a utility system. i) @ The minimum standard for an operating ratio for a utility system is 1.0, t meaning there is enough operating revenue to cover operating expenses. @ m@ A financially healthy utility system needs to maintain an ongoing operating ratio of greater than 1. ™@ The ratio’s value should be greater than 0. How it works... é ™@ An Operating Ratio of 1.25 indicates that revenue is 125 percent of ex- 4 penses and shows good financial condition. m@ A ratio of less than 1 indicates there is insufficient revenue to meet current j expenses. For example, if you had an Operating Ratio of .75, this means your revenue is 75 percent of expenses. In other words, you can only cover three-quarters of expenses. The Formula... 7 ‘ Click the Operating f- Operating Ratio (OR) Ratio button on Ratio 8 menu to view the bar eo OR = OR graph, showing _ the OE dollar amount of how much the utility operat- i 7 ing revenue is greater OR = Utility Operating Revenues | than or less than the utility operating OE = Utility Operating Expenses | “pense. Operating Revenue is account number 4000 Operating Expense is account number 6000 Ratio 8 — Environmental Finance Center at Boise State University Page 9 (2yRevenue > Per Capita Revenue Ratio (PR) What is the Per Capita Revenue Ratio? ™ The Per Capita Revenue ratio measures the amount of revenue based on the current population. m The ratio will change over time, based on population size and changes to revenues and expenses. Ratio 8 Tools M@ The ratio’s range is greater than 0. How it works... é ™ Showing the amount of revenue the water system receives per person, you Y will want to watch this ratio over time. For example, if this ratio is steadily increasing, then your customers will have to spend an increasingly higher percentage of their income for water service. ™ The ratio also reflects the need for operating and capital revenue. If the ratio increases over time, management might consider how to reduce the need for revenue, such as seeking more efficient operations, outsourcing and contracting, and receiving contributed capital. The Formula... : Click the Per Capita Per Capital Revenue (PR) Revenue Ratio button on Ratio 8 menu to view PR = UOR the bar graph, showing Population the dollar amount of how much the utility revenue is spent per person. @: UOR = Utility Revenues Population = Current Population Operating Revenue is account number 4000 For current population data see http://www.dced.state.ak.us/mra/CF_COMDB.htm Ratio 8 — Environmental Finance Center at Boise State University Page 10 (3yLiability > Current Ratio (CR) What is the Current Ratio? La) ° P f°] ™ The Current Ratio measures liquidity, calculated by taking the amount of 5 current assets divided by the amount of current liabilities. ° ™ The Current Ratio is used as a measure of short-term solvency and . demonstrates a utility’s ability to meet its current obligations in a timely 4 manner. ™ High ratios are favorable. @ The ratio’s range is greater than 0. How it works... é ™ For many business enterprises and operations a current ratio of 2.0 or 4 higher indicates a strong financial condition. Many successful water utilities have a Current Ratio greater than 1.0. To accomplish this, you might use a y cash budget, an amount of money budgeted in excess of operating ex- penses for cash management purposes. It is recommended to have a cash budget of 1.5 months worth of operating expenses. ™ Management should adjust the budget if the ratio’s trend line is moving below 1.0 over time. The Formula... sg: Current Ratio (CR) | lh cur at to view the pie chart, oe CR = CA showing what percent- CL age of current assets is greater than or less than current liabilities. CA = Current Assets CL = Current Liabilities Current Assets is account number 1100 Current Liabilities is account number 2100 Ratio 8 — Environmental Finance Center at Boise State University Page 11 (4ySales > Sales Ratio (SR) What is the Sales Ratio? 4 ? °) ™ The Sales Ratio measures user charges as a percent of total revenue. 2 m@ A financially healthy utility should have a ratio close to 1, such as .7. re) M@ The ratio’s range is 0-1. fo) PS A 4 How it works... é m@ A ratio of zero suggests revenue is coming from non-operating revenue CG other than user charges. This suggests utility management may be overly reliant on outside revenue sources. y m@ A high ratio or a ratio close to 1 indicates that revenue is coming from usage charges. a A high ratio combined with low receivables indicates users are accustomed to supporting the water system by paying their bills on time. The Formula... see Sales Ratio (SR) Click the Sales Ratio pm button on Ratio 8 menu v to view the pie chart, eo SR =UC showing usage charges TR and non-operating revenue as a percentage of total revenue. UC = Usage Charges TR = Total Revenue Usage Charge is account number 4000 Total Revenue is account number 4000 + 5000 Ratio 8 — Environmental Finance Center at Boise State University Page 12 (SyExpense > Expense Ratio (ER) Ratio 8 Tools What is the Expense Ratio? ? Re dn ™ The Expense Ratio measures the percent of operating expense that makes up total expense. @ Lower ratios are favorable, indicating utility infrastructure is being main- tained adequately. m@ A high ratio shows that most of the revenue is being used for operations and little remains for capital replacement and renewal. ™@ The ratio’s range is 0-1. How it works... é ™ An Expense Ratio of .5 indicates operating expenditures make up at least 4 50 percent of total expenditures. The Formula... . Click the Expense Ratio l- Expense Ratio (ER) button on Ratio 8 menu ? to view the pie chart, oe ER = OE showing the percent TE that expenses, including operating, general and . 7 administration and non- OE = Operating Expenditures operating, make up total expense. TE = Total Expenditures Operating Expenditure is account number 6000 Total Expenditure is account number 6000 + 7000 + 8000 Ratio 8 — Environmental Finance Center at Boise State University Page 13 (6yAssets > Quick Ratio (QR) What is the Quick Ratio? ™ The Quick Ratio measures the liquidity of the utility based on its most liq- uid assets, including cash, accounts receivable, short-term notes receivable and short-term investments in marketable securities. m™ A ratio greater than 1 suggests sufficient liquidity. Ratio 8 Tools @ = The ratio’s range is greater than 0. How it works... @ M@ The ratio excludes inventory. 4 ™@ The ratio of 1 is acceptable. If your ratio is less, you may need to increase revenues and/or collections. The Formula... Quick Ratio (QR) Click the Quick Ratio ~t- _ button on Ratio 8 menu QR = CA-I-PE to view the pie chart, oe CL showing the percent that CA-I-PE is greater CA = Current Assets than or less than current liabilities. I = Inventory PE = Prepaid Expenses CL = Current Liabilities Current Assets is account number 1100 Inventory is account number 1130 Prepaid Expense is account number 1140 Current Liabilities is account number 2100 Ratio 8 ~ Environmental Finance Center at Boise State University Page 14 [Debt > Debt Ratio (DR) What is the Debt Ratio? 2) ° P pOSISAOARIRDOO AB AEAMARABCpSEIn Ae On AE DREEEOmREEmEen SEA SEE mn ER EE CRUREeRORO) 2 ™@ The Debt Ratio measures to what extent a utility’s assets are financed 0 through loans. ° @ A low ratio is most favorable. . @ The ratio’s range is 0-1. & How it works... é @ A Debt Ratio of .25 indicates only 25 percent of a utility’s assets are debt 4 financed, while 75 percent remains as equity. @ When a utility’s debt ratio is high (close to 100 percent), meaning that total y liabilities and assets are equal, this implies a significantly higher proportion of revenues are needed to repay debt. @ A low debt ratio is likely if a system’s capital improvements are financed through grants, other transfer payments and/or contribution of capital. The Formula... : Click the Debt Ratio oe Debt Ratio (DR) button on Ratio 8 menu to view the pie chart, eo DR=TL showing the percent TA that total assets are TL = Total Liabilities TA = Total Assets Total Liabilities is account number 2000 Total Assets is account number 1000 Ratio 8 — Environmental Finance Center at Boise State University greater than total liabilities, or vice-versa. Page 15 'gAccounts Receivable > Receivables Ratio (RR) What is the Receivables Ratio? ™ The Receivables Ratio measures the percent of user charges that still need to be collected. ™ = This ratio helps explain the effects of unpaid utility bills. A low ratio suggest that receivables are collected in a timely manner. Ratio 8 Tools @ The ratio’s range is 0-1. How it works... m@ A low ratio indicates a timely collection of user charges. A ratio of .2 shows 20 percent of the utility’s invoices still need to be collected. m@ If viewed in a trend analysis, the goal should be to lower this ratio over time. The Formula... 7 : i Receivabl Receivables Ratio (RR) ai niuniaiepeniaahionap t- Ratio button on Ratio 8 ? menu to view the pie RR = A/R chart, showing _ the eS UC percent that usage charges is greater than . or less than accounts A/R = Accounts Receivable receivable. UC = Usage Charges Accounts Receivable is account number 1120 Usage Charge is account number 4000 Ratio 8 — Environmental Finance Center at Boise State University Page 16 Appendix A: Ratio 8 chart of accounts & ratio worksheets Contents Chart of accounts A chart of accounts is a cornerstone to a financial management system. It allows you to organize financial data by group code and function. For help with financial manage- ment system issues, contact your local RUBA staff person. Worksheets To assist Ratio 8 users, worksheets for each of the eight ratios allow you to transfer the data for each ratio from QuickBooks (or other method of recording your chart of ac- counts) to the worksheet. The data will generate ratio output pages (see pg. 5, n. 3). The worksheets also illustrate what line items from the chart of accounts comprises each ratio. Worksheets include: ° Operating Ratio Worksheet e Current Ratio Worksheet ° Per Capita Worksheet ° Sales Worksheet ° Expense Worksheet ° Quick Worksheet e Debt Worksheet ° Receivable Worksheet SR LS SSN PL ET TD IE ES RE Ratio 8 — Environmental Finance Center at Boise State University Page 17 Acct. # Account Name $ Amt W000, Assete EEE ern eer Toe eePee Cee Oe pT ttt0 Cash [LO CET as ig TGC PTTL ec ee ee || 1140 | Prepaidexpenses CC | 1200 | Property, Plant& Equipment OECD es) Peer ee ie 0 ee PoC AE cree Treen ITT APE eee Cee pT 1230 Utility System |_| 1240 ConstructionWorkin-Progress PE Ei 2p fe eee PL Geet Pee eee pep Liabilities 2100 Current Liabilities 2110 ———} Payroll Taxes Payable pT 2130 Accrued interestPayable CT NDC ORES TRE 6 eso TEU UT eg ST ree eo a eT 3000 Equity | CS Retained Earnings | tst—iOd Other [| 4000 Revenues ——-.| Lena 4110 Hook-up Fees | tL 141207 Other ee eee | 4200 Residential fel oi90.7cIoceeremiet SSE) Cee er fee Perce eee erence eee! 1 fewest eee ree eee eer 4500 Contractual | 4600 Other ater Utility Non-Operating Revenues por fp Interest Income | __—‘{5200 Gain on Sale of Assets HEE, FECEECE worms gee cera eer ee P-L ee ieee el we Ce CeCe Oe ICP GC [ec c [| 5320 | intergovernmental ————SSSCSCSCSCSCSCCSCSCSCSCC. fener nie essen treme oe Cte Ce et ee eee ee PAPER Cre eteah Oe set Oe poe ee PLL eso CEL pommel eee ee ee ce eC Water Utility Operating Expenses ake armen eee eee eee pCa fe] ETE | Deemer) alee eee ee Ee | sf 6130] Payroll Taxes Payable WE eo} | 6140 | Workers Compensation eC ee [—} 6160__| RetirementiPension __———SSSSSCSCSCSCSCSCSCCSCSCSCS | | 6160 |_Non-Retirement Benefits CE en Certifications aa: | ——s«dYs6180 Other | __|6200 Travel A 2 | Od lame m OL LL Se Acct. # Account Name $ Amt [| 6220 | GroundTransportation Pi ee genie ee CCS Ol Se aioe eee a ee Other Facilities Telephone Rent Electricity Water & Sewer Fuel Oil Repairs/Maintenance Buildings Other Equipment Materials Supplies Parts Other Interest & Late Charges Insurance & Bonding Member Dues & Subscriptions Bank Charges Audit/Accounting/Legal General & Administrative Contractual Capital Improvements Other Engineering and Professional Services Supplies Office & Clerical Postage Copier Chemicals Water Testing Other Salaries Workers Compensation Retirement/Pension Non-Retirement Benefits Village Population Ela Operating Ratio Worksheet Bette UM OPERATING REVENUE 4110 4120 4100 4200 4300 4400 4500 4600 4000 6110 6120 6130 6140 6150 6160 6170 6180 6100 6210 6220 6230 6240 6250 6200 6310 6320 6330 6340 6350 6360 6370 6300 6410 6420 6430 6400 6510 6520 6530 6540 6550 6500 6000 4000 6000 Fees & Service Hook-up Fees Other Total Fees & Service Residential Commercial School District Contractual Other Total Operating Revenue OPERATING EXPENSE Personnel Salaries Stipends Payroll Taxes Payable Workers Compensation Retirement/Pension Non-Retirement Benefits Certifications Other Personnel Total Travel Airfare Ground Transportation Per Diem Training Workshops Other Travel Total Facilities Telephone Rent Electricity Water & Sewer Fuel Oil Repairs/Maintenance Bldgs Other Facilities Total Equipment Materials Supplies Parts Equipment Total Other Interest & Late Charges Insurance & Bonding Member Dues & Subscriptions Bank Charges Audit/Accounting/Legal Other Total Total Operating Expense OPERATING RATIO CALCULATION Total Operating Revenue Total Operating Expense Operating Ratio Soe divided by equals Chart of Current Ratio Worksheet - ak Current Assets 1110 Cash 1120 A/R 1130 Inventories 1140 Prepaid Expenses 1100 Total Current Assets Current Liabilities 2110 A/P 2120 Payroll Taxes Payable 2130 Accrued Interest Payable 2140 Notes Payable 2100 Total Current Liabilities Current Ratio 1100 Current Assets divided by 2100 Current Liabilities equals Current Ratio ee for laaeli Per Capita Worksheet re OPERATING REVENUE 4110 4120 4100 4200 4300 4400 4500 4600 4000 5100 5200 5310 5321 5322 5320 5300 5400 5000 4000 5000 Fees & Service Hook-up Fees Other Total Fees & Service Residential Commercial School District Contractual Other Total Operating Revenue NON-OPERATING EXPENSE Interest Income Gain on Sales of Assets Transfers Interfund Intergovernmental State Federal Total Intergovernmental Total Transfers Other Total Non-Operating Revenue PER CAPITA RATIO CALCULATION Total Operating Revenue Total Non-Operating Revenue Total Revenue Population Per Capita Ratio plus equals divided by equals Chart of Bre OPERATING REVENUE 4110 4120 4100 4200 4300 4400 4500 4600 4000 5100 5200 5310 5320 5321 5322 5320 5300 5400 5000 4000 5000 Sales Worksheet Fees & Service Hook-up Fees Other Total Fees & Service Usage Charges Residential Commercial School District Total Usage Charges Contractual Other Total Operating Revenue NON-OPERATING REVENUE Interest Income Gain on Sales of Assets Transfers Interfund Intergovernmental State Federal Total Intergovernmental Total Transfers Other Total Non-Operating Revenue Total Revenue SALES RATIO CALCULATION Total Operating Revenue Total Non-Operating Revenue Total Revenue Usage Charges Total Revenue Sales Ratio pyRatiofgy plus equals divided by equals Chart of Evaro OPERATING EXPENSE 6110 6120 6130 6140 6150 6160 6170 6180 6100 6210 6220 6230 6240 6250 6200 6310 6320 6330 6340 6350 6360 6370 6300 6410 6420 6430 6400 6510 6520 6530 6540 6550 6500 6000 Personnel Travel Facilities Equipment Other Expense Worksheet Salaries Stipends Payroll Taxes Payable Workers Compensation Retirement/Pension Non-Retirement Benefits Certifications Other Personnel Total Airfare Ground Transportation Per Diem Training Workshops Other Travel Total Telephone Rent Electricity Water & Sewer Fuel Oil Repairs/Maintenance Bldgs Other Facilities Total Materials Supplies Parts Equipment Total Interest & Late Charges Insurance & Bonding Member Dues & Subscriptions Bank Charges Audit/Accounting/Legal Other Total Total Operating Expense yRatiofgy” Chart of Accounts 7110 7120 7100 7200 7310 7320 7330 7340 7350 7360 7300 7400 7500 7600 7700 7000 8110 8120 8130 8100 8000 6000 Expense Worksheet cont. GENERAL & ADMINISTRATIVE EXPENSE Contractual Capital Improvements Other Total Contractual Engineering & Professional Services Supplies Office & Clerical Postage Copier Chemicals Water Testing Other Total Supplies Salaries Workers Compensation Retirement/Pension Non-Retirement Benefits Total G & A Expense NON-OPERATING EXPENSES Capital/Special Projects Expenses State Funded Federal Funded Own Source Total Capital/Special Expense Total Non-Operating Expense Total Expenses EXPENSE RATIO CALCULATION Total Operating Expense Total Expense Expense Ratio ea divided by equals Chart of Accounts 1110 1120 1130 1140 1100 2110 2120 2130 2140 2100 1100 1130 1140 2100 Quick Ratio Worksheet Current Assets Cash A/R Inventories Prepaid Expenses Total Current Assets Current Liabilities A/P Payroll Taxes Payable Accrued Interest Payable Notes Payable Total Current Liabilities QUICK RATIO CALCULATION Total Current Assets Inventories Prepaid Expenses Quick Assets Total Current Liabilities Quick Ratio Ratiofg ” minus minus equals divided by equals Chart of Accounts 1110 1120 1130 1140 1100 1210 1220 1230 1240 1250 1200 1000 2110 2120 2130 2140 2100 2200 2000 2000 1000 Debt Worksheet Current Assets Cash AJR Inventories Prepaid Expenses Total Current Assets Property, Plant & Equipment Land Buildings Utility System Construction Work-in-Progress Equipment Total Property, Plant & Equipment Total Assets LIABILITIES Current Liabilities A/P Payroll Taxes Payable Accrued Interest Payable Notes Payable Total Current Liabilities Long-Term Debt Total Liabilities DEBT RATIO CALCULATION Total Liabilities Total Assets Debt Ratio ORatiofgy divided by equals Chart of Receivable Worksheet . = 1120 Accounts Receivable (A/R) Usage Charges 4200 Residential 4300 Commercial 4400 School District Total Usage Charges RECEIVABLE RATIO CALCULATION 1120 Accounts Receivable (A/R) divided by Usage Charges equals Receivable Ratio Ee “Broke” village water utility attempts to get back in ‘black’ By Wendy Writer — Somewhere Community Weekly “We're broke”, said utility clerk Sue Jones at the vil- lage council meeting Tuesday. Jones, Somewhere’s utility clerk since 1999, ex- plained she received a bank statement, indicating the account was substantially overdrawn. Jones also revealed many of the village utility accounts were behind by more than 45 days, creating a severe shortage of cash flow. This isn’t the first time the village utility found itself in the red. Last summer, Somewhere’s utility board rec- ommended increasing water rates but the council re- fused, citing high unemployment. The council also ex- pressed concern about the two years of poor fishing runs, impacting the local subsistence fishery. Most in- come was derived from subsistence fishing and local construction projects. Given the current conditions of the local economy, the utility was unable to raise rates and meet the costs of running the water system. After financial problems last year, a state Rural Util- ity Business Advisor (RUBA) recommended that the village utility accounting system be converted to a com- puterized bookkeeping system. Instead, Jones continued to use a manual bookkeeping system to track financial information. When Jones went on a six-month mater- nity leave, the financial reports were left incomplete. As a result, the council and board did not receive timely financial reports. Without these reports the local utility board was unable to determine the financial position of the water utility. “We knew we had a money problem, but we didn’t realize it was this bad,” Jones told the council. “The water plant operator assured me the system was working out OK, especially because the state and federal govern- ment funded the new upgrade to the utility system.” Council member Ted Tough remarked that “Jones financial report was disturbing. It indicated a lack of understanding of the importance of accurate financial information in order to communicate with the village utility board, an independent advisory committee to the village council.” fy Lalelh a ee Ree BCL ia meena eG! Jones said “I will do anything it takes to get this village utility back on track and in the black.” Although Jones began working at the village utility 18 months ago, she never received any formal book- keeping training in managing a village utility system. Jones lacked sufficient knowledge and understanding of policies regarding financial reporting. “T am looking forward to finally getting training,” Jones said. “It should help me to better produce under- standable monthly reports for the council. In addition, the use of Ratio 8 will help us evaluate the true financial condition of the village utility system.” To alleviate the village utility financial problems, the council decided to invite a RUBA to train village utility staff in computerized bookkeeping. The RUBA pro- gram provides training for village personnel to learn about management of rural utility operations. One of the programs recommended by RUBA is Ratio 8, a financial analysis software program to help improve financial capacity. The purpose of Ratio 8 is to track and evaluate a water system’s financial position. “The training and use of Ratio 8 are crucial in accu- rately tracking the financial condition of the village,” Tough said. After training, one of the things Somewhere’s utility did, as suggested by RUBA, was implement a program such as QuickBooks, which uses a chart of accounts system. “This system allows us to organize our financial information for easy use and understanding for those who need to review it,” Jones said. Now, Jones can easily create reports for the local water utility, council and board to use in monitoring their financial footing. The computerized bookkeeping system also allows Jones to utilize the Excel-based Ra- tio 8 financial tracking tool. The eight ratios help track important financial issues such as liquidity, expenses, receivables, and revenue generation. EE AS TTDI SS CITED AE TS A ASSES OTT NT RS MT EE EY SEE REET OR TET RRO SAAR SRR UA Ratio 8 — Environmental Finance Center at Boise State University Page 29 What it means: One of the problems that Somewhere’s utility faced was being liquid enough to pay current liabilities. There were no indicators available to Jones to show that the water utility was financially insolvent. Ratio 8 offers analysis that includes ratios such as current and quick, which measure the liquidity of the water system. By hav- ing this system in place, Jones will now be able to regu- late the financial condition of the water system. More about Ratio 8’s use: Ratio 8 can be used on an annual basis to collect data over time to conduct a trend analysis of the system. Using annual figures Ratio 8 can compare ratio trends of three, five, and ten years. In doing this, Somewhere’s utility can determine if the water system is heading for trouble and look to see where positive or negative trends are occurring. Ratio 8 helps identify the “true costs” of operating a water system. This type of analysis is beneficial for the village council. This will aid the local utility board in assessing the financial condition of the local water sys- tems. The Council needs to know how utilities are per- forming and if current rates are covering expenses. In addition, by having this analysis available, local govern- ing bodies can have accurate information to allocate and manage assets. Now, Sue Jones is using an electronic financial sys- tem. With this new platform in place she will be able to incorporate Ratio 8 into her budgeting system. Sue Jones can track her financials over time with Ratio 8 to detect any problems with her utility financials. This year, she noticed that her current ratio was beginning to trend downward. Upon this discovery, she brought it to the attention of the council who will inves- tigate the cause. The council might find that Some- where’s utility has not received enough revenue to cover expenses, which could lead to an increase in payables. By having an electronic tracking system in place, Some- where’s utility can head off the problem before it esca- lates too far. After three years of incorporating electronic chart of accounts and using Ratio 8, Somewhere’s water utility system is back on track and in the black. Jones has learned the importance of fiscal responsibility, financial planning and reporting. eee eens ee eee eae eee eee e eer rece eee ccre serra ceecrerer eer Ee aT Case study questions: 1. Identity what the major problems were for Somewhere’s water utility. 2. Did Sue Jones properly understand the roles of the operator, council, utility board and clerk’s office? 3. Why should the village council and the local utility board be interested in ratios and determining financial condition? 4. How can Ratio 8 be used to track the financial condition of Somewhere’s water utility? How often should Ratio 8 be used? Why? 6. How did Somewhere’s utility use Ratio 8 to detect problems with their water system? What problem was detected? ” Answers: 1. Somewhere’s utility lacked an adequate financial bookkeeping system to detect problems such as liquidity and poor receivables. They found that they were broke and had several accounts that were 45 days overdue that hurt revenues. 2. No. RUBA program provides training opportunities for village personnel to learn about management of rural utility operations. 3. If you don’t know where you are, how can you know where you are going? Ratios are a common tool of financial analysis, regardless of the industry. Local governing bodies with accurate information can allocate and manage assets. 4. Ratio 8 helps you to identify the “true costs” of run- ning a water utility system, such as liquidity, ex- penses, receivables, and revenue generations. It also allows Somewhere’s utility to collect data over time and conduct a trend analysis that shows positive or negative trends. 5. Ratio 8 should be used annually because the ratios are compared from year to year. You need at least three years or more of financial data to conduct a trend analysis for Ratio 8’s built in function (see Ap- pendix B). 6. Somewhere’s water utility used Ratio 8 to compare current ratios from year to year. This lead to the discovery of insufficient revenues to pay expenses leading to increases in payables. Ratio 8 — Environmental Finance Center at Boise State University Page 30 Appendix C: Case study exercise In the case study about Somewhere, elected officials of a small rural community struggle to cope with a financial crisis and critical management issues, including a gap in communi- cation. How might Ratio 8 help to resolve some of these financial issues and close the communi- cation gap? For the purposes of this exercise, we will assume that Sue Jones, the village utility clerk, contacted the local RUBA and received utility management training, including Ratio 8 instruction to analyze the financial condition of the water utility. To illustrate the utility’s financial trend in regards to cash assets, council member Ted Tough asked Jones to prepare a three-year trend analysis for the Current Ratio. This information will help Jones and the council understand cash flow over a three-year period, providing a basis to make good financial decisions. Here are steps Jones might take to approach this task: Gather data from chart of accounts (at least three years worth) and enter into the Ratio 8 Wizard. {\ aN Generate Current Ratio y’ o We @ output page by selecting “Current Ratio” in the “Charts” menu on the “Ratio 8” drop-down menu. 6 Evaluate the ratios by conducting a trend analysis. Simply, click on the “Ratio 8” drop-down menu, select “Graphing Analysis” and click “Three Year Trend.” e Ratio 8 will create anew workbook, showing trend graphs for all eight ratios for the three years of data entered into Wizard. 6 Present the analysis to the local governing body and discuss possible implications of chart results. Ratio 8 — Environmental Finance Center at Boise State University Page 31 References Hondale, B.W., & Lloyd-Jones, M. (1998). Public Budgeting & Finance. Analyzing Rural Local Governments’ Financial Condition: an Exploratory Application of Three Tools. Maryland Center for Environmental Training. (2000). Drinking Water and Wastewater Handbook for Local Officials. National Association of Regulatory Utility Commission- ers. (1996) Uniform System of Accounts for Class C Water Utilities. National Drinking Water Clearinghouse, West Virginia University (1999). Simple Ratios Allow Systems to Check Financial Health. National Environmental Training Center for Small Communities. (2000). Managing a S: Drinkin, (Module 10: Financial Options for System Projects or Upgrades): Morgantown, WV: West Virginia University. Olsen, A. B. (1999). Financial Management Handbook for Small Community Water Systems. Midwest Assistance Program, Inc. New Prague, MN. (Available from http://www.map-inc.org) Rural Community Assistance Corporation. (n.d.). Small Rural Community Assistance Corporation. (n.d.). Small System Guide to Financial Management. Sacramento, CA: Author. U.S. Environmental Protection Agency. (1989). A Waste W. + M: 1’s Guide for S' U.S. Environmental Protection Agency. (1989). Small C itv Municipal Fi ial Capabili Analysis Evaluation Guidebook. U.S. Environmental Protection Agency. (1992). The Road to Fi . : iI ing) mmuni fe Washington State Department of Health. (1995). Vue i Small Water Systems. Endnotes 1 rt_of Findi n_Improvin: Technical, Managerial and_Financi i f Alaska’s Publi Water Systems (2000). Citizen Advisory Board to the Alaska Department of Environmental Conservation. 2 Report of Findings: On Improving the Technical M fal and Fi ial C . f Alaska’s Publi Water Systems (2000). Citizen Advisory Board to the Alaska Department of Environmental Conservation. 3The Alaska Department of Community & Economic Development, Research & Analysis Section, maintains a detailed database with a wide range of community-based information and data for planning, policy making and technical assistance decisions. The database provides for comprehensive community profiles and customized queries or specific community data. Information is avail- able in hard copy, and through an interactive database on the worldwide web at: http://www.dced.state.ak.us/mra/CF_COMDB.htm This web site includes a feature that allows users to automatically e-mail themselves the data they need. To contact Research & Analysis, call (907) 465-4750. Ratio 8 ~ Environmental Finance Center at Boise State University Page 32