A well known technique to provide Division and Department reporting is to use GL Analysis Codes.
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
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 P&L by MTH
A pivot report with Accounts in rows and Months in columns Is shown.
Create a Copy
Select the edit pencil
To see the Departments as rows, open the Account table on the left and drag the Department field into the Rows box and replace the AccountName (hover to the right of AccountName to delete it). Ensure the indent under P&L Format Totals remains.
Press Save
The GL tag values (Department) replace accounts in the P&L Rows.
Remember to press Save in the Blue menu to save the changes to your workbook.
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
- Monthly Pivot - Divisional Summary P&L
- Visualisations can also work well - Track Monthly Income by Division
Divisions as Columns
- Click the Pencil icon to Edit the report
- 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
Divisions as Columns - Actual v Budget
- Click the Pencil icon to Edit the report
- Find Budget field in the Metrics Table on the left
- Drag 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 Hamburger icon (3 lines next to the Printer icon) to open the Report Settings
- Set Show Metrics in Rows to Off
Monthly Pivot - Actual v Budget P&L Filtered by One Department
- Add Filter (blue funnel icon) and select Department
- Click the Pencil icon to Edit the report
- Delete Department field from Columns box
- Find Transaction Date field under the Transactions table on the left
- Drag Transaction Date field to the Columns box
- Change Transaction Date by Year to Transaction Date by Month
- 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 Hamburger icon (3 lines next to the Printer icon) to open the Report Settings
- Set Show Metrics in Rows to Off
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.
