Sensors, data will help Portland identify risky routes for bicyclists
April 20, 2018
The city will collect congestion and speed data and self-reported rider comfort levels to improve pedestrian and bicycle transit.
People will always need spreadsheets, but when it comes to government budgets, more automated solutions are taking over.
Colin Wood is the managing editor of StateScoop. Before that, he was a staff writer for Government Technology magazine. Before that, he taught Engl...
A new tool by government cloud software provider OpenGov, called Budget Builder, has a lot of novel features, but the reason people really like it is because they don't have to build their budgets in Excel anymore.
Manually entering information and merging huge spreadsheet files can take government organizations months. If someone has a question about the budget, it can take weeks of emails and sorting through the data to find an answer. And many times the answer isn't even accurate.
According to a 1998 study produced by the University of Hawaii's College of Business Administration, spreadsheet use within organizations is subject to a "disturbing" and "unacceptable" rate of error. Of 113 spreadsheets audited, 88 percent were found to have errors, some of them critical to core business outcomes.
More than 30 cities and counties have adopted Budget Builder as a means to get information faster and simplify their budgeting process. Beyond the time savings, software features like intercity data sharing put budgets in a new light.
The time savings are the big attraction, though. The Jackson County Water and Sewage Authority in Georgia has been using Budget Builder for about a year, and finance director Judy Smith said the tool has cut months from her budgeting process.
"In prior years, my budgeting process would start in July or August and start gathering information from my department managers," Smith said. "After I exported everything into Excel, created their individual department budgets for them to populate, emailed them to them, and asked for deadlines to get the information back to me so that I could then put it back into the Excel format for the full budget."
After shuffling dozens of pages of spreadsheets, everything would be completed by November, she said. Now, she said, the process has been streamlined.
"You don't have to recreate the wheel," she said. "The templates are set and once they're populated by the users that need to go in and fill in the proposed budget, you can simply view the budget as a whole. You don't have to combine all those Excel worksheets anymore manually. That's a huge time saver, just in that. There's a column on the left that lets you look at the entire budget as a whole. It will show you whether or not you have a balanced budget on the fly and that's a really neat feature."
Excel does have some advantages, though, acknowledges OpenGov sales engineer Jason Carian.
"We spent a good year and a half watching and learning from governments and how they built budgets and what we learned is really 80 to 90 percent of the municipalities out there build in Excel," he said. "Which, there's a lot of upside to that. Everyone knows Excel, it's easy to use."
But the downsides compound as a government builds its budget, he said.
"What that process looks like … is somebody in the government will send out spreadsheets to all the department heads and analysts that need to submit budget proposals. Those folks will submit their request for the year into these 20-200 page spreadsheets and then re-send them with some narrative over email to the budget team. The team creates a formula and merges everything together into a master spreadsheet. It's a very error-prone, time consuming, nasty process."
Beyond offering the ability for direct collaboration, Budget Builder also includes a "change history" section that allows cities to give their councils the opportunity to "literally watch the budget get built," Carian said.
The most popular feature of the software, he said, is the ability to filter across funds and departments in real-time, a process that could take weeks using spreadsheets.
These budget visualization tools also fit into "a larger trend about making open data more useful," said Katherine Hillenbrand, project specialist at the Harvard Ash Center's Data-Smart City Solutions.
The software's network feature provides users with a map to browse and drill into budget data across the country. OpenGov distinguishes itself by treating budget data as simply one component of a broader realm of government open data.
For a lot of local officials, the speed is the big selling point. It was surprising how much faster the budget process was after Jackson County adopted the tool, Smith said.
"What did I do with myself?" she said. "I took a breath and I actually took a vacation."
Editor's note: Minor edits were made to this story on Jan. 27.