INSTRUCTIONS - EXCEL
To assist auditors and local governments in making financial condition assessments, the Auditor General is providing financial and other data, financial indicators, and benchmarks. Auditor General staff compile the information from audit reports and other sources. The Auditor General cannot certify the accuracy of the information provided since it originates from audit reports or other documents prepared by other organizations. Auditors/local government officials using the information will be responsible for reviewing the data for reasonableness; however, Auditor General staff will assist auditors/local government officials in determining the reasons for any data that appears questionable. Instructions on how to obtain data for a particular entity, and how to use the data to perform a preliminary financial condition assessment, are provided below. Additional information may also be found in Frequently Asked Questions. It is recommended that these instructions be printed first. Links to benchmarks and financial data are as follows:
Municipalities
Special Districts
Example Preliminary Financial Condition Assessment Using Microsoft Excel
NOTE: The following steps are intended to provide example procedures for making a preliminary assessment of an entity’s financial condition. If the preliminary financial condition assessment indicates deteriorating financial condition, further analysis is required before a conclusive financial condition assessment can be made. See Local Governmental Entity Example Financial Condition Assessment Indicators and Related Procedures, steps 5 and 6 and Appendix C – Guide for Analysis of Financial Indicators.
1. Click on the appropriate entity type/alphabetical link above and click on the link to the Financial Data compiled for your entity. The entity’s financial data will be contained on the Entity tab within a template document that also contains tabs for FIs, Graphs, and Benchmarks.
2. Complete the fiscal year 2011 or 2012 column of data in the Entity tab using entity financial and nonfinancial records. In compiling financial data for counties, countywide financial data should be used.
3. Click on the appropriate entity type benchmarks link above and choose the desired Benchmark Grouping to be used in your assessment. You may choose an unadjusted or adjusted benchmark grouping. Once determined, click on the link to the desired benchmark grouping for your entity type and print it. NOTE: For some benchmark groupings, we adjusted the calculation of some indicator averages by deleting entity indicator results that appeared to be skewing the average because they were significantly lower or higher than the remaining entities’ financial indicator results. If we adjusted the benchmark grouping, the cells containing the deleted financial indicator results will be highlighted in yellow on the unadjusted benchmark grouping document and the adjusted version of the benchmark grouping document will show these values deleted. If we did not adjust the benchmark grouping, the notation, “No Adjustments Made” will appear at the bottom of the benchmark grouping document and the link to the adjusted benchmark grouping will be to the unadjusted benchmark grouping as no adjustments were made.
4. If desired, adjust the benchmark grouping chosen by adding or deleting entities or deleting indicator results.
5. Copy the data in the Averages row (columns B through S) at the bottom of the benchmark grouping document. Within the Benchmarks tab, put your cursor in cell C5. Click on “Edit,” “Paste Special,” choose “Values,” check the box for “Transpose,” and click “OK.” This records the average benchmarks for each financial indicator in the spaces provided in the Benchmarks tab.
6. After completion of step 5, the financial indicator results and graphs sheets of the document should display the indicator results for the entity (on the FIs and Graphs tabs) and the Graphs tab should show the benchmark grouping’s financial indicator results (in a cell to the right of “Y5 Bench”).
7. A preliminary assessment can now be performed using the financial indicator results and graphs sheets from the document. The Graphs tab of the document is designed to enable ratings of the entity’s trend, the comparison of the entity’s 2011 or 2012 financial indicator result to the benchmark result, and an overall rating for each financial indicator. In addition, there is a space provided for an overall entity rating on the first page of the Graphs tab. To assign ratings, use the drop-down boxes that appear when you put your cursor in the rating cells (rating cells are highlighted in yellow). Click here Example Financial Condition Assessment Methodology to access an example methodology for evaluating preliminary financial indicator results and an example of a completed financial condition assessment. NOTE: To use ratings other than those provided in the drop-down boxes, place your cursor in the cell containing the drop-down box; click on “Data,” “Validation.” If you wish to change the choices and use the drop-down box method of rating, in the Settings tab under Source, replace the choices listed (i.e., Favorable, Unfavorable, etc.) with ratings of your choosing. If you wish to manually record ratings, in the Settings tab under Allow, choose “Any Value” using the drop-down arrow.