Run Divisions or Cost Centres - Using GL Tags with Monthly Budgets

If your transaction data is already coded for divisions then you have a great start but if not you can use Bi4Cloud GL Tags to categorise your GL accounts to achieve Divisional reporting

A well known technique to provide Division and Department reporting is to use GL Analysis Codes key against GL transactions. These analysis codes are called differently in each accounting product:

• Cost centres or Jobs (in MYOB) see  - Accounting for Divisions or Cost Centres Using MYOB
• Tracking Codes (in Xero) see  -  Xero : Tracking Code Report and Budgets
• Class / Locations (in QuickBooks Online)  - see     QuickBooks Online: Location and Class Reporting
• Sub-Accounts in Acumatica

This requires you to have allocated every transaction or line of a transaction to a Division / Department, while this is an effective way to account for Divisions it does require a lot of data entry.

BI4Cloud  provides an alternative method that we call GL Custom Lists where you Tag each GL account to belong to a Department or Division.

With tagging BI4Cloud can easily report on monthly Actuals v Budgets by Division. This is a great option for a small number of Divisions or where you already have separate GL accounts setup for each Division. 

This article covers:

• Setup to Use GL Custom List Labels / GL Tags
• Create Divisional/Department P&L
• Examples of other report options 

Setup to Use GL Custom List Labels / GL Tags

Using BI4cloud you can easily allocate or tag each GL account to different Departments or Divisions.

Login to BI4Cloud as the main BI Account Admin User so that you see the Admin screen or if already logged in go to the Company page.

1. Click on the Cool Stuff button
(Available in the Enterprise 5 user and above version of BI4Cloud. See Below for Initial Setup of Cool Stuff.)

2. Click on GL Custom Lists

 

3. Select the Company (if you have multiple Companies linked up)

4. Give the GL custom List a name e.g. Department, Division, Branch, State

5. Allocate each P&L account to a Department, Division, State, etc.
Select the GL accounts you wish to allocate to a Division using the Search/Filter field.

For example enter "4-" to select all GL account starting with "4-".

Then assign Departments.

6. Assign Values to each GL Account then Click Exit & Save. Highlighted options allow you make new, edit and reset values.

Create Divisional/Department P&L

Once you have your GL accounts Tagged by Department you are free to create a variety of different report formats using the new Department dimension.

Run Accounts Profit &  Loss [base] selecting tab @GLAccountList1

A Classic P&L with Departments summarised in rows is shown.

To see instead how much each Department/Division contributes to 
Income | Gross Margin | Expenses | Net Profit 

Under Filters change the P&L Format to Divisional. 
Learn more about using the different P&L Formats (Classic, Simple, Divisional, EBITDA) 
- Accounts Profit & Loss - Advanced P&L Formating

Examples of other report options 

Once you have your GL accounts Tagged by Department you are free to create a variety of different report formats using the new Department dimension. Following are just some examples:

• Divisions as Columns

• Divisions as Columns - Actual v Budget

• Monthly Pivot - Actual v Budget P&L Filtered by One Department

• Classic P&L - MTD v YTD and Actual v Budget Filtered by One Department

• Monthly Pivot - Divisional Summary P&L

• Visualisations can also work well - Track Monthly Income by Division 

Divisions as Columns

• Run the P&L by Mth (Pivot type report)
• Click the Pivot On Field
• Select Department to replace by Month in columns

Divisions as Columns - Actual v Budget

• Click the Eyeball to change to Edit mode
• Click on Settings then Pivot tab
• Add Budget field to the Metrics box
• Hover over Budget field to change format, e.g. Numeric displays with no $ and no decimals
• Add Department to the Columns box
• Leave or add Account Number then Account Name to the Rows box
• Ensure these are still indented under the P&L Format Totals field
• Run the report, if the Actual & Budget fields are displaying on Rows (not as Columns)
  • Click the Properties tab to open the Report Settings
  • Set Show Metrics in Rows to Off

Monthly Pivot - Actual v Budget P&L Filtered by One Department

• Run the P&L by Mth (Pivot type report)
• Add Filter for the Department field
• Select the Department to filter on (eg Equipment)
• Add Budget field to the Metrics box (see above)
• Run the report, if the Actual & Budget fields are displaying on Rows (not as Columns)
  • Click the Properties tab to open the Report Settings
  • Set Show Metrics in Rows to Off

Classic P&L - MTD v YTD and Actual v Budget Filtered by One Department

• Run the Classic P&L MTD v YTD 
• Add Filter for the Department field
• Select the Department to filter on (eg Equipment)

Monthly Pivot - Divisional Summary P&L

Visualisations can also work well - Track Monthly Income by Division 

Initial Setup 

This Feature is available for users of BI4Cloud on Enterprise 5 (Cool Stuff) or above. This allows you to allocate each P&L account to up to 3 different and unique Custom Lists in BI and store this outside of your MYOB, XERO or QuickBooks Online file. 

www.BI4Cloud.com