Suspense Accounts - Cash Aging Report (1 Viewer)

chip0105

Registered User.
Local time
Today, 08:13
Joined
Sep 12, 2014
Messages
19
I have collected data for a designated timeframe (Beginning Date and Ending Date) for Multiple Suspense Accounts. For discussion sake, let's say the reporting period is 1 week, though it could be different at times.

Additionally, I have collected data for 6 months prior to the Beginning Date for use in Part 2 below.

I have two tables. The first table contains Opening and Closing Suspense Account Balances based on the Beginning and Ending Dates the user enters in fields on the Main Menu. The second table is a details table showing all individual Transactions with the date the Transaction is Effective. This detail table contains the 6 months of data prior to the reporting Start and End Dates.

Part 1:
I am trying to create an Aging Report for a given timeframe. If, on the Starting Date, the account balance was $0 and anytime during that timeframe, amounts were either added or subtracted, how long was that cash in that account...even if the cash was deposited and withdrawn on the same day a 1 day timeframe would be identified.

Part 2:
On the Starting Date, if the Opening Balance was NOT $0, how long has THAT amount been in the account. This could be a single amount that was deposited or a number of Transactions that were deposited totaling the current Opening Balance; hence the additional 6 months previous data to be used to search for the aging of an existing balance that was NOT $0.

Any help would be appreciated and if additional information is needed or explained better, please do let me know.

Thank you in advance for your time and assistance.

Chip
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:13
Joined
Feb 19, 2002
Messages
42,970
There is no way to look back prior to the starting balance record unless you have all the available detail.

Some aging applications "bucket" amounts. They keep 30, 60, 90, 180, over buckets. And at the start of a new month, they move the amounts to the next bucket down. This, in my opinion, is harder to work with because you STILL need all open records.

I would just have your query select all open records up to an end date. So if you want an "as of" Jul 31 and today is 8/21/19, you select all amounts that were incurred prior to 8/21 as long as they were not paid prior to the as of date. You then compare the billing date with the as of date to determine how old the amount is.
 

chip0105

Registered User.
Local time
Today, 08:13
Joined
Sep 12, 2014
Messages
19
Thank you for your time and response. The data I have to draw from is from a Transactions table and not a General Ledger table. There is no "outstanding" invoices that I can reference.

What I do have is an individual transaction that comes in with an "ROC" (Receipt of Cash) designation in the "TRUST_TRAN_SMCD" field when funds are deposited and a "DIS" (Disbursement) or "CPO" (Cash Paid Out) in the same field when funds are taken out.

Additionally, the only Date field I have to work with is the, "Effective Date" which is when the specific transaction will take effect.

I collected data to show what the Opening and Closing balances are for the specific Transaction Reporting Period.

In addition to the data collected for the Transaction Reporting Period, I collect data for a 6 month period prior to the Transaction Reporting Period.

My plan was to use this 6 months of data prior to the Transaction Reporting Period to look back for individual, or a summed group, of Transactions and try to match an Opening Balance that was NOT $0. I would then need to collect the individual "Effective Date" for each and Age those individual Transactions until they get paid out as a "DIS" or CPO".

I already have a query to identify Aging for Transactions that came in and went out on the same day as well as a query for Transactions that are Aged more than 1 day within the Transaction Reporting Period.

What I cannot get to work is a process that looks at my Transactions Table and is able to look at an Opening Balance (Not $0) and collect the previous individual Transactions or multiple Transactions (as far back as 6 months prior to the Reporting Period) that might total the current Opening Balance.

Example: If there was an Opening Balance of $500 for the Beginning Reporting Period of 26 July 2019, when did that $500 Balance come in? Was the $500 from 1 Transaction or a group of Transactions that totaled $500? If a single Transaction could not be identified within a week or two of the Beginning Reporting Period, was there a group of Transactions that would Total the $500 Opening Balance?

If I am asking too much from MS Access to perform this type of a "Look Back" process, please let me know and I will break the news to my User.

