Trial Balance – Not Balancing

The following article explains the reasons as to why your Trial Balance may not have equal Credit and Debit amounts, along with what you can do to find then rectify this.

Preparation

Before continuing, you will need to have the following at hand:

1. Access to the Trial Balance for the KashFlow Bookkeeping Account
2. Access to the Audit Trail for the KashFlow Bookkeeping Account
3. The Nominal Codes, and Exact Names of all the Bank Accounts recorded in KashFlow. These include things like Loans, Tills, Petty Cash, PayPal etc.– anything that KashFlow considers a Bank Account.
4. The Nominal Codes for the Input, and Output VAT Control Accounts as used by KashFlow when allocating values on VATed goods. Required even if the account is not VAT registered.
5. The Nominal Codes for the Creditors and Debtors Control Accounts as used by KashFlow when allocating values of Invoices, Purchases and advanced payments.
6. On your computer/ device you will need to have either Microsoft Excel, or Apple Numbers.

You will also want to run the Trial Balance as to limit a Date Range the imbalance appears. This can be done using an augmented binary search. To elaborate:

1. Run the Trial Balance for the full length of the accounts. This does not need to be exact, so if your earliest Transaction is recorded for 25/05/2015, feel free to specify a Start Date of 01/01/2015 and an End Date of today
2. Calculate and record the difference between the Debit and Credit columns
3. Re-run the Trial Balance with a Start Date somwhere in between the current dates. So if the initial Start Date was 01/01/2015 and the End Date 31/07/2017, specify a new Start Date of 01/01/2016
4. If the Credit now equals the Debit, you know the cause of the imbalance is somewhere in between 01/01/2015 – 31/12/2015. If the Credit and Debit are imbalanced by the same amount, then you know the cause of the issue is in between 01/01/2016 – 31/07/2017. You can repeat the reduction steps as need be until you feel you have limited a small enough range to investigate
5. If the Credit does not equal the Debit, but not by the same amount as defined in point 2, then the causes of imabalance exist in both halves of the split period. In our example, this would mean there are issues in 01/01/2015 – 31/12/2015 and 01/01/2016 – 31/07/2017. In such situations, re-run the Trial Balance using the mid-date of the first period as the Start Date of the second period. So in our example this would make the new report run for 01/07/2015 – 31/07/2017
6. If the difference between the Credit and Debit now matches what was recorded from point 2, then you can assume you have specifed a period that includes all issues of the imbalance. From here, you can decrease the reported period in small increments, by three months or a single month at a time until you feel you have limited a small enough range to investigate

For records spanning 5 years, if you wanted to find the precise date of an imbalance issue using the above method would take no more than 11 runs of the Trial Balance.

Line Items Raised With Bank Account Nominal Codes

Cause
The most frequent cause of a Trial Balance having a significant difference between its total Credit and Debit values is the Line Items of an Invoice and/or Purchase being raised with a Bank Account Nominal Code. Due to the way KashFlow handles Bank Accounts, all monetary movements whether Money In or Money Out must be defined as clearly defined Transactions within the Bank Account– much in the way a Journal, Invoice/ Purchase Payment, or Transfer operates. Line Items do not create a Transaction and therefore the assigned amounts remain in a calculative limbo.

How
The capacity to assign a Line Item to a Bank Account Nominal Code is permitted once you choose to “Allow me to access all codes in all areas” from within the Chart of Accounts advanced configuration options menu. Please note that disabling this feature does not reverse any Nominal Codes that have been assigned outside of their areas, and will only work on prohibiting this action thereon.

Where
The most efficient way of discovering what Line Items may be raised with Bank Account Nominal Codes is through some quick and simple manipulation of the Audit Trail.

1. Navigate to:
   Reports > General Reports > Audit Trail
2. Therein, define the parameters of:
   – “Transactions dated between:”
   – A “Start Date” of 01/01/1980
   – An “End Date” of the Present Day *
   – Finally, ensure you only select the data types of “Include Invoices” and “Include Purchases”
3. With all the above parameters specified choose to “Run Report” then in the new screen click “Download CSV”.
   * Please note, that KashFlow may take sometime to produce the Audit Trail for the above period due to the amount of data it needs to compile. In such instances it may serve you well to run two Audit Trails, the first from 01/01/1980 up until half-way through your financial records of KashFlow, and a second report from half-way through your financial records of KashFlow up until the Present Day. You can break this down further to quarters, or smaller, if your data so needs it.
