Navision Depot Mini-Manual How to create and edit financial statements in Dynamics Nav / Navision

Purpose Detailed instructions to show you how to create and edit financial statements using account schedules in Dynamics Nav / Navision.

Navision Versions This applies to Navision 3.x, 4.x, 5.x, and 2009 Classic Client. The basic functions are applicable to the 2009 Role-Tailored Client but the navigation is different.

Overview Navision allows users to create and customize a wide range of financial and management reports using the built-in "account schedules" capability. Report options include but are not limited to traditional financial statements such as balance sheet and income statement.

Concepts and General Explanation You can use Account Schedules to customize your financial statements exactly as you desire. But since each statement may be different, learning to use Account Schedules is not so much about following a step by step process as it is about understanding the tools and options available and how to use them. Account Schedules are composed of two parts: the Account Schedule itself, or the rows, and the Column Layout. You can mix and match different Account Schedules with different Column Layouts to create a wider array of financial statements. You may also optionally tie an Analysis View to your Account Schedule. If you do this, the entries on your Financial Statement will be based on the data in that Analysis View, rather than current live General Ledger data. Remember that the data in an Analysis View may be filtered, and may not be current, depending on how you've set up your Analysis Views. There are two Account Schedule layouts – the base Account Schedule, which prints in Portrait by default and can show up to 6 columns, and the Account Schedule – Landscape, which can show up to 13 columns. NOTE: There are two different ways to create and edit printable reports in Navision: Account Schedules, and the Report Designer in Object Designer. Account Schedules is designed to handle financial statements, and is covered in this document. Report Designer handles other printable reports and documents such as aging reports and invoices, and is not addressed herein.

Navision Depot Everything for and about Microsoft Dynamics Nav www.navisiondepot.com

How to change the tab order on a form

Sample Financial Statement

Instructions: How to Access Account Schedules 1) Go to Financial Management > General Ledger > Analysis & Reporting > Account Schedules.

2) It will automatically open the topmost Account Schedule. If this is not the one you want, select the Account Schedule you want to edit from the Name lookup box, then click OK.

Navision Depot Everything for and about Microsoft Dynamics Nav www.navisiondepot.com

How to change the tab order on a form

3) To access the columns, once you've opened any account schedule, go to Functions > Set Up Column Layouts.

4) It will automatically open the topmost Column Layout. If this is not the one you want, select the Column Layout you want to edit from the Name lookup box, then click OK.

Navision Depot Everything for and about Microsoft Dynamics Nav www.navisiondepot.com

How to change the tab order on a form

Instructions: Create a New Account Schedule 1) Go to Financial Management > General Ledger > Analysis & Reporting > Account Schedules.

2) Click the Name lookup box, you'll see a list of existing Account Schedules, if there are any.

3) To make an entirely new Account Schedule, use F3 to add a new line in the Account Schedule Names screen, and then enter your desired name and description. Leave Default Column Layout blank for now. If you want your Account Schedule to use data from an Analysis View, select the desired Analysis View now, otherwise leave it blank. Click OK, then hit tab to see your new, blank Account Schedule.

Navision Depot Everything for and about Microsoft Dynamics Nav www.navisiondepot.com

How to change the tab order on a form

4) Now you need to add rows to your Account Schedule. There are several options for doing this. You can either add lines one at a time manually, by typing them in, or you can copy/paste a range of lines from the General Ledger. The copy/paste from the General Ledger option will be much more beneficial if you have previously organized your General Ledger with Begin/End Totals that line up with how you'll want your financial statements to look. a. To copy/paste from the Chart of Accounts: click Functions > Insert Accounts

This will bring up your Chart of Accounts. You can either click in the upper left corner to highlight the whole thing, or you can set filters to only select certain subsets of your Chart of Accounts, such as only Balance Sheet accounts, or only Income Statement Accounts, or only End Total accounts, and then click in the upper left to select those. Whatever you have selected will drop into your Account Schedule when you click OK. So when you are happy with your selection, click OK. For this sample, we're going to make a new Balance Sheet, so we set filters (F7) to show only Balance Sheet accounts of type End Total. Then we click in the upper left corner to highlight these lines, and then click OK.

Navision Depot Everything for and about Microsoft Dynamics Nav www.navisiondepot.com