Again, thank you in advance for your time and assistance.


Chip
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:13
Joined
Feb 28, 2001
Messages
26,996
Access can do this easily enough. Note, however, that all you can ever do is report the transactions based on the "Effective Date" (since that is all you have.)

I think I want to ask a couple of questions about intent for a couple of scenarios.

First, suppose we have a complex of transactions over a period of a couple of months for account A but account B has only one transaction last week. You are trying to match balances against transactions. How do you want to report these two cases?

Second, suppose that account C has a complex series of transactions that bounce around from credit to debit status but NEVER in the given look-back period actually lead to a zero balance on any day. How do you want to report this case?

My question has to do with identifying the reporting goals in greater detail.
 

chip0105

Registered User.
Local time
Today, 08:13
Joined
Sep 12, 2014
Messages
19
THANK YOU VERY MUCH FOR YOUR PROMPT RESPONCE!!!

In response to your additional questions:

Q: First, suppose we have a complex of transactions over a period of a couple of months for account A but account B has only one transaction last week. You are trying to match balances against transactions. How do you want to report these two cases?

A: The individual Transactions are only linked individual accounts and would be matched against the individual Account Opening Balance.


Q: Second, suppose that account C has a complex series of transactions that bounce around from credit to debit status but NEVER in the given look-back period actually lead to a zero balance on any day. How do you want to report this case?

A: If there is an Opening Balance for an Account, I would just need to know when the individual or group of transactions that equal the Opening Balance for that Account were deposited in the Suspense Account.

Note: For each of these Reporting Periods, the User may select one or several Account Numbers that need to be reported on. If selecting multiple Account Numbers will create a problem programmatically, I can limit the look-up process to single Account Numbers only.

If additional questions or clarification is needed, please do let me know.


Thanks Again for your time and assistance!!

Chip
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:13
Joined
May 7, 2009
Messages
19,169
please post an excel file showing some bogus accounts (first table).
and also the transactions (2nd table).
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:13
Joined
Feb 28, 2001
Messages
26,996
I probably didn't ask my questions correctly. Can you "dummy up" a short example of what you have and what you want to see?

Here is a complication that I also see. You say you can only do something against the initial account balance and can only go back 6 months. What do you do for an active account with weekly transactions, one that has been in non-zero balance for more than 7 months? You would be unable to reconcile past 6 months by your own description. How do you want to handle that?

I should also point out that what I am asking is the same kind of question I would ask if my boss gave me the job to build such a thing. I would have to determine and in fact would have to formally lay out the rules, goals, and resources before I could hope to build such an application. So these are the kinds of questions that YOU will need to know - intimately - when trying to implement this proposed app.

Arnelgp is asking a similar question about examples because I believe he and I are in the same boat - having trouble visualizing your actual goals here. Please understand that it is beginning to sound like a complex problem. Access CAN handle complex problems. But we have to understand enough of the problem to show you the way.
 

chip0105

Registered User.
Local time
Today, 08:13
Joined
Sep 12, 2014
Messages
19
I have provided a marked-up Excel example for your review. I also provided some explanations above the columns to identify the data and how it is used. If more information is required for this, please do let me know.

With that said, I will try to provide responses to the concerns you pointed out.

Regarding the initial account balance and going back 6 months. I pull an account balance as of COB the previous day to get the opening balance for the start of the reporting period selected. I also collect the closing balance for the last date of the reporting period specified; the manager indicated we are not looking for any aging on the Closing Balance. As for going back 6 months, this was a number the Unit Manager provided as MOST transactions clear within 7 to 10 days; though on a very rare occasion, some may remain 3 or 4 months, depending on unique circumstances. However, the 6-month data collection timeframe can be modified as needed.


Also, when the database closes, the data tables are cleared to keep the database as small as possible. Users will select the Account Number(s) they wish to report on each time the database is used.

