Creating Your Microsoft Dynamics Chart of Accounts – Your Reporting Foundation

When it comes to implementing an Enterprise Resource Planning (ERP) system (whether it’s Microsoft Dynamics GP (Great Plains), Microsoft Dynamics NAV (Navision) or any other ERP), the critical element to consider in a roll out is the Chart of Accounts.

Without a well-designed Chart of Accounts you run the risk of investing significant resources into a system without delivering the pay-off of decision supporting analytics and reports.

If you have ever found yourself spending hours or even days preparing an Excel spreadsheet for a department manager you will understand the advantage of capturing key information at source.

One of our clients realized huge benefits after the implementation of a new ERP system with a well-designed account structure. With their legacy structure, they would never have had the manpower to deliver business critical insight.

After implementing Microsoft Dynamics NAV and Dimensions for Product Category, Market Area, and Customer Type, they had valuable information at their fingertips and were able to answer questions such as:

• Which product range has fallen off in terms of sales dollars?
• Which market area with Western Canada is growing?
• Are we hitting our targets for attracting more institutions as customers?

There are a number of best practices to follow when choosing your account structure. Here are four:

1. Start at the End

• Although the General Ledger is the realm of accountants, you should start by asking the rest of the organization what reports they require.
• Are there clear product lines that need to be analyzed and listed separately on a Profit and Loss Statement?
• Are budgets created based on the branch locations of an operation?
• First determine what report layouts you are going to need, this will tell you what level of detail needs to be captured in the Chart of Accounts.
• Remember it is always possible to “roll up” accounts and dollars on a report but it is not possible to display detail that was never captured.  Prepare a binder of mocked up sample reports.  Include reports such as the Balance Sheet, Profit and Loss Statement, Operations Reports, etc.

2. Account Banding

• Determine bands of numbers that will be used for the key sections of your financial statements.  This allows you to keep similar accounts together and speeds up report design.  Be sure to leave enough room for expansion to accommodate organizational growth whether that is adding new branches or lines of business.

Section	From Account	To Account
Assets	10000	19999
Liabilities	20000	29999
Equity	30000	39999
Revenue	40000	49999
Cost of Goods Sold	50000	59999
Operating Expenses	60000	69999
Extraordinary Items	70000	79999

• Some ERP’s enable you to provide sub-total accounts used in onscreen inquiries or reports.  For example, Microsoft Dynamics NAV incorporates beginning and ending totals in the Chart of Accounts.  You may want to reserve accounts ending in “00” and “99” for totalling purposes.

3. Numbers over Letters

It may be tempting to use letters in your Chart of Accounts.  Many ERP’s allow for this option – but resist the temptation!  Numbers have inherent sorting (see Account Banding above).   If you intend to show Operating Expenses, then Sales Expenses, and then Administrative Expenses and on a report and you number these Departments as 100, 200, and 300 you can be assured that they will always be sorted in this order.  If you go the route of coding the Departments as OPERS, SALES, ADMIN you will have made more work for yourself because the default sort will be alphabetically ADMIN, OPERS, and SALES.

Another disadvantage of using letters is where they are on the keyboard.  After all, this is accounting and accountants tend to use the number pad on the keyboard more than the letters.

4. Segments versus Dimensions

A Chart of Account contains multiple building blocks.  At the very least, you need a main or “Natural” account that defines what type of data is entered to the account.  Consider travel expenses; at the highest level account 65100 could represent travel expenses.  If you need further breakdown on this account for reporting, you may combine it with other “Segments” or “Dimensions” such as Branch, Department, or Type of travel.

Microsoft Dynamics GP utilizes a Segment style chart of accounts.

You need to define how many segments you intend to use, how many characters long each segment is, and assign a meaning to each segment.  It is best to enter a value in each segment for every account number even if it means filling the space with zeros.  This provides consistency for data entry and for account sorting.  There is no doubt that “000” is an intentionally entered value while a “blank” may mean the field was accidently left blank.

Natural	Branch	Department	Type	Description
65100	001	200	10	Travel Expense-Branch 1-Sales-Transportation
65100	001	200	12	Travel Expense-Branch 1-Sales-Accomodation
65100	005	200	10	Travel Expense-Branch 5-Sales-Transportation
65100	005	200	12	Travel Expense-Branch 5-Sales-Accomodation
65200	000	200	00	Marketing-Sales

Do not mix your meanings.

If “10” within the Type segment means “Transportation” it should always mean “Transportation”.  Do not get tempted to have “10” mean “Coffee Supplies” when associated with the Office Expenses account.  You will quickly get yourself in a muddle when trying to analyze all the Transportation costs if you query all expenses with Type=10.

Filling in different numbers of characters within a segment can distort sorting so avoid this situation.

Consider the series of numbers 10, 20, 100, and 200.  How would you sort this?  If sorting by smallest to largest the order would be 10, 20, 100, 200.  However, some reporting tools sort by character from left to right and the order would be 10, 100, 20, 200.

Microsoft Dynamics NAV utilizes a Dimensions style chart of accounts.

You still require a main or “Natural” account that defines what type of data is entered to the account.  Above, we used the example of account 65100 to represent travel expenses.  Within Microsoft Dynamics NAV you can define unlimited “Dimensions” to record further breakdown such as Branch, Department, or Type of travel.  However, you do not need to enter every Dimension for every transaction.  This enables a very clean Chart of Account listing while supporting advanced analysis on Dimension values.  A Dimension can even have a hierarchy within it to provide for sub-totalling.

Natural	Branch	Department	Type	Description
65100	001	200	10	Travel Expense-Branch 1-Sales-Transportation
65100	001	200	12	Travel Expense-Branch 1-Sales-Accomodation
65100	005	200	10	Travel Expense-Branch 5-Sales-Transportation
65100	005	200	12	Travel Expense-Branch 5-Sales-Accomodation
65200		200		Marketing-Sales

A Microsoft Dynamics NAV dimension hierarchy.

Microsoft Dynamics NAV provides structure and rules around Dimensions.  You can select Default Dimension Values that will be applied whenever you enter a transaction for a particular Customer, Vendor, or Item.  You can indicate whether a Dimension is required or optional.  You can apply rules indicating that what the Dimension value must be under certain conditions.  The advantages are reduced transaction data entry efforts and increased accuracy.

In a system like Microsoft Dynamics NAV you have the ability to re-number your existing Chart of Accounts or Dimension Values without losing any of the history already posted.  This helps you avoid a string of “Do Not Use” values in your Chart of Account listing.

With a little up front effort, your well-designed Chart of Accounts will be the foundation for capturing and reporting business information used in decision making throughout your organization. Consider it carefully.