There are three foundational concepts you must understand in order to use account schedules to their full potential.
1. Row setup
The row setup is the area of the report where you’ll show either single general ledger accounts or groups of general ledger accounts. If you imagine a trial balance, the rows of a trial balance correspond to single general ledger accounts. The row setup of an income statement would be an example of where you might use groups of general ledger accounts.
2. Column layout
The column layout is where you will define periods of time to be used in your reports. Most financial reports are showing us some period of time compared to the data (in this case, general ledger accounts) we are reporting against. Common column layouts may be current month versus year to date, or a twelve month net change (income statement accounts) or twelve month balance (balance sheet accounts). You can apply any column layout you design against any of the row setups you design. As a result, most Dynamics 365 Business Central account schedule users end up with many more column layouts designed than row setups.
3. Analysis views
Analysis views are the element of account schedules that lend flexibility and power to your account schedules by accessing your dimensions. Account schedules can always use the two global dimensions you have set up even if you aren’t using analysis views. Once you start using analysis views, you can get to the shortcut dimensions you have defined. When using analysis views, you can access many combinations of any of your dimensions, but you are limited to only four at a time. By setting up multiple analysis views in different groupings, you can set up almost any combination you can think of to produce your financial reports using any of your defined dimensions.
There are all kinds of ways to expand on these three foundational concepts, but let’s start with these simple concepts first before we talk more about additional things you can do with row setups, column layouts, and analysis views.
In addition, you need to understand two other concepts in order to use account schedules in the most efficient way possible. Mastering these concepts will allow you to build and utilize account schedules quickly and easily.
4. Printing or exporting to Microsoft Excel
You have the option of either printing account schedules directly out of Dynamics 365 Business Central or exporting them to Microsoft Excel. I recommend learning how to use the export to Excel update option so you can easily and quickly export your account schedules every month by using a pre-formatted Excel “shell”. The shell only needs to be created the very first time. Then, each time you update your reports, you only need to update the numbers – there is no need to update the formatting each time.
5. Formulas
There are two types of formulas used in account schedules. The first type are basic mathematical formulas, generally based on row number references, that allow you to do simple calculations against your data. These are used in both the row setup and the column layout.
The second type are formulas which are only used in the column layout and are based on comparison period or comparison date fields. Most users find getting used to the conventions of these fields difficult as they are not intuitive at first. I’ve got three suggestions to help with that: 1) use the Dynamics 365 Business Central help menu to look at “comparison period formula” and “comparison date formula”; 2) get in there and play around – you’re not going to hurt anything, and if you know what numbers you should have, you’ll figure it out after some experimenting; and 3) use the account schedules formulas page from this blog. I developed the account schedules formula page because the number one request I have gotten in all my account schedules classes is for a cheat sheet of account schedule formulas.
This posting is part of the Dynamics 365 Business Central Account Schedules 101 series. Find the entire list of lessons here.