HomeMy WebLinkAbout05-2018 Software ProposalOpenGov May 2018 Page 1 of 8
Software Proposal for OpenGov
for Auditor’s Office – Accounting Department
We are looking to purchase OpenGov software to enable us to efficiently prepare
budget and financial reports in a timely manner, to allow departments to manage their budget
in an easy to understand way and to allow the preparation of the county budget and the
comprehensive annual financial report to be completed accurately and in a timely manner.
OpenGov uses our financial data, which we upload, enabling us to have financial data
timely for all users. This will create dashboards to allow the Board of County Commissioners and
Department heads/Elected Officials to view their financial data. A simple spreadsheet with
visual graph data can take me two hours to create while OpenGov prepares it in a matter of
seconds. The RCW states the official making or incurring an excess expenditure above their
budget shall be personally liable for the excess. OpenGov can be used countywide to make
departments more efficient. When all of us are strapped for time having the ability to view their
budget data makes an investment well worthwhile for the county.
The 2018 County budget is over one hundred million dollars; $101,860,914 divided by
the cost of the software for the first year is one‐tenth of one percent (.00102) per budget dollar.
In the second year the percentage is far less than one‐tenth of one percent (.00059) per budget
dollar.
Software Modules Year 2018 Year 2019
Budget and Planning 29,186 29,186
Operational & Performance 26,846 26,846
Integrations 4,080 4,080
Budget and Planning Deployment 32,050 0
Operational & Performance Deployment 6,820 0
TOTAL 98,982 60,112
OpenGov May 2018 Page 2 of 8
Personnel
Hiring additional staff to enable us to do the required reporting by the federal, state,
local governments is not cost effective. The cost of an employee including salary benefits
computer software licenses is $42,993 for six months of 2018 and $77,670 for 2019 (Appendix
A), almost equal to the cost of the software less the deployment costs. We also would have to
find a place to put in additional person. According to the Washington State Employment
Security Department, Accountants and Auditor are in demand all across Washington State
(Appendix B). The website CareerOneStop – Careers with Most openings in Washington State,
Accountants and Auditors are #17 on the list, there are 1,590 projected job openings across the
state (Appendix C). If we were authorized a new position, it might take a while to fill the
position. The last opening we had took six months to fill.
I prepared the 2017 budget document and it took me two solid weeks (80 hours) of my
time to create this document. We currently use our financial data and re‐enter it into Excel
creating graphs and pivot tables. OpenGov will create to documents enabling us to create the
document in timely manner. A couple of weeks ago, Commissioner Jewell requested some
information and the preparation of the information took two hours to complete, and with
OpenGov, the data would already be available to pull.
The software is not intended to replace an existing position, it will be used to streamline
mundane, routine tasks to allow staff to spend more time on higher‐valued tasks that requires
thought processes, enabling staff to feel less time stressed.
As you are aware, in the past three years we had several changes in management
personnel. This has been very time consuming for my staff and me, especially in the budget and
reporting area. Other time issues have to do with newly issued programs within the County, i.e.
Admissions Tax, Water Banks, 2016 GO Bond financing, FEMA disaster reporting and all the
medical changes. Please understand that we do not have issues with any of the new personnel
training, we would rather train them on the issues than have them struggle so they can excel in
their jobs. However, in two instances, I made mistake on the quarterly reporting with the IRS
and Labor & Industries. With the IRS, I reported an amount on the incorrect day; showing the
5th payroll on the 4th, and I received a notice of $32,000 for penalties and interest. After hours
of time corresponding verbally and written, they waive the amount. Labor & Industries sent us
a notice for penalties and interest in the amount of $6,382.44 because they had not received
their payment by April 30. The payment was mailed on April 30th; they had not received it until
May 7th. This is to indicate that when we make a mistake, there can be huge penalties.
Funding
One of the funding sources to cover the software purchase would be the indirect cost
allocation, which is billed to all funds in the County that have personnel. The billing is based
upon salaries and wage times the percentage of the costs allocation. The current rate is 12.50%
and if I apply the cost of the software it would increase the percentage billed to departments by
0.50% increasing it to 13.00%. The amount would generate $35,000, (based upon March 2018
wages). The deployment onetime costs can be funded from Fund Balance as in 2017 we budget
to receive $651,223 from the indirect cost allocation and we actually billed and received
$742,282, equaling $91,059 in additional revenues. The State Auditors should be able to use
OpenGov and save on audit costs and our time.
OpenGov May 2018 Page 3 of 8
Software advantages
When we prepare the Comprehensive Annual Finance Report (CAFR), we enter the same
numbers in several of the statements and they are all linked documents. The idea is when you
enter the amount in one spreadsheet at the lowest level you will open the other spreadsheets
and update the links. As you can see from the graph below we start out with the Balance Sheets
and Statement of Expenditures, Revenues and Changes in Fund Balance. There are 36 funds and
both statements must be prepared for each fund.
Once that is complete the Combining statements are created, a statement for each like
fund; Special Revenues, Capital Projects, etc. Once they are completed, the GASB 34 worksheets
OpenGov May 2018 Page 4 of 8
are created by entering the data again, so you can eliminate interfund transactions and add the
Long Term Debt and Capital Assets. Then the Government Wide Statements are completed
using the same numbers. Using all this data you need to add the amounts to the Notes to the
Financial Statements and to the Management, Discussion and Analysis.
These steps create problems, as we have review processes in place, and if we forget to
open one spreadsheet to refresh the link, the statements are incorrect. The State Auditor finds
these errors during the audit resulting in recommendations that there was an error and we need
more training.
From the demonstration that Jerry and I received, OpenGov would eliminate these
errors because the software is pulling the data and if you make the correction in any of the
statements it automatically updates the other statements.
Best Practices1
The Governmental Financial Officers Association (GFOA) creates and adopts Best
Practices as recommended practices for governments. Their best practice for Monitoring the
Budget states it is important to ensure that the financial, operational and capital plans that were
developed and approved for implementation as part of the budget processes are being
implemented. Budget monitoring is crucial for an organization to be able to enforce
accountability related to spending. In addition, regular, comprehensive monitoring of the
budget allows a government to evaluate service level provision, ensure any new initiatives are
making expected progress towards goals/expectations, learn more about trends and other
deviations that may impact future operations, and finally demonstrate transparency by sharing
findings from this regular monitoring.
Tools for conducting the analysis
1. Electronic system. An organization’s enterprise resource planning (ERP) system
should drive data gathering and analysis related to budget monitoring. Use of
the ERP system allows for real time information to be readily accessible for
encumbrances and other time sensitive information as opposed to reliance on
static, stand‐alone reports.
2. Automation. Budget monitoring data and reports should be integrated into and
generated by an organization’s ERP system to ensure efficient use of staff time
and also consistency of information.
3. Overspend Protection. Governments should also ensure that mechanisms are in
place in their ERP system to automatically prevent overspending of budget and
notify the organization’s financial leadership.
4. Consistency. Off‐system analysis should be consistently applied to all aspects of
monitoring the budget and clearly articulated to both staff conducting the
analysis and end users.
1 GFOA Best Practices identify specific policies and procedures that contribute to improved government management. They aim to promote and facilitate positive
change or recognize excellence rather than merely to codify current accepted practice. GFOA has emphasized that these practices be proactive steps that a government
should be taking. Best practices are applicable to all governments (both large and small). Best practices are approved by the GFOA executive board and represent the
official position of the organization
OpenGov May 2018 Page 5 of 8
Summary
As I near retirement, we are looking to technology to help with the transition, not only
for the auditor’s office but also for all departments and especially for the Board of County
Commissioners. The year 2019 will be a big transition especially with two new County
Commissioners.
Jerry and I believe OpenGov will help the County become more efficient in our financial
reporting and allow for the departments to be better informed on their budgets. The process
for the preliminary budget can be enhanced allowing for real time data, to allow the Board of
County Commissioners to make improved decisions regarding the budget preparation.
I would like to schedule 60 to 90 minutes of your time, for you to view a demo of the
software and to allow for discussion.
Respectfully,
Judy Pless, Budget and Finance Manager
Kittitas County Auditor’s office
“Change is the law of life. And
those who look only at the past
or present are certain to miss
the future.” John F Kennedy
OpenGov May 2018 Page 6 of 8
Appendix A
OpenGov May 2018 Page 7 of 8
Appendix B
OpenGov May 2018 Page 8 of 8
Appendix C
Rank Occupation 2014 Employment
Projected Annual
Job Openings Earnings Typical Education
1 Retail Salespersons 107,620 5,250
Median wage is
between $22,610 and
$34,720 per year
No formal educational
credential
2
Combined Food
Preparation and
Serving Workers,
Including Fast Food
68,800 3,850 Median wage is less
than $22,330 per year
No formal educational
credential
3 Waiters and
Waitresses
46,080 3,400 Median wage is less
than $22,330 per year
No formal educational
credential
4 Cashiers 63,420 3,310 Median wage is less
than $22,330 per year
No formal educational
credential
5 Software Developers,
Applications
60,450 3,310
Median wage is
greater than $56,230
per year
Bachelor's degree
6
Laborers and Freight,
Stock, and Material
Movers, Hand
52,360 2,690
Median wage is
between $22,610 and
$34,720 per year
No formal educational
credential
7 Registered Nurses 54,020 2,470
Median wage is
greater than $56,230
per year
Bachelor's degree
8
Farmworkers and
Laborers, Crop,
Nursery, and
Greenhouse
62,910 2,400 Median wage is less
than $22,330 per year
No formal educational
credential
9 Office Clerks, General 59,420 2,300
Median wage is
between $22,610 and
$34,720 per year
High school diploma or
equivalent
10
Janitors and Cleaners,
Except Maids and
Housekeeping
Cleaners
46,220 2,180
Median wage is
between $22,610 and
$34,720 per year
No formal educational
credential
11 Customer Service
Representatives
46,260 2,170
Median wage is
between $22,610 and
$34,720 per year
High school diploma or
equivalent
12 Stock Clerks and
Order Fillers
34,410 1,840
Median wage is
between $22,610 and
$34,720 per year
High school diploma or
equivalent
13 General and
Operations Managers
41,410 1,820
Median wage is
greater than $56,230
per year
Bachelor's degree
14
Sales
Representatives,
Wholesale and
Manufacturing, Except
Technical and
Scientific Products
39,680 1,700
Median wage is
greater than $56,230
per year
High school diploma or
equivalent
15
Landscaping and
Groundskeeping
Workers
30,860 1,640
Median wage is
between $22,610 and
$34,720 per year
No formal educational
credential
16 Teacher Assistants 37,470 1,610
Median wage is
between $22,610 and
$34,720 per year
Some college, no
degree
17 Accountants and
Auditors
31,290 1,590
Median wage is
greater than $56,230
per year
Bachelor's degree
18 Personal Care Aides 50,460 1,560 Median wage is less
than $22,330 per year
High school diploma or
equivalent
19
Maintenance and
Repair Workers,
General
33,260 1,410
Median wage is
between $34,760 to
$56,120 per year
High school diploma or
equivalent
20 Construction Laborers 31,270 1,330
Median wage is
between $22,610 and
$34,720 per year
No formal educational
credential