5 reasons you need to use NAV dimensions (part 2 of 15)

Posted: March 12, 2013 | Author: kpeters | Filed under: Uncategorized | Tags: accounts payable, dimensions, financial statement, general ledger, NAV, tips and tricks | 1 Comment

Still not convinced that leaving your old multi-segmented chart of accounts behind is a good idea? Don’t know exactly what in the world that crazy grid illustration with a number in only one box and words in the rest has to do with accounting, at all? Here are my five reasons for why you should be using dimensions.

1) Your chart of accounts is shorter. Seriously now, my company was the one with the 4,000 accounts in the chart of accounts before we installed NAV. We used project codes heavily, and had three to four segments to each base general ledger account number. Every member of the accounting team had this gigantic dog-eared book on their desk they would reference as they went through their day. Not only was it impossible to remember all that detail, it took a long time to type those numbers into the system, and with so many manually entered numbers, it was very easy to make a mistake. We spent a full two days every month during the close investigating and researching whether things went into the right place and then constructing reclassifying entries to correct the errors. My chart of accounts is less than 200 accounts now, and we eliminate a few more every year.

2) Account coding becomes intuitive. We still use project codes heavily, but now we’re able to give them a name as well as a number, which makes the coding process much more intuitive, less prone to error, and allows people outside the accounting department to enter data into the system. We work with a lot of non-numbers people at our company, and just talking about money has become easier since we started using dimensions. Nobody needs to get out the big dog-eared book to “determine the coding for your recent T&E”. We can use actual English and say things like, “Code that expense report to the Marketing team for the recent children’s sunday school event in Ohio” and everyone knows where that will be coded when the invoice gets entered to NAV.

3) Better control and accuracy. Certainly with having more intuitive coding and a shorter chart of accounts, we should all see some improvement in control and accuracy of postings. But wait, there’s more! NAV does a fantastic job of layering in more features for dimensions like allowing automatically populated default values, adding warning messages to make sure you get the right kind of dimension in the right place, allowing control at the level of your master data as well as your chart of accounts, and allowing restrictions on what dimensions can be combined with other dimensions and with what priority. All of these features combine to make sure the computer does the work instead of your employees and does it more accurately. Remember those two days my company used to spend to review and correct entries with reclassifications? Last year, I was able to send out an email out for three month end closes letting my team know they had a perfect posting month – no errors – not a single reclassification.

4) Greater flexibility in your reporting. Even with this shorter chart of accounts, we actually have more detail than we had before, because getting more detail through use of dimensions makes it possible. The idea of adding another segment in the old system to support a changing reporting need was heinously prohibitive. With dimensions, we gain that flexibility without adding another 400 pages to our chart of accounts. We produce more reports with more valuable information now than ever before, and we have the ability to combine the data we’ve gathered in more flexible combinations to assist us in making business decisions.

5) You’ll continue to have more options, even after go-live. I think a lot of people believe the only time they can make decisions about dimensions is when they are initially implementing. While this is a very important time to lay the foundation for your NAV system, you do have the option to add or change dimensions as you go along. We’ve actually added about one new dimension annually, as our business needs have changed, and as we’ve determined we need to report on different priorities.

Keep reading this month as we continue our series, 15 Days of NAV Dimensions.

Looking for a how to guide on running the year end close in NAV? Here are 3!

Posted: January 22, 2013 | Author: kpeters | Filed under: Uncategorized | Tags: CustomerSource, general ledger, manual, NAV, year end close | Leave a comment

All your journal entries are done, all of your modules balance to the general ledger, and your inventory period is closed. You are ready to get that income statement rolled up and close the year. Now where were those instructions? It’s not unusual to be a little unsure about the year end close process – we only get to do it annually, so no one will blame you if you can’t remember exactly what you did last year, and in what order. Here are a couple of resources to help you out.

ABC Computers, Inc. has a nice entry on their technology blog, “How to Close a Fiscal Year in Microsoft Dynamics NAV: Instructions for Annual Closing Operations“. This goes through a quick top level summary and then a deep dive into the closing process with field by field instructions.

If video learning is more your style, Archerpoint has a short 3-4 minute video out there, “Closing the Fiscal Year in NAV” that shows the annual closing process screen by screen.

Finally, you can always get the full manual on the year end closing process straight from Customer Source. Log in and type in 80041 into the search box. This will get you directly to the “Finance in Microsoft Dynamics 2009” Microsoft course. Download the course and go straight to the file for Chapter 10 – Year End Closing Processes.