Accounts with $0 Opening Balance
I review those accounts with a $0 opening balance. For those accounts, I use the "DateDiff" function to determine when a transaction was first entered to add (ROC) or remove (DIS or CPO) money from the suspense account. I am then able to match account numbers and alternate Type Codes (alt of DIS or CPO would be ROC and vice versa). Any transactions found with no match remain open/uncleared.

Accounts with a Closing Balance >$0
If the account had a $0 Opening Balance and a Closing Balance >$0, I need to look to see how long any uncleared transactions have been in the account; but not for the Closing Balance itself.

Accounts with an Opening Balance >$0 or Closing Balance $0
This is where the backward lookup process needs to be defined. It should also be noted that for every transaction (in or out) there is a corresponding Sequence Number for that transaction. The Sequence Numbers are unique on a DAILY basis so there may be repeating Sequence Numbers, but they correspond to a different day for that transaction under the account. The search criteria should be as follows:

1. Account Number
2. Sequence Number
3. Transaction Type (ROC = Receipt of Cash, DIS = Disbursement, CPO = Cash Pay Out)
Note: There could be either a DIS or a CPO Transaction entered to remove money from an Account prior to a ROC Transaction putting money into the Account.

4. Effective Date
5. Amount
Note: The amount could be a like amount ($500 and -$500) or it could be the sum of multiple transactions totaling the $500 (-$100, -$100, -$100, -$100, -$100).
6. Reversal (Y/N)
Occasionally, a transaction needs to be reversed. When this occurs, it could be within the same reporting period or prior to the reporting period. If, during the reporting period, the amount is simply reversed; money in and money out; no aging would need to be performed on any Aging for a Reversal Transaction. However, if the reversal is for an earlier reporting period, the reversal would either add additional money into the account thus increasing the closing balance or taking money out and reducing the closing balance. The Reversal for a ROC transaction would be either a DIS or a CPO. A reversal for a DIS or CPO transaction would be a ROC.


Note regarding Aging
If Transactions come in and go out on the same date, instead of a 0 Day Aging, they would be marked as being Aged 1 Day. As previously mentioned, the Closing Balance does not need to be Aged; only those transactions that make up the Closing Balance. In my attached Excel file, I indicated what the results of the Aging would be for each record. However, I was not sure if I should Age the alternate record that either added money into the Account or removed money so I used the same Aging Date. i.e., On 25 July 2019 a ROC Transaction for $125 was entered under Sequence Number 3, Sequence Number 6, Sequence Number 8, Sequence Number 11 and Sequence Number 14. Then on 26 July 2019, there was a DIS Transaction for $125 was entered under Sequence Number 9, Sequence Number 12, Sequence Number 14, Sequence Number 17 and Sequence Number 18. They should be matched individually by the earliest Sequence Number against each ROC and DIS Transaction; FIFO (First In, First Out) so as a Transaction set is processed, it should be removed from the data set as additional searches are performed.

If additional information is required or you would like more clarity, please do let me know.

As always, thanks in advance for your time and assistance.


Chip
 

Attachments

  • Susp Acct Recon.xlsx
    49.1 KB · Views: 343

chip0105

Registered User.
Local time
Today, 08:13
Joined
Sep 12, 2014
Messages
19
My apologies. The manager requesting this reporting changed his mind regarding the Aging process. If anything is taken out of the Account during the Reporting Period, he does not want that set of Transactions Aged. Only Amounts remaining in the Account at the end of the Reporting Period (Closing Balance) should be Aged. If there is an Opening Balance, he does want to know the Aging for the Transactions that made up the Opening Balance; how long was that money totaling the Opening Balance in the Account before the Beginning of the Reporting Period.

Does this make sense and perhaps simplify the process?

Please let me know if more information is needed.


Thank You VERY Much!!

Chip
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:13
Joined
Feb 28, 2001
Messages
26,996
If anything is taken out of the Account during the Reporting Period, he does not want that set of Transactions Aged.
...
how long was that money totaling the Opening Balance in the Account before the Beginning of the Reporting Period.