4. Once downloaded, open the file in either Microsoft Excel, or Apple Numbers and completely delete the first four rows so that Row 1 only contains the column headings (“Created/ Edited”, “TX number”, “Type” etc)
   If you are using Apple Numbers:
   – Click in the cell with “Created/ Edited” within it
   – On the right of the tool bar, click “Format” and therein choose “Table”
   – Specify a Header of “1”– by default this should turn the first row a dark grey, though it could be another colour
5. • If you are in Microsoft Excel:
     – Select all the data using the keyboard short-cut “Ctrl” + “A”
     – Under the “Data” tab, choose to “Sort”
     – You want to first sort by “Nominal Code”
     – Then below “Add Level” to then sort by “Reference”
     – When done, click “OK”
   • If you are using Apple Numbers:
     – Select the entire table by pressing “Cmd” + “A”
     – Of the tool bar click “Sort & Filter”
     – Specify “Sort Entire Table” from the drop-down list, then choose to “Add a Column” selecting “Nominal Codes”, then “Add a Column” specifying “Reference”
     – Finally, click “Sort Now”
6. Now, you can simply scroll to Nominal Code 1200, or wherever your Bank Accounts begin being recorded. If they are scattered amongst your Chart of Accounts you can do this using the Find function (“Ctrl” + “F” for Windows, or “Cmd” + “F” for MacOS) to search for any instances of your Bank Account Nominal Codes (ie, search for 1200, then 1500 etc).
7. For every Audit Trail Line assigned to a Bank Account Code, you will want to ensure that its associated “Reference” cell does not contain a reference to either an Invoice or Purchase. You can see this easily at a glance if the reference looks like “CUST01 – #1” or “SUPP01 – KF00001”.

Fix
To rectify the above, you would need to navigate to the respective Invoices or Purchases, choose to “Edit”, then amend the erroneous Line Item to use the appropriate Sales, Purchase, or Transaction Type Nominal Code

Self Assigned Bank Transfers

Cause
Although not as frequent as the latter, a Transfer assigned to the same Bank Account it is coming to/ from is a common cause of large discrepancies between the Debit and Credit columns of a Trial Balance. This occurs as a Transfer is a special type of Transaction that Credits or Debits a Bank Account Nominal Code directly, with a separate Transaction created to represent the monetary movement from the original Bank Account. When self-assigned, this causes an issue of the Transfers originating Transaction being omitted from the Nominal Ledger, and thus the calculation of the Trial Balance.

How
This usually happens when a Bank Account is deleted without the Transactions within it first being removed or re-allocated. When this occurs, the Bank Account within which you have chosen to “Set as default account” will inherit the Transactions, including any Transfers to itself. Another means of this happening is through direct assignment through the CSV Import Tool, the SOAP API, or another integration that communicates directly to the server.

Where
The most consistent method of discovering if this is the cause would be to look at the Nominal Ledger Report for each Bank Account, then search for any instances of it referencing itself.

1. To access the Nominal Ledger Report for a Bank Account first navigate to:
   Settings > Chart of Accounts
2. Therein, click on the two columns of lines, on the right of any Nominal Code other than a Bank Account
3. Then, from the Nominal Code drop-down list choose the first Bank Account you wish to interrogate
4. Specify the Date Range of 01/01/1980 up until the Present Date then choose to “View”
5. You can then either:
   • Download the CSV to reorganise the table by “Reference”, using similar guidance to the instruction above, and scroll down to where the Bank Account name would appear alphabetically. If it is not listed, then the Bank Account is not the issue in this instance
   • Or, you can simply use the inbuilt “Find” function of your browser to search any exact instances of the name for the currently interrogated Bank Account

Please note, in most cases you may be able to use the Audit Trail much like in the guidance above for searching if a Bank Account has been assigned to a Line Item. However, this is not the most consistent method as it may sometimes retain the “Reference” of the now Deleted Bank Account in spite of it actually being self-assigned.

Fix
To rectify this issue, you would need to navigate to the appropriate Transaction of the Bank Account and then simply change the associated Code to one other than the Bank Accounts own Nominal Code.

Transfers with VAT Amounts

Cause
Transfers are unique Transactions as they are those between two Bank Accounts inside of KashFlow. Such Transactions always provide matching Money Out, and Money In Transactions within the appropriate Bank Accounts, therefore any VAT Amounts are considered surplus and without opposing movement.

How
The most frequent cause of a Transfer being assigned a VAT Rate and Amount is through the use of the CSV Import Tool. As this tool uses our API it has the capacity to add values directly to the database, bypassing the standard user-interface and thus if the CSV you are importing declares that a Transfer has a VAT Rate and or Amount they will be recorded as such in the database. This is also possible through any other service that uses one of our APIs, including Bank Feeds.

