Financial Condition Assessment Instructions
To assist auditors and local governments in making financial condition assessments, the Auditor General provides information such as financial data (indexed below), 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 and local government officials using the information will be responsible for reviewing the data for reasonableness; however, Auditor General staff will assist auditors and local government officials in determining why data may appear questionable. Example procedures for obtaining data for a particular entity and using the data to perform a preliminary financial condition assessment are provided below. Additional information may also be found in Frequently Asked Questions.
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 a deteriorating financial condition, further analysis is required before a conclusive financial condition assessment can be made. See Example Local Governmental Entity Financial Condition Assessment Indicators and Related Procedures, steps 5 and 6 and Appendix C – Guide for Analysis of Financial Indicators.
1. Select your entity from the appropriate index above and click on the link to the Financial Data compiled for the entity. The entity’s financial data will be contained in the Entity worksheet within an Excel workbook template that also contains FIs, Graphs, and Benchmarks worksheets.
2. Select the Entity worksheet tab and complete the fiscal year 2019 or 2020 data column 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 to adjust the calculation of some indicator averages by deleting entity indicator results that appear to be skewing an average because they are significantly lower or higher than the remaining entities’ financial indicator results.
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 AC) at the bottom of the Benchmark Grouping workbook. Select the Benchmarks worksheet tab in the Excel workbook template and put your cursor in cell C5. Click on “Paste” in the Excel ribbon and select “Paste Special”. Choose “Values” and check the box for “Transpose” then click “OK”. This records the average benchmarks for each financial indicator in the spaces provided in the Benchmarks worksheet.
6. After completion of step 5, the financial indicator results and graphs worksheets of the Excel workbook template should display the indicator results for the entity (on the FIs and Graphs worksheets) and the Graphs worksheet 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. The Graphs worksheet is designed to enable ratings of the entity’s trend, a comparison of the entity’s 2019 or 2020 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 Graphs worksheet. 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). The Example Financial Condition Assessment Methodology provides access to an example methodology for evaluating preliminary financial indicator results. 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 the “Data” tab in the workbook ribbon, and select “Data Validation”. If you wish to change the choices and use the drop-down box method of rating, select the Settings tab and replace the “Source:” choices listed (i.e., Favorable, Unfavorable, Inconclusive, N/A) with ratings of your choosing. If you wish to manually record ratings, select the Settings tab and under “Allow:” choose “Any value” using the drop-down arrow.