Public Sector Financial Forecasting Solution

December 18, 2024

Overview

Etico was engaged by a large government entity responsible for managing over $2 billion in annual financial budgets to create a streamlined, secure, and centralized financial forecasting solution. The organization faced significant challenges with their existing process, which relied on dispersed, manual Excel spreadsheets to capture forecasts from over 1,200 users, including managers and financial management analysts (FMAs). This cumbersome process needed an upgrade to a more robust process to facilitate precise, organization-wide financial planning and forecasting while minimizing manual interventions and consolidating data for analytical reporting.

Objectives / Business Problem

The customer’s existing forecasting process was time-consuming, error-prone, and reliant on numerous Excel files circulated monthly among 300+ financial analysts. Consolidation was a lengthy, manual process that increased the risk of errors and limited the system’s ability to support complex financial requirements. The organization needed:

Centralization: A single, interim solution to capture financial forecasting data directly from departmental managers until a permanent SAP-based solution (SIGMA) was ready.
Standardization: Uniform data entry and integrity standards to replace varied Excel-based inputs, ensuring high-quality, consistent forecasting data.
Security: Enhanced user authentication and secure communication via encryption to safeguard financial data.
Efficiency: Reduced time for data collection and consolidation, facilitating quicker and more reliable monthly forecasts and budget adjustments.

The new web-based solution, therefore, needed to improve data accuracy, reduce manual tasks, and allow users at all levels to efficiently capture and access forecast information.

Data Capture

The system’s primary function is a comprehensive data entry interface, designed to mimic the familiar spreadsheet structure but within a centralized, controlled environment. Key features include:

Data Entry Interface: Users (managers, FMAs, executives) can enter, edit, and review forecast data across various cost centers, funds, and periods within the fiscal year. An intuitive interface captures diverse fields, such as funding sources, budget line items, forecast amounts, commitment items, and other critical financial metrics across varying financial dimensions.
Forecast Adjustments: Monthly adjustments can be made, allowing executives to refine projections based on the latest available data. Data fields are aligned with SIGMA standards to ensure compatibility when the permanent solution is implemented.
Controlled Vocabulary & Auto-complete: To streamline data entry, a controlled vocabulary was implemented for key fields, ensuring data consistency and reducing entry errors through auto-complete options for commonly used terms.

The web-based application effectively removed the need for Excel spreadsheets, consolidating all forecasting data into a single, accessible platform.

Data Management

Centralized Data Store: All forecast data, including adjustments and supporting metadata, are stored in an Oracle 11g DBMS, housed within a secure enterprise data center. Regular data extracts from SIGMA (covering Actuals, Commitments, Salary Forecasts, and Budgets) are imported on a monthly basis to keep the data current.
User Authentication & Authorization: A robust authentication system controls access based on user roles (e.g., managers, executives, FMAs), ensuring that only authorized personnel can access or modify specific data fields. Unique user tokens, distributed via email, allow secure 24-hour access, enhancing both security and user convenience.
Data Integrity Controls: Application-level validation rules prevent invalid data entry, such as non-numeric characters in numeric fields and compliance to business rules, keeping data quality at a high level.
Audit Logs: The system records detailed audit logs for all user actions, tracking modifications, deletions, and user access events, which helps monitor compliance and facilitates accountability.

Data Analytics / Insights

The web-based financial forecasting tool provided critical data analytics and reporting capabilities, offering insights into financial trends, budget alignment, and funding requirements:

Dynamic Reporting: The tool enables the generation of real-time financial forecasts, budget status updates, and allocation insights across different cost centers and commitment items, allowing managers and executives to make informed decisions quickly.
Integration with SAS: Data is seamlessly exported to SAS software, enabling advanced analytics and reporting for deeper insights. Executives and FMAs can use this capability to produce detailed forecasts, adjustments, and variance reports, offering a comprehensive view of the financial landscape.
Pre-filtered Data Views: Based on delegated authority, users can view commitment item totals and relevant forecasts for their specific cost centers, enhancing targeted analysis.

This streamlined approach has minimized redundant data handling and improved the organization’s capability to perform thorough and accurate financial analyses, ultimately improving decision-making and resource allocation.

Conclusion

The financial forecasting solution empowered this large organization by eliminating the reliance on Excel spreadsheets, standardizing data collection, and improving data integrity. With enhanced security features and centralized data management, the organization now has a secure, interim forecasting tool that supports its extensive financial operations and provides clear visibility into the allocation of funds. The solution has significantly reduced the time and effort required for monthly forecasts, setting a strong foundation for the organization to transition smoothly to the future SAP-based system and to maintain the quality of their financial data assets.

Key Takeaways

Enhanced Efficiency: The centralized, web-based application significantly reduces the time required for monthly forecast data collection and consolidation.
Improved Data Integrity: Standardized data entry fields and validation controls have reduced data entry errors and increased data accuracy.
Increased Security: Robust authentication and encryption measures protect sensitive financial data, addressing critical security concerns for government entities.
Better Decision-Making: Dynamic reporting and analytics capabilities provide actionable insights into financial forecasts, enabling better planning and allocation of resources.

This case study highlights the impact of an efficient and secure forecasting solution, demonstrating our commitment to solving complex financial management challenges for public sector clients.