Balance Sheet data can be presented in a variety of ways. Changing the style of presentation can help users to better understand how a business is tracking, be it compared to prior periods or seeing monthly trends.
BI4Cloud Balance sheet reports are very flexible, start by taking a look at the following Default Favourite reports. Use these as a guide to start building and saving your own bespoke Balance Sheet reports.
Report Types
- GL Balance Sheet - Analyse by Account (Analyze type report)
- GL Balance Sheet - by Account (Compare Dates type report)
- GL Balance Sheet - Monthly Movements (Pivot type report)
- Balance Sheet - More Advanced formatting tips
GL Balance Sheet - Analyse by Account (Analyze type report)
The GL Balance Sheet - Analyse by Account favourite is a simple Analyze type or list style report. Analyze type reports are great to see your data in more detail and to use to drill-down on numbers. Start here and explore what happens when you change rows, columns, dates and subtotals or click on a row to drill-down.
- Analyze by - controls the Rows you see (change to summary account level or divisions)
- Then by - controls the Drill-down sequence (drill-down to months or to journal transactions)
- +/- Cols - show or hide columns (use to see columns, net movements or Dr and Cr columns)
- Trans Date - controls the Date range selected (use for Opg, Movement and Closing dates)
- Group by - controls how data is Grouped and Sub-totaled (setup multiple subtotals and styles)
GL Balance Sheet - by Account (Compare Dates type report)
The Compare type report allows you to compare up to 4 pairs of custom balance dates. The Compare type report is ideal to create comparison Balance Sheet reports as at any chosen dates.
Use the Compare type report to compare balance sheets at multiple month ends.
GL Balance Sheet - Monthly Movements (Pivot type report)
The Pivot type report allows you to report over two dimensions, rows and columns.
Pivot by Months
The Pivot report is ideal to see trends over any period of time, this example shows balance sheet:
- Opening balance
- Monthly account Movements as columns across the page (to see month end balances across the columns use the Compare Type report above)
- Closing balance
Using the Pivot type report change the columns to other time periods e.g. weeks, quarters or years.
Pivot by Company
On consolidated data:
- pivot by company to see companies as columns
- change the column dimension from Months to Company when running over Group data; and
- choose to Display Closing Balance
To learn more about using Pivot type reports including adding and hiding fields, using Horizontal and Vertical Pivots formats see Pivot - To see Multiple Dimensions at once.
Balance Sheet - More Advanced formatting tips
Use Sub-totals to Group data
If reporting by Account suggest you also set up Sub-totalling by:
- Account Sub-Type e.g. Bank, Accounts Receivable, then by
- GL Account Levels e.g. using the Account levels setup in your accounting system
- Account-Type e.g. Asset, Liabilities
- Flexi Levels - Create your own account levels see Flexi Levels - Reorganise Sort and Group your Accounts
- Click here to learn how to create multiple Subtotals
Use Analyse by to see a summary Balance Sheet
To see a more summary Balance Sheet simply
- Change Row dimensions using the Analyse by field e.g.
- Account-Sub Type e.g. Bank, Accounts Receivable, then by
- GL Account Levels e.g. using the Account levels setup in your accounting system
- Remove or change the Group by first if already Sub-totaling by this field
Hide Zero Rows
To hide all Rows with zero $ click on the Properties icon and tick the Hide Zero Rows box.
Balance Check
Our standard Balance Sheet reports show a Grand Total which should add to $0. This is designed to be a Balance Check row at the bottom of the report. To hide this row, click on the Properties icon and untick the Show Grand Total box.
Filter the Balance Sheet to focus on only some accounts.
I like to always make sure I have a positive "Net Liquid Assets". That is, what I have as Cash at Bank plus what will soon be cash (my Accounts Receivable) less what I have to pay shortly (my Accounts Payable and maybe certain GST and Tax obligations).
- Run one of the Balance Sheet Favourite Reports
- Click on the Filter (Blue Funnel) - Click Here to learn about Filters
- Select which Account Sub-Types or GL Account Levels to report on
- Suggest you select account groups, then if later you add in new Bank accounts etc they will automatically be picked up in this report.
- If you filter for only specific accounts now, then when you add new accounts later you will need to remember to go back and change this report manually to pick up the new accounts.
- Click Done
The Report will look like this. Note the Net Liquid Assets are Negative so this business does not have enough cash plus cash coming in shortly to cover all Accounts Payable.
Save your Changes as a New Favourite
To keep your hard work Save it as a new Favourite report to run over and over again.
