Tenant Account Reconciliation
From time to time it might be necessary to do a reconciliation of the Tenant Account to check the integrity of the account. This will mostly be the case when you have deleted transactions or when you might have made allocation errors.
How to know when the integrity of a Tenant Account has been compromised
rpmsOnline provides an easy mechanism to spot Tenant Account errors. The Outstanding Balance on the Tenant Account must always be equal to the sum of all the Tenant Category Balances. If that is not the case, there is an error on the account.
How to read and interpret a Tenant Account
Please refer to the example for Glenwood 10 below:
Note in the example the Mandate rule for municipal services are set to Bill Tenant and Pay Service Provider.
On the Tenant Account above we can see the following:
- a category Admin item was billed for R 114
- a category Rent item was billed for R 5000 resulting in a balance of R 5114
- the Tenant then paid R 5114 (category Tenant Payment) to clear the balance
- a category Mun Account item was billed for R 350
- a category Rent item was billed for R 4000 resulting in a balance on the account of R 4350
- the Tenant then made a short payment of R 3000 (category Tenant Payment) resulting in a balance of R 1350.
To check the integrity of the Account:
Refer to the Tenant Category Balances shown below:
From the Category Balances panel we can see:
- that the Outstanding Tenant balance of R 1350 consists of
- unpaid Rent (category Rent) of R 1000 and
- unpaid Mun Services (category Mun Account) of R 350
Note: Since in the example above the Outstanding Tenant Balance is equal to the sum of all the Tenant Category balances, we know that the Account is correct.
How to find a Problem on the Account
If the Outstanding Tenant Balance is not equal to the sum of the category balances, you need to inspect the account to find which category balance is not aligned with the transactions on the account. The following steps are recommended:
- Check the Account recalc alert flag. If it is ticked, it means that a transaction has been deleted and that the account might require a recalc. Recalculate the account.
- If you deleted a Tenant Payment transaction, the account will still be incorrect even after the recalc. It is always a good idea to review the audit trail for the property to see which transactions were deleted.
- If the Account is still incorrect, you need to check each category individually to find the Category Balance that is not aligned with the Transactions of the same Category.
- When you have identified the discrepancy, you will have to do journals on the account (Debit Notes to increase the Amount due for a Category and Credit Notes to decrease the amount due for a Category). Note that if you create a Credit Note with Category Tenant Payment, the Tenant Balance will be reduced, but such a transaction will not have any affect on any Category balance.
How to check and verify a Category Balance
- Select the View All feature on the Tenant Account to open the full Tenant Account in a separate tab.
- Select the Payment History query to open the full payment history in another tab.
- Then for each Category and using the two queries above:
- Filter on the Category to view all transactions for that Category (Billing, Debit Notes and Credit Notes) - see example below.
- Filter on the Payment Category to view all Payment Splits for the Payment Category - see example below.
- The do the calculation to verify the Category Balance
Tenant Transaction (View All) example
In the example above you can see if we filter on the Rent category, the total Debits is R 9000 with zero credits.
Payment Split History example (sorted ascending on Category)
If you download the above query to a CSV file, it is easy to sum the Payment Categories in Excel to find:
- total Payment Splits for the Tenant Admin category - R 114
- total Payment Splits for the Mun Account category - R 0
- total Payment Splits for the Rent category - R 8000 (all splits where the From Rent flag is set to YES)
With reference to the Rent example above, we can now verify the Rent Category balance as follows:
Category Balance = SUM of Debits on Tenant Account for the Category - SUM of Credits on Tenant Account for the Category - SUM of Payment Splits for the Category.
Rent Category Balance = R 9000 - R 0 - R 8000 = R 1000 which is correct.
NOTE: For the Rent Category, the SUM of Payment Splits for the Rent Category would be the SUM of all Payment Splits where the From Rent flag has been set to YES, in other words the Payment Split was regarded as a split from Rent or Part of Rent.