That simplifies the question somewhat, but I still have the question: Does it matter that the balance at the beginning of the reporting period is never the exact result of prior transactions because of the longevity of the account? I.e. the account total is where it is because of stuff that happened before your 6-month reachback. I.e. at no time within your 6-month window of reachback does the account have zero balance.

And I'm not clear on what you do with this analysis. What does it matter that someone let money sit in an account 1 day or 5 days? Is there a running-balance interest computation involved here? The goal of this "aging" is not clear.

The good news is that you can easily write a query that will order your transactions precisely using a compound key of transaction date followed by daily sequence number. (I.e. ORDER BY TRANSDATE, DAYSEQ). If you need to look backwards in time, both of these can be reversed with the DESC keyword so that you can look at the records in reverse order. That means that a "running sum" can be created with an exact ordering so that you could detect the moment at which an account went to exactly zero - i.e. the point at which the prior transactions completely accounted for the balance at opening.

The less good news is that at the level of detail you are currently showing with that spreadsheet, you might be looking at some VBA code to do recordset operations in a loop because your "span" seems to have limited reach in a way I don't understand quite yet. I'm not sure yet whether a simple query can implement your logic.

To be honest, you threw TOO MUCH data in that spreadsheet thus making it harder to understand at first glance.

Another wrinkle is added by the boss's exclusion: You will probably have to do that exclusion as a separate step, which MIGHT mean adding a flag to the records in the table as a temporary marker to say "This account should not be analyzed for aging" (because it has a disbursement in the reporting period.) There are other ways to do that if you can't modify the table in question but that would be the simplest option.
 

chip0105

Registered User.
Local time
Today, 08:13
Joined
Sep 12, 2014
Messages
19
If VBA code would be easier to work through this process, then I am all for it. I am not locked down to creating this through the use of basic query structures by any means.

After speaking with the Manager, I modified the Excel sample spreadsheet to show how and what transactions should be Aged. Perhaps this will offer some insight?

If an Opening Balance is $0 and a Closing Balance is $0 the Manager does not need any Aging performed. Only on Amounts left making up any Closing Balance and to identify how long individual Transactions were in the Account making up the original Opening Balance at the Start of the Reporting Period.

The balance at the beginning of the reporting period may never be the exact result of a single prior transaction. It could be the result of the sum of multiple transactions as I outlined in the example Excel spreadsheet. The Manager indicated that in his 8 years with the company, he has never seen an account that did not clear within a 4 month period. This is why I chose to go back 6 months just to capture a little extra data as a reference.

The Manager just wants to list the Aging data in a Report; Account Number, Sequence Number, Transaction Type, the Amount and how long it has remained in the Account that make up the Opening Balance. For the Closing Balance that is not $0, the same information is needed showing how long a new Transaction has been in the Account.

The Aging process is for an Audit requirement the Manager reports on. The Suspense Accounts should always be $0 where possible. These accounts are not used for anything but a holding area and cannot accrue interest or dividends so they want to move the money in and out as quickly as possible.

I hope this sheds some light on the why's you asked. If not, please let me know.

Your time and effort to assist me with this is GREATLY appreciated!!


Thanks,

Chip
 

Attachments

  • Susp Acct Recon (Updated).xlsx
    51 KB · Views: 328

chip0105

Registered User.
Local time
Today, 08:13
Joined
Sep 12, 2014
Messages
19
Also, if a Transaction is part of the Opening Balance and is still open/unresolved and ends up as part of the Closing Balance, the Aging should reflect the number of days that specific Transaction has Aged.
 

chip0105

Registered User.
Local time
Today, 08:13
Joined
Sep 12, 2014
Messages
19
If there are multiple Transactions making up the sum of the Opening Balance, each individual Transaction would need to be Aged.
 

Users who are viewing this thread

Top Bottom