The URL can be used to link to this page

Home My WebLink About2011 06 13 Informational 102 New Method of Presenting Financial InformationCOMMISSION AGENDA ITEM 102 Consent Informational X Public Hearing Regular June 13, 2011 MGR. /DEPT. Regular Meeting Authorization REQUEST: City Manager and the Finance and Administrative Services Department would like to present for the Commission's review a new method of presenting vital financial, accounting and statistical information with regards to the City's performance to members of the City's Commission, Management and Citizens. SYNOPSIS: The Commission has previously recognized and expressed the need for improved Transparency, Customer Service, and Fiscal Responsibility in the operations of our City. Effective Financial Reporting is a key aspect of this effort. Thus, staff previously implemented several new Financial Reporting tools including a Quarterly Financial Report and Quarterly Investment Report. However, in our on -going efforts to continue to improve our delivery of service to both internal (Commission, staff) and external (citizens, business owners) users, staff has been working for several months on a new Financial Reporting tool (i.e., Digital Dashboard) that we believe will greatly enhance the delivery of financial /management information to both internal and external users. This tool is now ready for implementation. CONSIDERATIONS: A Digital Dashboard is an executive information system that (similar to an automobile's dashboard) is designed to be easy to read and to display critical information about the overall performance and financial health of an organization. These key metrics are displayed in a graphical format that can be easily understood and read quickly to determine if the organization is functioning within the acceptable range. Dashboards may be found to be useful to internal and external users looking to quickly assess a position or trend in data, and may be found to be very important in the everyday analysis of specific areas of concern on a departmental or entity -wide level. Managers may quickly access data which previously would have to be compiled from multiple reports and sources. Once the sources have been determined and the report has been created, they can be made interactive, with the user able to manipulate determined variables to perform useful "what -if' scenarios. Benefits of using Digital Dashboards include: • Visual presentation of performance measures • Ability to identify and correct negative trends • Measure efficiencies /inefficiencies • Ability to generate detailed reports showing new trends • Align strategies and organizational goals • Saves time compared to running multiple reports • Gain total visibility of all systems instantly City Commission — Regular Meeting June 13, 2011 Informational Agenda Item #102 Page 2 of 3 The user will be able to add or subtract any combination of graphs that they wish to view in a single presentation. Some Dashboard examples have been created and are described below: Overtime (exhibit A) The overtime report captures the cumulative balance of all the individual departmental overtime account balances in the general ledger at a specific point in time. The report can also be run to capture only the overtime balances of specific departments. Each point on the cost curve corresponds to the overtime expense of the month directly beneath it. This graph represents the trend in overtime expense over the course of several months for three years. Each year of expense is represented by a different color, which corresponds to the legend at the top of the graph. This report may be used by departments to track how well they are managing overtime costs and help in determining in which months these expenses are concentrated. Employee "Head- Count" (exhibit B) The employee head count report reflects the number of full and part-time employees who have received a paycheck in a defined three month period (quarter). The quarters coincide with calendar quarters. For example, quarter one is defined as January to March, quarter two from April to June. The report is a stacked chart, with full -time employees counted on the bottom and part-time employees added to the full - time count. The total for all employees can be calculated by adding the full -time and part-time counts. If a payment is made to a non - commissioned employee, that employee is counted toward the quarterly total. Each employee is only counted once per quarter, regardless of how many times payment was made to the employee in that time span. This report may be used to evaluate the city -wide trend of the ratio of full - time to part-time employees. This report may also be run by department. Additional trends may be considered when this report is used in conjunction with the overtime report listed above. Health Care Expense (exhibit C) As health care premiums continue to rise, it is useful to track how the City uses the services provided by its health care provider. This report is a graphical representation of just that. The number recorded in the "Premiums Paid" field of the report represents the cumulative dollar amount of premiums paid to the health care provider by way of employee premiums in the current year. The number in the claims paid field represents the cumulative dollar amount of claims paid by the provider to cover the cost of health services consumed by employees in the same period. The claims expense by type pie chart represents the breakdown of how these services are utilized. Medical expenses are services where employees have utilized medical health services such as doctor office visits, hospital stays, or other similar services. Rx expenses are claims expenses associated with drug prescriptions. The "cumulative claims as a percentage of cumulative premiums" fields represent the percentage of collected premiums that the health care provider has paid out for City claims. The insurer gross margin is represented as both a number and percentage. The numbers represent the difference between premiums collected by the insurer and claims paid by the insurer for claims of the City. The gauge to the right of these numbers is a representation of the insurer's gross margin percentage. Because a gross margin of 75% is considered the premium - neutral margin, the margin above which insures may consider raising premiums to compensate for a high level of claims, this gauge is color coded to reflect the City usage rate of premiums paid in relation the this rate. Rates of usage below 50% percent result in a green gauge, usage over 50% but below 75% results in a yellow gauge, and usage above 75% is considered a high usage rate, resulting in a red gauge. The what -if scenario sliders allow the user to adjust values in premiums, Rx costs and medical costs and observe how these adjustments affect the above values and percentages. City Commission — Regular Meeting June 13, 2011 Informational Agenda Item #102 Page 3 of 3 Cash and Investments (exhibit D) This report captures the balances of cash and investment accounts and represents them as of the end of each month. The balances are taken from the statements received from the financial institutions which hold these assets. The cumulative and individual balances and trends may be viewed graphically, allowing the user to easily and quickly determine where funds are located, as well as their overall value. This report may be useful in determining if appropriate reserve ratios are met, as well as if too much or too little cash is on hand at a given time. Profit and Loss Statement (P &L) (exhibit E) This report defines the revenue, expense, and gain or loss of the City's operating funds. Both month -to- date and year -to -date activity is reported. The user may select the month and year to be reported. Month - to -date values reflect the monthly activity of the month selected and year -to -date values represent to yearly activity up to the month selected. This allows to user to determine the general ledger values of the funds at any given time. The variances reported in the monthly and yearly columns reflect variances between actual activity from the current year and prior year for both monthly and yearly data. The variances listed to the far right represent the variance from budget for the same period. The report allows the user to quickly compare financial results from both months and years. FISCAL IMPACT: The fiscal impact of this new modeling will be measurable over the next several years. The cost of the software is nominal. COMMUNICATION EFFORTS: This Agenda Item Has Been Electronically Forwarded To The Mayor And City Commission, City Manager, City Attorney /Staff, And All eAlert/eCitizen Recipients; And Is Available On The City's Website, LaserFiche, And The City's Server. Additionally, Portions Of This Agenda Item Are Typed Verbatim On The Respective Meeting Agenda Which Has Also Been Electronically Forwarded To The Individuals Noted Above; And Which Is Also Available On The City's Website, LaserFiche, And The City's Server; Has Been Sent To City Staff, Media /Press Representatives Who Have Requested Agendas /Agenda Item Information, Homeowner's Associations /Representatives On File With The City, And All Individuals Who Have Requested Such Information; And Has Been Posted Outside City Hall; Posted Inside City Hall With Additional Copies Available For The General Public; And Posted At Five (5) Different Locations Around The City. This Agenda Item Is Also Available To Any New Individual Requestors. City Staff Is Always Willing To Discuss This Or Any Agenda Item With Any Interested Individuals. Furthermore, the financial reports have been disseminated to all Department Directors. RECOMMENDATION: It is recommended that the City Commission review and receive the information provided in this agenda item. ATTACHMENTS: • Exhibit A - Overtime (Graph) • Exhibit B - Headcount (Graph) • Exhibit C - Healthcare (Graph) • Exhibit D - Profit and Loss (Graph) • Exhibit E - Cash and Investments (Graph) 1 (016) NOIJAWn NOD -.3 ans aiwo- ojLy , dv-q I ST'08i H:)byW 9 3=1 t4Iuo NVr 0 AON O E I C)Y .LdWnSNOD ia!)Qn9 IIoz TTOZ 0 0102* 60OZ 0 — NOT MST qc 3 0 AIn ro IMS uv IDO 0 a su d - j auto xxx uu dS .j j ti, jA j 00, 170Z SOZ . l T b 6001 .7 S'iZ 60t: Lid op T LE SZ � tr Z CZ� � AZi 9Z $ 09 OOT Cr m F OS T �, vi DQ OS DOE sauAojdLu3 DWI - L- Jed ' saaA oldw g awj -jjn;j aalaena e ui M:)ay:)Aed e 6uin183811 saa►Loldw3 jv aagwnN sdu�ads .�a�uiM jo Al!:) Jal -i Pn b / .t eak lb 11 oz tr o I oz Eb oloz z b o T oz T b DTGLr Ob 60CIZ E b 6002 Zb 6002 i I :.li F -I I 00, 170Z SOZ . l T b 6001 .7 S'iZ 60t: Lid op T LE SZ � tr Z CZ� � AZi 9Z $ 09 OOT Cr m F OS T �, vi DQ OS DOE sauAojdLu3 DWI - L- Jed ' saaA oldw g awj -jjn;j aalaena e ui M:)ay:)Aed e 6uin183811 saa►Loldw3 jv aagwnN sdu�ads .�a�uiM jo Al!:) r I-( PA i i i i i i 1p [iol.v �I- .i.'(i F - I)f alt °j 11 E) M ;. - =ROM a p4 if Y Y FI a } nfi S &ILId A li R oJENS aff"PWI dR Iu- aLujsntp V x1l ivautls fp%r LanPLuQud 1 i °lo E'66 '_tiItt,.bLUIS11W i I - IF-il m # '• [ I `N ' J - %I IF 111111 ll,) JO "., 1-M --it[i191.) ,) 'k itVEJ[[[[i ll.) 4 tLq .%" jj P jaa,jjo S llxitlLj� *j ��i� � CITY OF WINTER SPRINGS 3 FLASH REPORT - MARCH 2017 Mib FISCAL YEAR 11 % COMPLETE TD MARCH ) TD BUDGET 2010 2011 MARCH MARCH 2010 YTD 2011 YTD MARCH BUDGET BUDGET BUDGET BUDGET FY 20 10 FY 2011 MARCH VARIANCE YTD VARIANCE VARIANCE VARIANCE VARIANCE VARIANCE MARCH ACTIVI R ETTEW FY 2010 YTD FY 2011 YTD BETTER) BETTER/ RETTERJ BETrERI B ETTEW AFT R ACTIVITY AC TMTY WOR MORS&I O RSE 1 rwoRsel R evenues And T -In General Fun $1,045 $2 3 v 110% $9,707,847 $9,971,900 (22%) 81° 1 22 Water an d Sewe $61 7,388 540 (35%) $3 ,551,610 $3f837,SW 3 (7%) (62%) (11%) (40%) Devekopment Se $18,628 , 4 1 $147 $144,123 (2%) (52%) (35%) (37%) (35%) torrrrwa r $95,311 $208,675 119 $497, 053 $620,958 38 (11 ) (31%) T Revenues And T $1, $2,826,6 59% $1 3,903,331 $ % (17 % 8% (11 % ( % E xpenses A Tra -Out C Fun ($1,388,296) (1 P 41 , 886) (2%) ($8,149,687) ($7,823076) 8 4° (1%) 8 1 0 Wier and Sewe ($1 tWO1963) ($2 117) (32x) ($4,018,461) ($4,719,370) (18%) (100%) (94%) 19% 31 13mmlopment Sere ($68f8) ($78,304) (1 ) ( $422,247) ($296A 30 1 (35%) 10% 1 3% S ffnwa r ($81,314) ($131,317) (114%) ($370 OMAN) (37%) 48 25 44 61° Total Ex penses A Transfers - Out ($3,179,421) ($3, 815,625) (0 %) ($12,958,9 ($13,1 (1 %) % 38% 22% 1 % ainlLoss G ems] Fund Gain/( ($U2,552) $777,459 327% $1,557 $2,348 1 (254%) 1, 763% 368% 937% 1 aterl mr f=und V(Loss) ($1 ($1 X (72%) (U , 872) ($88 (90%) (531% (2 63 ( �� op a ' (Lms) 00220) ($ (4%) ($275,226) 5,226) ( 1 I X91 ) (2 %) (168%) (1 ) ( S tormwater aI (Lam) $33,997 7257 127% $12 6,515 $ 1 14 1 . 1 (1 0%) 291 435% 1 8 183% Overall Garin /(Loss) : All Funds ($1,402,351) ($989,01 29% $944 $1,429,733 51 % (339 %) (50 4%) 149 6%