Success Story
How the Arizona Department of Environmental Quality replaced 26 disconnected spreadsheets with a connected planning process, one phase at a time.
State and Local Government
What does it take for a state agency to move from 26 disconnected budget spreadsheets to a planning process leadership can use every day? This success story shows how the Arizona Department of Environmental Quality partnered with Tru and Anaplan to build connected planning one phase at a time. It began with the annual spend plan and has grown into in-year management, reallocations, and multi-year grant budgeting. Today, decision makers can review every budget line, in every section, at any time.
Author
Tru Consulting
Topic
Spend Planning, Personnel and FTE Planning, Reallocations, Multi-Year Grant Budgeting
Share
Connect With an Expert
By Tru Consulting
Before Tru
Each year, the Arizona Department of Environmental Quality built its budget in 26 separate Excel workbooks, one for each section of the agency.
Every workbook needed the same formulas. Each one needed a tab for every funding combination. Once the workbooks were finished, the finance team still had to link them into a roll-up model before leadership could see the agency-wide picture.
"When we first started with our spend plans, we were creating 26 separate Excel spreadsheets that all had to have the same formulas in them. Each one had to have multiple tabs for different funding combinations, which would crash on a regular basis."
The structure behind those workbooks was complex. The Arizona Department of Environmental Quality manages multiple funding sources across 26 sections, along with expense classifications, activity codes, locations, and personnel. Grants and other external funds often span several fiscal years, which a one-year budget view cannot show clearly.
And the plan was only a starting point. Once the fiscal year began, actual spending came in. Staffing changed. Priorities shifted. Sometimes the legislature made a last-minute change. Leadership needed to know what was actually happening, what to expect next, and whether a decision needed to change.
Why Tru and Anaplan
The Arizona Department of Environmental Quality needed more than a new place to store budget numbers. It needed a planning process people across the agency could use, understand, and trust, and a partner who understood how government finance actually works.
The agency met with several firms before choosing Tru.
"We met with multiple different vendors while we were going through the process, and Tru had the best understanding of what we were looking to accomplish."
Anaplan gave the Arizona Department of Environmental Quality a simple, user-friendly, cloud-based platform the team could keep building on. Tru brought the public sector finance knowledge to design it around the agency’s funding structure, chart of accounts, and decision needs.
"It has been a great partnership with Tru Consulting. They have a vast knowledge of our chart of accounts, funding, and data background, which has made it easy to build the different models and pages in Anaplan."
The Partnership
Since 2021, the Arizona Department of Environmental Quality and Tru have built connected planning in phases. Rather than designing everything at once, each new capability grew out of a question the last one raised.
"The goal wasn't to solve everything on day one. It's to build useful capabilities as you go forward."
Together, the teams have:
- Replaced a previous annual budgeting tool, with a connected spend plan
- Built spend plan management that brings plan, actuals, and forecast together by budget line
- Connected personnel and FTE assumptions to the financial picture
- Created a reallocation process for redirecting funds that won't be spent as planned
- Built section and fund reporting for different levels of leadership
- Launched multi-year budgeting for grants that span three to six fiscal years
- Documented the model architecture and standard operating procedures, and trained staff along the way
As adoption grew, so did demand. The Arizona Department of Environmental Quality and Tru put a governance process in place to keep the model reliable: requests are clarified with the requester, evaluated for effort, testing, urgency, and capacity, and approved by the agency’s Change Control Board.
"They are always willing to work with us on optimizing our models, give us feedback on a more efficient way to build our pages, and moreover help us document the architecture within Anaplan as well as standard operating procedures. We have worked out a great governance plan through requirements gathering, testing, User Acceptance Testing and moving everything to production once sign-off has been confirmed."
The Impact
26 workbooks. One connected plan.
Spend planning now runs in one connected Anaplan environment. Here’s what changed.
Objective
Before
Today
Building the budget
26 separate Excel workbooks, one per section, each with matching formulas
One connected spend plan across all sections
Funding detail
A tab for every funding combination, with some workbooks at 12 or more tabs
Funding sources, sections, and expense classes in one structure
Agency-wide view
Workbooks linked into a separate roll-up model
Updates flow through as changes are made
Reliability
Files that crashed on a regular basis
One set of numbers the whole agency uses
In-year decisions
Knowing a variance existed
A process for deciding what to do about it
Every budget line, visible at any time
Leadership no longer waits for a roll-up to see where the agency stands.
“Every single budget line item from any section within our agency can be reviewed by the decision makers at any given time, and that was never possible before.”
Robert Guy, Deputy Director of Business and Finance, Arizona Department of Environmental Quality
From spotting a variance to acting on it
The finance team reviews spend plan management daily and meets with end users monthly. When a planned expense won’t happen, the reallocation process shows the available funds so leadership can direct them to another priority instead of letting money go to waste.
“Simply knowing that there’s a variance isn’t always enough. Users need a process for what happens next.” Keity Beasley, Business Analyst, Arizona Department of Environmental Quality
Less manual work
The team does less ad hoc reporting and fewer manual spreadsheet calculations that could change the next day. Budget teams and executive leadership can spot issues early and resolve them quickly.
A view beyond one fiscal year
Multi-year grant budgeting shows how each award is split across fiscal years and whether spending is on track across the full life of the grant.
Governance that scales with adoption
A formal intake and approval process keeps development focused on what users will actually use.
“In government, moving faster and becoming more forward-looking cannot come at the expense of governance, whether it comes to transparency, accountability, or the trust folks have in the numbers.” Brian Mehr, Senior Director of Delivery, Tru Consulting
What's Changed
Five years in, the Arizona Department of Environmental Quality’s finance team operates differently. The budget is a living plan rather than a one-time exercise. Personnel, reallocations, and grants connect to the same numbers. And the agency trusts the system it built.
"Everybody hates change at the beginning. But now, everybody goes to Anaplan and says, 'Anaplan says this.'"
The next phase builds revenue and cash management, moving toward multi-year budgeting for all funds and a clearer view of long-term fund health. For a largely self-funded agency, that means seeing where financial pressure could build three to five years out, and making decisions before an issue becomes urgent.
"The biggest change is now the focus around multi-year budgeting and what steps we are taking when building the next pieces to make that all come together. We have a one-year view down, but seeing fund health in the future is what is important. In short, you have to know what is coming to be able to plan for it."
Advice for Other Agencies
01
Start with a defined business process.
Robert’s advice: don’t build functionality and then try to invent a process around it.
02
Start focused and involve users early.
“Start with a focused idea, and involve early and often the people who are actually going to use it, so that you can continue to get their understanding and buy-in.”
Keity Beasley, Business Analyst, Arizona Department of Environmental Quality
03
Build for multiple years first.
As Robert put it, you’re going to need multi-year planning, and you’re going to want it.
Ready to Do the Same?
Tru Consulting helps public sector agencies build connected planning that grows with them, from a first use case to a multi-year view of fund health. The team brings government finance experience, platform expertise, and a phased approach that keeps governance and trust in the numbers intact.
Explore what a connected planning partnership could look like for your agency →
Not sure where to start? Tru’s free Financial Maturity Assessment benchmarks your organization across 15 planning domains. Or watch the Arizona Department of Environmental Quality’s full story on demand.
About Tru Consulting
Tru Consulting is a strategic planning modernization partner focused exclusively on higher education, academic medical centers, and public sector organizations. Our team helps institutions build connected planning environments that improve visibility, reduce manual effort, and support better decisions.
