For financial professionals tasked with reporting on data written to the general ledger in Microsoft Dynamics NAV 2013, Account Schedules have become an invaluable tool that every customer should explore.
With Account Schedules, NAV users can create customised and powerful reports on their GL and dimensions from directly in the system – without the help of a developer – and extend them into Microsoft Excel for producing good looking, graphical reports.
One of the best features in Microsoft Dynamics NAV 2013 for financial professionals has to be the Account Schedules reporting functionality. With Account Schedules, NAV users can create customised and powerful reports on their GL and dimensions from directly in the system – without the help of a developer – and extend them into Microsoft Excel for producing good looking, graphical reports.
NAV 2013 writes a huge amount of information into the general ledger by dimension, and Account Schedules are designed to make this information accessible for analysis. Beyond the general ledger, Account Schedules can incorporate GL Budgets, Cost Accounting, Cost Accounting Budgets and Cash Flow Forecast information into reports. They even go so far as to allow you to produce reports that contain all sources of information on a single report.
Reports like Profit and Loss, Overheads, and Cost Analysis by Branch, Department or Cost Centre are easy to produce, and perhaps more non-traditional reports like Customer and Vendor, or Inventory reports by various dimensions can be more easily produced from the GL and incorporated onto other financial reports.
Account Schedules in Microsoft Dynamics NAV are built around the concept of reuse. You will create a set of rows containing the accounts to analyse and then apply different sets of columns to them to view the information in different ways. So, taking a Profit and Loss statement with columns for Balance and Year to Date, then swapping out the columns with a set that shows balance by budget with a variance, both the rows that are created and the column sets that are created are reusable to create different views of the data.
So that’s enough background, let’s look at some specific concepts you need to know to get started with Dynamics NAV Account Schedules.
The first step in building a new report is creating the lines. Account Schedules have a lot of options for line type, but at a high level you can create lines to display GL accounts, consolidate accounts or split up accounts by dimension or as a formula. You can also create columns that display the ratio of lines against each other.
You are also able to choose the source of the records from GL, Cash Flow, Cost Accounting or a calculation based on other lines.
The lines also allow you to reverse the signs on the values so that you can show non-financial people amounts where the income is a positive and expenses are a negative; split debits and credits and apply some basic formatting to the lines so that the printed version of the report has some formatting. Here are some sample lines you could see in NAV:
Within columns you are able to specify several rules around of how values are rendered, including a wide range of data-driven formulas. Some examples are:
• Whether the value is a budget or actual amount
• Calendar vs financial days, weeks, months, years
• Specific financial periods, such as July, August or September
• Specific year as a comparison, so this month last year, year to date 2 years ago
• Calculated columns to show variances or other calculations across columns.
As with the lines, you are able to reverse signs to better present a report to non-financial people, hide columns when they are only used in calculations, round them off or filter by dimensions.
Here are a few examples of column layouts:
Microsoft Dynamics NAV Account Schedules give financial users in a company the ability to produce good looking and detailed reports from within NAV. They can also create new reports and adapt existing reports without having to get a developer to do the work. As this functionality is included in your NAV licence, I’d recommend that you give your internal people the opportunity to learn the skills to create these useful reports.