NAV AS 101 Lesson 7: Column Layout

The column layout is where you will define periods of time to be used in your reports. Most financial reports are showing us some period of time compared to the data (in this case, general ledger accounts) we are reporting against. You can apply any column layout you design against any of the row setups you design. 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 for beginning users of account schedules:

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 – Limit yourself to these three options 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 -1P 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 / 2013                                             

Classic Client                                                                          

Fields for experienced users of account schedules:

If you’re looking for what else you can do with Microsoft Dynamics NAV column layouts, then this is where you want to be. Keep reading to understand each and every field available for account schedule column layouts. The functionality of these fields are exactly the same between the Classic and Role Tailored Clients unless specifically noted. 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 / 2013                                             

This is a view of what I recommend you start with for basic column layout options.

This is a view of all available column layout options (click on the picture for a larger view).

Classic Client _________                                                   

This is a view of what I recommend you start with for basic column layout options.

This is a view of all available column layout options (click on the picture for a larger view).

Column layout options – apply to both clients

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: 7 options to choose from

• Formula allows you to perform calculations in a column.
• 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.
• Year to Date should be used to show a total in the column of the balance from the start of the year through the current filtered date.
• Rest of Fiscal Year can be used to show, for budgeted information, what is planned for the remainder of the fiscal year.
• Entire Fiscal Year can be used to show, for budgeted information, what has been planned for the entire fiscal year.

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 -1P for previous period and FY for fiscal year or -1FY for prior year.

Comparison Date Formula – This column allows you to define date formulas that are used to calculate the amounts shown.  However, the comparison date formula field references the calendar months of the year instead of the accounting periods set up in the fiscal year. Common data labels used in this field are CM for current month or -1M for previous period and CY for calendar year and -1Y for prior calendar year.

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.

Business Unit Totaling acts like a bonus dimension when producing consolidated financial statements from more than one company, designating a unique business unit per company.

Cost Center Totaling and Cost Object Totaling *New with NAV2013* These are new functions related to totaling of new cost center and cost objects.

Line No. is automatically designated by the software.

Rounding can be set for none, 1, 1000, or 1000000. Just like the show function, this only affects numbers shows on the printed account schedule. You’ll still see numbers rounded to two decimals on-screen or when you export to Excel.

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.

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.

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 part of the NAV Account Schedules 101 series.  Find the entire list of lessons here.

Don’t forget to visit the Account Schedule Formulas and Account Schedule Examples pages if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Microsoft Dynamics NAV.