Make OpenGov Intelligence your Primary Research Tool
April 14, 2016 – Mike McCann
Governments find value in publishing checkbooks in OpenGov Intelligence (OGI), for both internal management and external transparency. They may not realize this just scrapes the surface, offering a mere taste of the versatile functionality they would enjoy if they went a bit deeper.
With little additional effort, governments can make OGI the go-to platform for deep analysis across their government. Imagine giving your entire team non-technical, intuitive, access to detailed information posted to the general ledger from every subsystem of your ERP.
Once your site is up with a stable Chart of Accounts, your Customer Success Manager can help you update your current data-transfer format to upload complete transaction data sets to the OpenGov Data Manager.
Backstory: Details matter
Financial professionals know General Ledger account balances are simply the total of all the detailed transactions posted to those accounts. They know that research is best done by working with those transactions, which detail the amount posted to each account, the date, the poster, the purpose, and how to find the other side of a transaction (often the key detail).
OGI is system agnostic – it can take data from about any GASB-compliant accounting system – but many people do not realize it is also level-of-detail agnostic. Most initial customer sites are built at the “GL Object” detail level. This provides a good level of detail for public transparency purposes, general analysis, and enables development of an accurate model of the customer’s chart of accounts.
At the same time many customers set up checkbook reports using either check total or check line-item details. These reports enable spending review, searching and organizing data by any field contained in the records. These checkbook reports may even be linked to visualization reports on the site so the user can look at a specific set of accounts on a current or historical report and see the checks that were charged to those accounts.
This is useful and powerful, but has an important limitation. Because only checks have been uploaded, the GL object totals should neither be expected to balance to this checkbook data nor provide all the answers a researcher needs to understand expense totals and do deeper inquiries.
How to get the most value from your investment by uploading all the details with OGI
To give OGI the complete data it needs to be the daily tool your team deserves, expand your uploads to include all transactions that are posted to the General Ledger. Make sure you include every journal source, from all sources that feed your General Ledger.
This is not difficult and your Customer Success Manager will be your ally here. There are a few points to focus on:
1) Posting date is a critical field. This is the date when a transaction is approved and affects the totals in your GL. Once your history is uploaded, you will upload transactions with a specific posting date one time only.
2) GL Date or Period is the accounting period the transaction is applied to. Since most shops use a “soft close” approach, they actually have many months and even fiscal years, open for late entries.
The combination of posting date and effective date allows for entry to any open period at any time while avoiding duplicate entries or overstated totals.
3) Journal source tells us what subsystem a transaction originated in, and may tell us what type of activity it is. (i.e. AP and MC both come from the AP subsystem and represent system checks and hand checks)
4) Debits and Credits. In many systems the debit and credit entries are controlled by journal source, and may require sign reversal on entry to the general ledger. I.e. General journal (GJ) entries will post as entered, but budget entries may be recorded with signs reversed and need to be flipped for OGI.
5) Budget vs GL transactions. Some systems routinely process budget adjustments as a class of journal source codes. OGI needs to know which codes to identify as budgetary.
The data transformation process accommodates each of the above points. This is one-time work, and allows for a smooth, automated upload process going forward.
The data can be taken either from the individual subsystems or from the GL side, as it is posted. In either case the result is that the transaction report now balances to the historical and current period reporting. The choice may be driven by the difficulty of aggregating transactions from varied subsystems, or by limitations in the data fields passed to the general ledger by the ERP.
You can even go one step further: Since OGI is data-level agnostic, your complete transaction data set can be used as both a GL upload for transaction reports and as a transaction upload for grid type detail reporting.
Since full transaction data sets use the same chart of accounts segments that GL data uses, the same file can be uploaded for both uses. When loaded as GL data the OpenGov Data Manager simply ignores the descriptive transaction fields, while accepting the account segments, amounts and GL dates.
Mike McCann moved into government service in Ukiah, then Monterey CA, after beginning his career in corporate (ADP, Wells Fargo Bank, Blue Shield of CA), not-for-profit (Blue Shield of Ca, Mendocino Private Industry Council), and start-up accounting. For the last 20 years, Mike has been hands-on with budget, financial reporting and accounting operations, including City budgets and CAFRs. He holds a B.S. in Accounting from SJSU and M.S. in Instructional Technology from CSUMB.
Contact Mike with questions or comments at firstname.lastname@example.org.
Category: Product Advice