Financial Reporting

Financial Reporting with Microsoft Dynamics NAV 2017

Microsoft Dynamics NAV is a business management solution that helps small and mid-sized organizations manage their accounting and finances, supply chain, and operations.

It’s a powerful financial reporting tool that you can use to extract essential business data from your chart of accounts, budgets, cash flow accounts and cost types into financial reports.

This will help you efficiently monitor the financial health of your business and provide valuable input for decision makers.

New Features in NAV Dynamics 2017

1. Account Categories
2. Account Schedules
3. Column Layouts
4. Analysis Views 

Account Categories

On The G/L Account Categories card you can select an Account Category and assign the appropriate Income/Balance option. There are two new fields where you can assign both high-level categories and subcategories.

There are also 4 pre-defined core financial statements that are automatically updated with information based on the categories that you define on each of your general ledger chart of accounts.

These high-level categories cannot be deleted:

• Assets
• Liabilities
• Equity
• Income
• Costs of Goods Sold
• Expense

Selecting one of the G/L categories or subcategories on each G/L account will also update the G/L Accounts in Category List.

Use the “Generate Account Schedules” function to update any changes you made to GL accounts. This function may need to be run periodically to update the schedules with G/L accounts that have been added. Simply assigning the category doesn’t automatically update the Account Schedules.  Any other manual changes made to the core financials shown in General Ledger setup will be overwritten when the Generate Account Schedules function is performed.

Then, use “Edit Account Schedules” to view changes made to the Account Schedules.  NOTE: Again, any other manual changes made to the core financials shown in General Ledger setup will be overwritten when the “Generate Account Schedules” function is performed. The core financial statements are the:

• Balance Sheet
• Income Statement
• Cash Flow Statement
• Schedule for Retained Earnings

Account Schedules

Account Schedules are used to arrange accounts listed in the cart of accounts in ways that will provide information about those accounts.  You can set up various Column and Row Layouts to define the information to extract from the chart of accounts.

One of the main functions of Account Schedules is to provide a place for calculations that can’t be made directly in the chart of accounts, such as creating subtotals for groups of accounts which can be included in new totals, and can then be used in other tools.

• Row Number. This is optional. The same row number can be used on more than one line. The rows will then be treated as a group.  For example, if you include the column number in a formula, it will represent the sum of all the lines that share the column number.
• Enter a description to appear on the Account Schedule overview or Report.
• Totaling Type. Select the appropriate option for the row.
• Assign one account or multiple accounts on any row where amounts should appear.
• Row Type*. Select Net Change, Balance at Date, Beginning Balance.
• Amount Type. Select Net Amount (most common), Debit Amount, Credit Amount.

Remaining fields are “option formatting” and “filtering options.”

*The Row Layout table has a similar function to this field.  If a row and column have conflicting values, the most restricted type will apply.  For example, Net Change is the default value in this field, it will be overridden by any other value in the Column Type field.

Column Layouts

Sometimes you may want to include a column in an account schedule to calculate percentages of a total:

• Use Edit Account Schedule to set up an account schedule row to calculate the total on which the percentages will be based.
• Insert a line immediately above the first row for which you want to display a percentage
• Fill in the fields on the line. In the Totaling Type field, enter Set Base for Percent. In the Totaling field, enter a formula for the total that the percentage will be based on.
• Column Numbers. These aren’t mandatory but can be helpful. If the same column number is used on more than one line, the columns will then be treated as a group. For example, if you include the column number in a formula, it will represent the sum of all the lines that share the column number.
• Enter a description to appear on the Account Schedule overview or Report.
• Column Type. The type determines how the amounts in the column are calculated. For example: Net Change, Balance, Formula, Year to Date.
• Ledger Entry Type. This specifies the type of ledge entries that will be included in the amounts in the column. Options are: Entries and Budget Entries.
• Amount Type. This specifies the types of entries to include in the amount. Options are: Net Amount, Debit Amount, Credit Amount.
• Use this field to perform calculations on other columns.  Other columns are referenced through their column number.

Comparison Column Layouts:

• Comparison Date Formula. This contains a date formula that specifies which dates should be used to calculate the amount in this column.  For example, if the field is set to 1Y, Microsoft Dynamics NAV compares this to the same period 1 year earlier.
• Comparison Period Formula. This contains a period formula that specifies which accounting periods should be used to calculate the amount in this column.

Formulas and Calculate Percentage Column Layout:

• The result of the formula will appear in the column when the account schedule is printed.  This field can also be used to perform calculations on other columns.  Columns are referenced by their column number.

The following symbols can be used:

• + (addition)
• – (subtraction)
• * (multiplication)
• / (division)
• ^ (exponentiation)
• ( ) (parentheses)
• % (percent) – If used between two terms, the result is the same as for

/ (division), but multiplied by 100.  When used at the end of a formula, the amount from the most recent line with Totaling Type = Set Base for Percent is used as the second term.  The first term is then divided by the Set Base for Percent result.  The resulting amount is multiplied by 100, and then displayed, followed by the percent sign.

You can enter a maximum of 80 characters, both numbers and letters.

Set Defaults:

The Default Column Layout will be the default when viewing an Account Schedule in an Overview, or when running a report.  The defaults can be changed, and any column layout can be used in the row layout.

Analysis Views

Analysis Views are very powerful and highly underutilized. It specifies the views that are created when you update an analysis view. An analysis view entry is based on one or more G/L entries that fulfill a number of criteria concerning posting date, account number and dimension information that you have specified for an analysis view.

You can define what information should be shown in that view.  For example, the dimensions and dimension values in G/L entries must have an order to be included in a view.  Criteria can be defined to include G/L entries posted to certain accounts by using the Account Filter function.

When an analysis view is updated, the program compresses G/L entries that fit the criteria that were set for the analysis view, and it creates analysis view entries.  Each analysis view entry represents a unique combination of posting date, G/L account number, and dimension values.

Select Edit on the ribbon of the Analysis View list to open the Edit-Analysis View Card to define all the criteria for the view.

Analysis by Dimension:

• In the Search box, enter Analysis by Dimensions, and then choose the related link.
• Select the relevant analysis view.
• On the Home tab, in the Process group, choose Edit Analysis View.
• Go to the Search box, Chart of Accounts, and then select the related link.
• Select the Navigate Tab to find the G/L Balance by Dimension in the Balance group.

More

The Analysis by Dimensions matrix allows you to view amounts in the General Ledger using the analysis views that have already been set up.

• Fill in the Analysis by Dimensions window to define what will be shown in the matrix, and then choose Show Matrix to view the matrix.
• In the Search box, enter Analysis by Dimensions, and then choose the related link.
• Select the relevant analysis view. On the Home tab, in the Process group, choose Edit Analysis View.

General Tab

• The leftmost columns contain information based on what you have selected in the Show as Linesfield in the header.
• The rightmost columns contain information based on what you have selected in the Show as Columns field in the header.

Filters Tab

• Defaults with Filters set in the view.

Option Tab

• This offers many different choices to view the amounts according to your needs.

Matrix Options

• A period length shorter than the period specified for the date compression cannot be selected on the Analysis View

So how does this all relate to Account Schedules?  Once you’ve set up an Analysis View, you now have all 4 of the dimensions that can be filtered on both  Row and Column Layouts.

Adding the Analysis View Name on the Account Schedule Row Layout will allow for additional filtering in the Overview. Plus, adding the Analysis View Name on the Account Schedule Column Layout list will allow for additional filtering fields for comparative statements. 

In Summary

Helpful Tips

• Use the Insert G/L feature to quickly set up a range of accounts on a Row Layout.
• Use Analysis Views to add addition dimensional filtering to Account Schedule Overviews and Column layout filtering for comparative statements.
• Account Schedules can be used for more than just financial reporting. Consider designing some column and row layouts on accounts that are analyzed often during a close process.
• Users can drill down into the data on all the views—Edit Analysis View/Show Matrix and the Account Schedule Overview.
• Analysis Views can also be used in the Jet Reports GL Function for filtering capabilities into the shortcut dimensions.

For questions or more information about using these new features in Microsoft Dynamics NAV 2017, contact Adroit Solutions Limited

at: info@adroitltd.com or dokello@adroitltd.com