Remember, you can always make entries after you’ve gone through your initial income statement rollup. You don’t need to wait for that very last entry to get this process going.

The NAV accountant’s little helper: the reconciliation account check box

Posted: September 18, 2012 | Author: kpeters | Filed under: Uncategorized | Tags: balance after posting, Classic Client, general ledger, journal entry, NAV, net change, posting test report, reconciliation account, RTC, tips and tricks | Leave a comment

There’s a tiny feature in NAV that I take for granted. It’s there, in the background, hiding in the setup, and it helps me out just a little almost every time I make a manual journal entry. Especially if I’m in a bit of a rush, or distracted, this option gives me one more quick but effective look at what I’m doing before I commit a journal entry to my books permanently. This nifty little thing can be added to your NAV setup at any time without any help from IT and won’t require any maintenance. The option I’m talking about is the Reconciliation Account check box you can find on your general ledger account card. Here’s where you can find it:

Role Tailored Client

Classic Client

For any account where you’d like this additional double check, check the Reconciliation Account box found on the General Ledger Account Card. Now go put together your journal entry.

Instead of posting directly from the entry, choose Actions=>Posting=>Test Report=>Preview (if you’re RTC) or just choose Posting=>Test Report=>Preview (if you’re Classic). NAV will put together a handy dandy print preview which will now include a section at the bottom labeled Reconciliation. This little section will show you which direction your entry is going in, what sign your ending balance will be, and what the actual balance after posting will look like. This all happens before you commit it as final to the system. This may be just the extra bit of help you need in order to keep you from making an error. If you like what you see, post that journal entry!

If you have an account where you’ve got a lot of daily activity, you may not find this as useful as you do with accounts with just a little bit of activity throughout the month because you’ll have a more frequently changing end balance. Decide what might be useful to you.

Next time you do a journal entry, give it a try. Go to the account card and check the box. Make your journal entry. Before you post, run the posting test report. Is the net change in your journal entry going in the right direction? Did you enter the correct account? Is your ending account balance the right sign? Most importantly, is the balance after posting what you expected? This little helper can give you a quick preview before you hit post and save you from having to reverse and correct an entry.

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

Basic column layout options for NAV Account Schedules

Posted: September 10, 2012 | Author: kpeters | Filed under: Uncategorized | Tags: Account Schedules, balance at date, Classic Client, column layout, comparison date formula, comparison period formula, financial statement, fiscal year, general ledger, NAV, net change, RTC | 5 Comments

Column layouts in NAV account schedules are what you build to add more flexibility to your financial statements. You can have an unlimited number of column layouts to match together with your already existing row setups. In general, most basic row setups contain general ledger numbers and column layouts contain dates. As an example, when I produce my income statement, I will have one row setup that reflects a summarized income statement and at least four different column layouts that reflect different configurations of month to date, year to date, comparisons against budget, and twelve month trended views.

Just like with row setups, there are many available options in the column layouts. This large variety of options can sometimes be overwhelming to the new account schedule user. I’ll show you which columns to choose in the column layouts for account schedules as a beginning point, and go through some simple explanations of how they are used.

Fields to start with:

Column No. – The column number is completely optional, but highly recommended. This simple element of the column layout will eventually be one of the key features of your account schedule, allowing you to calculate and organize with ease.

Column Header – This is where you’ll define, in words, what you’re showing in each column of your report. Keep it short; there is a 30 character limit.

Column Type: Net Change, Balance at Date, or Formula – There are actually seven options to choose from here, but I recommend that you limit yourself to these three when you’re just getting started. The key here is knowing what type of accounts you’re reporting on. If you’re using income statement accounts (Revenue/Expense), then you need to use net change. If you’re using balance sheet accounts (Assets/Liabilities), then you need to use Balance at Date. Formula allows you to perform calculations in a column.

Ledger Entry Type – This column will allow you to define what type of ledger entries you will show. This is where you can choose actual general ledger entries or budgeted general ledger entries.

Formula – If you’ve chosen Formula as the Column Type, this is where you’ll put the formula.

Comparison Period Formula – This column allows you to define date formulas that are used to calculate the amounts shown. I generally recommend that beginning account schedule users start out using the comparison period formula field instead of the comparison date formula field. The comparison period formula field references the accounting periods set up in the fiscal year, so this option seems the most consistent, and is especially necessary for companies that may not follow a calendar fiscal year. Common data labels used in this field are CP for current period or -1CP for previous period and FY for fiscal year or -1FY for prior year.

