Identifying a discrepancy
When edits to earlier balances affect the running revenue balance, a discrepancy may occur between the running revenue balance and the tabulated totals report.
For example:
A transaction is assigned to Rent in with a 12% management fee on the 10th.
On the 11th, the fees are transferred.
On the 12th, the transaction is changed to Deposit in.
This will remove the previous 12% fee from the revenue report.
To check the fees available for transfer:
Go to Admin > Exports > Revenue (Letting agents only),
Tick the Include running balance box.
Set the required date range (typically ending on the previous working day).
Once generated, the revenue figure at the top of column L in the export is what is due for transfer out.
To check the tabulated totals for fees earned over a certain time frame:
Go to Admin > Exports > Revenue (Letting agents only),
Tick to include the tabulated totals.
Set the required date range (typically ending on the previous working day).
Once generated, the report will show a breakdown of total fee earnings.
Resolving a discrepancy
The most common cause is that bank lines are not locked during the month-end reconciliation process before reporting revenue, so they remain editable. This would apply in the above example.
Even when bank lines have been locked, historical fee balances can still be affected by certain actions. The following actions can change fees:
Adding backdated double entries, which affect fee subcategories.
Importing bank lines retrospectively for a previous period if that import was previously missed.
Leaving bank lines pertaining to fees unassigned and assigning these only after completing reporting.
Using a VLOOKUP can help in discovering where the difference lies between periods.
Go to Admin > Exports > Revenue (letting agents only) and set the date to cover the period in which the discrepancy occurred.
Once this export is downloaded, it can be cross-referenced with the revenue export generated at the time of the original reporting.
Open both exports in Microsoft Excel.
Bring up the latest report (Report 1).
Create a new worksheet within Report 1.
Copy Column A (bank line IDs) from Report 1 and paste it into Column A of the new worksheet.
Bring up the previous revenue report (Report 2).
Copy Column A (bank line IDs) and paste it into Column B of the new worksheet in Report 1.
At this stage, Report 1 should have a new tab containing the bank line ID numbers from both it and the previous report, in Columns A and B.
From that tab:
In cell C2, enter =VLOOKUP(A2,B:B,1,0). Press the ‘Enter’ key.
Double-click on the bottom right corner of cell C2.
Cells in the C column with a number indicate a match between the two spreadsheets. Cells showing #N/A mean that the line in Column A is not present in the previously generated report, and so must have been applied subsequently.
This ID can be searched for on SME Professional to identify the relevant transaction.
After, Columns A and B can be swapped (A applied to B, and B applied to A). This will let the user identify whether anything exists in the previous report, but not in the current one.