Blogiversary Top 20 (#11) Complete row setup options for NAV account schedules

Posted: April 15, 2013 | Author: kpeters | Filed under: Uncategorized | Tags: Account Schedules, balance at date, Classic Client, column layout, dimensions, Excel, export to excel, financial statement, general ledger, NAV, net change, posting accounts, row setup, RTC, set base for percent, setup options, show opposite sign, totaling type, trial balance | Leave a comment

We’re celebrating our one year blogiversary by reposting the Top 20 Most Viewed in the last year, as determined by you, our readers! Follow this link to see the entire list. Enjoy!

About a month ago, I published a post called Basic row setup options for NAV account schedules. This is a great place to look if you’re just getting started. However, if you’re looking for what else you can do with Microsoft Dynamics NAV account schedules, then this is where you want to be. This post will go through each and every field available on the account schedule row setup and talk about the available options. The functionality of these fields are exactly the same between the Classic and Role Tailored Clients. The only difference is in how they look on the screen and how you navigate between screens. Take a look at the pictures below to find the look you’re familiar with, but then keep reading for the explanations of what these fields will do in your financial reporting.

Role Tailored Client

This is a view of what I recommend you start with for basic row setup options.

This is a view of all available row setup options.

Classic Client

This is a view of what I recommend you start with for basic row setup options.

This is a view of all available row setup options.

Row setup options – apply to both clients

Row No. is completely optional, but highly recommended. This simple element of the row setup will eventually be one of the key features of your account schedule, allowing you to calculate and organize with ease.

Description is the one place you have to communicate, in words, what you’re showing on each line of your report.

Totaling Type: 6 options to choose from

Posting Accounts pull data from your accounts designated as posting accounts on the G/L Account Card Account Type field.
Total Accounts pull data from your accounts designated as a total or as an end-total on the G/L Account Card Account Type field.
Formula allows you to calculate a basic formula on this row.
Underline places a single underline .
Double Underline places a double underline.
- NOTE: This nifty addition showed up with NAV 2009 R2. If you are NAV 2009 SP1 or earlier, this won’t be available to you.
- Both of the underline functions are there to give folks who print account schedules directly out of NAV some formatting options. If you’re exporting to Excel you’ve got some better format options to work with than these.
Set Base for Percent allows you to designate which row from your row setup will be used as the beginning point for a section to be included in a percent of total calculation. This feature showed up in version 5.0 and must be used together with a formula on another row to designate the end point of the section as well as some setup in the column layout. It’s a little fussy in the setup and inelegant in execution, but is still effective to get those vital comparative percentages onto your account schedules.

Totaling will be which general ledger accounts you want to pull or what formula you want to calculate, depending on what you selected in totaling type.

Row Type: 3 options to choose from

Net Change should be used, generally, if you’re reporting on income statement accounts (revenue/expense).
Balance at Date should be used, generally, if you’re reporting on balance sheet accounts (assets/liabilities).
Beginning Balance will be sparingly used, but you’ll want this for your statement of cash flows at some point.

Amount Type gives you three options: Net Amount, Debit Amount and Credit Amount. You’re going to use Net Amount almost all the time, but I suppose if you were getting very granular in your account schedules and perhaps using them for detailed reconciling reports, this might be useful. If I’m getting down to this level of transactional detail, I’m usually going to the chart of accounts instead of account schedules.

Formatting Fields: Bold, Italic, and Underline These basic formatting fields are there to allow you to emphasize some of the rows of your account schedules. When you select these options on any row, the whole row is effected. You are not able to effect a single “cell” for formatting. What’s the difference between underline here and underline found in the Totaling Type options? You can get a more snug fit on your underline using this option since it appears in the same row of your data instead of in a row if its own.

New Page inserts a page break. This is commonly used when separating the pages of the balance sheet showing assets on the first page and liabilities and equity on the second page. I have also seen it used when folks build multi page account schedules.

Show Opposite Sign is the option you can select to change revenue credit balances or expense debit balances to the opposite sign for the purposes of presenting this data in a more non-finance friendly manner. No salesperson wants to see sales presented with a negative sign; use this to change that credit to a positive number on your reporting.

Show allows you to designate if a row will show (the default) or be hidden on the printed account schedule only. You can click this box as much as you want, but your rows will still show on-screen or when you export to Excel. If you print directly out of account schedules, this feature will work very well for you.

Dimension Totaling is the last option available and you might have noticed that I didn’t show them on pictures above. This is because they are unique for just about every user, and depending on your analysis view setup, may even be different from schedule to schedule. You can use just one, or you can use all four of the available dimension totaling fields, in whatever combination you like. These fields act very much like the Totaling field because this is where you will tell NAV what Dimensions you want shown and totaled from your general ledger accounts.

Complete row setup options for NAV account schedules

Posted: July 30, 2012 | Author: kpeters | Filed under: Uncategorized | Tags: Account Schedules, balance at date, Classic Client, column layout, dimensions, Excel, export to excel, financial statement, general ledger, microsoft dynamics nav, NAV, net change, posting accounts, row setup, RTC, set base for percent, setup options, show opposite sign, totaling type, trial balance | 1 Comment

Role Tailored Client

This is a view of what I recommend you start with for basic row setup options.

This is a view of all available row setup options.

Classic Client

This is a view of what I recommend you start with for basic row setup options.

This is a view of all available row setup options.

Row setup options – apply to both clients

Description is the one place you have to communicate, in words, what you’re showing on each line of your report.

Totaling Type: 6 options to choose from

Posting Accounts pull data from your accounts designated as posting accounts on the G/L Account Card Account Type field.
Total Accounts pull data from your accounts designated as a total or as an end-total on the G/L Account Card Account Type field.
Formula allows you to calculate a basic formula on this row.
Underline places a single underline .
Double Underline places a double underline.
- NOTE: This nifty addition showed up with NAV 2009 R2. If you are NAV 2009 SP1 or earlier, this won’t be available to you.
- Both of the underline functions are there to give folks who print account schedules directly out of NAV some formatting options. If you’re exporting to Excel you’ve got some better format options to work with than these.
Set Base for Percent allows you to designate which row from your row setup will be used as the beginning point for a section to be included in a percent of total calculation. This feature showed up in version 5.0 and must be used together with a formula on another row to designate the end point of the section as well as some setup in the column layout. It’s a little fussy in the setup and inelegant in execution, but is still effective to get those vital comparative percentages onto your account schedules.

Totaling will be which general ledger accounts you want to pull or what formula you want to calculate, depending on what you selected in totaling type.

Row Type: 3 options to choose from

Net Change should be used, generally, if you’re reporting on income statement accounts (revenue/expense).
Balance at Date should be used, generally, if you’re reporting on balance sheet accounts (assets/liabilities).
Beginning Balance will be sparingly used, but you’ll want this for your statement of cash flows at some point.

This posting is one of the Top 20 Most Viewed in the last year! Follow this link to see the entire list.

Use insert accounts for a fast start on building a trial balance

Posted: June 11, 2012 | Author: kpeters | Filed under: Uncategorized | Tags: Account Schedules, Classic Client, export to excel, general ledger, insert accounts, NAV, row setup, RTC, trial balance | Leave a comment

You’re diving into using account schedules – you’ve figured out where they are, created a brand new one, given it an easy short name and a longer descriptive name and now you’re ready to get started. Now what? You need to begin with the row setup; the foundation of all account schedules.

The row setup defines what you’ll see on the lines of your report. Generally you’ll want to define general ledger accounts, or groups of general ledger accounts, in the row setup when you’re just getting started. Later, you’ll want to add some column layouts but let’s focus on getting that foundation built first.

The easiest way to get going with a new row setup is to put together a trial balance. Your trial balance should be a listing of all your general ledger accounts, one line each, which will show the balance of each account on a particular date, generally a month or period end. If you add them all up, all the accounts should balance to zero, which is one way to prove that your books are in balance.

Role Tailored Client Instructions

If you’re using the role-tailored client (RTC), choose Edit Account Schedule from the action pane.

You’ll get a new screen with many blank rows ready to be used. From this screen, choose Actions=>Functions=>Insert Accounts.

This opens up your G/L Account List. Use your mouse to select all the rows you want to add to your row setup.

Once you’ve highlighted them all (they should turn light blue), hit OK, and all the accounts you’ve selected will be added to your row setup, one row per account.

At this point, don’t make any changes to the row setup. Let’s see if it matches the canned trial balance that you can print directly out of NAV. Choose the Overview button (shown in the above screenshot). Select a column layout. If you don’t have one yet, hang in there, we’ll cover that in an upcoming post. For now, I’m going to choose a simple one that shows balance only. Select Show Matrix from the action pane.

This will show all the accounts you selected in your row setup, now in your account schedule.

To prove out the balance, dump the account schedule into Excel. Do this by choosing Actions=>Functions=>Export to Excel. On the next window choose the option to Create a New Workbook. Hit OK, and your schedule will export to Excel. Watch out: If you’ve used Begin or End-Total lines in your chart of accounts, you’ll need to remove these lines. These are calculated fields, and should not be a part of your trial balance.

Let’s run the canned trial balance from NAV as a comparison to see if we’ve done this right. Follow this trail to get to the report: Departments=>Financial Management=>General Ledger=>Reports=>Financial Statement=>Trial Balance. Select Actual Balances as your report column and make sure the date filter is set to the same date you used when running the account schedule. Compare the report to the account schedule. Each line should match between the two reports and the total should be zero on both, proving your trial balance is indeed in balance.

Classic Client Instructions

For those who use the classic client, let’s run through the same steps. Starting from Financial Management=>General Ledger=>Analysis & Reporting=>Account Schedules, choose your new account schedule from the list.

Hit OK, and you’ll get a new screen with many blank rows ready to be used. From the Functions button on the bottom right, choose Insert Accounts.

This opens up your G/L Account List. Simply click on the blank cell immediately to the left of the No. field to select all accounts quickly.

Once you’ve highlighted them all (they should turn dark blue), hit OK, and all the accounts you’ve selected will be added to your row setup, one row per account.

At this point, don’t make any changes to the row setup. Let’s see if it matches the canned trial balance that you can print directly out of NAV. Hit OK, and then choose Overview from the Account Schedules button found on the bottom right.

Select a column layout from the Column Layout Name field. If you don’t have one yet, hang in there, we’ll cover that in an upcoming post. For now, I’m going to choose a simple one that shows balance only.

This will show all the accounts you selected in your row setup, now in your account schedule.

To prove out the balance, dump the account schedule into Excel. Do this by choosing Export to Excel from the Functions button on the bottom right.

On the next window choose the option to Create a New Workbook. Hit OK and your schedule will export to Excel. Watch out: If you’ve used Begin or End-Total lines in your chart of accounts, you’ll need to remove these lines. These are calculated fields, and should not be a part of your trial balance.

Let’s run the canned trial balance from NAV as a comparison to see if we’ve done this right. Follow this trail to get to the report: Financial Management=>General Ledger=>Reports=>Financial Statement=>Trial Balance. On the Options tab, select Actual Balances as your report column and make sure the date filter is set to the same date you used when running the account schedule. Compare the report to the account schedule. Each line should match between the two reports and the total should be zero on both, proving your trial balance is indeed in balance.