Happy Business Starts Here

Highlighted
Zuora Support
Zuora Support

The discrepancy between trial balance and journal run results.

Question:

How to troubleshoot if there’s a supposed discrepancy between accounts receivable detail report from Trial balance and summary journal entries from journal run?

 

Suggestion:

 

Precheck/Prerequisite first:

1. If the status of the accounting period is closed:  

    • The running time of both trial balance and journal run should be later than the accounting period closure time. Within Pending Close status, for users who have the Manage Close Process permission enabled, the accounting period behaves like an open period.

2. If the status of the accounting period is still open:

    • please make sure no transaction generated between running trial balance and running journal run, or we couldn’t promise the consistency. It’s recommended to re-run them and confirm no transaction generated. Make sure, you are always comparing based on the same currency. 

3. Make sure, you are always comparing based on the same currency. 

 

  • To check the AR(Accounts Receivable) / trial balance running time under Balance tab with “Updated on”. 

Screen Shot 2019-09-11 at 17.11.08.png

  • To check the journal run creation time.Screen Shot 2019-09-11 at 17.11.13.png 

Comparison Steps ( details can be referred to the subsequent example):

Step 1. Get the trial balance result (TB result):

  • Select the corresponding currency from the accounting period Balances tab drop-down list in Zuora UI, expand each transaction type to display amounts by transaction types.

Step 2.Get the journal run result (JR result)

Step 3. Compare the total credit or debit amount based on the transaction type against JR and TB result. 

 

Note: If the sum of the transaction amount for each transaction type matches the amount displayed in the trial balance UI data, the TB result matches the JR result, there is no discrepancy. Then you can disregard the below steps from step 4. 

 

If the sum of one transaction does NOT match the amount displayed in the trial balance UI, let’s calculate their gap, and call it as “gap against transaction type level” , then move on to #4

 

Step 4. Compare the sum amount based on JEI type within this transaction type just in JR excel file: 

Check if the sum amount of “Credit” JEI type = the sum amount of “Debit” JEI type?

  • If yes, move to step #5. 
  • If not, let’s calculate their gap, and call it as “gap based on JEI type level”. 
    • If “gap based on JEI type level” = “gap against transaction type level” , this is an expected result, which means the trial balance result matches the journal run result, there is no discrepancy. 
    • If “gap based on JEI type level” “gap against transaction type level” , move to step 5.

Step 5. Compare by accounting code within one single transaction type against JR and TB result.  

 

Once we find out the mismatched accounting code, let’s calculate their gap, and call it as “gap against accounting code level”

Then please contact Zuora Support with the attached metric of your comparison result and highlight the supposed discrepancy; also please include:

 

“gap against transaction type level”

“gap based on JEI type level”

“gap against accounting code level”.

 

One example to compare with USD currency (You can use more professional tools, such as PivotTable in Excel.):

 

Step 1. Get AR report from trial balance: 

  • After running the trial balance, navigate to Balances > select USD from the currency drop-down list.

Screen Shot 2019-09-11 at 17.12.49.png

Step 2. Get JR result:

 

      In downloaded JournalEntryItem.csv:

  • Filter column: “Journal Entry Item”: Currency: USD
  • Filter column: “Journal Entry: Transaction Type”: Invoice item & Taxation Item.
  • Filter column: “Journal Entry Item: Type” : Credit or Debit
  • Sum column: “Journal Entry Item: Amount”

Screen Shot 2019-09-11 at 17.13.01.png

In this example, the total amount of the “invoice item” & “taxation item” transaction types is 484,031.31.

 

Step 3. In this example, TB result from #1 = JR result from #2. So we move on to compare other transaction types by filtering different transaction types. 

 

If the sum of the transaction amount for each transaction type matches the amount displayed in the trial balance UI data, as showing in above examole, it means the TB result matches the JR result, there is no discrepancy. Then you can disregard the below steps from #4. 

 

If one transaction type’s amount sum the amount displayed in the trial balance UI result, let’s calculate the gap, and call it as “gap against transaction type level”, move on to #4 below.

 

Note: It’s unnecessary to compare “Revenue Event Item” type as the trial balance doesn’t cover revenue.

 

Refer to the attached doc for the transaction type mapping in JEI.csv and Balance tab. 

 

Step 4. If there is a discrepancy in one transaction type, please compare the sum amount based on JEI type within this transaction type just in JR excel file: 

  • Get Credit sum amount (credit sum) with Filter column: “Journal Entry Item: Type” as Credit 
  • Get Debit sum amount  (debit sum) with Filter column: “Journal Entry Item: Type” as Debit

Then, if (credit sum) =  (debit sum)?

  • If yes, move to step #5. 
  • If not, let’s calculate their gap, and call it as “gap based on JEI type level”. 
    • If “gap based on JEI type level” = “gap against transaction type level” , this is an expected result, which means the TB result matches the JR result, there is no discrepancy.  We can stop all below steps.
    • If “gap based on JEI type level” “gap against transaction type level” , move to step 5.

 

Step 5. Compare by accounting code within this transaction type against JR and TB results.  

  • Expand corresponding transaction type to display the amount sum based on accounting code under the Summary tab in the accounting period.

Screen Shot 2019-09-11 at 17.13.12.png

  • Filter column “Accounting Code: Name” in JournalEntryItem.csv based on #2 status. (Below is accoutning code “Training Accounting Code1” fitler result, 1000, which matches trial balance result above.)

Screen Shot 2019-09-11 at 17.13.24.png

  • Compare based on each accounting code to identify the mismatched accounting code and journal entry.

 

If there is one mismatch within one accounting code, please contact Zuora Support with the attached metric of your comparison result and highlight the supposed discrepancy, and:

“gap against transaction type level”

“gap based on JEI type level”

“gap against accounting code level”.

 

In addition, using the Excel PivotTable tool is better to compare:

Screen Shot 2019-09-11 at 17.13.36.png



If you found my answer helpful, please give me a kudo ↑
Help others find answers faster by accepting my post as a solution √