WebFOCUS Enables State of California to Forecast Spending Patterns and Stay on Budget
Snapshot
| Organization The California Department of Health Services (DHS), with over 5,000 employees, is one of the largest departments in the state's government and is responsible for administering a variety of programs that keep residents safe and healthy. |
| The Challenge Make it easier for program managers to stay on budget by accelerating access to critical financial data. |
| The Strategy Enable all users to view reports at various levels of summary detail by establishing a Web-based reporting and forecasting environment. |
| The Results Users can instantly access financial data needed to accomplish directives; managers can forecast results of monthly expenditures to get an accurate prediction of year-end balances; accounting professionals can obtain month-end reports one week sooner than before. |
| Information Builders Solution WebFOCUS, iWay Software, and Information Builders' Consulting. |
The recent budget crisis in California was a wake-up call to government officials across the U.S., prompting a review of fiscal procedures at the state and local levels. As financial planners grapple with the most effective use of their legislated appropriations, the question of the day is: What can be done to help program managers stay within their budgets?
"Instant data is the way to help us stay on track," answers Michael W. Wilken, accounting administrator for the California Department of Health Services (DHS). "We need the ability to quickly identify financial patterns and make accurate forecasts."
To that end, the California DHS (www.dhs.ca.gov) deployed Information Builders technology to deliver a real-time view of financial data directly to the desktops of more than 5,000 DHS employees. The Web-based solution includes an expenditure forecast reporting system that allows program managers to project end-of-fiscal-year balances so they can avoid overspending.
"Before WebFOCUS came along, we were relying entirely on a paper-based reporting system," Wilken explains. "We wanted to get financial data to our decision-makers faster. Today, everyone in the California DHS who has any financial responsibility uses WebFOCUS to monitor appropriations, expenditures, contract payments, travel claims, salaries, and virtually every facet of the department's fiscal data."
As one of the largest departments in the California state government, the California DHS is given appropriations for specific tasks related to issues such as AIDS, drinking water quality, food and drug inspections, and other programs that keep Californians healthy and safe. Program managers need to complete those missions, and they must make decisions about how to spend state budgets at every step along the way. "We're always trying to get the most that we can for the taxpayers' money," says Wilken.
In the past, meeting that goal was a labor-intensive process. "We had to pore over paper reports that might be hundreds of pages long," Wilken recalls. "Now, WebFOCUS lets us quickly click down through hyperlinks to find the exact information we need, instead of flipping through a two-inch-thick paper report."
Previously, the department has had a variety of other software applications to help managers stay on budget. What's new, Wilken says, is how quickly users can get the information they need and how quickly they can react. "Using WebFOCUS, the information is more instantaneous than ever before," he adds. "This speed is helping us make better budget decisions."
Evolving Systems for a Growing Population
Until recently, all financial reporting for the state was handled by the California Department of Finance using the California State Accounting and Reporting System (CALSTARS) – a mainframe accounting system developed in the early 1980s. CALSTARS is a powerful and comprehensive system, but its limited reporting functionality made it difficult for budget managers to obtain specific information when they needed it most.
California DHS wanted to enhance its support services to keep up with the growth of the state. After evaluating various solutions, accounting professionals realized that boosting responsiveness was largely a matter of delivering mainframe information directly to users. They envisioned a Web-based reporting environment that they could manage on their own. The California DHS Accounting Section has a very small IT staff, with a team of only four people supporting 125 accounting professionals serving over 5,000 health services employees.
At first they tried to improve reporting flexibility by building flat files of the mainframe accounting data. They used the Monarch data mining software from Datawatch to load the mainframe data into a Microsoft Access database, where it could be accessed and analyzed from the PC. It was an improvement, but maintaining the database was tedious and required a DBA to handle most procedures. Monarch was also constrained to its own proprietary format. "Monarch was not the final solution we were looking for," says Wilken.
The team also looked at products from Vision Journey, Flashpoint, and other reporting vendors, but none of them were able to seamlessly bring mainframe data down to the Web in a way that suited the needs of the DHS.
It was at about this time that a representative from Information Builders introduced Wilken and his team to WebFOCUS. "We were familiar with Information Builders' Mainframe FOCUS and iWay middleware software, which many state departments use to connect to a shared data center," Wilken says. "After viewing a demonstration of WebFOCUS, we believed it would fulfill our mission of providing users with Web-based access to financial information from anywhere in the state."
New Tools for the Golden State
With help from Information Builders' Consulting, the California DHS established an intranet-based reporting architecture that dramatically simplifies the process of accessing and distributing mainframe accounting data. They used iWay technology to create several ETL processes for accessing VSAM, Adabas, and sequential flat files, transforming it into the correct format, and loading it into a Microsoft SQL Server 2000 database structured for reporting. The development team also created two WebFOCUS applications to report against the data: the CALSTARS Online Reporting Environment (CORE) and the Expenditure Forecast Reporting System (EFR).
"Information Builders' Consulting played an integral role in this project," Wilken says. "They certainly have the technical capabilities, and they understand the government environment. They have worked marvelously with us and they have gained a good understanding of our CALSTARS accounting system."
Today, CORE enables more than 5,000 DHS employees across California to review financial data from their desktops, without any special involvement by the IT department. Using standard Web browsers, users can quickly review individual transactions or generate summary reports. "When we close the books at the end of each month, authorized users can review financial records the very next day," Wilken says. "Before we started using WebFOCUS, these users had to wait five to seven days to obtain this same information – and it wasn't targeted to their needs."
Best of all, says Wilken, CORE is easy to maintain, enhance, and scale. "At this point, CORE offers about a dozen reports that can be viewed at various levels of detail. We would have to generate thousands of hard copy reports to give users this same amount of information."
Users see an HTML environment with a menu screen that allows them to hyperlink to specific report styles. Reports can be chosen from a list or a query box. Radio buttons, drop-down menus, and other GUI techniques allow users to choose coding parameters and date ranges so they can select the transactions they are interested in. Reports can be output in PDF, HTML, Excel, and Microsoft Word formats.
Conveying a Real-Time View of Spending Trends
Meanwhile, the EFR System uses the same SQL Server data to deliver a real-time view of spending trends to upper management. "EFR helps managers stay on top of spending," says Wilken. "So they can pull in the reigns, if need be, to keep their budgets in line."
Projections are based on historical information in the accounting system extending back to 1999. Individual analysts within each division use WebFOCUS Maintain to adjust projections by fine-tuning line-item details that are not in the historical system. The result is a powerful financial reporting and forecasting tool that allows authorized users to view expenditures, make projections, and adjust spending plans from month to month to stay within their budgeted allocations.
"The majority of the people who access this system are not accountants," Wilken says. "Information Builders helped us design the database and build interactive reports that are easy for lay people to understand and use."
Wilken says the results of the project have exceeded his team's expectations. "Our goal was to reduce our report processing and delivery time by at least 80 percent, and also to eliminate the five-to-seven day delay at month-end," he explains. "As it turns out, WebFOCUS has eliminated 100 percent of the delay because reports are now available the morning after we close the month."
At the same time, the DHS has improved its analysis capabilities by enabling managers to sort, summarize, and drill down on information as required, as well as export data directly into Excel for further analysis. This negates the need for financial analysts throughout the department to type information from paper reports into spreadsheets. "We have eliminated this laborious process entirely, and improved our accuracy in the process," says Wilken, who estimates the department has gained tremendous savings in processing time, printing costs, and materials by eliminating paper reports.
But that's just a fraction of the estimated overall savings, once the improved efficiency of thousands of state employees is considered.
"In order for a department to manage its appropriations, decision-makers need to know where they stand at all times," Wilken sums up. "WebFOCUS provides us with a scalable reporting system that is easy to use and maintain – so we always know exactly where we stand with regard to the state budget."
