My operational reports do not match my accounting system

We often hear from clients who are trying to tie out their operational reports to an accounting report or to their accounting system. For example, my Ginsu report shows 100k of revenue in December, but my accounting system is only showing 90k. Where did the money go?

The first thing you'll need to wrap your head around is that operational data and financial data may not tie out for a given time period. This is common and it is okay. In this article you'll learn why this happens, how to easily view variances, how to mitigate those variances, and what it means for your organization.

I'm going to talk in the context of revenue for this article because 99% of the time it is what organizations are looking at. Use the principles you learn here to understand why it may occur in other circumstances.


Operational vs. Financial

Before we get too far, let's make sure you understand the basics. What is the difference between operational and financial revenue? 

Operational Revenue - the amount of revenue earned by a time card or cost card

Financial Revenue - the amount of revenue earned in an accounting period

Note the distinction here! One is associated with a time/cost card. The other with an accounting period. This is the key to how misalignment happens. When you lock an accounting period for financials, that period can no longer alter revenue. If you then change operational data in that closed accounting period, financial revenue may be created or destroyed and flow into a later period. Or, operational revenue may move earlier or later than the original accounting period. You have now disconnected financial and operational revenue alignment.


Examples

I'll be showcasing three common reasons that operational and financial data become misaligned. To do this, we'll be using an Accounting Analysis Report since that report type gets both operational and financial data. We'll then manually adjust how that data is displayed in Excel to build what I'm calling a "staircase." The staircase helps you visualize when financial data is flowing into later periods.

Follow these steps to create an Accounting Analysis report and build your staircase:

  1. Make an accounting transactions analysis report for the GL batches in question
  2. Row Fields - include Accounting Period and Operational Period
  3. Run the report
  4. Open the report and right click in the pivot table | Choose Field List
  5. Drag Operational Period over to the Column Fields 


Example 1

This is an example of revenue being created in a closed accounting period and flowing into a later period.

We'll use a T&M engagement for our example. In this case, approving time cards late causes their revenue to fall into a later accounting period. Let's use a little story that is all too common to explain what happens.

On Feb. 1 you send an email to everyone in your company that they better have their time cards in for January or else. Everyone swears their time is in so you close the January accounting period, create a GL batch, and send it off to your accounting package. Then a week later Jim from project management tells you he forgot to submit his time for January. You silently curse Jim, reopen January for time, and get his time approved. However, you did not reopen January for GL because you are done with January accounting. The revenue associated with Jim's time cards is now going to land in the next open accounting period - February.

Following the steps in the previous section I've built a staircase showing operational period vs. accounting period. See the screenshot below and note the following:

  • Staircase pattern - the staircase represents where operational and financial data align. See how in the first cell all the revenue is in Jan 2016 for both the accounting period and the operating period?
  • Below the staircase - starting in 2016-04, some revenue falls below the stair. This is an example of where time cards were approved after the accounting period was closed. The financial revenue fell down to the next open accounting period.
  • Double below - looking at 2016-05, you can see that revenue fell not just into the next month, but the month after too. Again, this was due to time cards being approved late.


The addition of new revenue in closed accounting periods can occur for more than just time cards being approved. Any adjustments to operational data causes this. For example, writing cards up/down, approving expenses that generate revenue, or issuing a time or cost card credit on an invoice.


Example 2

This is an example of operational revenue being redistributed to both before and after the original accounting period.

We'll use a FP engagement for our example. In this case, revenue recognition is going to push operational revenue out of the natural accounting period and into both later and earlier operational periods. Here is another little story to help you visualize this.

At the beginning of each month Jane runs revenue recognition for the previous month. Jane always makes sure that she runs it just for the previous month. After revenue recognition is completed she closes the accounting period for GL and Adjustments. After several months of this, Jane suddenly realizes that there is a mistake in the contract amount. The amount is too small and she has been under reporting revenue. Jane corrects the contract amount. She then reopens the previous accounting periods for Adjustments, but not for GL. She reruns revenue recognition. The operational revenue in all previous periods increases to reflect the contract bump. 

Let's look at a visualization of this just like we did in example 1. Again, we have the stair effect. The operational adjustments all land in the same accounting period. You can see them in the bottom row of the screenshot.


Example 3

In this example, we are again using revenue recognition on a fixed price engagement. In this case, Sally always reruns revenue recognition from the beginning of an engagement through the previous month. That means she is always recalculating the operational revenue for past periods. This causes fluctuations in operational revenue as depicted in the screenshot below. Notice how all the columns show an initial large amount of revenue, and then small changes in subsequent periods. 


Mitigate Variances

So what should you do to minimize variations between operational and financial revenue? Well, it depends on the contract type.

T&M / NTE - Always make sure that all time cards are in, approved, and have the correct rates before you create your GL batch and close the accounting period. 

FP - Always run revenue recognition for the preceding accounting period only. Do not rerun revenue recognition for past accounting periods. 

It is more than likely you are going to run into situations where the above is not possible. Mistakes are made. Time cards have the wrong rates, or contracts are set up incorrectly. Historical accounting data is too old to reopen, etc. In these cases you'll end up with misalignment as described in this article. 


Fix Variances

You can fix a variance by following these steps. It may not always be practical, especially if a large number of batches have been created since the problem started.

  1. Delete all batches that contributed to the misalignment. You can bring the batch number into the Accounting Analysis Report to discover which ones contributed.
  2. If the batches were sent to your financial package, you'll need to manually remove them from there.
  3. Open your accounting periods for GL back to when the problem started
  4. Make a new batch