How to change the tab order on a form

Will result in:

b. To manually enter lines: Manually type in each line, assigning a row number and selecting or typing in values as needed. Reference the next section to understand the possible row values. 5) When you are satisfied with your rows, create a new column layout, or assign an existing column layout to this account schedule.

Instructions: Editing Account Schedule Rows 1) Open your desired account schedule. See "Accessing Account Schedules" section for detailed instructions. 2) For each row, enter or edit the appropriate information, based on what you want to see. Here are the main things you can set, change or control using account schedule rows: • Text format: Bold, Italics, Underline • Line Breaks – Single and Double Underlines • Page Breaks • Sign – show the sign, or show opposite sign (for those who don't like seeing revenue numbers with a negative sign, for example) • Text - what words are written on the rows, typically account descriptions such as "Liquid Assets" • Amounts/values – whether the amounts shown are net change, debits only, credits only • Amounts as of date – the effective date of the amounts, for example, net change, balance at date, beginning balance • Filters on amounts – you can set dimension filters so that only transactions with particular dimension value(s) are included • Line show/hide – you can set the show/hide for particular lines, including the option to show lines only if there's a nonzero amount (in other words, hide any lines that would be zero or blank)

Navision Depot Everything for and about Microsoft Dynamics Nav www.navisiondepot.com

How to change the tab order on a form

3) Edit your rows per the below field reference: Field Name What it is and what it does Row Number An arbitrary number or letter combination. If you used the 'insert accounts' function, it was set as your G/L account number, but you can change it if you want. You can use anything you want for row numbers, so long as each row number is unique. Row numbers are not used to order the lines, they are only used as reference numbers when you want to include that row in a formula. Description Description of that row's value – what you type here is what you'll see on your financial statement for that row. Totaling Type This field determines how the contents of the next field are handled, so it is important. You have several choices here: Posting Accounts – the amounts on this line will come from one or more G/L accounts of type 'posting account' Total Accounts – the amounts on this line will come from one or more G/L accounts of type 'Total' or 'End Total' Formula – the amounts on this line will be calculated using a formula that you will write, which can involve amounts on other lines in this financial statement and one or more arithmetic operators, for example row1+row2 Underline – puts a single horizontal line on this row, in all numbers columns Double Underline – puts a double horizontal line on this row, in all numbers columns Set Base for Percent – if you're going to have a percent calculation on this financial statement, this is where you set the basis or denominator for that calculation. Totaling What you see or can enter here will be determined by what Totaling Type you picked in the previous field. This is where you control what will be in the amount shown on this line. If you set Totaling Type to Posting Accounts, you can enter or pick one or more posting accounts from the Chart of Accounts. Enter any values separated by "|", so for example 1110|1120. The net of these accounts will be shown on your financial statement. If you picked Total Accounts for your Totaling Type, it works the same except you can only pick from accounts of type Total or End Total. Note you cannot mix posting accounts and total accounts on the same line. If you picked Formula for Totaling Type, you'll enter your formula here, using row numbers and arithmetic operators. Row Type Select the source of the amount you'll show on your financial statement here – you can pick from net change (typical for Income Statements), balance at date (typical for Balance Sheets), or Beginning Balance. You'll set the actual date or date range when you run the financial statement. Amount Type Choose the amount type to show – net amount (most common), or debit only or credit only (usually used when setting up a report that will show debits and credits on different lines or in different reports). Dimension Code There are 4 columns for 4 different dimensions that can be used for row-level totaling. For Totaling each of these four dimensions, you can pick one or more dimension values, and these values will be used as filter so that only entries containing those dimension values will be included in the amounts shown on that row. For example, if you have a dimension called "region" and dimension values of "north" and "south", you can make one revenue row with the dimension code totaling for region set to "north" and another row below it with totaling set to "south", and you'll get a financial report with "north" revenue on one line and "south" revenue separate and on the next line. Note that you can enter dimension values for more than one dimension per row, but your financial statement will only show

Navision Depot Everything for and about Microsoft Dynamics Nav www.navisiondepot.com

How to change the tab order on a form

Show

Bold, Italic & Underline Show Opposite Sign

New Page

entries that meet ALL of the specified criteria. Here you set the rules for when this row will be visible and when it won't. Your options are: Yes – row is always visible No – row is always hidden, good for rows you need to include for use in calculations but don't want to see on your report If Any Column Not Zero – very handy option, allows you to show the row if it has any nonzero values, and hide it if all values are zero or blank. Be careful, this option can mess up your page breaks if you aren't careful, because it can dramatically change the number of included rows. When Positive Balance – Only shows the row and amounts if the balance is positive. Be careful, this can really mess up your totals if a value is different that you're expecting. When Negative Balance – Only shows the row and amounts if the balance is negative. Be careful, this can really mess up your totals if a value is different that you're expecting. Very simple – put a checkbox in any or all of these formatting options, and all text on the associated line will show that format. If you check all three, your text and amount will be bold, italicized, and underlined. Checking this box makes the report show the opposite of the correct sign for this row. This can be very useful for those who don't want their Revenue numbers to show a negative sign. However, be careful, because this is purely a visual change, it has no impact on the underlying math, so if you forget to check one row, or mistakenly check a row, you can wind up with financial statements where your amounts look like they don't tie to your totals. Checking this checkbox will insert a hard page break and make the row where you check the checkbox the first row on a new page.

Instructions: Editing Account Schedule Columns 1) Open your desired column layout. See "Accessing Account Schedules" section for detailed instructions. 2) For each column, enter or edit the appropriate information, based on what you want to see. Here are the main things you can set, change or control using account schedule column layouts: • Rounding – Round to the nearest penny, dollar, thousand, or million • Sign – show the sign, or show opposite sign (for those who don't like seeing revenue numbers with a negative sign, for example) • Column Headers – what titles are used as the column headers • Amounts/values – whether the amounts shown are net change, debits only, credits only • Type of data shown – whether to show actual or budgetary numbers in each column, and what period(s) to include • Filters on amounts – you can set dimension filters so that only transactions with particular dimension value(s) are included • Line show/hide – you can set the show/hide for particular columns, including the option to show columns only if there's a positive or negative amount

Navision Depot Everything for and about Microsoft Dynamics Nav www.navisiondepot.com

How to change the tab order on a form

3) Edit your columns per the below field reference: Field Name What it is and what it does Column No. An arbitrary number or letter combination. You can use anything you want for column numbers, so long as each column number is unique. Column numbers are not used to order the columns, they are only used as reference numbers when you want to include that column in a formula. Column Header Description of that column's contents - what you type here is what you'll see on your financial statement for that column. Column Type Select the source of the amount you'll show on your financial statement here – you can pick from: Formula – you'll create a formula to generate amounts based on other data in this report Net Change (typical for Income Statements) Balance (typical for Balance Sheets) Beginning Balance – period starting balance for the specified period Year to Date – net change from the start of the fiscal year to the end of the period Rest of Fiscal Year – net change from the end of the specified period to the end of the associated fiscal year Enter Fiscal Year – net change for the whole fiscal year associated with the specified period You'll set the period by specifying the actual date or date range when you run the financial statement. Ledger Entry Type Specify whether you want this column to contain actual G/L entries, or budget numbers Amount Type Choose the amount type to show – net amount (most common), or debit only or credit only (usually used when setting up a report that will show debits and credits in different columns or in different reports). Formula If you specified a Column Type of Formula, you can enter your formula here, and the results will show in your column. Your formula can be up to 80 characters long, and can include column numbers, normal arithmetic operators (+,-,*,/), ^ for exponent, parenthesis, and a special shortcut for percents: %, which can be used between two numbers to divide and then multiply the result by 100 in one action (for example, A%B is the same as A/B*100). You can also use the % operator with Set Basis for % as an implied denominator, by omitting the denominator – so A% is the same as A/(content of closest previous set basis for percent field value)*100. Show Opposite Sign Checking this box makes the report show the opposite of the correct sign for this column. This can be very useful for those who don't want their Revenue numbers to show a negative sign. However, be careful, because this is purely a visual change, it has no impact on the underlying math, so if you forget to check one column, or mistakenly check a column, you can wind up with financial statements where your amounts look like they don't tie to your totals. Comparison Date This is where you put the formula for Nav to calculate the comparison date, if you Formula want a column to show data from a comparison date, for example this year vs last year. Alternatively you can use the Comparison Period Formula and calculate by period rather than date. Available formula elements include: Y – year, for example, -1Y is last year M – month

