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.
By Neville Foyn, Lead ERP Consultant and Microsoft Certified Trainer at EBS. Neville is one of only a handful of Microsoft Certified Trainers in Australia and has been developing with NAV for more than a decade. For more information on Microsoft Dynamics NAV please contact Paul Woods at EBS on 1300 303 973 or email email@example.com
Subscribe to the EBS monthly enewsletter to get the latest Microsoft Dynamics NAV, Arrow Financials news and more. EBS enews.
Evolution Business Systems (EBS) specialises in business management solutions that give you the freedom to focus on your business. Melbourne based EBS does this by automating your core business processes and integrating your specialist business applications to deliver the right financial management solution. With deep expertise in leading financial applications including Microsoft Dynamics NAV and Arrow Financials, EBS works beside clients to ensure their software solves problems rather than creating more. EBS has a detailed implementation strategy that is tailored to customers in terms of timelines, requirements, risks and responsibilities. While it does not sell or support hardware or site network environments, EBS informally partners with selected specialists in these areas to provide clients with a “total solution”.