Shown below are both the design view and the user view of a column layout for a summarized income statement showing year to date and prior year to date information in the columns to demonstrate the use of these six basic options in an account schedule.

Role Tailored Client

Classic Client

For more information on row setups, please see these posts: Basic row setup options for NAV Account Schedules and Complete row setup options for NAV account schedules.

If you’re just beginning to use account schedules, see Getting started with a new account schedule.

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

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

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.

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

NAV Error Message: G/L Account No. ” does not exist

Posted: July 16, 2012 | Author: kpeters | Filed under: Uncategorized | Tags: Account Schedules, consolidation, error, financial statement, general ledger, NAV | 1 Comment

I was merrily finalizing my financial statements last week, and got this error when running my consolidation. I had successfully run my consolidation error free for over a year, so in disbelief, I immediately reran it, expecting a change in result. Same error. I began to run the mental check list. What was different? What had changed?

Of course! I had added a new account at the end of the year, and this month was the first month there was activity in the account. Not remembering exactly what I did six months ago, I rechecked my work.

I had added the new account on my main company
I had added the new account on my consolidation company

I reran the consolidation; still the same error. What was I missing? The culprit was the Consolidation tab on the on the G/L Account Card. I only had it set up correctly in the consolidation company and had missed filling out these same fields on the main company. I needed for this to be filled out exactly the same on both cards in order for this to work. I fixed the two fields, and voila!, my consolidation was back to its usual, reliable old self and finished up like a champ.

The culprit, showing missed fields

Now properly filled out

Just for good measure, I took a quick tour through my account schedules to make sure I had incorporated this new account either through ranges or individually so this new activity would also be properly captured in the financial statements.

This error can occur in other places, and in plain English is basically telling you that you’ve missed some setup where a general ledger account must be defined. That nifty little double apostrophe means “blank” to NAV. I put “NAV G/L Account No. ” does not exist” into BING looking for someone who may have answered this question before, so if you’re looking for a solution for this error in other areas, try these links.

http://support.microsoft.com/kb/2274642 Microsoft hotfix available when error is generated when you run the export to excel function on the analysis by dimensions reporting in the Spanish version

http://www.mibuso.com/forum/viewtopic.php?t=52338 when posting a sales credit note

http://dynamicsuser.net/forums/p/279520/279520.aspx when posting an invoice

Where can I learn more about NAV analysis reports and analysis by dimensions?

Posted: July 9, 2012 | Author: kpeters | Filed under: Uncategorized | Tags: analysis by dimensions, Analysis Reports, CustomerSource, export to excel, general ledger, item ledger entries, NAV, pivot table, query tool, reporting tool | 1 Comment

Analysis reports and analysis by dimensions are the native reporting options that extend NAV reporting to the item ledger entries generated from the sales, purchasing, and inventory areas of the application. I find that many users don’t know that this reporting option exists and think it is one of the more underutilized areas by NAV financial users.

These reporting options can be found on the general ledger, sales & marketing, purchasing, and inventory menus.

The main advantage this reporting tool has over account schedules is that it reaches a further level of detail that just isn’t recorded on the general ledger. Being able to get to quantity information at the item or location level in addition to the dollar values posted makes these tools great as a way to get operational reporting.

What is the difference between the two?

Analysis by dimensions is a query tool. The key to using this tool effectively is to use aggressive filtering, especially if you have a large number of items. It can be a very quick way to get information about what may be going on with a single or small group of items. You can also export to Excel and it shows up in a pivot table.

Analysis Reports allow you to configure and save row setups and column layouts for later use, which makes it a reporting tool. These reports work a lot like account schedules, with some added features to accommodate the additional data you can reach because you’re reporting against the item ledger entry tables instead of the general ledger.

A few hints on analysis reports and analysis by dimensions:

Skip the analysis report on the general ledger menu and use accounts schedules; you’re not going to get any added value here.
When posting sales or purchase orders, you must be fully utilizing the sub module, i.e. do not allow any posting of lines to general ledger accounts on the sales or purchase order documents. Any posting that goes “around” these modules will cause your analysis report to show a lower number than what is on your general ledger. Avoid the argument of reporting credibility by understanding this, and either being able to explain the variance, or prevent it from happening altogether.
Know that analysis reports will not automatically update if you add new items. You’ll need to go in and do this manually to each report every time you add new items.

For those of you who are looking for more resources on Analysis by Dimension and Analysis Views, reference the documents below, found on Customer Source:

Documentation => User Guides => Overview of Training Manuals and Hands-On Labs for Microsoft Dynamics NAV 2009