Flexible General Ledger Reporting for EXO
Bi4Cloud provides Profit and Loss and Balance Sheet reporting with flexibility in filtering and analysis dimensions.
Bi4Cloud utilises the Exo detail transaction tables and so detail drill down from P&L to transactions is possible.
Multiple Segmentation
Bi4Cloud segments the general ledger into 3 areas and permit analyse by each separately or in combination
- Account Groups
- Asset | Liability | Equity | Income | Expense
- Account Types
- Income | Cost of Sales | Expense | Other Income | Other Expense
- Account SubTypes (this is determined from the setup in Exo Account Groups - examples only)
-
- Current Assets | Fixed Assets
- Current Liabilities | Non Current Liabilities
- Equity
- Income
- Cost of Sales
- Administration Expenses | Finance Expenses | Despatch Expenses
- Travel and Accommodation Expenses | Motor Vehicle Expenses
- Employment Expenses | Property Expenses
- Other Income
- Other Expenses
The accounts can be shown in detail or summary levels (account and account level 1 and 2 ) as well as branch analysis.
Ultimate Profit and Loss
The Bi4Cloud Forecast Profit and Loss pre-calculates most of the commonly required metrics and these can be selectively displayed allowing you to create the Ultimate Month End Profit and Loss. Learn more P&L Forecast - Ultimate Month End
Financial Ratios and KPIs *beta*
The General Ledger data can be further processed to display Financial Ratios, KPIs and Alarms. Learn more Financial Ratios, KPIs and Metrics
Things you need to prepare
GL Groups and Account Segmentation
For the Bi4Cloud P&L and Balance Sheet to operate correctly and order and group accounts in correct order there needs to be a common grouping standard. EXO provides the ability to create this but it is not always implemented.
Firstly accounts need to be categorised as Balance Sheet or P&L.
The definition of whether the account is a balance sheet account and if it is Asset, Liability or Equity needs too be resolved as well as the Usual Credit / Usual Debits (green)
Each Account is a member of an GL Group. Each GL Group needs to be a member of a Report Code that is predefined in EXO. This relationship is shown in the screen images below.
You need to ensure each Account belongs to an Account Group (red) and that each Account Group belongs to a Report Code and that the report code should not be 0. Default (blue).
Example 1: Assume you define an account called Rebates. If you're a Distributor then Rebates may be an an Expense. If your are a Retailer then Rebates may be Income. You may want to see Rebates reported in Income or maybe in Other Income. By using the Report Codes you define if it's Income or Expense and where it should placed in reports.
Example 2: You may define Non Operating Income and Expense GL Group and you may then have both Expense and Income Accounts as members of this GL Group. Account can't be in two places at once and so you would need to define (green) that these are P&L Accounts and the Income accounts as Normal Type C-Credit and the Expense accounts as Type D-Debit
Budgets in Exo
Exo provides the ability to have several budgets. These budgets have a name and then period amounts against the various account but they do not specify a Financial Year to which they apply.
The GL reporting in Bi4Cloud expects a Budget with period entries under the same Budget name with definitions for every year and so there is an inconsistency between Bi4Cloud and Exo which we will work on.
If you are only concerned with the current financial year then use the name DEF_BUDG and this will be used for the current financial year.
If you wish to setup multiple years of budgets then name the budgets FYnnBUDG where NN is the last two digits of the financial year. eg FY21BUDG.
Detail GL Sub-Types
If you want ti make use of the Financial Rations and KPI features of Bi4Cloud you will need to have your accounts precisely categorised.
Many SME accounting systems pre-define the detail segmentation of the GL account and so when a GL account is created it must be have its usage full resolved.
For example MYOB AccountRight defines the detail SubCodes that GL accounts must fit in.
|
Nmemonic |
Account Usage |
Maps To Internal Code |
| AR | Accounts Receivable | Current Assets -Accounts Rec (ASSET-CUR-AR) |
| OCA | Other Current Asset | Current Asset - Other (ASSET-CUR-OTH) |
| OA | Other Asset | Current Asset - Other (ASSET-CUR-OTH) |
| FA | Fixed Asset | Non-Current Asset - Fixed Assets (ASSET-NC-FA) |
| CC | Credit Card | Current Liabilities - Other (LIAB-CUR-OTH) |
| AP | Accounts Payable | Current Liabilities - Accounts Payable (LIAB-CUR-AP) |
| OCL | Other Current Liability | Current Liabilities - Other (LIAB-CUR-OTH) |
| LTL | Long Term Liability | Non Current Liabilities- Other (LIAB-NC-OTH) |
| OL | Other Liability | Current Liabilities- Other (LIAB_CUR_OTH) |
| EQ | Equity | Equity - other - (EQ-OTH) |
| I | Income | Revenue (REV) |
| COS | Cost Of Sale | COS Fixed (COS-FIXED) |
| EXP | Expense | Expense Fixed (EXP-FIXED) |
| OI | Other Income | Abnormal Income (INC_OTH_ABNORMAL) |
| OE | Other Expense | Oth Expense - Oth (EXP-OTH-OTH) |
Using the definitions (in Blue) Bi4Cloud can determine what an account's purpose is and the Internal Code it maps to (Lemon). This is fundamental for KPI and Financial Ratio features of Bi4Cloud.
Within EXO however the GL Groups Names are free form and although this provides report flexibility it's not possible to decode from the numerous permutation of names what an account usage can actually be.
This requires intervention to align the account name with it's purpose.
This facility is provided in Bi4Cloud in the Cool Stuff > KPI > Edit KPI > Report Codes.
Learn more Financial Ratios, KPIs and Metrics
Known Issues
Movements vs Transactions
EXO GL reporting uses the Monthly Movement tables (glmovements) to determine GL amounts and posting dates.
It is a known issue that this movement table can get out of whack with the GL Transaction table and provide incorrect monthly values and accordingly incorrect reports .
Bi4Cloud, because it provides detail transaction drill down, uses the GL Transaction table (gltrans) and so the report output of EXO and Bi4Cloud can differ.
This is a known issue and MYOB have released a white-paper (attached) on this issue and your EXO support partner can run a program that will rectify this.
Period Dates
EXO period dates can be wrong.
Because Bi4Cloud uses the transaction table it needs to reference the posting period whose dates are defined in the Period Status table.
The symptom of this is the Bi4Cloud P&L will not match the EXOI P&L and / or the Bi4Cloud Balance Sheet will not balance because the period information in EXO can be wrong.
EXO stores period start and stop dates in a Period Status table. The EXO month end can inadvertently mangle these dates such that they do not run in sequence.
From the User Interface perspective will not be seen and the actual dates are not displayed.
and yet the period status values for the period are incorrect. July 2018 start dates should be "1/07/2018".
This is a known issue a your EXO support partner will be aware of this and will likely have a script to rectify this or may fix this with SQL. This will need to be fixed so that Bi4Cloud provides the correct period movements and balances.