Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to reconcile general ledger data with trial balance. General ledger contains all of the account transactions and trial balance contains all of the account balances.
I've tried to perform reconciliation in following way:
1. I have summarized account balaces in general ledger by using straight table. This gives me unique account numbers and one balance for each account. Example:
Orginal general ledger | New straight table (summarized general ledger) | ||||||||||||||||||||||||||||||
Account | Amount | Account | Amount | ||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1111 | 100 | SUMMARIZE -> | 1111 | 150 | |||||||||||||||||||||||||||
1111 | -50 | ||||||||||||||||||||||||||||||
1111 | 100 | ||||||||||||||||||||||||||||||
2222 | 100 | 2222 | 300 | ||||||||||||||||||||||||||||
2222 | 200 | ||||||||||||||||||||||||||||||
3333 | 300 | 33333 | 300 | ||||||||||||||||||||||||||||
etc. | etc. | etc... | etc.. |
2. Next, I'm trying to bring trial balance accounts and balances next to summarized balances for comparison. Example:
Straight table (summarized general ledger | Trial balance | |||
Account | Amount | Account | Amount | Difference |
---|---|---|---|---|
1111 | 150 | 1111 | 200 | 50 |
2222 | 300 | 2222 | 300 | 0 |
3333 | 300 | 3333 | 300 | 0 |
etc. | etc... | etc... | etc... | etc... |
Problem is that I can't figure out how to match accounts in G/L and trial balance. I was able to bring them into same straight table but they did not match. Moreover, when I brought trial balance account to straight table with summarized G/L accounts, the table didn't summarize them correctly anymore but instead listed all of the individual account transactions.
What am I doing wrong and is this the best way to accomplish this type of task? Could some one point me into right direction?
Hi
Without being able to see your model or a sample, I can only guess at the problem - here's my guess.
I suspect that you have a different field name for you GL account number in the two source files and you have loaded those into two separate tables. Load the data something like this:
Data:
LOAD GLAccount,
Amount,
'Trans' As Source ,
....
FROM GLTransacations;
Concatenate
LOAD [TB GL Account] As GLAccount,
[TB Amount],
'TB' As Source,
...
FROM TrialBalance;
Now create a straight table with GLAccount as the dimension, Sum(Amount) as the GL amount, Sum([TB Amount]) as the TB amount and COlumn(2) - Column(1) as the difference.
If you need more specific help, I suggest that your post your qvw or a at least a sample. If the data is confidential, scramble it before uploading.
Regards
Jonathan
Hi
Without being able to see your model or a sample, I can only guess at the problem - here's my guess.
I suspect that you have a different field name for you GL account number in the two source files and you have loaded those into two separate tables. Load the data something like this:
Data:
LOAD GLAccount,
Amount,
'Trans' As Source ,
....
FROM GLTransacations;
Concatenate
LOAD [TB GL Account] As GLAccount,
[TB Amount],
'TB' As Source,
...
FROM TrialBalance;
Now create a straight table with GLAccount as the dimension, Sum(Amount) as the GL amount, Sum([TB Amount]) as the TB amount and COlumn(2) - Column(1) as the difference.
If you need more specific help, I suggest that your post your qvw or a at least a sample. If the data is confidential, scramble it before uploading.
Regards
Jonathan
Hi Rao,
Actually this depends on your data model so please explain about it.
Hi,
Thanks for both of you! I had to relate G/L data and trial balance by account number. After that I could sum the G/L accounts and match them with TB accounts.
Hi,
I have a little continuation question on the topic.
I was able to reconcile G/L with Trial Balance. However, there are a couple accounts in G/L that are not in TB and a couple accounts in TB that are not in G/L.
In my reconciliation table there are only accounts that are found in both G/L and TB. Is it possible to create two fields in reconciliation table: G/L account and TB account. If either is missing from the other table, values in that row would be show up as blanks. Something like this:
G/L Account | TB account | G/L account balance | TB account balance | Difference |
---|---|---|---|---|
1000 | 1000 | 100 | 100 | 0 |
2000 | 2000 | 200 | 200 | 0 |
3000 | - | 400 | - | 400 |
- | 4000 | - | 100 | -100 |
5000 | 5000 | 150 | 150 | 0 |
Here's a screenshot of my data model if it helps: