How to Use Your GL to Prepare Your CFR Reports and other NYS Reports
Many nonprofit agencies have reporting requirements that go beyond GAAP. For example, in New York State, social service agencies depend on state funding to serve their clients. To get this funding, they must submit various CFRs (Consolidated Fiscal Reports) to various NYS departments, including the OPWDD, OMH, OASAS, and OCFS (i.e. Office for Persons with Developmental Disabilities, Office of Mental Health, Office of Alcohol and Substance Abuse Service, and Office of Children and Family Services).
Faced with these different requirements, finance folks often download their financial statements into Excel and start making adjustments. This is not a good idea for several reasons:
- Excel spreadsheets are easy to change and don’t provide an audit trail.
It’s so easy to make a change in Excel. You’re rushed. You need to submit a report now. So you make the change. But six hours/days/weeks/months later, you might have the “before” and “after” versions of your spreadsheets at best. You don’t know why the change was made. You have no documentation. And you probably don’t have the backup. So you have no idea where that number came from.
- Excel sheets are prone to error. Estimates vary as to what percentage of spreadsheets have errors. But it’s not hard to find scary stories.
- Excel spreadsheets are often only understood by the person who created them. Has this ever happened to you? “John built this incredible system with Excel. But he left, and we now can’t figure it out.” This is part of the pivot table gateway drug You start simple. Just one VLOOKUP. But before you know it, you’re in too deep.
- Others can’t easily analyze the information. Maybe your Excel sheets work. You document and track your changes. You get the right results. But what if other folks in your department or agency want to work with the results? They end up cutting and pasting from Excel. Which leads to more problems.
Excel may seem like a quick and dirty solution. And it is dirty. But it’s only quick the first time. Then things get dirtier and more complex.
So, what to do? Theoretically, you could build another application. But you already have a general ledger. So, why not use that as a cheap data warehouse?
This solution works in multiple systems. I’ve used it in Dynamics GP, Dynamics NAV, Lawson and Oracle. (Not all the solutions were for non-profits. But the same principles apply.)
How to Use Your General Ledger as a Cheap Data Warehouse
In brief, this is how you would proceed:
1. Add additional segments/dimensions/attributes to your accounting string.
Most organizations post their accounting entries to a natural account and cost/profit center. They may also add a fund or project depending on what they’re using the system for.
But for whatever reason, they don’t have all the dimensions they need for their CFR or other, Non-GAAP reporting. This is silly. Yes, there was a time when systems ran slower and disk space was expensive. But that time has passed. I find that most companies can increase their level of detail by a factor of 10 with no reduction in performance.
So, whatever detail you need, find a way to get it into your ledger. And as I said above, I’ve found ways to do this with multiple kinds of accounting software.
2. Learn how your system performs allocations—and get it to allocate in detail.
Most GLs have allocation functionality. But usually, it’s only set up to do bulk allocations.
For example, you may have 10 to 20 accounts that post to your administrative departments. But your allocation process may only post to an overhead account for each operating department. This may not be adequate for your state reporting.
Instead, see if you can allocate each overhead number in detail. It’s more work at setup, but it will pay benefits for years to come.
3. Create a separate company in your general ledger for entries that only apply to state reporting.
Once you start posting to your ledger, you want to make sure to separate GAAP book entries from those needed to adjust for CFR and other state reporting. Many larger systems have functionality for multiple sets of books. But if you don’t, you can generally create another company. This is possible in almost any accounting software I’ve used (although it’s easier when companies don’t require multiple database tables or databases).
Why do I recommend the “create a separate company” route? Several reasons:
- You don’t want to touch your GAAP audited numbers. Often, CFR preparation begins after your regular audit has been completed. And often, you need things for CFR that auditors don’t care about, such as allocation of overhead, expensing of fixed assets, or simple reclassing of expense amount programs. All these changes don’t affect your GAAP numbers but can be necessary for CFR.
- You can make all changes via journal entry. Once you have a separate company, you can make all the changes you need via journal entry, which generates the audit trail you’ll need when you later need to decipher what you’ve done.
- You have the same controls as for any regular company. For example, every accounting software has some way to “close the books” (even if it’s harder in some that others). Here, you can close your GAAP companies and keep your adjustment companies open longer if needed.
- Use spreadsheets to automate journal entry.
As I said above, Excel is a bad place to store your data. However, it can be an excellent way to enter your data. Every software I’ve worked with has had some mechanism for uploading journal entries from spreadsheets. Since you’re going to be creating fairly complex adjusting entries, it’s sometimes easier to create the entries in Excel and then “push a button” and have them transfer to your accounting software.
5. Create reports combing the two companies.
To get your CFR reports, you combine the data from your two “companies” in your reports. Most tools allow something like this, including Jet Reports, Management Reporter, and SSRS (SQL Server Reporting Services).
6. Consider moving your payroll detail data into your ledger.
Almost every organization I know imports their payroll data into their general ledger. The problem, however, is that these imports are done to satisfy financial reporting, not CFR reporting. And it isn’t adequate when reporting requires information at the person or position level.
Therefore, if possible, it’s best to post payroll data by person. This is especially important if you need to reclass payroll expenses because the payroll system had incomplete or incorrect data. (Such as, for example, a manager switched divisions and the payroll system was updated with current accounting information after a payroll had been issued.)
Many people are (rightly) concerned about the security of payroll data. Logically, it makes sense to use employee number as part of the posting string, but someone might get salary information from the ledger. However, I’ve found ways in multiple systems to use proper security to ensure that this doesn’t happen. The solution varies depending on the software involved. But generally, I find it’s doable.
In summary, you need to know how to make your ledger do EVERYTHING it can. Because once your data is in the ledger, you have an audit trail, and you can distribute information. And you use Excel only to prepare data for uploads or to make pretty reports—not to perform complex calculations or store adjustments.