Would you like to separate your debit and credit balances while using the Excel Design Pane? If so, then the Balance Type function is for you. An example is if you want to create a Trial Balance with debit balances reflected in a debit column and credit balances reflected in a credit column.
Take note that this functionality applies to Sage Intelligence for Accounting for the following regions: Africa, Middle East and Australasia.
The Balance Type function works with account level detail and is accessed via the Balance Type parameter in Actual, Actual YTD and Closing Balance formulas.
In the below example we have used the Actual formula (movement for the period) to return account level detail in both debit and credit columns. If the account is a credit, zero is returned under the debit column, and if a debit, zero is returned under the credit column.
All you need to do is reference the word ‘Debit’ or ‘Credit’ from your layout in the Balance Type parameter in the formula builder for the formulas in their respective columns. In the below example, the cell is also absolute referenced, with the row being locked (D$11). If the formula is dragged across to the next column, the correct keyword would be referred to.
Something to remember, as you can see in the below image, instances may exist where you have both a debit and credit balance for an account. This is due to the account having been a debit during one period and a credit during another. The example also makes use of the Actual YTD and Closing Balance formulas.
By drilling down on the closing balance amount (in column J) which has no balance type applied to the formula, you can see the breakdown of debits and credits for the different periods. Period 4 has the debit balance shown in the layout.
To view more information on this, check out the following webcast: