You can use account schedules to create financial reports in a flexible way and without any development needed.
You use account schedules to arrange accounts that are listed in the chart of accounts in ways that provide information about those accounts. Users can set up various layouts to define the information that they want to extract from the chart of accounts.
One of the main purposes of account schedules is to provide a place for calculations that cannot be made directly in the chart of accounts. For example, users can create account schedules to calculate profit margins on dimensions such as departments or customer groups. Additionally, they can filter any G/L entries and G/L budget entries, for example, by net change or debit amount.
By using cost types and cash flow entry accounts in the account schedules, users can build cost accounting reports and cash flow reports.
With account schedules, users can do the following:
- Create customized financial reports without using the Report Designer.
- Create as many account schedules as needed, each with a unique name.
- Set up various report layouts and print the reports with the current figures.
- Export financial data to Microsoft Excel.
Account Schedules Names
An account schedule consists of the following components:
- An account schedule name
- Several lines
- A column layout
To access the Account Schedule, in the Finance Tab, click on the Account Schedule, or the same can be accessed through the Search option.
The Account Schedule Names page contains the following fields:
- Name – Specifies the name of the account schedule.
- Description – Specifies a description of the account schedule.
- Default Column Layout – Specifies a column layout name that you want to use as a default for this account schedule.
- Analysis View Name – Specifies the name of the analysis view on which you want the account schedule based. If you link an analysis view to an account schedule, you can use the dimensions assigned to the analysis view in the account schedule lines. Otherwise, you can use only the two global dimensions in an account schedule.
The ribbon of the Account Schedule Names page gives you single-click access to the following pages:
- Overview – Runs the account schedules and shows figures based on the combination of the specified account schedule and column layout.
- Edit Account Schedule – Opens the Account Schedule page that you use to create account schedule lines.
- Edit Column Layout Setup – Opens the Column Layout page that you use to create account schedule columns.
Account Schedules Page
After you name an account schedule, you can define the lines in the Account Schedule page, accessed from the Account Schedule Names page, by clicking Edit Account Schedule in the ribbon. You provide information such as a description and which accounts to total, and set whether it appears in reports or is set up for calculations only.
To create an account schedule, you must set up several rows with the following fields.
You can use the Insert G/L Account to insert the G/L from the Chart of Accounts.
Select the G/L Accounts to be inserted in the Account Schedule and Ok.
|
Field |
Description |
|
Row No. |
Shows a number for the account schedule line. The same row number can be used on more than one line. The rows with the same number are then treated as a group. For example, if the row number is included in a formula in the Totalling field, it will represent the sum of all the lines that have that row number. If the Insert Accounts function is used, this field is automatically copied from the No. field in the chart of accounts. |
|
Description |
Specifies the text that will appear on the account schedule line. If the Insert Accounts function is used, this field is automatically copied from the Name field in the G/L account list. If you use an account schedule in the Finance Performance Window, the description must be unique. If the field is empty, the account schedule line is not displayed in the Finance Performance window. |
|
Totalling Type |
Determines which accounts within the totalling interval, specified in the Totalling field, are totalled. The options are as follows: • Posting Accounts: The total is calculated from amounts in posting accounts in the chart of accounts. This is the default value. • Total Accounts: The total is calculated from amounts in total and end-total accounts in the chart of accounts. • Formula: The total is calculated from amounts in other rows in the account schedule. The formula is entered in the Totalling field. • Set Base for Percent: Used to insert a column that displays the percentage of a total. The formula in the Totalling field on this line is used to calculate the total on which the percentage is based. This line does not appear on Account Schedule reports. • Cost Type: The total is calculated from amounts in posting accounts in the chart of cost accounts. • Cost Type Total: The total is calculated from amounts in total and end-total accounts in the chart of cost accounts. • Cash Flow Posting Accounts: The total is calculated from amounts in posting accounts in the chart of cash flow accounts. • Cash Flow Total Accounts: The total is calculated from amounts in total and end-total accounts in the chart of cash flow accounts. If you use the Insert Accounts function, accounts that have the Account Type Total or End-Total on the chart of accounts are assigned the totalling type Total Accounts. All other lines are automatically assigned the totalling type Posting Accounts.
|
|
Totalling |
Indicates which accounts are totalled on this line. This field is filled in based on the contents in the Totalling Type field: • If the Formula totalling type is assigned to the account schedule line, enter a formula that includes row numbers (or totalling intervals) from the account schedule. • For the following types, enter an account number (or totalling interval) from the specified chart of accounts: o Posting Accounts o Total Accounts o Cost Type o Cost Type Total o Cash Flow Entry Accounts o Cash Flow Total Accounts When you create formulas, use the following symbols: • addition: + • subtraction: – • multiplication: * • division: / • exponentiation: ^ • parentheses: () • percentage: % In the ribbon, the following three functions are available to insert accounts: • Insert G/L Accounts: The Totalling field is automatically copied from the No. field in the G/L account list. • Insert CF Accounts: The Totalling field is automatically copied from the No. field in the cash flow account list. • Insert Cost Types: The Totalling field is automatically copied from the No. field in the cost type list. Note: To include a number in the formula, do not use that number as a row number in the account schedule.
|
|
Row Type |
• Determines how the amounts in the row are calculated for the period defined in the Date Filter field on the report or page. The options are as follows: • Net Change: The total is calculated by using the total changes from the start to the end of the specified date range. • Balance at Date: The total is calculated by using the total of all entries from the initial transaction in Microsoft Dynamics NAV through the date at the end of the specified date range. • Beginning Balance: The total is calculated by using the total of all entries from the initial transaction in Microsoft Dynamics NAV to the last entry before the start of the specified date range.
|
|
Amount Type |
• Determines the type of entries that are included in the amounts in the account schedule row. The options are as follows: • Net Amount: The calculated amount includes debit and credit entries. • Debit Amount: The calculated amount includes only debit entries. • Credit Amount: The calculated amount includes only credit entries
|
|
Show |
Determines whether the account schedule line is printed on the report. The options are as follows: • Yes: The row is printed. • No: The row is not printed. • If Any Column Not Zero: The row is printed unless all amounts on the row are equal to zero. • When Positive Balance: The row is printed only if the balance of the amounts on the row is positive. • When Negative Balance: The row is printed only if the balance of the amounts on the row is negative.
|
|
New Page |
If selected, there a page break is added after the current account when the account schedule is printed |
Optional Account Schedule Fields
The following table shows the additional columns that users can add to the Account Schedule page by using the Choose Columns function.
|
Field |
Description |
|
Dimension Totalling Filters |
Dimension values or dimension value ranges that are used to filter the amounts shown in the row. These filters are described in more detail in the “Filter Account Schedules Using Dimensions” procedure. |
|
Bold, Italic, and Underline |
If selected, the row contents are bold, italic, or underlined. Only bold formatting is displayed in the Acc. Schedule Overview page, whereas the printed Account Schedule report and exported data in Microsoft Excel worksheets display all three formats. |
|
Show Opposite Sign |
If selected, debits in this row are displayed in reports as negative amounts (that is, with a minus sign) and credits are displayed as positive amounts. |
Column Layout Overview
The Account Schedule column layout contains the numeric information. You can set up both the visible columns that appear in the reports and the intermediary columns that are used for calculation only. To open the Column Layout window, click Edit Column Layout Setup in the ribbon of the Account Schedule window.
After you create a column layout, you can assign it as a default to any existing account schedule on the Account Schedule Names page.
The Column Layout page contains the fields that are shown in the following table.
|
Field |
Description |
||||||||||||||
|
Column No. |
Identifies the line for the column. The column numbers are optional and the same column number can be used on more than one line. The columns with the same number are then treated as a group. For example, if the column number is included in a formula, it will represent the sum of all the lines that share the column number. |
||||||||||||||
|
Column Header |
The text in this field is printed on reports that include the column. |
||||||||||||||
|
Column Type |
Determines how the amounts in the column are calculated. The amounts are calculated for the period defined in the Date Filter field on the report or page as follows: • Formula: The column displays amounts calculated from amounts in other columns in the account schedule. Enter the formula in the Formula field. • Net Change: The column displays the net change in the account balances during the period. • Balance at Date: The column displays the account balances at the end of the period. • Beginning Balance: The column displays the account balances at the beginning of the period. • Year to Date: The column displays the net change in the account balances from the start of the fiscal year to the end of the period. • Rest of Fiscal Year: The column displays the net change in the account balances from the end of the period to the end of the fiscal year in which the period ends. • Entire Fiscal Year: The column displays the net change in the account balances for the fiscal year in which the period ends.
|
||||||||||||||
|
Ledger Entry Type |
Determines the type of ledger entries that are included in the amounts in the account schedule column. The options are as follows: • Entries: The total is calculated from entries. • Budget Entries: The total is calculated from budget entries. The type of ledger entry is based on the Totalling Type field on the account schedule line. The following options are used:
|
||||||||||||||
|
Amount Type |
• Determines the type of entries that are included in the amounts in the account schedule column. The options are as follows: • Net Amount: The amount includes both credit and debit entries. • Debit Amount: The amount includes debit entries only. • Credit Amount: The amount includes credit entries only. |
||||||||||||||
|
Formula |
If Formula is selected as the Column Type, enter a formula for the line. The result of the formula is displayed in the column when the account schedule is printed. Use this field to perform calculations on other columns. Other columns are referenced through their column number. The calculation symbols are the same as those used in the row formulas. For example, to calculate the difference in the percentage of the G/L entries and the budget entries, enter the following formula: 100*(Column No. of the G/L entry column/ Column No. of the G/L Budget entry column -1) |
||||||||||||||
|
Show Opposite Sign |
If selected, debits in this column are shown in reports as negative amounts (that is, with a minus sign) and credits are shown as positive amounts. |
||||||||||||||
|
Comparison Date Formula |
Date formula that specifies which dates must be used to calculate the amount in this column. Microsoft Dynamics NAV uses this formula to calculate the amount from the comparison period in relation to the period represented by the date filter on the report request. Use the standard time periods of W(Week), M(Month), Y(Year), or Q(Quarter) to create the formulas. For example, -1Y is the same period one year earlier. |
||||||||||||||
|
Show |
• If selected, the amounts in the column are shown in reports. No more than five columns in an account schedule layout can be shown in the printed report. The options are as follows: • Always: All amounts in the column are always shown. This is the default value. • Never: The column does not appear on reports. It is used only for calculations. • When Positive: Only positive amounts (amounts that are positive before Show Opposite Sign is applied) are shown in the column. • When Negative: Only negative amounts (amounts that are negative before Show Opposite Sign is applied) are shown in the column. |
||||||||||||||
|
Rounding Factor |
Rounding factor for amounts in the column. The default value is None, no rounding. Be aware that amounts on printed reports are rounded. For example, if 1000 is selected, all amounts are shown in thousands. Amounts in the Acc. Schedule Overview Matrix page are not rounded. |
Column Layout Options
The following table shows the additional columns that users can add to the Column Layout page by using the Choose Columns function.
|
Field |
Description |
|
Comparison Period Formula |
Specifies which accounting periods to use when users are calculating the amount in this column. An accounting period does not have to match the calendar. But each fiscal year must have the same number of accounting periods, even though each period can be different in length. Microsoft Dynamics NAV uses the period formula to calculate the amount from the comparison period in relation to the period represented by the date filter on the report request. The abbreviations for period specifications are as follows: • P = Period. • LP = Last period of a fiscal year, half-year, or quarter. • CP = Current period of a fiscal year, half-year, or quarter. • FY = Fiscal year. For examples of formulas, press F1 to access Help for this field. |
|
Business Unit, Global Dimensions, and Dimensions 3 and 4 |
Totalling fields used to create column layouts that show global dimension information across columns. • When the column layout is not linked to an analysis view, the global dimensions are available in the Column Layout page. • When the column layout is linked to an analysis view, the dimensions specified in the analysis view is displayed in the Column Layout page. Analysis views are linked to column layouts in the Column Layout Names page, in the Analysis View Name field. |
Acc. Schedule Overview Matrix
With the Acc. Schedule Overview Matrix page, users can view account schedules in the various layouts they have set up. These layouts are also available in the Dimensions – Total report.
To view the Acc. Schedule Overview Matrix page from the Account Schedule page, click Overview on ribbon.
The following table shows the fields in the Acc. Schedule Overview page, on the General section.
|
Field |
Description |
|
Account Schedule Name |
To view a different row layout, select the required account schedule in this field. |
|
Column Layout Name |
To view a different column layout, select the required column layout in this field. |
|
View By |
Specifies the date interval used to display the amounts in the account schedules. The following options are available: • Day • Week • Month • Quarter • Year • Accounting Period |
|
Date Filter |
Specifies the dates that are used to filter the amounts on account schedule lines. |
|
Show Amounts in Add. Reporting Currency |
If selected, report amounts are shown in the Additional Reporting Currency. Usually, the amounts are shown in the company’s local currency (LCY). |
In the Dimension Filters section, you can set up filters for the following fields:
- Global Dimensions and Dimensions 3 and 4
- Cost Center
- Cost Object
- Cash Flow
- G/L Budget
- Cost Budget