Report says city has hundreds of inactive and ineffective accounts on its books, and its data hasn’t been archived since at least 2006
City council has agreed to spend almost $80,000 fixing and updating the software and data used to balance Prince Albert’s books.
The decision came after a spirited conversation about why the funding was necessary, and why it took until halfway through the year for staff to flag the issue.
The city will partner with Central Square to provide consulting services at a $70,000 cost to restructure, model, map, convert, test and integrate services for the chart of accounts. They will also enter into an agreement to provide database archiving at a cost of $7,900.
The Chart of Accounts lists all accounts used by the city to budget and to track expenses. It serves as the foundation of the financial data and has implications from budgeting and spending to reporting and invoicing. The current system is full of outdated information and hasn’t been archived since 2006, and perhaps ever.
In 2006, the city replaced its existing accounting software with Great Plains, which it still uses today. At that time, there was little to no review of the chart of account’s structure.
New software was purchased in 2015, but due to changes that are needed in the chart of accounts and turnover in the financial department, it was never put to use.
Moving to the new software and making further digital updates at city hall would require fixing a minimum of 791 accounts. Instead, city staff say, a project should be undertaken to fix the
In all, the city has 35,689 chart of accounts, of which 28,964 are active and in use.
Additionally, irrelevant data was transferred into the system back in 2006, and account numbers aren’t grouped properly.
“The current accounts do not meet the need and are hindering the ability to implement budgeting software and other projects,” director of financial services Cheryl Tkachuk said, adding that the project should allow the city to clean up its data, allow forecasting, more detailed budgets and provide better financial information to council and to city managers.
“I believe we could have more effective and meaningful information,” Tkachuk said.
To fix the myriad of issues, city staff are recommending a total overhaul of the general ledger.
That’s what the $70,000 is for, to bring someone in who can set up the new system and train city staff on how to use it. Those changes, Tkachuk said, will allow for fore efficient line-by-line budget reporting, a review of historical information and reporting, forecasting and the implementation of more digital systems to improve efficiency at city hall.
As Tkachuk told council, since 2006, as accounts changed, infrastructure changed and services changed, the chart of accounts didn’t as it lacked flexibility.
The current software is also limited in how reports can be generated. That means that most city departments need to involve IT for basic information. That slows down the financial reporting process and prevents most managers from accessing even basic information.
She used the water treatment plant as an example of some of the problems with the current system. The plant has one budget line for chemicals. Lacking flexibility, the plant manager is tasked with tracking which chemicals he uses and how much and budgeting for those essential supplies on his own. The city’s accounting software can’t help him.
The proposed project will allow for more reports to be generated by managers themselves, not only allowing them to get a better sense of where their accounts sit, but also freeing up manpower in the IT department, as staff won’t be tasked with generating reports for other departments.
The remaining, $7,900 will allow the existing software data to be archived, which hasn’t been done since 2006 at least. Tkachuk estimated that the last archive would have been before Y2K, though she’s doubtful it even happened then.
The data to be archived goes back to 1998, she said, and the sheer amount of data is causing problems. A recent attempt to merge data for just one customer took over two hours due to the huge volume of data.
While councillors saw the benefit of reorganizing the city’s chart of accounts and archiving the data, they were displeased that the request came six months after budget deliberations were done.
They were also displeased to learn that the new software, purchased in 2015 for about $100,000, hadn’t been used yet.
Tkachuk explained that as they reviewed some of the issues and started the work, they began to find more and more deficiencies. What they thought would only take adjusting about 200 accounts soon turned into a monumental task.
She estimated that in addition to the $70,000 for the consultant, city staff will be heavily involved in the project, putting in about 1.5 times the hours.
“As we go more and more into our system, we’re finding more and more things that need to be cleaned up, Tkachuk said.
“(The consultant) will be providing assistance with showing us how to do it ourselves. They will do modelling for us and we will carry on from there.”
Despite their apprehension, council voted to support the project.
“If we need it, I guess we need it,” said Ward 4 Coun. Don Cody.
“From time to time we have to bit the bullet. If this makes a difference to our budgeting process, I’m happy to proceed with it.”
The motion to approve the purchase passed 7-2, with only Couns. Ted Zurakowski and Tony Head voting against.
