…A Case of the Shoemaker’s Children
By Kristy Neckowicz
Each year, financial and capital program managers must strike a balance between rate increases and the requirement to modernize our water and wastewater utilities. As our sights are fixated on the nation’s aging and failing water infrastructure, we have neglected to look internally at the costly and manual processes that desperately need improvement also.
The typical budget planning process for a utility is akin to a leaky water pipe; the wasted productivity is expected and tolerated. And, just like a leaky pipe runs the risk of becoming a big break, there is a risk of adverse financial performance if we neglect to streamline the process. So, why do we turn a blind eye to the inefficiencies of the typical planning process? It’s because most of us have a status-quo bias called loss aversion. We have a hard time making decisions that are in our long-term interests if they require any sacrifice in the short term. It is also a common belief that fixing this inefficient process requires a huge investment in money and effort to overhaul our enterprise financial systems and business intelligence tools. Like the fabled shoemakers, planners allocate budget for everyone else, but rarely reserve any budget for their own needs. Luckily, with the right tools and a good partnership, there are some easy solutions for our typical planning challenges.
For utilities, budgeting and financial planning can often be similar to the proverb about the shoemaker who was so busy making shoes for his customers, that he neglected his own children, who went shoeless. Similarly, planners can sometimes allocate budget for everyone else without reserving the right budget for their own needs.
Budget Planning Is a Team Sport
Budgeting is a collaborative process, and various departments and business units build their budgets from the bottom up with details. They have a long list of requests from everyone and their brother for new or replacement pipes, mains, hydrants, meters, tools…you name it. Their asset management system can generate a list of everything that needs to be serviced or replaced soon. There are new quality standards they have to meet, and everyone wants his or her request to be included in the budget. All these bottom-up numbers and asset attributes from different enterprise systems are consolidated on one or more manual spreadsheets, then submitted as a wish list of sorts to a centralized function for approval. Of course, ratepayers do not have an appetite for big rate increases so planners are told from the top down that only a portion of all the requests/needs can be approved for spending in the next 12 to 24 months. They are told to go back to the drawing board and prioritize their requests, and delay everything that is not critical. This annual budgeting ritual involves many iterations of negotiations and pencil-sharpening, which takes lots of effort, time, and are subject to many issues with disconnected spreadsheets.
The Devil is in the Spreadsheets
The collaborative budgeting process involves many versions of disconnected top-down and bottom up spreadsheets to depict different scenarios for different audiences. To optimize their annual budget, the planners have to fit projects within many constraints – not just the obvious financial constraint. Executives want to make sure they are funding a balanced strategic mix of projects. They don’t just want to know how much they are spending, but they need to know what strategic initiatives they are spending money on. They also want to know how many linear feet of pipe they are replacing, for example. And, ideally, the projects should be put “in-service” right before the rate increase date, to optimize their cash flow.
These demands have our capital planners and administrators buried in spreadsheets, doing the manual equivalence of playing “Tetris” with each project’s estimated costs. They have become very familiar with this train of thought:
“Hmmm, maybe if I move this block of costs for project IP2486 about 3 cells to the right, I can fit it within the fourth quarter’s budget? Oh no, now there’s a big gap in the third quarter. Rats, I just broke the link to the other cells and I am getting an error in the formula at the bottom. Let me fix that now. Wait, which project was I just looking at? Why did I do that?”
No wonder the typical process for budget planning takes five-plus months to complete.
Capital planners and administrators are often buried in spreadsheets, doing the manual equivalence of playing Tetris with each project’s estimated costs.
The best capital managers and planners want to create multiple budget scenarios so they can choose the “best fit” answer: to allocate budget to as many projects as possible within multiple “top down” constraints. But then there is the chore of creating pivot tables and charts and graphs to compare different scenarios.
With these challenges, valuable what-if analysis and driver-based planning are difficult to perform at a meaningful level of detail. As a result, capital planners and administrators spend 80 percent of their time worrying about the mechanics of the spreadsheets’ cells, formulas, references, macros, look-up and pivot tables; leaving only 20 percent of their time for analysis and strategic insight. The proliferation of disjointed spreadsheets for various scenarios also create their own set of problems: delayed and error-prone analysis, inconsistent assumptions, wasted iterations, hidden cost impacts and lost opportunities. Capital planners and administrators have to repeatedly and meticulously integrate disjointed data and audit formula cells on spreadsheets, spending more time being “Excel Ninjas” than focusing on the value-adding part of their jobs.
The Rear-View Mirror Is Insufficient for Driving Business
The work of capital program managers and planners do not end once the annual budget is approved. As costs are incurred on projects and invoices are received, they have to gleam from those costs any performance variances, and re-plan capital spending accordingly to maximize financial benefits of upcoming rate cases. But actual costs collected in one enterprise system is only one piece of the puzzle – like looking in your car’s rear-view mirror while you are driving. While actual costs are typically stored with a financial chart of accounts structure, project budgets, schedules and work orders are usually identified according to a different work breakdown structure and stored in a different enterprise system altogether. This mismatch of structure, level of detail, and enterprise system further limits the usefulness of information for planners and managers. Physical and mental mappings and translations are required to match up detailed actual costs by chart of accounts with the project plan and budget. When this mapping exercise takes too long or fails to occur, remaining forecasts cannot be captured in a timely manner – resulting in missed opportunities for course corrections and less-than-optimal financial performance.
Horizontal and Vertical Traceability
To properly plan or re-plan for the purposes of maximizing financial performance for a utility, relationships and dependencies between projects can be extremely useful. Whether a project’s start depends on another project’s in-service date, or locations or shared equipment dictate the sequencing of their work, these dependencies are often ignored or forgotten when manually manipulating data in spreadsheets. Without the transparency, project sequences are not optimized, and assumptions and rationale are lost between spreadsheet versions and different managers. These dependencies are the horizontal traceability we need for more realistic planning, in addition to the cost structure visibility which are hierarchical (vertical) in nature. When our budget allocations consider both vertical and horizontal dependencies, our resource allocations can be optimized and we can reduce the risks of delays. During the course of budget planning or reforecasting, we want the impacts and consequences of these dependencies to be revealed as we respond to performance variances, in-service delays, or changing constraints.
Utilities Need an Affordable and Effective Solution
Utilities need an affordable and effective solution to replace cumbersome and error-prone spreadsheets that integrate financial and project data from different enterprise systems at mismatched levels of detail. We need a system that supports the iterative process involved in budget planning with vertical and horizontal traceability, without having to depend on manual spreadsheets and heroics of our planners and administrators. However, whenever we hear the word “integrated” in close proximity of the words “enterprise system” in a sentence, we can expect to see big dollar signs. The rigid, point-to-point integrations required by ERP systems and recommended by systems integrators can’t even join data at different levels of detail, and typically take 18-plus months of professional services accompanied by a hefty price tag. The industry needs a planning application (not an expensive integration tool that overhauls or replaces our existing systems), that can quickly merge in our enterprise data in a semi-structured way that can be immediately useful.
To better plan the much-needed improvements to our nation’s water infrastructure, we owe it to ourselves to improve the tools and processes internally. It is time we join some progressive utilities that already found a better way, to outfit the shoemaker’s children with proper gear.
Kristy Neckowicz, PMP, is president of InVizion LLC, a software company that helps utilities integrate information from various financial, project and work order systems to analyze and defend capital budget and roadmap decisions. InVizion works to extend existing enterprise systems without costly integrations and systems. Instead of wasting precious time configuring and checking formulas in spreadsheet cells and between worksheets, managers and planners in finance and engineering can rapidly collaborate on budget models and forecasts and respond to monthly variances, changing budgets, key drivers and milestone dates, to optimize budget and resource allocations.