Navision Depot Everything for and about Microsoft Dynamics Nav www.navisiondepot.com

How to change the tab order on a form

Show

Comparison Period Formula

Rounding Factor

Dimension Code Totaling

D - day Here you set the rules for when this row will be visible and when it won't. Your options are: Always – column amounts are always visible Never – column is always hidden, good for columns you need to include for use in calculations but don't want to see on your report When Positive – Only shows amounts in the column if the amounts are positive. Be careful, this can really mess up your totals if a value is different that you're expecting. When Negative – Only shows amounts in the column if the amounts are negative. Be careful, this can really mess up your totals if a value is different that you're expecting. This is where you put the formula for Nav to calculate the comparison period, if you want a column to show a comparison period, for example this year vs last year. Alternatively you can use the Comparison Date Formula and calculate by date rather than period. Available formula elements include: P – period, for example, -1P is the previous period LP – last period CP – current period FY – fiscal year FY[X..Y] – range of months in the fiscal year, for example, FY[1..4] is the first 4 months of the current fiscal year To what level do you want to round all of your amounts – you can select from the following: None – no rounding, which means your amounts will be exact to the penny 1 – Round to the dollar 1000 – Round to the nearest thousand dollars 1000000 – Round to the nearest million dollars There are 4 columns for 4 different dimensions that can be used for column-level totaling. For each of these four dimensions, you can pick one or more dimension values, and these values will be used as filter so that only entries containing those dimension values will be included in the amounts shown in that column. For example, if you have a dimension called "region" and dimension values of "north" and "south", you can make one revenue column with the dimension code totaling for region set to "north" and another column next to it with totaling set to "south", and you'll get a financial report with "north" revenue in one column and "south" revenue separate and in the next column. Note that you can enter dimension values for more than one dimension per column, but your financial statement will only show entries that meet ALL of the specified criteria.

Navision Depot Everything for and about Microsoft Dynamics Nav www.navisiondepot.com

How to change the tab order on a form

Instructions: Printing Account Schedules 1) You can print your account schedule based financial statements directly from the same screen where you edit them, or you can print them by going to Financial Management > General Ledger > Reports > Financial Statement, Account Schedule (for the 6 column version) or Account Schedule, Landscape (for the 13 column version). 2) On the Options tab, select your account schedule and column layout, set all your filters, date ranges, and other preferences, as follows:

Acc. Schedule Name – Select the account schedule you want to print Column Layout Name – This will default to whatever column layout is assigned to your account schedule, but you can change it here if you want Date Filter – Enter your date range here Budget Filter – If you want to apply a budget filter, enter it here Business Unit Filter – If you want to apply a business unit filter, enter it here Dimension Filters – You'll see your 4 selected dimension here, apply one or more dimension filters if you desire. Show Error – If you are testing or troubleshooting your report, this is a useful feature that can show the nature of any error that occurs. If you leave it set at None, any error will print a blank instead of an amount.

Navision Depot Everything for and about Microsoft Dynamics Nav www.navisiondepot.com

How to change the tab order on a form

Show Neg. Amounts with – Select how you want to see negative amounts shown – minus sign, parentheses, and several other options. Note this only applies to amounts that will show a negative of some sort – obviously if you've used the "show opposite sign" to turn a negative, positive, it won't show any type of negative sign. Show Neg. Percents with – Select how you want to see negative percents – minus sign, parentheses, or several other options. Show Amounts in Add. Reporting Currency – If you use multi-currency and want to see an additional reporting currency on your report, check this box.

Get More Help Need more assistance with Navision? Get expert help at a reasonable price from the staff at Navision Depot! We are the Navision experts – we've been with Navision from the beginning, and Navision is all we do. Email to [email protected] or http://www.navisiondepot.com/navision-consultants.html With our worldwide network of Navision consultants and developers, we can handle big projects or just answer the occasional question. And since we don't have to support the overhead of a sales force, we are usually much more costeffective than traditional VARs. If this Mini-Manual, or our other support products, answered your questions, great! If not, please drop us a line, we want to make sure you are 100% satisfied. And if you have any other needs, we'd be delighted to work with you!

Navision Depot Everything for and about Microsoft Dynamics Nav www.navisiondepot.com