{"text_format":"1","workbook":"xlsx","n_sheets":9,"sheets":["Cover&Contents","Introduction","SummaryChecklist","Indicators","DataGathering-GenFund","SpecProgrFund1","SpecProgrFund2","SpecProgrFund3","Notes to the Analyst"],"not_rendered":{"charts":0,"images":4,"macros":1,"external_links":0,"defined_names":15,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Cover&Contents","index":0,"n_rows":18,"n_cols":4,"rows_kept":18,"cut":false,"hidden":false,"merged":[[14,1,14,4],[3,1,3,4],[1,1,1,4],[4,3,4,5]]}
[]
[null,"The California County Financial Health Diagnostic"]
[]
[null,"August 2016"]
[null,null,"Contents\n ","page \n(click to hyperlink to worksheet)"]
[null,null,"Introduction ………………………………………...",2]
[null,null,"Financial Health Indicators"]
[null,null,"     Summary  Checklist……………………………",4]
[null,null,"     Indicators  …………………………………………",6]
[null,null,"     Data Gathering Worksheets  …………………………………………",{"$f":"+D9+8","v":14}]
[null,null,"     Special Program Fund 1: Indicators, Data …………………………………………",{"$f":"+D10+5","v":19}]
[null,null,"     Special Program Fund 2: Indicators, Data …………………………………………",{"$f":"+D11+6","v":25}]
[null,null,"     Special Program Fund 3: Indicators, Data …………………………………………",{"$f":"+D12+6","v":31}]
[null,null,"     Notes to the Analyst  …………………………………………",{"$f":"+D13+6","v":37}]
[null,"The California Municipal Financial Health Diagnostic was developed by Michael Coleman with substantial contributions from Andy Belknap, Robert Leland, Dave Millican, Mary Bradley, Ron Bates, Bob Biery, Brent Mason, Max Neiman, and Chris McKenzie among many others.  Jeremy Goldberg's assistance in testing draft versions of the tool was invaluable.\n\nThe county version, The California County Financial Health Diagnostic, was developed with invaluable guidance from:\nTerry Schutten, County Administrative Officers Association of California\nLeanne Link, County of Napa\nMeegan Jessee, County of Butte\nMartin Polt, County of Nevada\nAnil Kukreja, County of Orange\nPaul Derse,  County of Ventura\nGraham Knaus, California State Association of Counties"]
[null,null,"© 2016 CaliforniaCityFinance.com         The California Local Government Finance Almanac"]
[]
[null,null,"Sid Kikkawa, County of Los Angeles\nChristina Rivera, County of Sonoma\nJoan XXXXx, County of San Diego\nDennis Bozanich, County of ALameda\nEmily Jackson, County of San Luis Obispo\nkkk, County of Amador\nElsa xxx, County of Kern\nAlan XXX, County of Mendocino\nNicole Temple, Rebecca Campbell, County of Kings\nElsa XXX, County of Kern"]

{"sheet":"Introduction","index":1,"n_rows":36,"n_cols":3,"rows_kept":36,"cut":false,"hidden":false,"merged":[]}
[null,"How Are We Doing?","return to \ncontents"]
[null,{"$f":"+\"Using the \"&'Cover&Contents'!B2:E2","v":"Using the The California County Financial Health Diagnostic"}]
[null,"to Evaluate Your Agency’s Financial Health "]
[]
[null,"According to economists, California's \"great recession\" ran from December 2007 and ended in August 2009.  These years, and those following have been difficult for local governments all around. In addition to deep reductions in local government revenue, cities, counties and special districts have struggled with state take-aways of local funds, the dissolution of redevelopment agencies, and mounting costs of retiree pensions and benefits.  The severity and combination of these financial impacts varies but in a few well-known instances, cities have had to seek the legal protections of Chapter 9 Bankruptcy to restructure their contractual obligations, and at least one county required financial intervention from the state.  "]
[null,"Grappling effectively with financial distress before it becomes a crisis first requires a shared understanding of the financial condition of the local government.  A critical component of the difficulties of the most financially distressed local governments has been a lack of recognition and agreement among local leaders, staff and key interests (labor, retirees, creditors) regarding the agency’s financial condition and what must be done in order to achieve sustainability.  Most financial crises can be managed without court or outside agency intervention if leaders, staff and key interests have the courage, competence, and collaborative attitudes to 1) recognize and agree upon the local government’s financial condition, and 2) implement the necessary changes to set the local government on a sustainable financial course."]
[null,"The purpose of this tool is to provide county officials with an in depth analysis of the fiscal health of the agency currently and going forward. This is NOT an excercize in comparisons among various agencies. Counties differ and comparisons are often misleading or not pertinent to the question of the financial viability of the county and it's ability to deliver services. The most important standards to measure against are your own, developed and established in financial policies taking into account common industry standards as well as the unique aspects of your agency. "]
[null,"Defining Local Government Financial Health"]
[null,"Government financial health may be viewed in four related financial contexts: 1) cash solvency - the ability to meet immediate financial obligations – generally over the next 30 or 60 days (accounts payable, payroll); 2) budgetary solvency - the ability to meet all financial obligations during a budget year; 3) long-run solvency – the ability to meet all financial obligations into the future; and 4) service-level solvency – the ability to provide the desired level of services for the general health and welfare of a community.[1]  In the context of today’s difficult financial climate, we are primarily concerned with the ability of the local government to meet its financial commitments now and into the future (cash, budgetary, and long-run solvency).  A more expansive evaluation of financial condition would examine the community’s economic environment and ability to meet the service level desires of the community (service-level solvency)."]
[null,"A government is in financial distress if it has a continuing imbalance between its level of financial commitments and its available financial resources over time.  If revenues and spending are not brought into balance, financial distress can progress into financial crisis, when the government is unable to meet its financial obligations. "]
[null,"Determining Local Government Financial Health"]
[null,"There have been many government financial health monitoring systems devised over the last several decades.  Each approach consists of a series of financial indicators or measures that address a local government’s ability to meet the needs of its constituents.   The International City/County Management Association’s (ICMA) venerable Financial Trend Monitoring System (FTMS) consists of some 42 financial measures including factors assessing the economic environment of the community, the finances of the jurisdiction, and its managerial practices and legislative policies.[2] Building on the FTMS and various other systems and policies developed by academics and governments, this tool distills the most essential indicators assessing financial health with a pragmatic focus on an agency’s level of financial distress heading into financial crisis."]
[null,"This California County Financial Health Diagnostic is primarily concerned with the ability of the county to meet its financial commitments now and into the future (cash, budgetary, and long-run solvency).  In contrast to many other financial health monitoring systems, this tool looks at recent past and near future financial measures and indicators to provide an assessment of the county's level of financial distress and tendency toward financial crisis."]
[null,"The California County Financial Health Diagnostic is focused first and foremost on the general fund, but may also be applied to other funds. It focuses on the county’s operating position, going beyond reported finances to focus on recurring revenues and expenditures (by distinguishing temporary non-recurring revenues and expenditures) and taking into account any unbudgeted but real current costs of services. The tool includes an evaluation of unsustainable or risky budget practices that indicate an agency in distress and tending toward crisis.  The foundation of the diagnosis is an examination of discretionary revenues and spending, distinguishing and identifying the effects of programatic revenues and spending on discretionary resources. "]
[null,"Unique Aspects of California Local Government Finance[3]\n\nCalifornia local governments’ tax and revenue raising choices are strictly limited.  Property tax is the single most important source of general purpose revenue for most cities, counties and many special districts.  Yet local governments have no authority over the property tax base, rate or allocation.  Other tax increases require voter approval.  \n\nOver the last several decades since Proposition 13 (1978), the state has shifted property tax revenues, vehicle license taxes, and eliminated virtually all general purpose state aid to local governments. At the same time, the Legislature has shifted greater program responsiblities and imposed greater mandates but with limited funding. In response, local governments sought and voters approved, several constitutional measures protecting local government finances from further legislative actions.  \n\nMost remaining city general purpose revenues are no longer vulnerable to additional state takeaways.[4] Consequently, unlike municipalities in other states or in California in previous times, assessing the financial health of California cities has little to do with the amount or proportion of intergovernmental revenues (which, in any case, is minimal for nearly all cities and special districts).\n\nCalifornia counties have much more limited revenue raising authority than cities and remain, by their nature, highly dependent on state budgetary actions. The state Legislature may delegate to counties any of the functions which belong to the state itself.  Counties are responsible for frontline municipal services in unincorporated areas; for countywide local services such as jails, elections, and property tax administration; and for state programs delegated to counties such as health and human services, mental health, and portions of the law enforcement and corrections systems."]
[null,"The California County Financial Health Diagnostic consists of:"]
[null,"1.    The Financial Distress Checklist.  A fourteen point list of key indicators to assess the near term financial health of your local government’s general fund and other operations.  The checklist is intended for use by policy makers and community members to ask the important questions and get the necessary answers.  "]
[null,"2.    The Financial Health Indicators.  Linked to the fourteen point Financial Distress Checklist, the Financial Health Indicators provide more detailed formulas and methods for determining financial condition and will need to be completed by a team of qualified financial analysts."]
[]
[null,"WARNING SIGNS"]
[null,"Indications of Crisis"]
[null,"ð    Failure to pay an undisputed claim from a creditor within 90 days past claim date."]
[null,"ð    Failure to forward income taxes withheld or Social Security contributions for over 30 days past the due date."]
[null,"ð    Failure to make required pension fund contributions on time."]
[null,"ð    Missing a payroll for 7 days."]
[null,"ð    Available unrestricted balance for the general fund at the end of the current fiscal year will be negative."]
[null,"© 2016 CaliforniaCityFinance.com"]
[]
[null,"[1] Justice, Jonathan and Scorsone, Eric. “Measuring and Predicting Local Government Fiscal Stress” in Levine, Helisse; Justice, Jonathan; Scorsone, Eric. Handbook of Local Government Fiscal Health. Jones and Bartlett Learning, Burlington, MA 2012."]
[null,"[2] Groves, Sanford M. and Valente, Maureen Godsey. Revised Nollenburg, Karl. Evaluating Financial Condition: A Handbook for Local Government, 4th edition. International City/County Manager Association (ICMA), USA, 2003."]
[null,"[3] For more information, see Multari, Coleman, Hampian and Statler, Guide to Local Government Finance in California, Solano Press Books, 2012."]
[null,"[4] The continuing impacts of the dissolution of Redevelopment Agencies notwithstanding.  These impacts should be taken into account as appropriate in these financial health measures."]
[]
[null,"Questions, comments, improvements?  Michael Coleman 530-758-3952  coleman@munwest.com"]
[null,"CaliforniaCityFinance.com, The California Local Government Finance Alamanac"]
[null,"© 2016 CaliforniaCityFinance.com"]

{"sheet":"SummaryChecklist","index":2,"n_rows":32,"n_cols":8,"rows_kept":32,"cut":false,"hidden":false,"merged":[[5,1,10,1],[11,1,18,1],[2,1,2,6],[3,1,3,6],[10,2,10,4],[7,2,7,4],[8,2,8,4],[9,2,9,4],[1,4,1,6],[4,2,4,4],[4,5,4,6],[5,2,5,4],[6,2,6,4],[22,2,22,6],[11,2,11,4],[12,2,12,4],[13,2,13,4],[14,2,14,4],[15,2,15,4],[16,2,16,4],[17,2,17,4],[18,2,18,4],[20,4,20,6],[21,2,21,6],[29,2,29,5],[30,2,30,5],[23,2,23,5],[24,2,24,5],[25,2,25,5],[26,2,26,5],[27,2,27,5],[28,2,28,5]]}
[]
[null,null,{"$f":"+'DataGathering-GenFund'!I1","v":"County of"},{"$f":"+'DataGathering-GenFund'!J1:J1","v":0},{"$f":"+'DataGathering-GenFund'!I2:I2","v":"General Fund - Discretionary"},null,null,"<<<<  These values load automatically from the Data Gathering worksheet."]
[null,{"$f":"+'Cover&Contents'!B2:B2","v":"The California County Financial Health Diagnostic"}]
[null,"Financial Health Indicators - Summary Checklist",null,null,null,null,null,"return to \ncontents"]
[null,null,"Indicator",null,null,"Rating"]
[null,"Measures"," 1.  Net Operating deficit / surplus. There are no recurring general fund operating deficits. General revenues are sufficient to meet the net general revenue demand of programs.",null,null,null,{"$f":"+IF(Indicators!L20=1,\"Warning\",IF(Indicators!L20=2,\"Caution\",IF(Indicators!L20=3,\"Healthy\",\"\")))","v":""},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null," 2.  Fund balance. Reserves are sufficient over multiple consecutive years and in compliance with adopted policy.",null,null,null,{"$f":"+IF(Indicators!L30=1,\"Warning\",IF(Indicators!L30=2,\"Caution\",IF(Indicators!L30=3,\"Healthy\",\"\")))","v":""},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null," 3.  Capital Asset Condition. The county is keeping pace with the aging of its capital assets.",null,null,null,{"$f":"+IF(Indicators!L43=1,\"Warning\",IF(Indicators!L43=2,\"Caution\",IF(Indicators!L43=3,\"Healthy\",\"\")))","v":""},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null," 4.  Liquidity. Cash and short-term investments are suffieicent to cover current liabilities (including short-term debt and accounts payable within 60 days).",null,null,null,{"$f":"+IF(Indicators!L51=1,\"Warning\",IF(Indicators!L51=2,\"Caution\",IF(Indicators!L51=3,\"Healthy\",\"\")))","v":""},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null," 5.  Fixed costs & labor costs. Fixed costs (including debt service and other contractual obligations), salaries and benefits are stable or decreasing over multiple years relative to reasonably expected revenue growth. ",null,null,null,{"$f":"+IF(Indicators!L77=1,\"Warning\",IF(Indicators!L77=2,\"Caution\",IF(Indicators!L77=3,\"Healthy\",\"\")))","v":""},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null," 6.  General fund subsidies of other funds. Any general fund subsidies other enterprises or special funds are sustainable and in compliance with adopted policy.",null,null,null,{"$f":"+IF(Indicators!L3=84=1,\"Warning\",IF(Indicators!L85=2,\"Caution\",IF(Indicators!L85=3,\"Healthy\",\"\")))","v":""},"<<<<  These values load automatically from the Indicators worksheet."]
[null,"Practices and Conditions"," 7.  Constraints on budgetary discretion. The board of supervisors' authority to make budget changes is not excessively constrained by charter, contract, or law. ",null,null,null,{"$f":"+IF(Indicators!L93=1,\"Warning\",IF(Indicators!L93=2,\"Caution\",IF(Indicators!L93=3,\"Healthy\",\"\")))","v":""},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null," 8.  Balancing the budget with temporary funds. The use of reserves, selling assets, deferring asset maintenance to balance the general fund has been limited, prudent and in compliance with adopted policy.",null,null,null,{"$f":"+IF(Indicators!L101=1,\"Warning\",IF(Indicators!L101=2,\"Caution\",IF(Indicators!L101=3,\"Healthy\",\"\")))","v":""},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null," 9.  Balancing the budget with borrowing. The use of short-term borrowing, internal borrowing or transfers from special funds to balance the general fund has been limited, prudent and in compliance with adopted policy.",null,null,null,{"$f":"+IF(Indicators!L107=1,\"Warning\",IF(Indicators!L107=2,\"Caution\",IF(Indicators!L107=3,\"Healthy\",\"\")))","v":""},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null,"10.  Balancing the budget by deferring employee compensation costs. Pension liabilities and other non-salary benefits have been determined, disclosed and actuarially funded in compliance with adopted policy.",null,null,null,{"$f":"+IF(Indicators!L113=1,\"Warning\",IF(Indicators!L113=2,\"Caution\",IF(Indicators!L113=3,\"Healthy\",\"\")))","v":""},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null,"11. Balancing the budget with backloaded debt service payments. Debt service payments have not been “backloaded” into future years.",null,null,null,{"$f":"+IF(Indicators!L120=1,\"Warning\",IF(Indicators!L120=2,\"Caution\",IF(Indicators!L120=3,\"Healthy\",\"\")))","v":""},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null,"12. Funding operating costs with non-recurring revenues. Ongoing operating costs are being funded with ongoing rather than temporary revenues (e.g., from development, etc.)",null,null,null,{"$f":"+IF(Indicators!L126=1,\"Warning\",IF(Indicators!L126=2,\"Caution\",IF(Indicators!L126=3,\"Healthy\",\"\")))","v":""},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null,"13. Timeliness and accuracy of financial reports. Financial Reports are being filed on time. (CAFR, Annual Audit, State Controller’s Financial Transactions Report)",null,null,null,{"$f":"+IF(Indicators!L131=1,\"Warning\",IF(Indicators!L131=2,\"Caution\",IF(Indicators!L131=3,\"Healthy\",\"\")))","v":""},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null,"14. Alignment of Service Level Priorities to Financial Health. Given indicators 1-13, public service levels meet standards. ",null,null,null,{"$f":"+IF(Indicators!L135=1,\"Warning\",IF(Indicators!L135=2,\"Caution\",IF(Indicators!L135=3,\"Healthy\",\"\")))","v":""},"<<<<  These values load automatically from the Indicators worksheet."]
[]
[null,null,{"$f":"+C2","v":"County of"},{"$f":"+D2","v":0},{"$f":"+E2","v":"General Fund - Discretionary"},null,null,"<<<<  These values load automatically from the Data Gathering worksheet."]
[null,null,{"$f":"+B3","v":"The California County Financial Health Diagnostic"}]
[null,null,"Adopted Financial Management Policies",null,null,null,null,"return to \ncontents"]
[null,null," 1.  Use of Temporary Funds",null,null,null,{"$f":"+IF(Indicators!L102=1,\"Yes\",IF(Indicators!L102=2,\"No\",IF(Indicators!L102=0,\"-\",\"\")))","v":"-"},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null," 2.  Reserves and Fund Balance",null,null,null,{"$f":"+IF(Indicators!L103=1,\"Yes\",IF(Indicators!L103=2,\"No\",IF(Indicators!L103=0,\"-\",\"\")))","v":"-"},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null," 3.  Short term borrowing",null,null,null,{"$f":"+IF(Indicators!L108=1,\"Yes\",IF(Indicators!L108=2,\"No\",IF(Indicators!L108=0,\"-\",\"\")))","v":"-"},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null," 4.  Interfund Transfers",null,null,null,{"$f":"+IF(Indicators!L109=1,\"Yes\",IF(Indicators!L109=2,\"No\",IF(Indicators!L109=0,\"-\",\"\")))","v":"-"},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null," 5. Payment of employee compensation costs",null,null,null,{"$f":"+IF(Indicators!L114=1,\"Yes\",IF(Indicators!L114=2,\"No\",IF(Indicators!L114=0,\"-\",\"\")))","v":"-"},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null," 6. Pay-as-you-go Financing",null,null,null,{"$f":"+IF(Indicators!L121=1,\"Yes\",IF(Indicators!L121=2,\"No\",IF(Indicators!L121=0,\"-\",\"\")))","v":"-"},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null," 7.  Debt Management / Affordability",null,null,null,{"$f":"+IF(Indicators!L122=1,\"Yes\",IF(Indicators!L122=2,\"No\",IF(Indicators!L122=0,\"-\",\"\")))","v":"-"},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null," 8.  Long range financial planning",null,null,null,{"$f":"+IF(Indicators!L127=1,\"Yes\",IF(Indicators!L127=2,\"No\",IF(Indicators!L127=0,\"-\",\"\")))","v":"-"},"<<<<  These values load automatically from the Indicators worksheet."]
[null,null,null,null,null,null,"© 2016 CaliforniaCityFinance.com"]

{"sheet":"Indicators","index":3,"n_rows":258,"n_cols":14,"rows_kept":258,"cut":false,"hidden":false,"merged":[[99,4,100,4],[99,5,100,5],[99,6,100,6],[105,4,106,4],[105,5,106,5],[105,6,106,6],[117,4,119,4],[117,5,119,5],[117,6,119,6],[2,0,2,10],[129,0,129,3],[98,0,100,3],[103,1,103,10],[62,0,62,10],[63,0,63,10],[64,0,64,10],[65,0,65,10],[71,1,71,10],[59,0,59,10],[47,8,47,10],[79,1,79,10],[80,0,80,1],[80,4,80,5],[80,6,80,7],[72,4,72,5],[72,6,72,7],[81,0,81,1],[72,0,72,1],[74,0,76,3],[80,8,80,10],[85,3,85,5],[85,7,85,10],[37,1,37,10],[68,0,68,1],[68,4,68,5],[68,6,68,7],[68,8,68,10],[82,4,82,9],[29,4,29,8],[60,0,60,10],[35,7,35,10],[61,0,61,10],[47,6,47,7],[44,0,44,10],[46,0,46,1],[58,7,58,10],[30,0,30,10],[48,4,48,9],[49,4,49,9],[50,4,50,9],[69,0,70,5],[69,6,70,10],[36,0,36,10],[72,8,72,10],[73,8,73,10],[73,4,73,5],[8,6,8,10],[7,4,7,5],[7,6,7,7],[7,8,7,10],[27,4,27,8],[28,4,28,8],[67,6,67,7],[67,8,67,10],[26,0,26,1],[25,4,25,5],[23,0,23,10],[11,4,11,5],[11,6,11,7],[17,4,17,9],[18,4,18,9],[19,4,19,9],[26,4,26,5],[25,6,25,7],[22,7,22,10],[38,0,38,1],[39,0,39,1],[40,0,42,3],[33,0,33,1],[4,1,4,10],[26,6,26,7],[56,0,56,10],[51,0,51,10],[52,0,52,10],[53,0,53,10],[54,0,54,10],[48,0,50,3],[46,4,46,5],[46,6,46,7],[47,0,47,1],[25,8,25,10],[26,8,26,10],[46,8,46,10],[55,0,55,10],[27,0,29,3],[47,4,47,5],[31,0,31,10],[5,6,5,7],[5,4,5,5],[13,7,13,10],[5,0,5,1],[25,0,25,1],[8,0,8,5],[3,0,3,10],[20,0,20,10],[6,6,6,7],[6,4,6,5],[6,0,6,1],[14,1,14,10],[15,0,15,1],[15,4,15,5],[15,6,15,7],[16,0,16,1],[16,4,16,5],[16,6,16,7],[12,7,12,10],[12,0,12,4],[12,5,12,6],[9,1,9,10],[11,8,11,10],[15,8,15,10],[16,8,16,10],[17,0,19,3],[10,0,10,1],[10,4,10,5],[10,6,10,7],[11,0,11,1],[1,3,1,5],[13,3,13,5],[22,3,22,5],[35,3,35,5],[58,3,58,5],[78,3,78,5],[94,3,94,5],[114,3,114,5],[1,7,1,10],[87,0,88,6],[82,0,84,3],[86,1,86,10],[89,0,89,6],[90,0,90,6],[91,0,91,6],[92,0,92,6],[96,0,96,10],[114,7,114,10],[5,8,5,10],[6,8,6,10],[10,8,10,10],[66,1,66,10],[67,0,67,1],[67,4,67,5],[38,8,38,10],[39,4,39,5],[39,6,39,7],[39,8,39,10],[40,4,40,9],[41,4,41,9],[42,4,42,9],[38,4,38,5],[38,6,38,7],[109,1,109,10],[110,0,110,3],[111,0,111,3],[111,4,112,4],[111,5,112,5],[111,6,112,6],[104,0,106,3],[73,0,73,1],[74,4,74,9],[75,4,75,9],[76,4,76,9],[87,7,89,9],[90,7,90,9],[91,10,92,10],[83,4,83,9],[84,4,84,9],[91,7,92,9],[73,6,73,7],[94,7,94,10],[78,7,78,10],[88,10,89,10],[81,8,81,10],[81,4,81,5],[81,6,81,7],[32,1,32,10],[130,0,130,3],[130,7,130,9],[131,1,131,10],[129,4,130,4],[129,5,130,5],[129,6,130,6],[128,7,128,9],[129,7,129,9],[122,1,122,10],[123,0,125,3],[127,1,127,10],[128,0,128,3],[115,1,115,10],[112,0,112,3],[124,4,125,4],[124,5,125,5],[124,6,125,6],[116,0,119,3],[97,1,97,10],[95,0,95,5],[112,7,112,9],[116,7,117,9],[118,7,118,9],[132,5,132,9],[133,5,133,9],[134,5,134,9],[132,0,132,4],[133,0,133,4],[33,4,33,5],[33,6,33,7],[33,8,33,10],[34,0,34,1],[34,4,34,5],[34,6,34,7],[34,8,34,10],[119,7,119,9],[123,7,123,9],[124,7,124,9],[125,7,125,9],[98,7,98,9],[99,7,99,9],[100,7,100,9],[104,7,104,9],[105,7,105,9],[106,7,106,9],[110,7,110,9],[111,7,111,9]]}
[null,"Use the Data Gathering worksheet to collect and input the data that will rollup through these formulas."]
[null,null,{"$f":"+'DataGathering-GenFund'!I1","v":"County of"},{"$f":"IF('DataGathering-GenFund'!$J$1=0,\"\",'DataGathering-GenFund'!$J$1)","v":""},null,null,null,{"$f":"IF('DataGathering-GenFund'!$I$2=0,\"\",'DataGathering-GenFund'!$I$2)","v":"General Fund - Discretionary"},null,null,null,null,"<= These fields populate from data entered in the \"data gathering\" tab."]
[{"$f":"SummaryChecklist!B3&\": Financial Health Indicators\"","v":"The California County Financial Health Diagnostic: Financial Health Indicators"},null,null,null,null,null,null,null,null,null,null,null,null,"return to \ncontents"]
["Operating Deficit/Surplus\n          The simplest measure of annual operating deficit/surplus is the difference between total revenues and total spending.  General fund operating deficits may be an indication that the government’s financial condition is unsustainably out of balance and that corrections to bring the finances into a sustainable condition are not occurring.  Though an operating deficit in any one year may not be a cause for concern (because, for example, reserves or one-time remedies might be available to cover the difference), frequent and increasing deficits may indicate that the governments activities are not sustainable within the revenues available over time.\n          Taken as a percentage of total revenues, the local government’s operating deficit/surplus tells us very plainly if current resources in the fund are sufficient to cover current expenditures.  The data to compute this measure should be readily available in the financial statements (comprehensive annual financial reports) of the agency. This is measure 1A below.\n          But a budget can be balanced and an ongoing structural problem masked if a deficit is covered by temporary solutions, such as non-recurring revenues like limited-term grants, land sale income, or transfers from other funds. This approach cannot be relied upon in the long-run (over multiple years).  Measure 1B takes a step further than 1A by taking these temporary revenues out of the equation to see how much worse the revenue shortfall becomes at current spending levels.\n          Still, this may not show the whole picture.  If certain current financial obligations are being delayed and not budgeted, then the financial position shown in measure 1B is still incomplete.  Measure 1C takes into account “unbudgeted current liabilities” such as Other Post-Employment Benefits (OPEB); unbudgeted earned leave cash-out liabilities; maintenance and replacement costs of vehicles, technology, buildings, streets, and other properties and infrastructure.  For the purpose of determining long run solvency, Measure 1C is the best indicator of a local government’s true fiscal operating position because it reflects the level of spending actually needed to sustain your current level of service over time.  If these deferred costs are significant, then your fiscal imbalance may be much worse than you thought.  If you cannot find sufficient information to determine these unbudgeted liabilities, that, in itself, is reason for substantial concern (See indicator # 9). "]
["1A","Net General Revenue Demand",null,null,null,null,null,null,null,null,null,null,null,"Want to add more years? I can help you. 530-758-3952 coleman@muniwest.com"]
[{"$f":"+'DataGathering-GenFund'!F5","v":"2014-15"},null,{"$f":"+'DataGathering-GenFund'!G5","v":"2015-16"},{"$f":"+'DataGathering-GenFund'!H5","v":"2016-17"},{"$f":"+'DataGathering-GenFund'!I5","v":"2017-18"},null,{"$f":"+'DataGathering-GenFund'!J5","v":"2018-19"},null,{"$f":"+'DataGathering-GenFund'!K5","v":"2019-20"}]
[{"$f":"IF(ISERROR('DataGathering-GenFund'!$F$12-'DataGathering-GenFund'!$F$15),\"\",('DataGathering-GenFund'!$F$12-'DataGathering-GenFund'!$F$15))","v":0},null,{"$f":"IF(ISERROR('DataGathering-GenFund'!$G$12-'DataGathering-GenFund'!$G$15),\"\",('DataGathering-GenFund'!$G$12-'DataGathering-GenFund'!$G$15))","v":0},{"$f":"IF(ISERROR('DataGathering-GenFund'!$H$12-'DataGathering-GenFund'!$H$15),\"\",('DataGathering-GenFund'!$H$12-'DataGathering-GenFund'!$H$15))","v":0},{"$f":"IF(ISERROR('DataGathering-GenFund'!$I$12-'DataGathering-GenFund'!$I$15),\"\",('DataGathering-GenFund'!$I$12-'DataGathering-GenFund'!$I$15))","v":0},null,{"$f":"IF(ISERROR('DataGathering-GenFund'!$J$12-'DataGathering-GenFund'!$J$15),\"\",('DataGathering-GenFund'!$J$12-'DataGathering-GenFund'!$J$15))","v":0},null,{"$f":"IF(ISERROR('DataGathering-GenFund'!$K$12-'DataGathering-GenFund'!$K$15),\"\",('DataGathering-GenFund'!$K$12-'DataGathering-GenFund'!$K$15))","v":0},null,null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
[{"$f":"IF(ISERROR(('DataGathering-GenFund'!$F$15-'DataGathering-GenFund'!#REF!)/'DataGathering-GenFund'!$F$15),\"\",('DataGathering-GenFund'!$F$15-'DataGathering-GenFund'!#REF!)/'DataGathering-GenFund'!$F$15)","v":""},"percent change =>",{"$f":"IF(ISERROR(C7/A7-1),\"\",C7/A7-1)","v":""},{"$f":"IF(ISERROR(D7/C7-1),\"\",D7/C7-1)","v":""},{"$f":"IF(ISERROR(E7/D7-1),\"\",E7/D7-1)","v":""},null,{"$f":"IF(ISERROR(G7/E7-1),\"\",G7/E7-1)","v":""},null,{"$f":"IF(ISERROR(I7/G7-1),\"\",I7/G7-1)","v":""},null,null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,null,null,"For comparison purposes"]
["1B  ","Net County Cost - Deficit/Surplus – omitting non-recurring revenues"]
[{"$f":"+A6","v":"2014-15"},null,{"$f":"+C6","v":"2015-16"},{"$f":"+D6","v":"2016-17"},{"$f":"+E6","v":"2017-18"},null,{"$f":"+G6","v":"2018-19"},null,{"$f":"+I6","v":"2019-20"}]
[{"$f":"IF(ISERROR(('DataGathering-GenFund'!$F$22-$A$7)/'DataGathering-GenFund'!$F$22),\"\",(('DataGathering-GenFund'!$F$22-$A$7)/'DataGathering-GenFund'!$F$22))","v":""},null,{"$f":"IF(ISERROR(('DataGathering-GenFund'!$G$22-$C$7)/'DataGathering-GenFund'!$G$22),\"\",('DataGathering-GenFund'!$G$22-$C$7)/'DataGathering-GenFund'!$G$22)","v":""},{"$f":"IF(ISERROR(('DataGathering-GenFund'!$H$22-$D$7)/'DataGathering-GenFund'!$H$22),\"\",('DataGathering-GenFund'!$H$22-$D$7)/'DataGathering-GenFund'!$H$22)","v":""},{"$f":"IF(ISERROR(('DataGathering-GenFund'!$I$22-$E$7)/'DataGathering-GenFund'!$I$22),\"\",('DataGathering-GenFund'!$I$22-$E$7)/'DataGathering-GenFund'!$I$22)","v":""},null,{"$f":"IF(ISERROR(('DataGathering-GenFund'!$J$22-$G$7)/'DataGathering-GenFund'!$J$22),\"\",('DataGathering-GenFund'!$J$22-$G$7)/'DataGathering-GenFund'!$J$22)","v":""},null,{"$f":"IF(ISERROR(('DataGathering-GenFund'!$K$22-$I$7)/'DataGathering-GenFund'!$K$22),\"\",('DataGathering-GenFund'!$K$22-$I$7)/'DataGathering-GenFund'!$K$22)","v":""},null,null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,null,"Negative Indicator\n·   Recurring and static or increasing negative values",null,"For comparison purposes"]
[null,null,{"$f":"+C2","v":"County of"},{"$f":"IF('DataGathering-GenFund'!$J$1=0,\"\",'DataGathering-GenFund'!$J$1)","v":""},null,null,null,{"$f":"IF('DataGathering-GenFund'!$I$2=0,\"\",'DataGathering-GenFund'!$I$2)","v":"General Fund - Discretionary"}]
["1C","Net True County Cost - Deficit/Surplus - complete, adding unbudgeted general fund liabilities"]
[{"$f":"+A11","v":"2014-15"},null,{"$f":"+C11","v":"2015-16"},{"$f":"+D11","v":"2016-17"},{"$f":"+E11","v":"2017-18"},null,{"$f":"+G11","v":"2018-19"},null,{"$f":"+I11","v":"2019-20"}]
[{"$f":"IF(ISERROR(('DataGathering-GenFund'!$F$22-$A$7-'DataGathering-GenFund'!$F$24)/'DataGathering-GenFund'!$F$22),\"\",(('DataGathering-GenFund'!$F$22-$A$7-'DataGathering-GenFund'!$F$24)/'DataGathering-GenFund'!$F$22))","v":""},null,{"$f":"IF(ISERROR(('DataGathering-GenFund'!$G$22-$C$7-'DataGathering-GenFund'!$G$24)/'DataGathering-GenFund'!$G$22),\"\",('DataGathering-GenFund'!$G$22-$C$7-'DataGathering-GenFund'!$G$24)/'DataGathering-GenFund'!$G$22)","v":""},{"$f":"IF(ISERROR(('DataGathering-GenFund'!$H$22-$D$7-'DataGathering-GenFund'!$H$24)/'DataGathering-GenFund'!$H$22),\"\",('DataGathering-GenFund'!$H$22-$D$7-'DataGathering-GenFund'!$H$24)/'DataGathering-GenFund'!$H$22)","v":""},{"$f":"IF(ISERROR(('DataGathering-GenFund'!$I$22-$E$7-'DataGathering-GenFund'!$I$24)/'DataGathering-GenFund'!$I$22),\"\",('DataGathering-GenFund'!$I$22-$E$7-'DataGathering-GenFund'!$I$24)/'DataGathering-GenFund'!$I$22)","v":""},null,{"$f":"IF(ISERROR(('DataGathering-GenFund'!$J$22-$G$7-'DataGathering-GenFund'!$J$24)/'DataGathering-GenFund'!$J$22),\"\",('DataGathering-GenFund'!$J$22-$G$7-'DataGathering-GenFund'!$J$24)/'DataGathering-GenFund'!$J$22)","v":""},null,{"$f":"IF(ISERROR(('DataGathering-GenFund'!$K$22-$I$7-'DataGathering-GenFund'!$K$24)/'DataGathering-GenFund'!$K$22),\"\",('DataGathering-GenFund'!$K$22-$I$7-'DataGathering-GenFund'!$K$24)/'DataGathering-GenFund'!$K$22)","v":""},null,null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula ",null,null,null,"Score:\nWarning-Red: Persistent & increasing deficits over consecutive years.",null,null,null,null,null,null,null,null,"Beige cells may be altered"]
[null,null,null,null,"Caution-Yellow: Deficits are infrequent or relatively marginal compared to fund balance (see #2 below) and/or there is a reasonable plan for bringing revenues and spending into balance."]
[null,null,null,null,"Good-Green: not an issue of concern.",null,null,null,null,null,null,0]
["Definitions\n\nNet General Revenue Demand. Net General Revenue Demand equals total general fund expenditures and transfers out, minus one-time expenditures of general purpose revenue minus department revenue.\n\nDepartment Revenues. Revenues restricted to specific programs.\n\nGeneral Purpose Revenues. Discretionary taxes including property taxes (other than voter approved debt service), property transfer tax, general sales and use tax, business license tax, transient occupancy tax, utility users tax, construction tax, and other discretionary taxes; franchises; fines, forfeitures and penalties; investment earnings; rents, concessions, royalties; homeowners property tax relief reimbursement; non-restricted state and federal grants; and non-restricted transfers in.\n\nOne-time (temporary) Revenues. One-time (temporary) revenues include any non-recurring revenues that derive from short-term activities and cannot be relied upon in the long-run (over multiple years).  This might include revenues from land sales, one-time transfers from other funds, limited-term grants, court settlements or major donations.  Optionally, you might consider also excluding receipt of construction-related revenues from a given project that are significantly in excess of an “average” year, if this represents a level of revenue unlikely to be repeated.  Back taxes and late payments do not have to be omitted because they just make up for what was not received in a prior year.\n\nOngoing General Propose Revenues. Ongoing General Propose Revenues equals total general purpose revenues minus one-time (temporary) general purpose revenues. \n\nNet Operating Expenditures. Net operating expenditures equals total expenditures (after credits/reimbursements) and transfers out, minus one time expenditures capital improvement expenditures (or transfers out for capital purposes).\n\nUnbudgeted Current Liabilities. The amortized costs of long-term general fund liabilities not already included in “net operating expenditures.” This includes amounts not budgeted or expended that “should be” in order to pay the current year portion of liabilities. Examples: unbudgeted actuarially required contributions (ARC) to Other Post-Employment Benefits (OPEB) or pension systems; unbudgeted earned leave cash-out liabilities; maintenance and replacement costs of vehicles, technology, buildings, streets, and other properties and infrastructure. If there is insufficient information to determine these unbudgeted liabilities, that, in itself, is reason for substantial concern (See indicator #9). "]
[]
[null,null,{"$f":"+C14","v":"County of"},{"$f":"IF('DataGathering-GenFund'!$J$1=0,\"\",'DataGathering-GenFund'!$J$1)","v":""},null,null,null,{"$f":"IF('DataGathering-GenFund'!$I$2=0,\"\",'DataGathering-GenFund'!$I$2)","v":"General Fund - Discretionary"},null,null,null,null,null,"<= These fields populate from data entered in the \"data gathering\" tab."]
["Fund Balance\n        A positive fund balance, or reserves, is important for any government to withstand financial risk over time. Unanticipated fluctuations in revenues may occur from economic impacts or state take-ways. “Financial reserves” are important to meet unforeseen revenue shortfalls or expenditure overages.  But reserves cannot be relied upon to cover financial shortfalls that are more than temporary. (See Indicator #7)  An unplanned decline in unreserved fund balances as a percentage of operating revenues over time suggests the government is less able to withstand financial emergencies.\n        The right level of fund balance varies depending on many factors including levels of risk and revenue volatility but, generally speaking, dropping below 8% may be cause for concern.  The Government Finance Officers Association recommends maintaining a 5-15% reserve.\n"]
["2A","Fund Balance"]
[{"$f":"+A16","v":"2014-15"},null,{"$f":"+C16","v":"2015-16"},{"$f":"+D16","v":"2016-17"},{"$f":"+E16","v":"2017-18"},null,{"$f":"+G16","v":"2018-19"},null,{"$f":"+I16","v":"2019-20"}]
[{"$f":"IF(ISERROR('DataGathering-GenFund'!$F$29/'DataGathering-GenFund'!$F$12),\"\",'DataGathering-GenFund'!$F$29/'DataGathering-GenFund'!$F$12)","v":""},null,{"$f":"IF(ISERROR('DataGathering-GenFund'!$G$29/'DataGathering-GenFund'!$G$12),\"\",'DataGathering-GenFund'!$G$29/'DataGathering-GenFund'!$G$12)","v":""},{"$f":"IF(ISERROR('DataGathering-GenFund'!$H$29/'DataGathering-GenFund'!$H$12),\"\",'DataGathering-GenFund'!$H$29/'DataGathering-GenFund'!$H$12)","v":""},{"$f":"IF(ISERROR('DataGathering-GenFund'!$I$29/'DataGathering-GenFund'!$I$12),\"\",'DataGathering-GenFund'!$I$29/'DataGathering-GenFund'!$I$12)","v":""},null,{"$f":"IF(ISERROR('DataGathering-GenFund'!$J$29/'DataGathering-GenFund'!$J$12),\"\",'DataGathering-GenFund'!$J$29/'DataGathering-GenFund'!$J$12)","v":""},null,{"$f":"IF(ISERROR('DataGathering-GenFund'!$K$29/'DataGathering-GenFund'!$K$12),\"\",'DataGathering-GenFund'!$K$29/'DataGathering-GenFund'!$K$12)","v":""},null,null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,"Score:\nWarning-Red: Persistently & substantially decreasing or is below 8%."]
[null,null,null,null,"Caution-Yellow: Has decreased but remains above 8% and there is a reasonable plan for stabilizing."]
[null,null,null,null,"Green - not concerning",null,null,null,null,null,null,0]
["Definitions\nUnreserved fund balance  is fund balance not reserved in accordance with state law, charter or contractual obligation.  This includes total fund balance minus nonspendable, restricted or committed resources. However, you should review resources categorized as \"committed\" and include in \"unreserved fund balance\" any amounts that, even if only in time of emergency, could be accessed with action of the Board of Supervisors. County General Reserves (per the County Budget Act, GC§29086) are to be included in \"unreserved fund balance.\"\nNet operating expenditures. Net operating expenditures equals expenditures (after credits/reimbursements) and transfers out, minus capital improvement expenditures (or transfers out for capital purposes), minus expenditures of revenues legally restricted to special purposes."]
[" 1. See Stephen J. Gauthier, “Governmental Accounting, Auditing, and Financial Reporting.” (The “Blue Book”) 2012 Government Finance Officers Association."]
["2B","Fund Balance - Taking into account the current cost of unbudgeted liabilities"]
[{"$f":"+A26","v":"2014-15"},null,{"$f":"+C26","v":"2015-16"},{"$f":"+D26","v":"2016-17"},{"$f":"+E26","v":"2017-18"},null,{"$f":"+G26","v":"2018-19"},null,{"$f":"+I26","v":"2019-20"}]
[{"$f":"IF(ISERROR('DataGathering-GenFund'!$F$31/'DataGathering-GenFund'!$F$12),\"\",'DataGathering-GenFund'!$F$31/'DataGathering-GenFund'!$F$12)","v":""},null,{"$f":"IF(ISERROR('DataGathering-GenFund'!$G$31/'DataGathering-GenFund'!$G$12),\"\",'DataGathering-GenFund'!$G$31/'DataGathering-GenFund'!$G$12)","v":""},{"$f":"IF(ISERROR('DataGathering-GenFund'!$H$31/'DataGathering-GenFund'!$H$12),\"\",'DataGathering-GenFund'!$H$31/'DataGathering-GenFund'!$H$12)","v":""},{"$f":"IF(ISERROR('DataGathering-GenFund'!$I$31/'DataGathering-GenFund'!$I$12),\"\",'DataGathering-GenFund'!$I$31/'DataGathering-GenFund'!$I$12)","v":""},null,{"$f":"IF(ISERROR('DataGathering-GenFund'!$J$31/'DataGathering-GenFund'!$J$12),\"\",'DataGathering-GenFund'!$J$31/'DataGathering-GenFund'!$J$12)","v":""},null,{"$f":"IF(ISERROR('DataGathering-GenFund'!$K$31/'DataGathering-GenFund'!$K$12),\"\",'DataGathering-GenFund'!$K$31/'DataGathering-GenFund'!$K$12)","v":""}]
[null,null,{"$f":"+C23","v":"County of"},{"$f":"IF('DataGathering-GenFund'!$J$1=0,\"\",'DataGathering-GenFund'!$J$1)","v":""},null,null,null,{"$f":"IF('DataGathering-GenFund'!$I$2=0,\"\",'DataGathering-GenFund'!$I$2)","v":"General Fund - Discretionary"},null,null,null,0,null,"<= These fields populate from data entered in the \"data gathering\" tab."]
["Capital Asset Condition\n        Capital assets must be maintained and replaced over time.  This requires annual resource commitment such that the value of assets and asset improvements does not decline. Use general fund data if available, government-wide if not."]
[3,"Capital Asset Condition"]
[{"$f":"+A81","v":"2014-15"},null,{"$f":"+C81","v":"2015-16"},{"$f":"+D81","v":"2016-17"},{"$f":"+E81","v":"2017-18"},null,{"$f":"+G81","v":"2018-19"},null,{"$f":"+I81","v":"2019-20"}]
[{"$f":"IF(ISERROR(('DataGathering-GenFund'!F34-'DataGathering-GenFund'!F33)/'DataGathering-GenFund'!F33),\"\",('DataGathering-GenFund'!F34-'DataGathering-GenFund'!F33)/'DataGathering-GenFund'!F33)","v":""},null,{"$f":"IF(ISERROR(('DataGathering-GenFund'!G34-'DataGathering-GenFund'!G33)/'DataGathering-GenFund'!G33),\"\",('DataGathering-GenFund'!G34-'DataGathering-GenFund'!G33)/'DataGathering-GenFund'!G33)","v":""},{"$f":"IF(ISERROR(('DataGathering-GenFund'!H34-'DataGathering-GenFund'!H33)/'DataGathering-GenFund'!H33),\"\",('DataGathering-GenFund'!H34-'DataGathering-GenFund'!H33)/'DataGathering-GenFund'!H33)","v":""},{"$f":"IF(ISERROR(('DataGathering-GenFund'!I34-'DataGathering-GenFund'!I33)/'DataGathering-GenFund'!I33),\"\",('DataGathering-GenFund'!I34-'DataGathering-GenFund'!I33)/'DataGathering-GenFund'!I33)","v":""},null,{"$f":"IF(ISERROR(('DataGathering-GenFund'!J34-'DataGathering-GenFund'!J33)/'DataGathering-GenFund'!J33),\"\",('DataGathering-GenFund'!J34-'DataGathering-GenFund'!J33)/'DataGathering-GenFund'!J33)","v":""},null,{"$f":"IF(ISERROR(('DataGathering-GenFund'!K34-'DataGathering-GenFund'!K33)/'DataGathering-GenFund'!K33),\"\",('DataGathering-GenFund'!K34-'DataGathering-GenFund'!K33)/'DataGathering-GenFund'!K33)","v":""},null,null,null,null,"<= These fields populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,"Score:\nWarning-Red: persistent and substantially negative trend"]
[null,null,null,null,"Caution-Yellow: negative or decreasing but there is a reasonable plan for stabilizing."]
[null,null,null,null,"Good-Green: not an issue of concern.",null,null,null,null,null,null,0]
[]
["Liquidity\n        A decreasing amount of cash and short-term investments as a percentage of current liabilities suggests the government is less able to pay its short-term obligations.  Increasing current liabilities at the end of the year as a percentage of net operating revenues indicates liquidity problems and/or deficit spending.  Liquidity measures the amount of readily available financial resources relative to immediate financial commitments (current liabilities).  \n"]
[4,"Liquidity"]
[{"$f":"+A26","v":"2014-15"},null,{"$f":"+C26","v":"2015-16"},{"$f":"+D26","v":"2016-17"},{"$f":"+E26","v":"2017-18"},null,{"$f":"+G26","v":"2018-19"},null,{"$f":"+I26","v":"2019-20"}]
[{"$f":"IF(ISERROR('DataGathering-GenFund'!$F$37/'DataGathering-GenFund'!$F$39),\"\",'DataGathering-GenFund'!$F$37/'DataGathering-GenFund'!$F$39)","v":""},null,{"$f":"IF(ISERROR('DataGathering-GenFund'!$G$37/'DataGathering-GenFund'!$G$39),\"\",'DataGathering-GenFund'!$G$37/'DataGathering-GenFund'!$G$39)","v":""},{"$f":"IF(ISERROR('DataGathering-GenFund'!$H$37/'DataGathering-GenFund'!$H$39),\"\",'DataGathering-GenFund'!$H$37/'DataGathering-GenFund'!$H$39)","v":""},{"$f":"IF(ISERROR('DataGathering-GenFund'!$I$37/'DataGathering-GenFund'!$I$39),\"\",'DataGathering-GenFund'!$I$37/'DataGathering-GenFund'!$I$39)","v":""},null,{"$f":"IF(ISERROR('DataGathering-GenFund'!$J$37/'DataGathering-GenFund'!$J$39),\"\",'DataGathering-GenFund'!$J$37/'DataGathering-GenFund'!$J$39)","v":""},null,{"$f":"IF(ISERROR('DataGathering-GenFund'!$K$37/'DataGathering-GenFund'!$K$39),\"\",'DataGathering-GenFund'!$K$37/'DataGathering-GenFund'!$K$39)","v":""},null,null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,"Score:\nWarning-Red: Persistently & substantially decreasing."]
[null,null,null,null,"Caution-Yellow: Has decreased but there is a reasonable plan for stabilizing."]
[null,null,null,null,"Good-Green: not an issue of concern.",null,null,null,null,null,null,0]
["Definitions"]
["Current liabilities  is the sum of all liabilities due at the end of the fiscal year including short-term debt, current portion of long-term debt, all accounts payable accrued liabilities and other current liabilities due to be paid within 60 days. "]
["IMPORTANT: Include "]
["·         Advances and amounts due to other funds, including internal funds from pooled cash transactions or borrowing."]
["·         Increases in debt service payments due to financings."]
["Cash and short-term investments includes cash on hand and in the bank as well as other assets that can easily be converted to cash.  "]
[]
[null,null,{"$f":"+C36","v":"County of"},{"$f":"IF('DataGathering-GenFund'!$J$1=0,\"\",'DataGathering-GenFund'!$J$1)","v":""},null,null,null,{"$f":"IF('DataGathering-GenFund'!$I$2=0,\"\",'DataGathering-GenFund'!$I$2)","v":"General Fund - Discretionary"},null,null,null,null,null,"<= These fields populate from data entered in the \"data gathering\" tab."]
["Fixed Costs and Budget Flexibility\n     Increasing fixed costs as a percentage of net operating expenditures may indicate an unsustainable financial structure where the government has limited ability to make necessary budget changes. Fixed does not mean static.  Fixed costs may be changing over time but cannot be easily altered. \n     A major component of general fund spending is labor costs for salaries and benefits: often these costs are anything but fixed, as costs continue to escalate, but as long as the employees are on the payroll, these costs are unavoidable. This indicator computes the proportion of net annual operating expenses that these fixed and labor costs represent, to show the degree of flexibility the agency has in making budget reductions. As with all these measures, compute this for general fund and other funds separately.\n    Some cities and counties find themselves using the general fund to subsidize golf course rates; water, sewer, transit, parking or other enterprise operations; or pay debt service or capital improvement costs that should arguably be paid by proprietary or special revenue funds.  Often this is because user fees are not sufficiently high enough to be self-supporting.  Sometimes it is because debt obligations were incurred that burdened the general fund with a pledge to support bonds if the intended repayment source (development fees, enterprise fund) proved inadequate.  These subsidies limit budget flexibility if they are a significant portion of the general fund net operating expenditures and/or if the trend is increasing.\n    Constraints on the budgetary discretion of the agency include binding arbitration or required formulas or third party agreement to alter compensation, spending or minimum staffing.  These legal constraints impair the agency’s ability to achieve solutions when in fiscal distress and may also accelerate cost increases over time."]
["Definitions"]
["Salaries and wages are compensation paid directly to employees. "]
["Benefits include costs for contributions to FICA, pension, life insurance, health insurance, etc. and current contributions to self-insurance funds.  "]
["Fixed costs are those costs over which the government has little control in the short run because of contractual agreements, charter restrictions, or state or federal law, other than those costs already included under salaries, wages or benefits.  Non-labor fixed costs include debt service, retiree health payments, lease-purchase payments, utilities, contracted goods and services, etc.   "]
["Net operating expenditures. Net operating expenditures equals expenditures (after credits/reimbursements) and transfers out, minus capital improvement expenditures (or transfers out for capital purposes), minus expenditures of revenues legally restricted to special purposes."]
["Subsidy Expenditures / Transfers Out.  Total of expenditures and transfers out to support enterprises or special funds."]
["5a","Fixed Costs"]
[{"$f":"+A47","v":"2014-15"},null,{"$f":"+C47","v":"2015-16"},{"$f":"+D47","v":"2016-17"},{"$f":"+E47","v":"2017-18"},null,{"$f":"+G47","v":"2018-19"},null,{"$f":"+I47","v":"2019-20"}]
[{"$f":"IF(ISERROR(('DataGathering-GenFund'!$F$54)/'DataGathering-GenFund'!$F$12),\"\",('DataGathering-GenFund'!$F$54)/'DataGathering-GenFund'!$F$12)","v":""},null,{"$f":"IF(ISERROR(('DataGathering-GenFund'!$G$54)/'DataGathering-GenFund'!$G$12),\"\",('DataGathering-GenFund'!$G$54)/'DataGathering-GenFund'!$G$12)","v":""},{"$f":"IF(ISERROR(('DataGathering-GenFund'!$H$54)/'DataGathering-GenFund'!$H$12),\"\",('DataGathering-GenFund'!$H$54)/'DataGathering-GenFund'!$H$12)","v":""},{"$f":"IF(ISERROR(('DataGathering-GenFund'!$I$54)/'DataGathering-GenFund'!$I$12),\"\",('DataGathering-GenFund'!$I$54)/'DataGathering-GenFund'!$I$12)","v":""},null,{"$f":"IF(ISERROR(('DataGathering-GenFund'!$J$54)/'DataGathering-GenFund'!$J$12),\"\",('DataGathering-GenFund'!$J$54)/'DataGathering-GenFund'!$J$12)","v":""},null,{"$f":"IF(ISERROR(('DataGathering-GenFund'!$K$54)/'DataGathering-GenFund'!$K$12),\"\",('DataGathering-GenFund'!$K$54)/'DataGathering-GenFund'!$K$12)","v":""},null,null,null,null,"<= These fields populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,null,null,"For comparison purposes"]
[]
["5b","Fixed Costs plus Labor Costs"]
[{"$f":"+A68","v":"2014-15"},null,{"$f":"+C68","v":"2015-16"},{"$f":"+D68","v":"2016-17"},{"$f":"+E68","v":"2017-18"},null,{"$f":"+G68","v":"2018-19"},null,{"$f":"+I68","v":"2019-20"}]
[{"$f":"IF(ISERROR(('DataGathering-GenFund'!$F$45+'DataGathering-GenFund'!$F$54)/'DataGathering-GenFund'!$F$12),\"\",('DataGathering-GenFund'!$F$45+'DataGathering-GenFund'!$F$54)/'DataGathering-GenFund'!$F$12)","v":""},null,{"$f":"IF(ISERROR(('DataGathering-GenFund'!$G$45+'DataGathering-GenFund'!$G$54)/'DataGathering-GenFund'!$G$12),\"\",('DataGathering-GenFund'!$G$45+'DataGathering-GenFund'!$G$54)/'DataGathering-GenFund'!$G$12)","v":""},{"$f":"IF(ISERROR(('DataGathering-GenFund'!$H$45+'DataGathering-GenFund'!$H$54)/'DataGathering-GenFund'!$H$12),\"\",('DataGathering-GenFund'!$H$45+'DataGathering-GenFund'!$H$54)/'DataGathering-GenFund'!$H$12)","v":""},{"$f":"IF(ISERROR(('DataGathering-GenFund'!$I$45+'DataGathering-GenFund'!$I$54)/'DataGathering-GenFund'!$I$12),\"\",('DataGathering-GenFund'!$I$45+'DataGathering-GenFund'!$I$54)/'DataGathering-GenFund'!$I$12)","v":""},null,{"$f":"IF(ISERROR(('DataGathering-GenFund'!$J$45+'DataGathering-GenFund'!$J$54)/'DataGathering-GenFund'!$J$12),\"\",('DataGathering-GenFund'!$J$45+'DataGathering-GenFund'!$J$54)/'DataGathering-GenFund'!$J$12)","v":""},null,{"$f":"IF(ISERROR(('DataGathering-GenFund'!$K$45+'DataGathering-GenFund'!$K$54)/'DataGathering-GenFund'!$K$12),\"\",('DataGathering-GenFund'!$K$45+'DataGathering-GenFund'!$K$54)/'DataGathering-GenFund'!$K$12)","v":""},null,null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,"Score:\nWarning-Red: Increasing or over 80%."]
[null,null,null,null,"Caution-Yellow: Has increased but is projected to stabilize or decline."]
[null,null,null,null,"Good-Green: not an issue of concern.",null,null,null,null,null,null,0]
[]
[null,null,{"$f":"+C59","v":"County of"},{"$f":"IF('DataGathering-GenFund'!$J$1=0,\"\",'DataGathering-GenFund'!$J$1)","v":""},null,null,null,{"$f":"IF('DataGathering-GenFund'!$I$2=0,\"\",'DataGathering-GenFund'!$I$2)","v":"General Fund - Discretionary"},null,null,null,null,null,"<= These fields populate from data entered in the \"data gathering\" tab."]
[6,"General Fund Subsidy of Other Funds"]
[{"$f":"+A73","v":"2014-15"},null,{"$f":"+C73","v":"2015-16"},{"$f":"+D73","v":"2016-17"},{"$f":"+E73","v":"2017-18"},null,{"$f":"+G73","v":"2018-19"},null,{"$f":"+I73","v":"2019-20"}]
[{"$f":"IF(ISERROR('DataGathering-GenFund'!$F$57/'DataGathering-GenFund'!$F$12),\"\",'DataGathering-GenFund'!$F$57/'DataGathering-GenFund'!$F$12)","v":""},null,{"$f":"IF(ISERROR('DataGathering-GenFund'!$G$57/'DataGathering-GenFund'!$G$12),\"\",'DataGathering-GenFund'!$G$57/'DataGathering-GenFund'!$G$12)","v":""},{"$f":"IF(ISERROR('DataGathering-GenFund'!$H$57/'DataGathering-GenFund'!$H$12),\"\",'DataGathering-GenFund'!$H$57/'DataGathering-GenFund'!$H$12)","v":""},{"$f":"IF(ISERROR('DataGathering-GenFund'!$I$57/'DataGathering-GenFund'!$I$12),\"\",'DataGathering-GenFund'!$I$57/'DataGathering-GenFund'!$I$12)","v":""},null,{"$f":"IF(ISERROR('DataGathering-GenFund'!$J$57/'DataGathering-GenFund'!$J$12),\"\",'DataGathering-GenFund'!$J$57/'DataGathering-GenFund'!$J$12)","v":""},null,{"$f":"IF(ISERROR('DataGathering-GenFund'!$K$57/'DataGathering-GenFund'!$K$12),\"\",'DataGathering-GenFund'!$K$57/'DataGathering-GenFund'!$K$12)","v":""},null,null,null,null,"<= These fields populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,"Score:\nWarning-Red: Increasing or over adopted policy."]
[null,null,null,null,"Caution-Yellow: Has increased but is projected to stabilize or decline.",null,null,null,null,null,null,0]
[null,null,null,null,"Good-Green: not an issue of concern.",null,null,null,null,null,null,0]
[]
[7,"Constraints on Budgetary Discretion"]
["Do charter provisions or other legal commitments (contracts, court decisions/settlements) restrict the Board of Supervisor’s authority?",null,null,null,null,null,null,"Score:\nWarning-Red: \nYes - restrictions."]
[]
["·   Binding arbitration: required submission of a dispute to a third person whose decision is obligatory. "]
["·   Formulas require minimum employee compensation, hiring or staffing levels, or spending levels or require the agreement of others.",null,null,null,null,null,null,"Caution-Yellow: Yes but minimal/workable."]
["·   General fund is pledged as support, or public facilities as security, for non-general fund debt.",null,null,null,null,null,null,"Green - no restrictions"]
["·   Others: restrictions on contracting out, voter-approved tax expiring.",null,null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,{"$f":"+C79","v":"County of"},{"$f":"IF('DataGathering-GenFund'!$J$1=0,\"\",'DataGathering-GenFund'!$J$1)","v":""},null,null,null,{"$f":"IF('DataGathering-GenFund'!$I$2=0,\"\",'DataGathering-GenFund'!$I$2)","v":"General Fund - Discretionary"},null,null,null,null,null,"<= These fields populate from data entered in the \"data gathering\" tab."]
["Financial Practices"]
["An agency’s financial and budgetary management practices may indicate the degree of financial distress.  Practices that should be rarely used include solving budgetary imbalances with temporary revenues or cuts (such as furloughs), internal borrowing from special funds beyond budget years (i.e. for more than cash flow), deferring pension or other employee costs, and backloaded debt service schedules.  Financial trouble is also strongly correlated with a local government’s failure to file financial reports on time."]
[8,"Balancing the Budget with Temporary Funds"]
["Has the general fund (budget or financial year close) been balanced with   l  reserves    l selling assets   l deferring asset maintenance or operating costs ?",null,null,null,{"$f":"+'DataGathering-GenFund'!F5","v":"2014-15"},{"$f":"+'DataGathering-GenFund'!G5","v":"2015-16"},{"$f":"+'DataGathering-GenFund'!H5","v":"2016-17"},"Score: Warning-Red: Yes, multiple years."]
[null,null,null,null,null,null,null,"Caution-Yellow: Yes but minimal/workable."]
[null,null,null,null,null,null,null,"Good-Green: \nNo. Not a concern.",null,null,null,0]
[null,null,null,null,null,null,"Is there an adopted policy on the use of temporary funds?",null,null,null,null,0]
[null,null,null,null,null,null,"Is there an adopted reserves and fund balance policy? ",null,null,null,null,0]
[9,"Balancing the Budget with Borrowing",null,null,null,null,null,null,null,null,null,0]
["Has the general fund (budget or financial year close) been balanced with     l  short-term borrowing, l  internal borrowing (including transfers that must be repaid),  l  amounts owed to other funds from pooled cash ?",null,null,null,{"$f":"+E99","v":"2014-15"},{"$f":"+F99","v":"2015-16"},{"$f":"+G99","v":"2016-17"},"Score: Warning-Red: Yes, multiple instances."]
[null,null,null,null,null,null,null,"Caution-Yellow: Yes but minimal/workable."]
[null,null,null,null,null,null,null,"Good-Green: \nNo. Not a concern.",null,null,null,0]
[null,null,null,null,null,null,"Is there an adopted policy on short-term borrowing?",null,null,null,null,0]
[null,null,null,null,null,null,"Is there an adopted policy on interfund transfers?",null,null,null,null,0]
[10,"Balancing the Budget by Deferring Employee Compensation Costs",null,null,null,null,null,null,null,null,null,0]
["Has the general fund been balanced by deferring payments for or not paying the current actuarially determined costs of …",null,null,null,{"$f":"+E105","v":"2014-15"},{"$f":"+F105","v":"2015-16"},{"$f":"+G105","v":"2016-17"},"Score: Warning-Red: Yes, multiple years."]
["l  pension, other post-employment benefit liabilities (e.g., compensated absences, deferred comp, retiree medical, etc.)",null,null,null,null,null,null,"Caution-Yellow: Yes but minimal/workable."]
["l   risk programs (e.g., workers comp and liability funds, etc.)\nl pension obligation bonds which presume overly optimistic payroll growth?",null,null,null,null,null,null,"Good-Green: \nNo. Not a concern.",null,null,null,0]
[null,null,null,null,null,null,"Is there an adopted policy on the payment of employee compensation costs?",null,null,null,null,0]
[null,null,{"$f":"+C95","v":"County of"},{"$f":"IF('DataGathering-GenFund'!$J$1=0,\"\",'DataGathering-GenFund'!$J$1)","v":""},null,null,null,{"$f":"IF('DataGathering-GenFund'!$I$2=0,\"\",'DataGathering-GenFund'!$I$2)","v":"General Fund - Discretionary"},null,null,null,null,null,"<= These fields populate from data entered in the \"data gathering\" tab."]
[11,"Balancing the Budget with Backloaded Debt Service "]
["Have general fund debt payment schedules been backloaded?\n(e.g., capital appreciation bonds, pension obligation bonds, or other financings with ballon payments or deferred/increasing payment schedules over time)",null,null,null,{"$f":"+E111","v":"2014-15"},{"$f":"+F111","v":"2015-16"},{"$f":"+G111","v":"2016-17"},"Score: \nWarning-Red: Backloaded payments"]
[]
[null,null,null,null,null,null,null,"Caution-Yellow: minimal/workable."]
[null,null,null,null,null,null,null,"Good-Green: \nNot a concern.",null,null,null,0]
[null,null,null,null,null,null,"Is there an adopted policy favoring pay-as-you-go financing?",null,null,null,null,0]
[null,null,null,null,null,null,"Is there an adopted debt management / affordability policy?",null,null,null,null,0]
[12,"Funding Operating Costs with Non-recurring Revenues (e.g. development, etc.)",null,null,null,null,null,null,null,null,null,0]
["Has the general fund (budget or financial year close) been balanced relying on non-recurring revenues, such as from land-use development, to fund on-going operating costs or debt service other than work associated with those temporary revenues? (e.g., developer fees or taxes, sales taxes from construction, etc. funding other than building/planning staff)",null,null,null,{"$f":"+E117","v":"2014-15"},{"$f":"+F117","v":"2015-16"},{"$f":"+G117","v":"2016-17"},"Score: \nWarning-Red: Yes."]
[null,null,null,null,null,null,null,"Caution-Yellow: Yes but minimal/workable."]
[null,null,null,null,null,null,null,"Good-Green: \nNo. Not a concern.",null,null,null,0]
[null,null,null,null,null,null,"Is there an adopted long-range financial plan and policy?",null,null,null,null,0]
[13,"Timeliness and Accuracy of Financial Reports",null,null,null,null,null,null,null,null,null,1]
["Have annual financial reports not been filed on time  ? ",null,null,null,{"$f":"+E124","v":"2014-15"},{"$f":"+F124","v":"2015-16"},{"$f":"+G124","v":"2016-17"},"Score: Warning-Red: Yes. Not filed on time."]
["l  Comprehensive Annual Financial Report (CAFR)   ",null,null,null,null,null,null,"Caution-Yellow: Yes but special circumstances."]
["l  State Controller’s Financial Transactions Report ",null,null,null,null,null,null,"Green- No: filed correctly and on time.",null,null,null,0]
[14,"Alignment of Service Level Priorities to Financial Health",null,null,null,null,null,null,null,null,null,0]
["Given indicators 1-13, do public service levels meet standards in this community ? ",null,null,null,null,"Score: Warning-Red:  Service levels standards are out of sync with financial condition."]
["e.g., emergency response times, road condition, facility maintenance, etc.",null,null,null,null,"Caution-Yellow: Service level standards exceed financial capacity but there is a plan for improvement."]
[null,null,null,null,null,"Green- Service level standards are aligned with the county's financial health. ",null,null,null,null,null,0]
[null,null,null,null,null,null,null,null,null,null,null,0]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,0]

{"sheet":"DataGathering-GenFund","index":4,"n_rows":58,"n_cols":13,"rows_kept":58,"cut":false,"hidden":false,"merged":[[21,2,21,3],[26,2,26,3],[18,1,18,10],[50,2,50,3],[51,2,51,3],[27,2,27,3],[37,1,37,10],[29,1,29,10],[39,1,39,10],[44,2,44,3],[36,2,36,3],[38,2,38,3],[32,2,33,3],[34,1,34,10],[30,2,30,3],[22,1,22,10],[3,0,3,1],[3,2,3,3],[17,2,17,3],[15,1,15,10],[5,0,9,0],[4,1,4,4],[5,2,9,3],[14,0,15,0],[17,0,18,0],[13,1,13,10],[0,9,0,10],[1,8,1,10],[16,2,16,3],[23,0,24,0],[24,1,24,10],[23,2,23,3],[14,2,14,3],[12,1,12,10],[11,0,12,0],[11,2,11,3],[10,2,10,3],[20,1,20,10],[19,0,20,0],[19,2,19,3],[0,3,0,7],[1,3,1,7],[53,0,54,0],[46,2,46,3],[47,2,47,3],[48,2,48,3],[53,2,53,3],[54,1,54,10],[49,2,49,3],[36,0,37,0],[28,2,28,3],[28,0,29,0],[38,0,42,0],[44,0,45,0],[45,1,45,10],[33,0,34,0],[57,1,57,10],[25,1,25,10],[31,1,31,10],[35,1,35,10],[43,1,43,10],[55,1,55,10],[52,2,52,3],[56,2,56,3]]}
[null,null,null,{"$f":"+'Cover&Contents'!B2:B2","v":"The California County Financial Health Diagnostic"},null,null,null,null,"County of",null,null,"Beige cells may be altered"]
[null,null,null,"Data Gathering Worksheet",null,null,null,null,"General Fund - Discretionary",null,null,"<== You can alter the fund name here."]
[" This worksheet is a tool to 1) help the analyst find the figures and put them together, 2) more clearly define all of the data elements and terms – and tie them back to standard GASB elements etc. as much as possible."]
["Data Component",null,"Source of Data",null,"Use for Indicator","Year-2","PriorYr","CurrentYr (estimated)","Next Year (projected)","Year+2 (projected)","Year+3 (projected)",null,"return to \ncontents"]
[null,null,null,null,null,"2014-15","2015-16","2016-17","2017-18","2018-19","2019-20","<== You can alter the years here."]
["a\n","     Salaries, wages and benefits","financial system / staff analysis"]
[null,"     Services and supplies"]
[null,"     Other"]
[null,"     Transfers out"]
[null,{"$f":"+\"Gross \"&I2&\" expenditures & transfers out\"","v":"Gross General Fund - Discretionary expenditures & transfers out"},null,null,1,{"$f":"+F6+F7+F8+F9","v":0},{"$f":"+G6+G7+G8+G9","v":0},0,0,0,0]
["b","One-time expenditures including capital project expenditures",null,null,1]
["c","Net operating expenditures","line “a” minus \nline \"b\"",null,"1, 2, 5, 6",{"$f":"+F10-F11","v":0},{"$f":"+G10-G11","v":0},0,0,0,0]
[null,"Net operating expenditures. Net operating expenditures equals gross expenditures minus capital improvement expenditures minus expenditures of revenues legally restricted to special purposes."]
[null,"Notes, methods, assumptions on a-c."]
["d","Department revenues","financial system / staff analysis",null,1]
[null,"Department revenues. Revenues restricted to specific programs."]
["e","Net general revenue demand","line \"c\" minus \nline \"d\"",null,1,{"$f":"+F12-F15","v":0},{"$f":"+G12-G15","v":0},0,0,0,0]
["f","General purpose revenues","Staff analysis"]
[null,"General purpose revenues. Discretionary taxes including property taxes (other than voter approved debt service), property transfer tax, general sales and use tax, business license tax, transient occupancy tax, utility users tax, construction tax, and other discretionary taxes; franchises; fines, forfeitures and penalties; investment earnings; rents, concessions, royalties; homeowners property tax relief reimbursement; non-restricted state and federal grants; and non-restricted transfers in."]
["g","One-time (temporary) general purpose revenues ","Staff analysis"]
[null,"One-time (temporary) revenues. One-time (temporary) revenues include any non-recurring revenues that derive from short-term activities and cannot be relied upon in the long-run (over multiple years).  This might include revenues from land sales, one-time transfers from other funds, limited-term grants, court settlements or major donations.  Optionally, you might consider also excluding receipt of construction-related revenues from a given project that are significantly in excess of an “average” year, if this represents a level of revenue unlikely to be repeated.  Back taxes and late payments do not have to be omitted because they just make up for what was not received in a prior year."]
["h","Ongoing general purpose revenues","line \"e\" minus \nline \"g\"",null,1,{"$f":"+F18-F20","v":0},{"$f":"+G18-G20","v":0},0,0,0,0]
[null,"Notes, methods, assumptions on d-h."]
["\ni","Unbudgeted current liabilities","Staff analysis",null,1,0,0,0]
[null,"Unbudgeted current liabilities. The amortized costs of long term general fund liabilities not already included in “net operating expenditures.” This includes amounts not budgeted or expended that “should be” in order to pay the current year portion of liabilities. Examples: unbudgeted actuarially required contributions (ARC) to Other Post-Employment Benefits (OPEB) or pension systems; unbudgeted earned leave cash-out liabilities; maintenance and replacement costs of vehicles, technology, buildings, streets, and other properties and infrastructure."]
[null,"Notes, methods, assumptions on unbudgeted liabilities."]
["j","Total Fund Balance","CAFR* Balance Sheet",null,null,null,null,null,{"$f":"+I29+I28","v":0},{"$f":"+J29+J28","v":0},{"$f":"+K29+K28","v":0}]
["k","Nonspendable, restricted and committed fund balance","CAFR* Balance Sheet",null,null,null,null,null,{"$f":"+H28","v":0},{"$f":"+I28","v":0},0]
["\nl","Unreserved fund balance including county General Reserve","line “j” \nminus line “k” *",null,2,{"$f":"+F27-F28","v":0},0,0,{"$f":"+H29+I22-I17","v":0},{"$f":"+I29+J22-J17","v":0},{"$f":"+J29+K22-K17","v":0}]
[null,"Unreserved fund balance is fund balance not reserved in accordance with state law, charter or contractual obligation.  This includes total fund balance minus nonspendable, restricted or committed resources.  However:  \u0086\n\u0086 You should review resources categorized as \"committed\" and include in \"unreserved fund balance\" any amounts that, even if only in time of emergency, could be accessed with action of the Board of Supervisors. County General Reserves (per the County Budget Act, GC§29086) are to be included in \"unreserved fund balance.\"\n\u0086  Uncollectible loans or other receivables should not be included in this measure of unreserved fund balance.\n\n\n\n\n\n\n\n\n 1. See Stephen J. Gauthier, “Governmental Accounting, Auditing, and Financial Reporting.” (The “Blue Book”) 2012 Government Finance Officers Association.\n\n* \u0086By default, this worksheet computes future years from prior year unreserved fundbalance (line l) plus on-going general purpose revenues (line h) minus net general revenue demand (line e)."]
["l2","Unreserved fund balance taking into account undbudgeted current liabilities","line “l” \nminus line “i” *",null,2,{"$f":"+F29-F24","v":0},{"$f":"+G29-G24","v":0},0,0,0,0]
[null,"Notes, methods, assumptions on fund balance."]
["m","Beginning net value of capital assets, government-wide","CAFR Notes to financial statements and staff analysis (for projection)",null,3]
["n","Ending net value of capital assets, government-wide",null,null,3]
[null,"Capital Asset Condition.   A high ratio suggests the government is keeping pace, on average, with the aging of its capital assets."]
[null,"Notes, methods, assumptions on capital asset condition."]
["o","Cash and short-term investments (fund level)","CAFR* Balance Sheet",null,4]
[null,"Cash and short-term investments includes cash on hand and in the bank as well as other assets that can easily be converted to cash.  "]
["p","Current liabilities (fund level)","CAFR* Balance Sheet* ",null,4]
[null,"*Current liabilities is the sum of all liabilities due at the end of the fiscal year including short-term debt, current portion of long-term debt, all accounts payable accrued liabilities and other current liabilities due to be paid within 60 days. "]
[null,"IMPORTANT: Include "]
[null,"      l         Advances and amounts due to other funds, including internal funds from pooled cash transactions or borrowing."]
[null,"      l         Increases in debt service payments due to financings."]
[null,"Notes, methods, assumptions on o-p."]
["q","Salaries + wages + benefits","(from line \"a\" above)",null,"5b",{"$f":"+F6","v":0},{"$f":"+G6","v":0},0,0,0,0]
[null,"Salaries and wages are compensation paid directly to employees.  Benefits include costs for contributions to FICA, pension, life insurance, health insurance, etc. and current contributions to self-insurance funds.  "]
["r","Debt service principal \n(long term debt only)","Staff analysis"]
["s","Debt service interest\n(long term and short term debt)","Staff analysis"]
["t","Expenditures and transfers for federal, state or court mandated activities other than those included in \"q\" above.","Staff analysis"]
["u","Pension payments required by contract or other legal obligation (other than those included in \"q,\" \"s,\" or \"t\" above)","Staff analysis \nand CAFR"]
["v","Lease purchase payments","Staff analysis \nand CAFR"]
["w","Other long term contracts","Staff analysis"]
["x","Other fixed costs","Staff analysis"]
["y","Fixed costs ","Sum of r through x above",null,"5a, 5b",{"$f":"+F53+F52+F51+F50+F49+F48+F47","v":0},0,0,0,0,0]
[null,"Fixed costs are those costs over which the government has little control in the short run because of contractual agreements, charter restrictions, or state or federal law, other than those costs already included under salaries, wages or benefits.  Fixed costs include debt service, retiree health payments, lease-purchase payments, utilities, contracted goods and services, etc.  When it is unclear if certain costs are \"fixed,\" consider a rule of thumb such as \"can elected officials cut these costs by at least ten percent in one year.\""]
[null,"Notes, methods, assumptions on fixed costs."]
["z","Subsidy expenditures and      subsidy transfers out","Staff analysis based on CAFR Statement of Revenues and Expenditures ",null,6]
[null,"Notes, methods, assumptions on Interfund subsidies."]

{"sheet":"SpecProgrFund1","index":5,"n_rows":60,"n_cols":15,"rows_kept":60,"cut":false,"hidden":false,"merged":[[58,1,58,2],[38,0,38,2],[12,0,12,11],[7,0,7,10],[11,8,11,10],[11,0,11,7],[26,6,26,7],[26,8,26,9],[26,10,26,11],[44,1,44,2],[42,1,42,2],[41,1,41,2],[40,1,40,2],[39,1,39,2],[16,6,16,7],[16,8,16,9],[16,10,16,11],[25,2,25,3],[25,4,25,5],[25,6,25,7],[25,8,25,9],[25,10,25,11],[10,8,10,9],[10,10,10,11],[26,0,26,1],[27,0,29,4],[26,2,26,3],[26,4,26,5],[20,0,20,11],[22,4,22,6],[23,0,23,11],[25,0,25,1],[27,5,27,10],[28,5,28,10],[29,5,29,10],[16,0,16,1],[17,0,19,4],[17,5,17,10],[18,5,18,10],[19,5,19,10],[16,2,16,3],[16,4,16,5],[13,4,13,6],[14,1,14,11],[15,0,15,1],[15,2,15,3],[15,4,15,5],[15,6,15,7],[15,8,15,9],[15,10,15,11],[13,7,13,11],[10,0,10,1],[10,2,10,3],[10,4,10,5],[10,6,10,7],[8,1,8,11],[9,0,9,1],[9,6,9,7],[9,8,9,9],[9,10,9,11],[9,2,9,3],[9,4,9,5],[5,0,5,1],[5,4,5,5],[6,0,6,1],[6,4,6,5],[5,10,5,11],[1,4,1,6],[2,0,2,11],[3,0,3,11],[4,1,4,11],[5,2,5,3],[6,2,6,3],[5,6,5,7],[5,8,5,9],[6,10,6,11],[6,8,6,9],[6,6,6,7],[1,7,1,11],[30,0,30,11],[31,0,31,11],[32,0,32,11],[33,0,33,11],[56,3,56,4],[57,3,57,4],[58,0,59,0],[58,3,58,4],[59,1,59,11],[56,1,56,2],[57,1,57,2],[51,3,51,4],[52,0,53,0],[52,3,52,4],[53,1,53,11],[54,0,55,0],[54,3,54,4],[55,1,55,11],[51,1,51,2],[52,1,52,2],[54,1,54,2],[46,0,47,0],[46,3,46,4],[47,1,47,11],[49,3,49,4],[50,3,50,4],[46,1,46,2],[48,1,48,2],[49,1,49,2],[50,1,50,2],[40,3,40,4],[41,3,41,4],[42,3,42,4],[43,3,43,4],[44,0,45,0],[44,3,44,4],[45,1,45,11],[35,4,35,8],[35,10,35,11],[36,4,36,8],[36,9,36,11],[38,3,38,4],[48,3,48,4]]}
[null,"Use the Data Gathering worksheet BELOW to collect and input the data that will rollup through these formulas."]
[null,null,null,{"$f":"+J36","v":"County of"},{"$f":"+'DataGathering-GenFund'!J1:J1","v":0},null,null,{"$f":"+J37","v":"Special Program or Fund (e.g. Mental Health, etc.)"},null,null,null,null,null,null,"<= These fields populate from data entered in the \"data gathering\" tab."]
[{"$f":"'Cover&Contents'!B2:B2&\": Financial Health Indicators\"","v":"The California County Financial Health Diagnostic: Financial Health Indicators"},null,null,null,null,null,null,null,null,null,null,null,null,null,"return to \ncontents"]
["Operating Deficit/Surplus - Special Program or Fund - also internal service funds and enterprises. \n          Most programs receive some degree of functional or program revenue such as user fees, earmarked intergovernmental aid, or special taxes. To the extent that operating revenues do not cover operating expenditures, the government's general purpose revenues bear the burden for the program.  In some cases, this support from general revenues is validated by adopted policy that essentially finds those funds necessary and justified for the good of the general community. But in other cases, this general revenue support may be excessive (in the opinion of the governing board), growing in an unsustainable pattern, or placing undue risk on other programs supported by general revenues.  In such a case, the financial health of the local government is impaired.  "]
["1A","Gross Annual Deficit/Surplus - unadjusted, using all general fund revenues and expenditures",null,null,null,null,null,null,null,null,null,null,null,null,"Want to add more years? I can help you. 530-758-3952 coleman@muniwest.com"]
[{"$f":"+'DataGathering-GenFund'!F5","v":"2014-15"},null,{"$f":"+'DataGathering-GenFund'!G5","v":"2015-16"},null,{"$f":"+'DataGathering-GenFund'!H5","v":"2016-17"},null,{"$f":"+'DataGathering-GenFund'!I5","v":"2017-18"},null,{"$f":"+'DataGathering-GenFund'!J5","v":"2018-19"},null,{"$f":"+'DataGathering-GenFund'!K5","v":"2019-20"}]
[{"$f":"IF(ISERROR(($G$41-$G$49)/$G$41),\"\",($G$41-$G$49)/$G$41)","v":""},null,{"$f":"IF(ISERROR(($H$41-$H$49)/$H$41),\"\",($H$41-$H$49)/$H$41)","v":""},null,{"$f":"IF(ISERROR(($I$41-$I$49)/$I$41),\"\",($I$41-$I$49)/$I$41)","v":""},null,{"$f":"IF(ISERROR(($J$41-$J$49)/$J$41),\"\",($J$41-$J$49)/$J$41)","v":""},null,{"$f":"IF(ISERROR(($K$41-$K$49)/$K$41),\"\",($K$41-$K$49)/$K$41)","v":""},null,{"$f":"IF(ISERROR(($L$41-$L$49)/$L$41),\"\",($L$41-$L$49)/$L$41)","v":""},null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,null,null,null,null,null,null,null,"For comparison purposes"]
["1B  ","Net Operating Deficit/Surplus – sustainable, omitting non-recurring revenues"]
[{"$f":"+A6","v":"2014-15"},null,{"$f":"+C6","v":"2015-16"},null,{"$f":"+E6","v":"2016-17"},null,{"$f":"+G6","v":"2017-18"},null,{"$f":"+I6","v":"2018-19"},null,{"$f":"+K6","v":"2019-20"}]
[{"$f":"IF(ISERROR(($G$45-G47-$G$53)/$G$45),\"\",($G$45-G47-$G$53)/$G$45)","v":""},null,{"$f":"IF(ISERROR(($H$45-H47-$H$53)/$H$45),\"\",($H$45-H47-$H$53)/H45)","v":""},null,{"$f":"IF(ISERROR(($I$45-I47-$I$53)/$I$45),\"\",($I$45-I47-$I$53)/$I$45)","v":""},null,{"$f":"IF(ISERROR(($J$45-J47-$J$53)/$J$45),\"\",($J$45-J47-$J$53)/$J$45)","v":""},null,{"$f":"IF(ISERROR(($K$45-K47-$K$53)/$K$45),\"\",($K$45-K47-$K$53)/$K$45)","v":""},null,{"$f":"IF(ISERROR(($L$45-L47-$L$53)/$L$45),\"\",($L$45-L47-$L$53)/$L$45)","v":""},null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,null,null,null,null,"Negative Indicator\n·   Recurring AND static or increasing deficit over consecutive years.",null,null,"For comparison purposes"]
[]
[null,null,null,{"$f":"+D2","v":"County of"},{"$f":"IF('DataGathering-GenFund'!$J$1=0,\"\",'DataGathering-GenFund'!$J$1)","v":""},null,null,{"$f":"+J37","v":"Special Program or Fund (e.g. Mental Health, etc.)"}]
["1C","Net True Operating Deficit/Surplus - complete, adding unbudgeted general fund liabilities"]
[{"$f":"+A10","v":"2014-15"},null,{"$f":"+C10","v":"2015-16"},null,{"$f":"+E10","v":"2016-17"},null,{"$f":"+G10","v":"2017-18"},null,{"$f":"+I10","v":"2018-19"},null,{"$f":"+K10","v":"2019-20"}]
[{"$f":"IF(ISERROR(($G$45-G53-$G$53-G55)/$G$45),\"\",($G$45-G53-$G$53-G55)/$G$45)","v":""},null,{"$f":"IF(ISERROR(($H$45-H53-$H$53-H55)/$H$45),\"\",($H$45-H53-$H$53-H55)/$H$45)","v":""},null,{"$f":"IF(ISERROR(($I$45-I53-$I$53-I55)/$I$45),\"\",($I$45-I53-$I$53-I55)/$I$45)","v":""},null,{"$f":"IF(ISERROR(($J$45-J53-$J$53-J55)/$J$45),\"\",($J$45-J53-$J$53-J55)/$J$45)","v":""},null,{"$f":"IF(ISERROR(($K$45-K53-$K$53-K55)/$K$45),\"\",($K$45-K53-$K$53-K55)/$K$45)","v":""},null,{"$f":"IF(ISERROR(($L$45-L53-$L$53-L55)/$L$45),\"\",($L$45-L53-$L$53-L55)/$L$45)","v":""},null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula ",null,null,null,null,"Score:\nWarning-Red: Persistent & increasing deficits over consecutive years.",null,null,null,null,null,null,null,null,"Beige cells may be altered"]
[null,null,null,null,null,"Caution-Yellow: Deficits are infrequent or relatively marginal compared to fund balance (see #2 below) and/or there is a reasonable plan for bringing revenues and spending into balance."]
[null,null,null,null,null,"Good-Green: not an issue of concern.",null,null,null,null,null,null,0]
["Definitions\n \nGross total revenues. See CAFR Statement of Revenues and Expenditures “total revenues.”\n\nGross total expenditures. See CAFR Statement of Revenues and Expenditures “total expenditures”\n\nNet operating revenues. Net operating revenues equals gross revenues and transfers in, minus revenues restricted to capital improvements minus   revenues legally restricted to special purposes. Transfers related to non-operating activities should be excluded.\n\nTemporary revenues. Temporary revenues include any non-recurring revenues that derive from short-term activities and cannot be relied upon in the long-run (over multiple years).  This might include revenues from land sales, one-time transfers from other funds, limited-term grants, court settlements or major donations.  You might also consider excluding receipt of construction-related revenues from a given project that are significantly in excess of an “average” year, if this represents a level of revenue unlikely to be repeated.  Back taxes and late payments do not have to be omitted because they just make up for what was not received in a prior year.\n\nNet operating expenditures. Net operating expenditures equals total expenditures (after credits/reimbursements) and transfers out, minus capital improvement expenditures (or transfers out for capital purposes), minus expenditures of revenues legally restricted to special purposes.\n\nUnbudgeted current liabilities. The amortized costs of long-term general fund liabilities not already included in “net operating expenditures.” This includes amounts not budgeted or expended that “should be” in order to pay the current year portion of liabilities. Examples: unbudgeted actuarially required contributions (ARC) to Other Post-Employment Benefits (OPEB) or pension systems; unbudgeted earned leave cash-out liabilities; maintenance and replacement costs of vehicles, technology, buildings, streets, and other properties and infrastructure. If there is insufficient information to determine these unbudgeted liabilities, that, in itself, is reason for substantial concern."]
[]
[null,null,null,{"$f":"+D14","v":"County of"},{"$f":"+'DataGathering-GenFund'!J24:J24","v":0},null,null,{"$f":"+J37","v":"Special Program or Fund (e.g. Mental Health, etc.)"},null,null,null,null,null,null,"<= These fields populate from data entered in the \"data gathering\" tab."]
["Fund Balance\n        A positive fund balance, or reserves, is important for any government to withstand financial risk over time. Unanticipated fluctuations in revenues may occur from economic impacts or state take-ways. “Financial reserves” are important to meet unforeseen revenue shortfalls or expenditure overages.  But reserves cannot be relied upon to cover financial shortfalls that are more than temporary. (See Indicator #7) An unplanned decline in unreserved fund balances as a percentage of operating revenues over time suggests the government is less able to withstand financial emergencies.\n        The right level of fund balance varies depending on many factors including levels of risk and revenue volatility but, generally speaking, dropping below 8% may be cause for concern.  The Government Finance Officers Association recommends maintaining a 5-15% reserve.\n"]
[2,"Fund Balance"]
[{"$f":"+A16","v":"2014-15"},null,{"$f":"+C16","v":"2015-16"},null,{"$f":"+E16","v":"2016-17"},null,{"$f":"+G16","v":"2017-18"},null,{"$f":"+I16","v":"2018-19"},null,{"$f":"+K16","v":"2019-20"}]
[{"$f":"IF(ISERROR(($G$59)/$G$53),\"\",($G$59)/$G$53)","v":""},null,{"$f":"IF(ISERROR(($H$59)/$H$53),\"\",($H$59)/$H$53)","v":""},null,{"$f":"IF(ISERROR(($I$59)/$I$53),\"\",($I$59)/$I$53)","v":""},null,{"$f":"IF(ISERROR(($J$59)/$J$53),\"\",($J$59)/$J$53)","v":""},null,{"$f":"IF(ISERROR(($K$59)/$K$53),\"\",($K$59)/$K$53)","v":""},null,{"$f":"IF(ISERROR(($L$59)/$L$53),\"\",($L$59)/$L$53)","v":""},null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,null,"Score:\nWarning-Red: Persistently & substantially decreasing or is below 8%."]
[null,null,null,null,null,"Caution-Yellow: Has decreased but remains above 8% and there is a reasonable plan for stabilizing."]
[null,null,null,null,null,"Green - not concerning",null,null,null,null,null,null,0]
["Definitions"]
["Unreserved fund balance  is fund balance not reserved in accordance with state law, charter or contractual obligation.  This includes total fund balance minus nonspendable, restricted or committed resources. However, you should review resources categorized as \"committed\" and include in \"unreserved fund balance\" any amounts that, even if only in time of emergency, could be accessed with action of the Board of Supervisors. "]
["Net operating expenditures. Net operating expenditures equals expenditures (after credits/reimbursements) and transfers out, minus capital improvement expenditures (or transfers out for capital purposes), minus expenditures of revenues legally restricted to special purposes."]
[" 1. See Stephen J. Gauthier, “Governmental Accounting, Auditing, and Financial Reporting.” (The “Blue Book”) 2012 Government Finance Officers Association."]
[]
[null,null,null,null,"The California Municipal Financial Health Diagnostic ",null,null,null,null,{"$f":"+'DataGathering-GenFund'!I1","v":"County of"},{"$f":"+'DataGathering-GenFund'!J1:J1","v":0},null,"Beige cells may be altered"]
[null,null,null,null,"Data Gathering Worksheet",null,null,null,null,"Special Program or Fund (e.g. Mental Health, etc.)",null,null,null,"<== You can alter the fund name here."]
[" This worksheet is a tool to 1) help the analyst find the figures and put them together, 2) more clearly define all of the data elements and terms – and tie them back to standard GASB elements etc. as much as possible."]
["Data Component",null,null,"Source of Data",null,"Use for Indicator","Year-2","PriorYr","CurrentYr (estimated)","Next Year (projected)","Year+2 (projected)","Year+3 (projected)",null,"return to \ncontents"]
[null,"Year (e.g., 2014-15, etc.)",null,null,null,null,"2013-14","2014-15","2015-16","2016-17","2017-18","2018-19",null,"<== You can alter the years analyzed here."]
["a","Gross program revenues and transfers in",null,"CAFR* Statement of Revenues and Expenditures \n“total revenues”",null,"SF-1A"]
["b","General revenue transfers in if included in \"a\" above",null,"CAFR* Statement of Revenues and Expenditures \n“transfers in”"]
["c","Revenues restricted to capital improvements (by law or contract) ",null,"Staff analysis"]
["d","Revenues legally restricted to special purposes",null,"Staff analysis"]
["e","Net operating revenues",null,"line “a” minus lines \"b\" , and “c”",null,"SF-1B,                                    SF-1C",{"$f":"+G41+G42-G43-G44","v":0},0,0,0,0,0]
[null,"Net operating revenues. Net operating revenues equals gross revenues and transfers in, minus revenues restricted to capital improvements."]
["f","Temporary revenues \n(other than those included in \"c\" or \"d\")",null,"Staff analysis",null,"SF-1B,                                    SF-1C"]
[null,"Temporary revenues. Temporary revenues include any non-recurring revenues that derive from short-term activities and cannot be relied upon in the long-run (over multiple years).  This might include revenues from land sales, one-time transfers from other funds, limited-term grants, court settlements or major donations.  Optionally, you might consider also excluding receipt of construction-related revenues from a given project that are significantly in excess of an “average” year, if this represents a level of revenue unlikely to be repeated.  Back taxes and late payments do not have to be omitted because they just make up for what was not received in a prior year."]
["g","Gross expenditures",null,"CAFR* Statement of Revenues and Expenditures \n“total expenditures”",null,"SF-1A"]
["h","Transfers out",null,"CAFR* Statement of Revenues and Expenditures \n“transfers out”"]
["i","Capital project expenditures                  (other than capital outlay that is not included in current expenditures)",null,"CAFR* Statement of Revenues and Expenditures \n“capital outlay”"]
["ia","Other one-time or temporary expenditures",null,"Staff analysis"]
["j","Net operating expenditures",null,"line “g” plus line \"h\" \nminus line \"i\"       minus line \"ia\"",null,"SF-1B,             SF-1C, SF- 2, SF-4, SF-5",{"$f":"+G49+G50-G51-G44-G52","v":0},0,0,0,0,0]
[null,"Net operating expenditures. Net operating expenditures equals gross expenditures minus capital improvement expenditures.."]
["k","Unbudgeted current liabilities",null,"Staff analysis",null,"SF-1C"]
[null,"Unbudgeted current liabilities. The amortized costs of long term general fund liabilities attributable to this program or fund not already included in “net operating expenditures.” This includes amounts not budgeted or expended that “should be” I order to pay the current year portion of liabilities. Examples: unbudgeted actuarially required contributions (ARC) to Other Post-Employment Benefits (OPEB) or pension systems; unbudgeted earned leave cash-out liabilities; maintenance and replacement costs of vehicles, technology, buildings, streets, and other properties and infrastructure."]
["l","Total Fund Balance",null,"CAFR* Balance Sheet",null,"-"]
["m","Nonspendable, restricted and committed fund balance",null,"CAFR* Balance Sheet",null,"-"]
["n","Unreserved fund balance",null,"line “l” \nminus line “m”",null,"SF-2",{"$f":"+G57-G58","v":0},0,0,0,0,0]
[null,"Unreserved fund balance is fund balance not reserved in accordance with state law, charter or contractual obligation.  This includes total fund balance minus nonspendable, restricted or committed resources.  Review \"committed\" fund blance for any amounts that could be considered available and unhindered by law or contract."]

{"sheet":"SpecProgrFund2","index":6,"n_rows":60,"n_cols":15,"rows_kept":60,"cut":false,"hidden":false,"merged":[[56,1,56,2],[56,3,56,4],[57,1,57,2],[57,3,57,4],[58,0,59,0],[58,1,58,2],[58,3,58,4],[59,1,59,11],[52,0,53,0],[52,1,52,2],[52,3,52,4],[53,1,53,11],[54,0,55,0],[54,1,54,2],[54,3,54,4],[55,1,55,11],[49,1,49,2],[49,3,49,4],[50,1,50,2],[50,3,50,4],[51,1,51,2],[51,3,51,4],[46,0,47,0],[46,1,46,2],[46,3,46,4],[47,1,47,11],[48,1,48,2],[48,3,48,4],[42,1,42,2],[42,3,42,4],[43,3,43,4],[44,0,45,0],[44,1,44,2],[44,3,44,4],[45,1,45,11],[38,0,38,2],[38,3,38,4],[39,1,39,2],[40,1,40,2],[40,3,40,4],[41,1,41,2],[41,3,41,4],[32,0,32,11],[33,0,33,11],[35,4,35,8],[35,10,35,11],[36,4,36,8],[36,9,36,11],[27,0,29,4],[27,5,27,10],[28,5,28,10],[29,5,29,10],[30,0,30,11],[31,0,31,11],[26,0,26,1],[26,2,26,3],[26,4,26,5],[26,6,26,7],[26,8,26,9],[26,10,26,11],[23,0,23,11],[25,0,25,1],[25,2,25,3],[25,4,25,5],[25,6,25,7],[25,8,25,9],[25,10,25,11],[17,0,19,4],[17,5,17,10],[18,5,18,10],[19,5,19,10],[20,0,20,11],[22,4,22,6],[16,0,16,1],[16,2,16,3],[16,4,16,5],[16,6,16,7],[16,8,16,9],[16,10,16,11],[15,0,15,1],[15,2,15,3],[15,4,15,5],[15,6,15,7],[15,8,15,9],[15,10,15,11],[11,0,11,7],[11,8,11,10],[12,0,12,11],[13,4,13,6],[13,7,13,11],[14,1,14,11],[10,0,10,1],[10,2,10,3],[10,4,10,5],[10,6,10,7],[10,8,10,9],[10,10,10,11],[7,0,7,10],[8,1,8,11],[9,0,9,1],[9,2,9,3],[9,4,9,5],[9,6,9,7],[9,8,9,9],[9,10,9,11],[5,10,5,11],[6,0,6,1],[6,2,6,3],[6,4,6,5],[6,6,6,7],[6,8,6,9],[6,10,6,11],[1,4,1,6],[1,7,1,11],[2,0,2,11],[3,0,3,11],[4,1,4,11],[5,0,5,1],[5,2,5,3],[5,4,5,5],[5,6,5,7],[5,8,5,9]]}
[null,"Use the Data Gathering worksheet BELOW to collect and input the data that will rollup through these formulas."]
[null,null,null,{"$f":"+J36","v":"County of"},{"$f":"+'DataGathering-GenFund'!J1:J1","v":0},null,null,{"$f":"+J37","v":"Special Program or Fund (e.g. Mental Health, etc.)"},null,null,null,null,null,null,"<= These fields populate from data entered in the \"data gathering\" tab."]
[{"$f":"'Cover&Contents'!B2:B2&\": Financial Health Indicators\"","v":"The California County Financial Health Diagnostic: Financial Health Indicators"},null,null,null,null,null,null,null,null,null,null,null,null,null,"return to \ncontents"]
["Operating Deficit/Surplus - Special Program or Fund - also internal service funds and enterprises. \n          Most programs receive some degree of functional or program revenue such as user fees, earmarked intergovernmental aid, or special taxes. To the extent that operating revenues do not cover operating expenditures, the government's general purpose revenues bear the burden for the program.  In some cases, this support from general revenues is validated by adopted policy that essentially finds those funds necessary and justified for the good of the general community. But in other cases, this general revenue support may be excessive (in the opinion of the governing board), growing in an unsustainable pattern, or placing undue risk on other programs supported by general revenues.  In such a case, the financial health of the local government is impaired.  "]
["1A","Gross Annual Deficit/Surplus - unadjusted, using all general fund revenues and expenditures",null,null,null,null,null,null,null,null,null,null,null,null,"Want to add more years? I can help you. 530-758-3952 coleman@muniwest.com"]
[{"$f":"+'DataGathering-GenFund'!F5","v":"2014-15"},null,{"$f":"+'DataGathering-GenFund'!G5","v":"2015-16"},null,{"$f":"+'DataGathering-GenFund'!H5","v":"2016-17"},null,{"$f":"+'DataGathering-GenFund'!I5","v":"2017-18"},null,{"$f":"+'DataGathering-GenFund'!J5","v":"2018-19"},null,{"$f":"+'DataGathering-GenFund'!K5","v":"2019-20"}]
[{"$f":"IF(ISERROR(($G$41-$G$49)/$G$41),\"\",($G$41-$G$49)/$G$41)","v":""},null,{"$f":"IF(ISERROR(($H$41-$H$49)/$H$41),\"\",($H$41-$H$49)/$H$41)","v":""},null,{"$f":"IF(ISERROR(($I$41-$I$49)/$I$41),\"\",($I$41-$I$49)/$I$41)","v":""},null,{"$f":"IF(ISERROR(($J$41-$J$49)/$J$41),\"\",($J$41-$J$49)/$J$41)","v":""},null,{"$f":"IF(ISERROR(($K$41-$K$49)/$K$41),\"\",($K$41-$K$49)/$K$41)","v":""},null,{"$f":"IF(ISERROR(($L$41-$L$49)/$L$41),\"\",($L$41-$L$49)/$L$41)","v":""},null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,null,null,null,null,null,null,null,"For comparison purposes"]
["1B  ","Net Operating Deficit/Surplus – sustainable, omitting non-recurring revenues"]
[{"$f":"+A6","v":"2014-15"},null,{"$f":"+C6","v":"2015-16"},null,{"$f":"+E6","v":"2016-17"},null,{"$f":"+G6","v":"2017-18"},null,{"$f":"+I6","v":"2018-19"},null,{"$f":"+K6","v":"2019-20"}]
[{"$f":"IF(ISERROR(($G$45-G47-$G$53)/$G$45),\"\",($G$45-G47-$G$53)/$G$45)","v":""},null,{"$f":"IF(ISERROR(($H$45-H47-$H$53)/$H$45),\"\",($H$45-H47-$H$53)/H45)","v":""},null,{"$f":"IF(ISERROR(($I$45-I47-$I$53)/$I$45),\"\",($I$45-I47-$I$53)/$I$45)","v":""},null,{"$f":"IF(ISERROR(($J$45-J47-$J$53)/$J$45),\"\",($J$45-J47-$J$53)/$J$45)","v":""},null,{"$f":"IF(ISERROR(($K$45-K47-$K$53)/$K$45),\"\",($K$45-K47-$K$53)/$K$45)","v":""},null,{"$f":"IF(ISERROR(($L$45-L47-$L$53)/$L$45),\"\",($L$45-L47-$L$53)/$L$45)","v":""},null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,null,null,null,null,"Negative Indicator\n·   Recurring AND static or increasing deficit over consecutive years.",null,null,"For comparison purposes"]
[]
[null,null,null,{"$f":"+D2","v":"County of"},{"$f":"IF('DataGathering-GenFund'!$J$1=0,\"\",'DataGathering-GenFund'!$J$1)","v":""},null,null,{"$f":"+J37","v":"Special Program or Fund (e.g. Mental Health, etc.)"}]
["1C","Net True Operating Deficit/Surplus - complete, adding unbudgeted general fund liabilities"]
[{"$f":"+A10","v":"2014-15"},null,{"$f":"+C10","v":"2015-16"},null,{"$f":"+E10","v":"2016-17"},null,{"$f":"+G10","v":"2017-18"},null,{"$f":"+I10","v":"2018-19"},null,{"$f":"+K10","v":"2019-20"}]
[{"$f":"IF(ISERROR(($G$45-G53-$G$53-G55)/$G$45),\"\",($G$45-G53-$G$53-G55)/$G$45)","v":""},null,{"$f":"IF(ISERROR(($H$45-H53-$H$53-H55)/$H$45),\"\",($H$45-H53-$H$53-H55)/$H$45)","v":""},null,{"$f":"IF(ISERROR(($I$45-I53-$I$53-I55)/$I$45),\"\",($I$45-I53-$I$53-I55)/$I$45)","v":""},null,{"$f":"IF(ISERROR(($J$45-J53-$J$53-J55)/$J$45),\"\",($J$45-J53-$J$53-J55)/$J$45)","v":""},null,{"$f":"IF(ISERROR(($K$45-K53-$K$53-K55)/$K$45),\"\",($K$45-K53-$K$53-K55)/$K$45)","v":""},null,{"$f":"IF(ISERROR(($L$45-L53-$L$53-L55)/$L$45),\"\",($L$45-L53-$L$53-L55)/$L$45)","v":""},null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula ",null,null,null,null,"Score:\nWarning-Red: Persistent & increasing deficits over consecutive years.",null,null,null,null,null,null,null,null,"Beige cells may be altered"]
[null,null,null,null,null,"Caution-Yellow: Deficits are infrequent or relatively marginal compared to fund balance (see #2 below) and/or there is a reasonable plan for bringing revenues and spending into balance."]
[null,null,null,null,null,"Good-Green: not an issue of concern.",null,null,null,null,null,null,0]
["Definitions\n \nGross total revenues. See CAFR Statement of Revenues and Expenditures “total revenues.”\n\nGross total expenditures. See CAFR Statement of Revenues and Expenditures “total expenditures”\n\nNet operating revenues. Net operating revenues equals gross revenues and transfers in, minus revenues restricted to capital improvements minus   revenues legally restricted to special purposes. Transfers related to non-operating activities should be excluded.\n\nTemporary revenues. Temporary revenues include any non-recurring revenues that derive from short-term activities and cannot be relied upon in the long-run (over multiple years).  This might include revenues from land sales, one-time transfers from other funds, limited-term grants, court settlements or major donations.  You might also consider excluding receipt of construction-related revenues from a given project that are significantly in excess of an “average” year, if this represents a level of revenue unlikely to be repeated.  Back taxes and late payments do not have to be omitted because they just make up for what was not received in a prior year.\n\nNet operating expenditures. Net operating expenditures equals total expenditures (after credits/reimbursements) and transfers out, minus capital improvement expenditures (or transfers out for capital purposes), minus expenditures of revenues legally restricted to special purposes.\n\nUnbudgeted current liabilities. The amortized costs of long-term general fund liabilities not already included in “net operating expenditures.” This includes amounts not budgeted or expended that “should be” in order to pay the current year portion of liabilities. Examples: unbudgeted actuarially required contributions (ARC) to Other Post-Employment Benefits (OPEB) or pension systems; unbudgeted earned leave cash-out liabilities; maintenance and replacement costs of vehicles, technology, buildings, streets, and other properties and infrastructure. If there is insufficient information to determine these unbudgeted liabilities, that, in itself, is reason for substantial concern."]
[]
[null,null,null,{"$f":"+D14","v":"County of"},{"$f":"+'DataGathering-GenFund'!J24:J24","v":0},null,null,{"$f":"+J37","v":"Special Program or Fund (e.g. Mental Health, etc.)"},null,null,null,null,null,null,"<= These fields populate from data entered in the \"data gathering\" tab."]
["Fund Balance\n        A positive fund balance, or reserves, is important for any government to withstand financial risk over time. Unanticipated fluctuations in revenues may occur from economic impacts or state take-ways. “Financial reserves” are important to meet unforeseen revenue shortfalls or expenditure overages.  But reserves cannot be relied upon to cover financial shortfalls that are more than temporary. (See Indicator #7) An unplanned decline in unreserved fund balances as a percentage of operating revenues over time suggests the government is less able to withstand financial emergencies.\n        The right level of fund balance varies depending on many factors including levels of risk and revenue volatility but, generally speaking, dropping below 8% may be cause for concern.  The Government Finance Officers Association recommends maintaining a 5-15% reserve.\n"]
[2,"Fund Balance"]
[{"$f":"+A16","v":"2014-15"},null,{"$f":"+C16","v":"2015-16"},null,{"$f":"+E16","v":"2016-17"},null,{"$f":"+G16","v":"2017-18"},null,{"$f":"+I16","v":"2018-19"},null,{"$f":"+K16","v":"2019-20"}]
[{"$f":"IF(ISERROR(($G$59)/$G$53),\"\",($G$59)/$G$53)","v":""},null,{"$f":"IF(ISERROR(($H$59)/$H$53),\"\",($H$59)/$H$53)","v":""},null,{"$f":"IF(ISERROR(($I$59)/$I$53),\"\",($I$59)/$I$53)","v":""},null,{"$f":"IF(ISERROR(($J$59)/$J$53),\"\",($J$59)/$J$53)","v":""},null,{"$f":"IF(ISERROR(($K$59)/$K$53),\"\",($K$59)/$K$53)","v":""},null,{"$f":"IF(ISERROR(($L$59)/$L$53),\"\",($L$59)/$L$53)","v":""},null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,null,"Score:\nWarning-Red: Persistently & substantially decreasing or is below 8%."]
[null,null,null,null,null,"Caution-Yellow: Has decreased but remains above 8% and there is a reasonable plan for stabilizing."]
[null,null,null,null,null,"Green - not concerning",null,null,null,null,null,null,0]
["Definitions"]
["Unreserved fund balance  is fund balance not reserved in accordance with state law, charter or contractual obligation.  This includes total fund balance minus nonspendable, restricted or committed resources. However, you should review resources categorized as \"committed\" and include in \"unreserved fund balance\" any amounts that, even if only in time of emergency, could be accessed with action of the Board of Supervisors. "]
["Net operating expenditures. Net operating expenditures equals expenditures (after credits/reimbursements) and transfers out, minus capital improvement expenditures (or transfers out for capital purposes), minus expenditures of revenues legally restricted to special purposes."]
[" 1. See Stephen J. Gauthier, “Governmental Accounting, Auditing, and Financial Reporting.” (The “Blue Book”) 2012 Government Finance Officers Association."]
[]
[null,null,null,null,"The California Municipal Financial Health Diagnostic ",null,null,null,null,{"$f":"+'DataGathering-GenFund'!I1","v":"County of"},{"$f":"+E2","v":0},null,"Beige cells may be altered"]
[null,null,null,null,"Data Gathering Worksheet",null,null,null,null,"Special Program or Fund (e.g. Mental Health, etc.)",null,null,null,"<== You can alter the fund name here."]
[" This worksheet is a tool to 1) help the analyst find the figures and put them together, 2) more clearly define all of the data elements and terms – and tie them back to standard GASB elements etc. as much as possible."]
["Data Component",null,null,"Source of Data",null,"Use for Indicator","Year-2","PriorYr","CurrentYr (estimated)","Next Year (projected)","Year+2 (projected)","Year+3 (projected)",null,"return to \ncontents"]
[null,"Year (e.g., 2014-15, etc.)",null,null,null,null,"2013-14","2014-15","2015-16","2016-17","2017-18","2018-19",null,"<== You can alter the years analyzed here."]
["a","Gross program revenues and transfers in",null,"CAFR* Statement of Revenues and Expenditures \n“total revenues”",null,"SF-1A"]
["b","General revenue transfers in if included in \"a\" above",null,"CAFR* Statement of Revenues and Expenditures \n“transfers in”"]
["c","Revenues restricted to capital improvements (by law or contract) ",null,"Staff analysis"]
["d","Revenues legally restricted to special purposes",null,"Staff analysis"]
["e","Net operating revenues",null,"line “a” minus lines \"b\" , and “c”",null,"SF-1B,                                    SF-1C",{"$f":"+G41+G42-G43-G44","v":0},0,0,0,0,0]
[null,"Net operating revenues. Net operating revenues equals gross revenues and transfers in, minus revenues restricted to capital improvements."]
["f","Temporary revenues \n(other than those included in \"c\" or \"d\")",null,"Staff analysis",null,"SF-1B,                                    SF-1C"]
[null,"Temporary revenues. Temporary revenues include any non-recurring revenues that derive from short-term activities and cannot be relied upon in the long-run (over multiple years).  This might include revenues from land sales, one-time transfers from other funds, limited-term grants, court settlements or major donations.  Optionally, you might consider also excluding receipt of construction-related revenues from a given project that are significantly in excess of an “average” year, if this represents a level of revenue unlikely to be repeated.  Back taxes and late payments do not have to be omitted because they just make up for what was not received in a prior year."]
["g","Gross expenditures",null,"CAFR* Statement of Revenues and Expenditures \n“total expenditures”",null,"SF-1A"]
["h","Transfers out",null,"CAFR* Statement of Revenues and Expenditures \n“transfers out”"]
["i","Capital project expenditures                  (other than capital outlay that is not included in current expenditures)",null,"CAFR* Statement of Revenues and Expenditures \n“capital outlay”"]
["ia","Other one-time or temporary expenditures",null,"Staff analysis"]
["j","Net operating expenditures",null,"line “g” plus line \"h\" \nminus line \"i\"       minus line \"ia\"",null,"SF-1B,             SF-1C, SF- 2, SF-4, SF-5",{"$f":"+G49+G50-G51-G44-G52","v":0},0,0,0,0,0]
[null,"Net operating expenditures. Net operating expenditures equals gross expenditures minus capital improvement expenditures.."]
["k","Unbudgeted current liabilities",null,"Staff analysis",null,"SF-1C"]
[null,"Unbudgeted current liabilities. The amortized costs of long term general fund liabilities attributable to this program or fund not already included in “net operating expenditures.” This includes amounts not budgeted or expended that “should be” I order to pay the current year portion of liabilities. Examples: unbudgeted actuarially required contributions (ARC) to Other Post-Employment Benefits (OPEB) or pension systems; unbudgeted earned leave cash-out liabilities; maintenance and replacement costs of vehicles, technology, buildings, streets, and other properties and infrastructure."]
["l","Total Fund Balance",null,"CAFR* Balance Sheet",null,"-"]
["m","Nonspendable, restricted and committed fund balance",null,"CAFR* Balance Sheet",null,"-"]
["n","Unreserved fund balance",null,"line “l” \nminus line “m”",null,"SF-2",{"$f":"+G57-G58","v":0},0,0,0,0,0]
[null,"Unreserved fund balance is fund balance not reserved in accordance with state law, charter or contractual obligation.  This includes total fund balance minus nonspendable, restricted or committed resources.  Review \"committed\" fund blance for any amounts that could be considered available and unhindered by law or contract."]

{"sheet":"SpecProgrFund3","index":7,"n_rows":60,"n_cols":15,"rows_kept":60,"cut":false,"hidden":false,"merged":[[56,1,56,2],[56,3,56,4],[57,1,57,2],[57,3,57,4],[58,0,59,0],[58,1,58,2],[58,3,58,4],[59,1,59,11],[52,0,53,0],[52,1,52,2],[52,3,52,4],[53,1,53,11],[54,0,55,0],[54,1,54,2],[54,3,54,4],[55,1,55,11],[49,1,49,2],[49,3,49,4],[50,1,50,2],[50,3,50,4],[51,1,51,2],[51,3,51,4],[46,0,47,0],[46,1,46,2],[46,3,46,4],[47,1,47,11],[48,1,48,2],[48,3,48,4],[42,1,42,2],[42,3,42,4],[43,3,43,4],[44,0,45,0],[44,1,44,2],[44,3,44,4],[45,1,45,11],[38,0,38,2],[38,3,38,4],[39,1,39,2],[40,1,40,2],[40,3,40,4],[41,1,41,2],[41,3,41,4],[32,0,32,11],[33,0,33,11],[35,4,35,8],[35,10,35,11],[36,4,36,8],[36,9,36,11],[27,0,29,4],[27,5,27,10],[28,5,28,10],[29,5,29,10],[30,0,30,11],[31,0,31,11],[26,0,26,1],[26,2,26,3],[26,4,26,5],[26,6,26,7],[26,8,26,9],[26,10,26,11],[23,0,23,11],[25,0,25,1],[25,2,25,3],[25,4,25,5],[25,6,25,7],[25,8,25,9],[25,10,25,11],[17,0,19,4],[17,5,17,10],[18,5,18,10],[19,5,19,10],[20,0,20,11],[22,4,22,6],[16,0,16,1],[16,2,16,3],[16,4,16,5],[16,6,16,7],[16,8,16,9],[16,10,16,11],[15,0,15,1],[15,2,15,3],[15,4,15,5],[15,6,15,7],[15,8,15,9],[15,10,15,11],[11,0,11,7],[11,8,11,10],[12,0,12,11],[13,4,13,6],[13,7,13,11],[14,1,14,11],[10,0,10,1],[10,2,10,3],[10,4,10,5],[10,6,10,7],[10,8,10,9],[10,10,10,11],[7,0,7,10],[8,1,8,11],[9,0,9,1],[9,2,9,3],[9,4,9,5],[9,6,9,7],[9,8,9,9],[9,10,9,11],[5,10,5,11],[6,0,6,1],[6,2,6,3],[6,4,6,5],[6,6,6,7],[6,8,6,9],[6,10,6,11],[1,4,1,6],[1,7,1,11],[2,0,2,11],[3,0,3,11],[4,1,4,11],[5,0,5,1],[5,2,5,3],[5,4,5,5],[5,6,5,7],[5,8,5,9]]}
[null,"Use the Data Gathering worksheet BELOW to collect and input the data that will rollup through these formulas."]
[null,null,null,{"$f":"+J36","v":"County of"},{"$f":"+'DataGathering-GenFund'!J1:J1","v":0},null,null,{"$f":"+J37","v":"Special Program or Fund (e.g. Mental Health, etc.)"},null,null,null,null,null,null,"<= These fields populate from data entered in the \"data gathering\" tab."]
[{"$f":"'Cover&Contents'!B2:B2&\": Financial Health Indicators\"","v":"The California County Financial Health Diagnostic: Financial Health Indicators"},null,null,null,null,null,null,null,null,null,null,null,null,null,"return to \ncontents"]
["Operating Deficit/Surplus - Special Program or Fund - also internal service funds and enterprises. \n          Most programs receive some degree of functional or program revenue such as user fees, earmarked intergovernmental aid, or special taxes. To the extent that operating revenues do not cover operating expenditures, the government's general purpose revenues bear the burden for the program.  In some cases, this support from general revenues is validated by adopted policy that essentially finds those funds necessary and justified for the good of the general community. But in other cases, this general revenue support may be excessive (in the opinion of the governing board), growing in an unsustainable pattern, or placing undue risk on other programs supported by general revenues.  In such a case, the financial health of the local government is impaired.  "]
["1A","Gross Annual Deficit/Surplus - unadjusted, using all general fund revenues and expenditures",null,null,null,null,null,null,null,null,null,null,null,null,"Want to add more years? I can help you. 530-758-3952 coleman@muniwest.com"]
[{"$f":"+'DataGathering-GenFund'!F5","v":"2014-15"},null,{"$f":"+'DataGathering-GenFund'!G5","v":"2015-16"},null,{"$f":"+'DataGathering-GenFund'!H5","v":"2016-17"},null,{"$f":"+'DataGathering-GenFund'!I5","v":"2017-18"},null,{"$f":"+'DataGathering-GenFund'!J5","v":"2018-19"},null,{"$f":"+'DataGathering-GenFund'!K5","v":"2019-20"}]
[{"$f":"IF(ISERROR(($G$41-$G$49)/$G$41),\"\",($G$41-$G$49)/$G$41)","v":""},null,{"$f":"IF(ISERROR(($H$41-$H$49)/$H$41),\"\",($H$41-$H$49)/$H$41)","v":""},null,{"$f":"IF(ISERROR(($I$41-$I$49)/$I$41),\"\",($I$41-$I$49)/$I$41)","v":""},null,{"$f":"IF(ISERROR(($J$41-$J$49)/$J$41),\"\",($J$41-$J$49)/$J$41)","v":""},null,{"$f":"IF(ISERROR(($K$41-$K$49)/$K$41),\"\",($K$41-$K$49)/$K$41)","v":""},null,{"$f":"IF(ISERROR(($L$41-$L$49)/$L$41),\"\",($L$41-$L$49)/$L$41)","v":""},null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,null,null,null,null,null,null,null,"For comparison purposes"]
["1B  ","Net Operating Deficit/Surplus – sustainable, omitting non-recurring revenues"]
[{"$f":"+A6","v":"2014-15"},null,{"$f":"+C6","v":"2015-16"},null,{"$f":"+E6","v":"2016-17"},null,{"$f":"+G6","v":"2017-18"},null,{"$f":"+I6","v":"2018-19"},null,{"$f":"+K6","v":"2019-20"}]
[{"$f":"IF(ISERROR(($G$45-G47-$G$53)/$G$45),\"\",($G$45-G47-$G$53)/$G$45)","v":""},null,{"$f":"IF(ISERROR(($H$45-H47-$H$53)/$H$45),\"\",($H$45-H47-$H$53)/H45)","v":""},null,{"$f":"IF(ISERROR(($I$45-I47-$I$53)/$I$45),\"\",($I$45-I47-$I$53)/$I$45)","v":""},null,{"$f":"IF(ISERROR(($J$45-J47-$J$53)/$J$45),\"\",($J$45-J47-$J$53)/$J$45)","v":""},null,{"$f":"IF(ISERROR(($K$45-K47-$K$53)/$K$45),\"\",($K$45-K47-$K$53)/$K$45)","v":""},null,{"$f":"IF(ISERROR(($L$45-L47-$L$53)/$L$45),\"\",($L$45-L47-$L$53)/$L$45)","v":""},null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,null,null,null,null,"Negative Indicator\n·   Recurring AND static or increasing deficit over consecutive years.",null,null,"For comparison purposes"]
[]
[null,null,null,{"$f":"+D2","v":"County of"},{"$f":"IF('DataGathering-GenFund'!$J$1=0,\"\",'DataGathering-GenFund'!$J$1)","v":""},null,null,{"$f":"+J37","v":"Special Program or Fund (e.g. Mental Health, etc.)"}]
["1C","Net True Operating Deficit/Surplus - complete, adding unbudgeted general fund liabilities"]
[{"$f":"+A10","v":"2014-15"},null,{"$f":"+C10","v":"2015-16"},null,{"$f":"+E10","v":"2016-17"},null,{"$f":"+G10","v":"2017-18"},null,{"$f":"+I10","v":"2018-19"},null,{"$f":"+K10","v":"2019-20"}]
[{"$f":"IF(ISERROR(($G$45-G53-$G$53-G55)/$G$45),\"\",($G$45-G53-$G$53-G55)/$G$45)","v":""},null,{"$f":"IF(ISERROR(($H$45-H53-$H$53-H55)/$H$45),\"\",($H$45-H53-$H$53-H55)/$H$45)","v":""},null,{"$f":"IF(ISERROR(($I$45-I53-$I$53-I55)/$I$45),\"\",($I$45-I53-$I$53-I55)/$I$45)","v":""},null,{"$f":"IF(ISERROR(($J$45-J53-$J$53-J55)/$J$45),\"\",($J$45-J53-$J$53-J55)/$J$45)","v":""},null,{"$f":"IF(ISERROR(($K$45-K53-$K$53-K55)/$K$45),\"\",($K$45-K53-$K$53-K55)/$K$45)","v":""},null,{"$f":"IF(ISERROR(($L$45-L53-$L$53-L55)/$L$45),\"\",($L$45-L53-$L$53-L55)/$L$45)","v":""},null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula ",null,null,null,null,"Score:\nWarning-Red: Persistent & increasing deficits over consecutive years.",null,null,null,null,null,null,null,null,"Beige cells may be altered"]
[null,null,null,null,null,"Caution-Yellow: Deficits are infrequent or relatively marginal compared to fund balance (see #2 below) and/or there is a reasonable plan for bringing revenues and spending into balance."]
[null,null,null,null,null,"Good-Green: not an issue of concern.",null,null,null,null,null,null,0]
["Definitions\n \nGross total revenues. See CAFR Statement of Revenues and Expenditures “total revenues.”\n\nGross total expenditures. See CAFR Statement of Revenues and Expenditures “total expenditures”\n\nNet operating revenues. Net operating revenues equals gross revenues and transfers in, minus revenues restricted to capital improvements minus   revenues legally restricted to special purposes. Transfers related to non-operating activities should be excluded.\n\nTemporary revenues. Temporary revenues include any non-recurring revenues that derive from short-term activities and cannot be relied upon in the long-run (over multiple years).  This might include revenues from land sales, one-time transfers from other funds, limited-term grants, court settlements or major donations.  You might also consider excluding receipt of construction-related revenues from a given project that are significantly in excess of an “average” year, if this represents a level of revenue unlikely to be repeated.  Back taxes and late payments do not have to be omitted because they just make up for what was not received in a prior year.\n\nNet operating expenditures. Net operating expenditures equals total expenditures (after credits/reimbursements) and transfers out, minus capital improvement expenditures (or transfers out for capital purposes), minus expenditures of revenues legally restricted to special purposes.\n\nUnbudgeted current liabilities. The amortized costs of long-term general fund liabilities not already included in “net operating expenditures.” This includes amounts not budgeted or expended that “should be” in order to pay the current year portion of liabilities. Examples: unbudgeted actuarially required contributions (ARC) to Other Post-Employment Benefits (OPEB) or pension systems; unbudgeted earned leave cash-out liabilities; maintenance and replacement costs of vehicles, technology, buildings, streets, and other properties and infrastructure. If there is insufficient information to determine these unbudgeted liabilities, that, in itself, is reason for substantial concern."]
[]
[null,null,null,{"$f":"+D14","v":"County of"},{"$f":"+'DataGathering-GenFund'!J24:J24","v":0},null,null,{"$f":"+J37","v":"Special Program or Fund (e.g. Mental Health, etc.)"},null,null,null,null,null,null,"<= These fields populate from data entered in the \"data gathering\" tab."]
["Fund Balance\n        A positive fund balance, or reserves, is important for any government to withstand financial risk over time. Unanticipated fluctuations in revenues may occur from economic impacts or state take-ways. “Financial reserves” are important to meet unforeseen revenue shortfalls or expenditure overages.  But reserves cannot be relied upon to cover financial shortfalls that are more than temporary. (See Indicator #7) An unplanned decline in unreserved fund balances as a percentage of operating revenues over time suggests the government is less able to withstand financial emergencies.\n        The right level of fund balance varies depending on many factors including levels of risk and revenue volatility but, generally speaking, dropping below 8% may be cause for concern.  The Government Finance Officers Association recommends maintaining a 5-15% reserve.\n"]
[2,"Fund Balance"]
[{"$f":"+A16","v":"2014-15"},null,{"$f":"+C16","v":"2015-16"},null,{"$f":"+E16","v":"2016-17"},null,{"$f":"+G16","v":"2017-18"},null,{"$f":"+I16","v":"2018-19"},null,{"$f":"+K16","v":"2019-20"}]
[{"$f":"IF(ISERROR(($G$59)/$G$53),\"\",($G$59)/$G$53)","v":""},null,{"$f":"IF(ISERROR(($H$59)/$H$53),\"\",($H$59)/$H$53)","v":""},null,{"$f":"IF(ISERROR(($I$59)/$I$53),\"\",($I$59)/$I$53)","v":""},null,{"$f":"IF(ISERROR(($J$59)/$J$53),\"\",($J$59)/$J$53)","v":""},null,{"$f":"IF(ISERROR(($K$59)/$K$53),\"\",($K$59)/$K$53)","v":""},null,{"$f":"IF(ISERROR(($L$59)/$L$53),\"\",($L$59)/$L$53)","v":""},null,null,null,"<= These formulas populate from data entered in the \"data gathering\" tab."]
["Formula",null,null,null,null,"Score:\nWarning-Red: Persistently & substantially decreasing or is below 8%."]
[null,null,null,null,null,"Caution-Yellow: Has decreased but remains above 8% and there is a reasonable plan for stabilizing."]
[null,null,null,null,null,"Green - not concerning",null,null,null,null,null,null,0]
["Definitions"]
["Unreserved fund balance  is fund balance not reserved in accordance with state law, charter or contractual obligation.  This includes total fund balance minus nonspendable, restricted or committed resources. However, you should review resources categorized as \"committed\" and include in \"unreserved fund balance\" any amounts that, even if only in time of emergency, could be accessed with action of the Board of Supervisors. "]
["Net operating expenditures. Net operating expenditures equals expenditures (after credits/reimbursements) and transfers out, minus capital improvement expenditures (or transfers out for capital purposes), minus expenditures of revenues legally restricted to special purposes."]
[" 1. See Stephen J. Gauthier, “Governmental Accounting, Auditing, and Financial Reporting.” (The “Blue Book”) 2012 Government Finance Officers Association."]
[]
[null,null,null,null,"The California Municipal Financial Health Diagnostic ",null,null,null,null,{"$f":"+'DataGathering-GenFund'!I1","v":"County of"},{"$f":"+E2","v":0},null,"Beige cells may be altered"]
[null,null,null,null,"Data Gathering Worksheet",null,null,null,null,"Special Program or Fund (e.g. Mental Health, etc.)",null,null,null,"<== You can alter the fund name here."]
[" This worksheet is a tool to 1) help the analyst find the figures and put them together, 2) more clearly define all of the data elements and terms – and tie them back to standard GASB elements etc. as much as possible."]
["Data Component",null,null,"Source of Data",null,"Use for Indicator","Year-2","PriorYr","CurrentYr (estimated)","Next Year (projected)","Year+2 (projected)","Year+3 (projected)",null,"return to \ncontents"]
[null,"Year (e.g., 2014-15, etc.)",null,null,null,null,"2013-14","2014-15","2015-16","2016-17","2017-18","2018-19",null,"<== You can alter the years analyzed here."]
["a","Gross program revenues and transfers in",null,"CAFR* Statement of Revenues and Expenditures \n“total revenues”",null,"SF-1A"]
["b","General revenue transfers in if included in \"a\" above",null,"CAFR* Statement of Revenues and Expenditures \n“transfers in”"]
["c","Revenues restricted to capital improvements (by law or contract) ",null,"Staff analysis"]
["d","Revenues legally restricted to special purposes",null,"Staff analysis"]
["e","Net operating revenues",null,"line “a” minus lines \"b\" , and “c”",null,"SF-1B,                                    SF-1C",{"$f":"+G41+G42-G43-G44","v":0},0,0,0,0,0]
[null,"Net operating revenues. Net operating revenues equals gross revenues and transfers in, minus revenues restricted to capital improvements."]
["f","Temporary revenues \n(other than those included in \"c\" or \"d\")",null,"Staff analysis",null,"SF-1B,                                    SF-1C"]
[null,"Temporary revenues. Temporary revenues include any non-recurring revenues that derive from short-term activities and cannot be relied upon in the long-run (over multiple years).  This might include revenues from land sales, one-time transfers from other funds, limited-term grants, court settlements or major donations.  Optionally, you might consider also excluding receipt of construction-related revenues from a given project that are significantly in excess of an “average” year, if this represents a level of revenue unlikely to be repeated.  Back taxes and late payments do not have to be omitted because they just make up for what was not received in a prior year."]
["g","Gross expenditures",null,"CAFR* Statement of Revenues and Expenditures \n“total expenditures”",null,"SF-1A"]
["h","Transfers out",null,"CAFR* Statement of Revenues and Expenditures \n“transfers out”"]
["i","Capital project expenditures                  (other than capital outlay that is not included in current expenditures)",null,"CAFR* Statement of Revenues and Expenditures \n“capital outlay”"]
["ia","Other one-time or temporary expenditures",null,"Staff analysis"]
["j","Net operating expenditures",null,"line “g” plus line \"h\" \nminus line \"i\"       minus line \"ia\"",null,"SF-1B,             SF-1C, SF- 2, SF-4, SF-5",{"$f":"+G49+G50-G51-G44-G52","v":0},0,0,0,0,0]
[null,"Net operating expenditures. Net operating expenditures equals gross expenditures minus capital improvement expenditures.."]
["k","Unbudgeted current liabilities",null,"Staff analysis",null,"SF-1C"]
[null,"Unbudgeted current liabilities. The amortized costs of long term general fund liabilities attributable to this program or fund not already included in “net operating expenditures.” This includes amounts not budgeted or expended that “should be” I order to pay the current year portion of liabilities. Examples: unbudgeted actuarially required contributions (ARC) to Other Post-Employment Benefits (OPEB) or pension systems; unbudgeted earned leave cash-out liabilities; maintenance and replacement costs of vehicles, technology, buildings, streets, and other properties and infrastructure."]
["l","Total Fund Balance",null,"CAFR* Balance Sheet",null,"-"]
["m","Nonspendable, restricted and committed fund balance",null,"CAFR* Balance Sheet",null,"-"]
["n","Unreserved fund balance",null,"line “l” \nminus line “m”",null,"SF-2",{"$f":"+G57-G58","v":0},0,0,0,0,0]
[null,"Unreserved fund balance is fund balance not reserved in accordance with state law, charter or contractual obligation.  This includes total fund balance minus nonspendable, restricted or committed resources.  Review \"committed\" fund blance for any amounts that could be considered available and unhindered by law or contract."]

{"sheet":"Notes to the Analyst","index":8,"n_rows":17,"n_cols":4,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
[null,{"$f":"+'Cover&Contents'!B2:E2","v":"The California County Financial Health Diagnostic"}]
[null,"Notes to the Analyst",null,"return to \ncontents"]
[null,"To complete the California County Financial Health Diagnostic:\n1) Compile the data in the biege fields of the \"Data Gathering\" worksheet tab. White fields automatically compute. \n2) In the \"Indicators\" worksheet tab, values for indicators 1 - 6 will compute automatically from the data provided in the \"Data Gathering\" worksheet. Based on these values, rate each indicator. \n3) In the \"Indicators\" worksheet tab, gather information and complete the beige fields and ratings for indicators 7 - 14.\n4) In the \"Indicators\" worksheet tab, answer related questions regarding financial policies.\n5) Ratings are automatically summarized in the \"Summary Checklist\" tab.\n6) Prepare a narrative report describing each of your findings."]
[null,"The \"Summary Checklist\" summarizes the questions and reponses of your Financial Health Diagnostic. This is a summary of the more detailed analysis of the fourteen indicators in the \"Indicators\" worksheet. THe quantitative data needed to complete the Indicators worksheet is contained in the \"Data Gathering\" worksheet.  Many of the measures require projections for at least the next three years.  "]
[null,"Data Gathering.  Most of the data necessary to complete the fields in the Data Gathering worksheet for the “prior year” and “year-2” values can be obtained from your government’s annual financial statements, budgets and debt schedules. However, certain measures will take some additional analysis and computation.  For example, indicators “1b” and “1c” for operating deficit/surplus go beyond the simple difference between gross expenditures and gross revenues.  You will need to adjust for revenues and related expenditures restricted to one-time (temporary) purposes or that are legally restricted to specific purposes.   You will also need to take into account the amortized costs of all unbudgeted liabilities.  Further, you will need to make forecasts of these ongoing net revenues, net expenditures and resulting balances in future years. "]
[null,"Forecasting.  For the purpose of projecting future amounts and indicators in these worksheets, the financial analyst should assume:"]
[null,"·         Current tax rates, allocations and laws.  Do not presume your voters or the legislature will increase or otherwise alter the current tax base, rates or revenue allocations."]
[null,"·         Non-voter approved fees increase at levels consistent with existing policy of the local government."]
[null,"·         Temporary and one-time grant revenues and related expenditures do not continue (see “net operating revenues” and “net operating expenditures.”)"]
[null,"·         Employee compensation changes per current contract or policy including anticipated merit increases.  For out-year costs, assume no further changes. Then, run an alternative scenario (sensitivity analysis) with reasonable assumptions about increases in future years. (i.e. cost of living adjustments at California CPI or 2%)"]
[null,"·         Changes in staffing (including growth, turnover and vacancies) and other expenses necessary to maintain the current level of service."]
[null,"·         Changes in staffing and other expenses needed to maintain and operate any new facilities that are expected to be completed and operational."]
[null,"In addition, there are worksheets for Special Program Funds which may be used to evaluate proprietary or special revenue funds. These worksheets include the data gathering and indicators elements to calculate indicators #1 and #2 for the each fund. "]
[]
[null,{"$f":"+Introduction!B34","v":"Questions, comments, improvements?  Michael Coleman 530-758-3952  coleman@munwest.com"}]
[null,{"$f":"+Introduction!B35","v":"CaliforniaCityFinance.com, The California Local Government Finance Alamanac"}]
[null,{"$f":"+Introduction!B36","v":"© 2016 CaliforniaCityFinance.com"}]