This article first appeared on the OneStream blog by Charlie Harris
If government finance is about anything, it is about data. Often vast amounts of data. Data that is received (from source systems such as ERPs or other agencies), data that is processed (such as budget formulation, allocations, and projections), and data that goes out the door (data to other agencies and reports to the pubic).
In virtually any step of the financial data journey, we find ourselves in need of additional information about the number in front of us at a particular moment. If it is an aggregated value, what are the component parts? Where did the number come from? Was it imported from another system? Did someone enter the number? Was it calculated? Is this number tied to a specific fund, bureau, program, project, or strategic goal? Has this number changed? Who changed it? When did they change it? What was it before they changed it? Did it require approval to be changed? Who approved it, and when? What other numbers are impacted if this number changes?
This all comes down to what is possibly one of the most over-used, erroneously defined, and diversely understood terms in government finance: analysis. This is perhaps because the term is used outside of government finance in virtually every field imaginable. In fact, I recall in a music composition class in college, we analyzed Bach concertos. But, when it comes to government financial data analysis, it can be summed up as the process of uncovering the “back story” of numbers. How it got here and what it really represents. There are possibly as many ways to analyze financial data as there are to interpret the term. The following is a discussion of some of the most common methods of financial analysis in government today and some of the pros and cons of each:
- Call someone
- Use spreadsheets
- Use business intelligence (BI) tools
- Use a financial management platform with analysis included
1 – Call Someone
This is the most basic solution to the analysis problem. We need to know detailed information about a value so we phone/email the person we think may have the required information. This may be the correct person, or maybe not. The response may be swift, or maybe not. There is often no knowledge of the level of effort required from the responder to produce the information being requested. This method is most effective for executives or consumers of information who typically are just dealing with very high-level aggregations of data and infrequently have inquiries of this nature. The return on investment of their time to get access and training to use any other method may not be worth it to them or the agency.
- Simple for the requester
- No training nor cost to the requester
- Specific answers to specific questions
- Possibly very slow turnaround time
- Dependence on others
- Possibly requiring many hours of effort from others
- Possible inability to obtain an answer at all
2 – Use Spreadsheets
This method is widely used. This is the method used by many of the people on the receiving end of the requests in method 1. This involves IT produced data extracts which then are mapped and uploaded into legacy data structures such as Essbase or TM1. Then the add-ins are used to connect to that data. The effectiveness of this method can vary greatly depending on the structure of the source data, the structure of the intermediary data storage area, and skill and availability of the IT team involved in extracting and maintaining the data. Many agencies continue using this method simply because they have done so for a very long time.
While there certainly is a high level of familiarity in this method, getting to the needed information can be very time consuming. The needed data often resides in more than one system. There may be financial transactional data in one system, budget data in another, workflow and approval tracking in another, account reconciliations in another, and audit information in yet another. This can make the process extremely complex, or depending on the requirements, impossible.
- Spreadsheets are ubiquitous, requiring no additional costs nor implementation
- Most finance users in federal agencies have extensive experience with spreadsheets
- Spreadsheets are highly portable and can be shared via email attachments
- Complexity of dealing with data from multiple sources
- Can be very labor intensive for the financial analysts as well as IT
- Data joining and usage is uncontrolled and open to interpretation
- Individual models yield different results, there is no consistency
- Lack of process control, data integrity, audit trail, or security
3 – Use Business Intelligence Tools
Many agencies have various business intelligence (BI) tools such as Tableau, Qlik, or Cognos. These are used to explore data, build dashboards, track key performance indicators, and produce reports. Many of them have fairly sophisticated ETL (extract, transform, load) capability to join tables and pull data from source systems while others rely on 3rd party ETL tools. In most cases they rely on utilizing data in a data universe, warehouse, data lake, or data mart.
While BI tools require specialized training, most agencies with these tools in house have experts on staff. However, these experts tend to reside in an IT (Information technology) group or other operational teams and may not have the financial acumen needed. Rarely does any type of audit or control information get moved from source systems to a data warehouse and the BI tools lack any audit capability on their own. BI tools also lack financial intelligence, so any financial treatment of data requires extensive configuration and/or programming.
- Many agencies already have BI tools
- Most BI tools have excellent dashboard and visualization capabilities
- Most BI tools facilitate connectivity and table joins to data warehouse tables
- BI tools have no financial intelligence
- Some of the critical information often needed does not reside in the data warehouse
- There is no audit trail
- There is no control nor curation of the data
- Different users can produce different results
4 – Use a Financial Management Platform with Analysis Included
A newer option to address this need is utilizing an intelligent finance platform that has financial analysis capability built in such as OneStream. Instead of pulling data from a budget system, a consolidation system, an account reconciliation system, a document management system, a reporting system, and a workflow system, this is all done in a single platform. Several forward-thinking agencies are currently using this new technology or in the process of rolling it out. But the majority of agencies still have multiple siloed systems to manage these various functions as this was the only technology available until fairly recently.
These older systems were state-of-the-art when implemented 15 to 20 years ago. The newer technology manages these functions in a single platform with all the analytic capability residing in the same platform. This allows a user to drill-down and analyze a data element from anywhere in the system with full audit and data control. This could be a budget formulation data entry screen, a KPI dashboard, a CARS reconciliation, or a section of a CBJ or AFR. When a user sees a number and has a question regarding that number or visibility into who made any changes, they can get the “back story” from wherever they are in the process in real time. This is possible since all the functionality is contained in a single platform.
- All information is available on demand
- Information is available in real time, no turnaround time required
- There is no reliance on others
- There are no special analytic skills required
- There is no data warehouse required
- No 3rd party tools are required
- Everyone accessing information is seeing the same results
- Significant time savings and productivity increases
- Significant accuracy and data integrity improvements
- The majority of agencies do not currently have this type of platform (OneStream)
- Implementation of the new system is required
- Executive level support to move to a modern system is required
- Initial pushback due to cost (although it generally results in savings once old systems are retired)
- Need to overcome general resistance to change
Hopefully this was a helpful overview of some of the most common ways to get the underlying details of your numbers. All have their place and their pros and cons. And every agency has to decide what works best to understand the “back story” of their numbers.
To learn more visit the OneStream web site.