Bi4Cloud and EXO General Ledger Reports

 Flexible General Ledger Reporting for EXO

Bi4Cloud provides Profit and Loss and Balance Sheet reporting with flexibility in filtering and analysis dimensions.

  1. Sample Reports and Financial Ratios
  2. Things you need to prepare
  3. Known Issues

Bi4Cloud utilises the Exo detail transaction tables and so detail drill down from P&L to transactions is possible.

Screen_Shot_2019-03-08_at_10.17.28_am.png

Multiple Segmentation

Bi4Cloud segments the general ledger into 3 areas and permit analyse by each separately or in combination

  1. Account Groups
    •  Asset | Liability | Equity | Income | Expense
  2. Account Types
    • Income | Cost of Sales | Expense | Other Income | Other Expense
  3. 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.

Screen_Shot_2019-03-08_at_10.46.16_am.png

 

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

Screen_Shot_2019-03-08_at_10.57.10_am.png

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

Screen_Shot_2019-03-20_at_8.30.14_pm.png

Screen_Shot_2019-03-20_at_8.33.22_pm.png

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).

 

 

Screen_Shot_2019-03-20_at_8.12.55_pm.png

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.

In the meantime the work around for the Exo budget is to use the name DEF_BUDG and this will be used for the current financial year

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.

Screen_Shot_2019-03-08_at_11.47.48_am.png

and yet the period status values for the period are incorrect. July 2018 start dates should be "1/07/2018".

Screen_Shot_2019-03-08_at_11.50.25_am.png

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.

 

 

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Please sign in to leave a comment.

Powered by Zendesk