Cash Source Reporting

Overview

The key to ‘thinking’ about this subject is to remember that the original transaction in G/L is Linked TO a Cash Account.  The logic here, is that the original transaction is part of General Ledger whether it has been paid by a bill payment check or deposited into a bank account.  It has "first dibs".

So, we are operating in reverse.

The transaction is entered into General Ledger e.g. as an expense via a bill, or income via an invoice.  At some point (you would hope anyway), the bill gets paid from a particular bank account, and the invoice gets a payment received against it, and the payment in turn gets deposited to a bank account.  

There are exceptions to those rules depending upon how people setup and use their QB, but the premise is the same.  Base transaction – eventually gets linked to a bank account.

There are three pieces to this analysis:

1. Original Transaction
2. Linked Transaction
3. Cash Source Transaction

Original Transaction:

This is the base G/L item whether linked or not.  e.g. expense from bill, income from invoice:

Linked Transaction:

This is the next step from the base transaction e.g. bill payment from bill, receive payment from invoice

Cash Source Transaction:

The Cash Source Transaction is the final ‘resting place’ for any series of linked transactions, and can have the following values:

1. NULL (no linked transactions)
2. Cash Account (final resting place)
3. Undeposited Funds (this account will show ONLY if there has been a receive payment or payment with invoice, and no deposit has occurred)
4. Credit Card Account

Tying it all together:

Example (1)

• Original Transaction:   Expense to Account 6000
• Linked Transaction:  Bill
• Cash Source Transaction (IF paid):  Bill Payment
• Cash Source Account: Account 1000

Example (2)

• Original Transaction:   Income to Account 4000
• Linked Transaction (IF paid):  Receive Payment
• Cash Source Transaction:  Deposit
• Cash Source Account: Account 1000

Example (3)

• Original Transaction:   Expense to Account 6500
• Linked Transaction: Check
• Cash Source Transaction:  Check (it is the final ‘Resting’ transaction)
• Cash Source Account: Account 1000

Pivot Table Considerations:

There is ONE singular problem with databases and linked transactions:  you can have MULTIPLE links for one transaction, e.g. one invoice may have several payments applied to it.

The result is that the original GL item is REPEATED AS MANY TIMES AS THERE ARE LINKED TRANSACTIONS.

You could average the figures in your amount column to have the correct individual amount, but you won’t be able to summarize that data amount using the natural pivot table natural functions.

In this example, we see three payments (Ref# 5096,5127,5139) for Invoice 1008.  This results in the same information being repeated. 

The actual amount of the invoice is $5,516.05 but in this Pivot Table Example it would look like this:

Solving the Issue:

What we have done in QQube, is create a ‘divisor’, so that your amount totals will ALWAYS act to give you the correct summary result.

It is actually the combination of two fields:  Txn Detail Count, and Txn Linked Count.

Here is what you do:  Create a Calculate Field.

And fill it in with the following formula:

='Line Amount'  * ('Txn Detail Count' /'Txn Linked Count' )

That will give you the following Field, which now sums up correctly, as the payment amounts are split evenly.

NOTE:  THIS FIELD IS ONLY USED TO CALCULATE THE GRAND TOTAL FOR THE SOURCE CHECKING ACCOUNT.  IT DOES NOT ACCURATELY REFLECT THE AMOUNTS FOR ACTUAL ITEMS, ETC.  THIS IS STRICTLY A CALCULATED FIELD FOR A SPECIFIC PURPOSE.