The Chart of Accounts could now be considered your biggest financial reporting tool in Business Central. Thanks to Microsoft’s development of filtering and Excel functionality, generating your financials has become a whole lot quicker and extremely accessible. Add a Date filter, Account Type filter and then Open in Excel. Seriously, that’s it.
Let’s look at why it makes sense. Your Chart of Accounts is the core of your financial functionality. All your financial information runs through here and it provides you with account by account reporting. With the awesome ability to ‘stack’ filters in Business Central, you can simply turn this page into a financial report.
The main convenience of using the Chart of Accounts with filters is that there is no setup involved. You simply go to the Chart of Accounts page, apply your filters and it gives you the accounts and updated data that you require. All you need to do is add a couple of headings in Excel and totaling lines (e.g. Gross profit if exporting Income Statement lines) and hey presto – you have your report.
There is no maintenance required, unlike Account Schedules where you must manually add the line every time you make a new GL account. Not to mention that this must be done for every GL in every company you have on your database. When using the Chart of Accounts, it is super easy to update your filter ranges to get a whole different set of data for different time periods. Check out some examples below!
Simply ‘Filter totals by’ using a ‘Date filter’ for the financial period you want the report for. For example, 12/31/19 for a standard 2019 January to December financial year.
Now we want to ‘Filter list by’ and select the ‘Income/Balance’ option. Then select Balance Sheet.
The ‘Balance’ column now displays our financial data for our 2019 Balance Sheet.
With two quick filters we have limited our totals to transaction within our financial period and have only Balance Sheet accounts on display. With the ‘Open in Excel’ option, this data is now exported for our desired use.
For our Income Statement we will follow a similar process.
‘Filter totals by’ then use ‘Date filter’. However, this time we are going to set a range. For example, 01/01/19..12/31/19 for a 2019 January to December financial year.
Now we want to ‘Filter list by’ and select the ‘Income/Balance’ option. Then select Income Statement.
This time we are looking at the ‘Net Change’ column for our Income Statement financial data. Again, we can simply ‘Open in Excel’ and use this data as needed.
Cool tips and tricks!
Have a bunch of accounts that didn’t get used this year for you Income Statement that you don’t want to show? Add an extra filter to your stack to remove everything that has a $0 Net Change!
Why just look at the financial data for a year? Here’s how you could use some cool date formulas for on the fly reporting:
Leaving the top filters the same, we can change the ‘Date filter’ to -CY..T to give us year-to-date data.
This formula would come out like this:
Need a month-to-date Income Statement? Easy!
How about a quarter-to-date?
Okay, I think you get the idea now.
NAV’s infamous ‘Account Schedules’ are still here in Business Central deriving reporting from your ‘Account Subcategory’ setup and catering for a wide range of FP&A reporting.
Try making use of the of these awesome new features as they’re only going to get better. Our inside sources tell us the ability to save filters is coming soon! We do still recommend using Jet Reports and Power BI for official reporting at a detailed level. However, the filtering ability of the Chart of Accounts now gives a decent level of quick access reporting to use for most day-to-day and month-to-month reporting and contemporary reporting needs.
Stay tuned for more insight into the benefits of updated features as well as tips on how to get the most out of Business Central. For a bunch more information on date filtering for reporting check out this link here.