NAV AS 101 Lesson 4: Basic Elements of Account Schedules

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 NAV 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 three other concepts in order to use account schedules in the most efficient way possible.  Mastering these three 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 NAV 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 NAV 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.

6. Manipulating the matrix

The largest change between Classic NAV versions (NAV2009 Classic, 5.0, or earlier versions) and Role Tailored Client NAV versions (NAV2009 RTC, NAV 2013, and future versions) is the development of the matrix. The new matrix allows for a whole lot of functionality that we just didn’t have before which makes account schedules a little more Excel-like for many users. If you used one of the Classic versions previously, you’ll find the navigation pathways of RTC versions are significantly different and can take some getting used to. Microsoft did not do a great job with account schedules in the NAV2009 RTC version – there are some things there that are clunky, non-functional, and cumbersome. The good news is they fixed all those problems with NAV2013 and threw in some additional features on top of it, making account schedules even better with that version.

This posting is part of the NAV Account Schedules 101 series.  Find the entire list of lessons here.

Don’t forget to visit the Account Schedule Formulas and Account Schedule Examples pages if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Microsoft Dynamics NAV.