Where
There are two methods of finding Transfers with VAT Amounts:

The Audit Trail

1. Download an Audit Trail much like you had when searching for Bank Nominal Codes Assigned to Line Items, however, only choose to “Include Bank Transactions” before running the report
2. As in the previous guidance, remove the unnecessary rows and reorganise the document by “Nominal Code” though instead of “Reference”, choose “Input VAT”, and add a further specification of “Output VAT”
3. Now, check for any rows that contain a Nominal Code for one of your Bank Accounts as well as a VAT Amount

The Nominal Ledger

1. As in the guidance for Self Assigned Bank Transfers, navigate to the Nominal Ledger Report for the Input VAT Nominal Code
2. Specify a Date Range that encompasses the length of your records (or smaller portions if unable to compile the amount of data)
3. Use your browsers search-on-page function to look for any References to your currently recorded Bank Accounts
4. Once completed do the same for the Output VAT Nominal Code

Fix
The nuance with this issue is that if you click on the Transfer within the “View/Add” screen of the Bank Accounts you will always be displayed with a VAT Rate of “N/A” and a a VAT Amount of “0”, so you would suspect no issue existing. The reason for this is because KashFlow knows Transfers should not ever be VAT Rated and to avoid such instances of this happening by mistake in-app disallows the user from editing these fields entirely. To fix the matter you simply want to update the database record of the Transaction with these null values, which can be done by clicking “Update” of the Transaction without making any other changes.

Input VAT and Output VAT Code Assignment

Cause
The Input and Output VAT Nominal Codes are special Codes, much like the Debtors, and Creditors Control Accounts. When they are used on Line Items, or Transactions with VAT Amounts it can set-up a situation of self-assignment of values thus leading to superfluous Debit and Credit values.

How
This often occurs through erroneous assignment after you permit the capacity to “Allow me to access all codes in all areas” from within the Chart of Accounts advanced configuration options menu. As stated previously, turning of this specification does not reverse anything already assigned to these codes and such items will need to be amended manually.

Where
The following is the most efficient way of finding instances of Transactions or Line Items raised against the Input VAT, and Output VAT Nominal Codes:

1. Download the Audit Trail, specifying only to “Include Invoices”, “Include Purchases” and “Include Bank Transactions”. More elaboration on this can be found in the earlier guidance for “Line Items Raised With Bank Account Nominal Codes”
2. Re-organise this data by “Nominal Code”, and “Input VAT”
3. Search for any items assigned to the Input VAT “Nominal Code”, that have an “Input VAT” value greater than 0
4. Now re-organise the data by “Nominal Code”, and “Reference”, looking for any “Reference” that refers to an Invoice (as defined by “#…” where the ellipsis is an Invoice Number)
5. Finally, re-organise the data by “Nominal Code”, and “Output VAT”
6. Look against the “Nominal Code” of Output VAT for any items that have an “Output VAT” value greater than 0

Fix
In instances of Money In Transactions/ Invoices raised against the Output VAT Nominal Code, or Money Out Transactions/ Purchases raised against the Input VAT Nominal Code, simply removing any VAT Amounts, or including said amounts in the Total, will rectify the balance. In the instances of Invoices with Line Items raised against the Input VAT Nominal Code, these will have to be changed to using a different Nominal Code entirely.

Missing Nominal Code

Cause
In rare instances a Transaction, Line Item, or Journal may reference the ID of a Nominal Code that no longer exists within the account.

How
When a Nominal Code is deleted KashFlow endeavours to automatically reallocate items to a default Nominal Code (usually Other 0 / 9998), or provide you with the means to specify which Nominal Code to use. In situations we have not yet been able to confirm as legitimate user cases, the automatic process can be usurped, leaving a stale ID on record for the Transaction, Line Item, or Journal. In line with this, it is advised that a user reallocates all the Transactions of a Nominal Code before they choose to delete that Nominal Code.

Where
This issue is usually the easiest to identify, though like the VATed Transfer, the results of the investigation can often cause confusion. The following is the most efficient way of locating said issues:

1. Download the Audit Trail, specifying only to “Include Invoices”, “Include Purchases”,”Include Bank Transactions”, and “Include Journal Entries”. More elaboration on this can be found in the earlier guidance for “Line Items Raised With Bank Account Nominal Codes”
2. Re-organise this data by “Nominal Code”
3. This will either shift all items with invalid Nominal Code IDs to the bottom, or top, as their respective “Nominal Code” field will be empty
4. Take note of all the TX Numbers for the invalid items

Fix
Further to the mention of confusion earlier, in the case of Invoices, Purchases, or Journal Entries, when viewing the items within the KashFlow application the Nominal Code field will be filled with a default value– though this is simply a placeholder and as per proof of the Audit Trail, is not an actual assignment. To rectify these matters however, simply (re)select the appropriate Nominal Code then save the changes as to update the records with a valid Nominal Code.

Bank Transactions are more confusing still, as even though they eventually exhibit the above behaviour, because they do not have a valid Nominal Code ID they will not be displayed in the “View / Add” screen of the Bank Account thus making it incredibly difficult to amend unless you have a direct URL to the Transaction. This is where the TX Numbers you recorded earlier come into play, add the number to the end of the following URLs as per your need:
New KashFlow: https://app.kashflow.com/report-audit-history.asp?tx=
Classic KashFlow: https://securedwebapp.com/report-audit-history.asp?tx=

For example, for New KashFlow, this may look like https://app.kashflow.com/report-audit-history.asp?tx=42. This will present you with an Audit Trail of changes for this specific item, as well as a button for you to “View Transaction”. Click the button to be taken to the Transaction, which can now be clicked and amended to use an approrpaite Nominal Code.

Penny Differences

Cause
Although beyond a Currency Conversion Rate, you are unable to natively work to more than two decimal places, KashFlow’s database records values up to four decimal places to ensure effective calculations when working out VAT and Currency Conversion. When running the Trial Balance, the Debtors and Creditors Control Account Nominal Codes take the full Invoice/ Purchase Total from four decimal places and round this to two. In contrast, the Sales, Purchase and Transaction Type Nominal Codes are calculated to four decimal places then rounded to two on a per Line Item basis.

How
The most frequent cause of this occurring is the Currency Conversion Rate with a close second being defining a Quantity with a Decimal. Although the former cannot be avoided, we advise that users address the latter by specifying a Quantity of “1” and re-entering the Line Item Total to the full amount; you can enter specific quantities in the Description field of the Line Item.

Where
This is the hardest difference to find as it is the smallest, and also requires the reference of multiple reports. To begin:

1. Make Note of the the Trial Balance period you have noticed the penny difference
2. Reduce the Trial Balance period by half (up to the nearest year), then re-run it. For instances, if initially running from 01/01/1980 – 31/12/2015, reduce it the period to 01/01/1988 – 31/12/2015
3. If the penny difference remains then you have isolated it to the right half, now simply half this period again. If the penny difference is not present anymore, then your current period is recorded correctly and you will need to interrogate the opposing period.
4. Once you have reduced the period to a single year, half the year period until you get to a month, then half this until you get to a day (On average at the most this will require you to run the Trial Balance 11 times before finding the exact day the difference occurs, though you will have to do this for every penny difference)
5. Once you have defined the period to be as small as you deem reasonably possible, you then want to run the Audit Trail for the period and make note of all the Purchases and Invoices
6. With the list of documents at hand take a quick glance at each respectively to see if they have either a Currency foreign to what you have defined as your Home Currency, or if they have a Quantity with a decimal place

Fix
The only way to amend these differences is to create either an Invoice, or Purchase that rectifies the imbalance. I will explain this using a Purchase as an example where there is greater Debit than Credit value in the Trial Balance, though the same principles can be applied to the rectification of the Debtors Control Account and Invoices:

1. Create a Supplier entitled “Currency Gain/Loss”, or “Rounding Discrepancies”, or anything of equal meaning to you
2. Create a Nominal Code to be used in rectifying these balances
3. Create an Purchase for this new Supplier on the day of the Purchase causing the imbalance and raise two Line Items against the newly created Nominal Code
4. Set each Nominal Code to a Quantity of 0.01 and a Rate of negative 0.45 (for example, -0.45)
5. This should have the Line Item totals recorded as 0.00, and the Purchase Total recorded as negative 0.01
6. You will need to do this for each penny discrepancy on the day
7. When done, create a Line Item against the Nominal Code you created, with a negative quantity to the number of penny differences for the day, and a Rate of 0.01. This should leave the Purchase with a Gross Total of 0.00, but effect the Trial Balance as to have the Credit match the Debit

Please note, we understand if you are unable to, or are having difficulty in attempting to find these imbalances within your accounts. If you think it would be a quicker process for you, please feel free to send in a ticket to the KashFlow Support Team requesting assistance in the matter. Though please note, depending on the size of your data it can take a significant time longer than standard queries to find the exact cause of your imbalances.