NAV AS 101 Lesson 10: Comparison Period v. Comparison Date

NAV Account Schedules uses a curious convention to determine periods of time in the column layout. The default option if you open account schedules for the very first time is Comparison Date, but I always recommend NAV users use the other option, Comparison Period.  Let’s talk about why.

cpvcd

Comparison Period is more flexible

If you read the NAV help on this topic you’ll find a fairly long listing of what you can do with comparison period and a very short entry on what you can do with comparison date. That’s because you can just do a lot more with comparison period. Comparison dates use standard NAV date formulas that are really more appropriate for use in payment terms and journal entries.

I’ve copied both entries below, directly from the NAV help menus, for easy reference.

Comparison Date Formula Field

This field contains a date formula that specifies which dates should be used to calculate the amount in this column.

The program uses this formula to calculate the amount from the comparison period in relation to the period represented by the date filter on the report request.

For example:

-1Y

Same period 1 year earlier

Comparison Period Formula Field

This field contains a period formula that specifies which accounting periods should be used to calculate the amount in this column. An accounting period does not have to match the calendar, but each fiscal year must have the same number of accounting periods, even though each period can be different in length.

The program uses the period formula to calculate the amount from the comparison period in relation to the period represented by the date filter on the report request. The comparison period is based on the period of the start date of the date filter. The abbreviations for period specifications are:

P

Period

LP

Last period of a fiscal year, half-year or quarter.

CP

Current period of a fiscal year, half-year or quarter.

FY

Fiscal year. For example, FY[1..3] denotes first quarter of the current fiscal year

 Examples of formulas:

<Blank>

Current period

-1P

Previous period

-1FY[1..LP]

Entire previous fiscal year

-1FY

Current period in previous fiscal year

-1FY[1..3]

First quarter of previous fiscal year

-1FY[1..CP]

From the beginning of previous fiscal year to current period in previous fiscal year, inclusive

-1FY[CP..LP]

From current period in previous fiscal year to last period of previous fiscal year, inclusive

Comparison Period reduces the possibility of accidentally reporting incorrect numbers

This is a classic example of an account schedule I see in use all the time by beginning account schedule users. Here is a 12 month net change column layout design using Comparison Date.

13

Here is a 12 month net change column layout design using Comparison Period.

12

The primary difference between the two methods is that comparison period fixes the fiscal periods when you report and comparison date is wholly dependent upon what your date filter is set for.

If you are producing your financial statement package at the end of the month, you’ll produce your income statement, balance sheet, statement of cash flows and any other reports you use with the date filter set for the end of the month you are reporting against. When you get to this report, using comparison date, you’ve got to remember to manually switch your date filter to the end of the year, because the formulas calculate backward from the date set in order to produce the report.

This is very risky because if you forget to do this, you can very easily forget to not switch the date. In this case, the report will still produce 12 columns of numbers, but they’ll be wrong, because they are using the date from your date filter as your “year end” date, which is incorrect. When using the comparison period setup in the way shown above, you can change your date filter all you want within the current fiscal year and you’ll get the same correct result every time.

If you use an accounting period other than calendar month, you’ve got to use comparison period

You just can’t use comparison date if you’ve got 13 periods, if you use a 4-4-5 convention, or if you’ve got anything other than 12 calendar months defined as your fiscal periods.

Comparison date, what the heck’s it good for?

Despite everything you’ve read above, comparison date does have one really good use, and that is if you need to report on something on a daily or even weekly basis. Comparison period can’t go below the fiscal period unit, but comparison date sure can. Here’s an example of a column layout that defines time periods of daily for a week. Remember, in order for this to work, the date filter needs to be set to the last day of the week.

211

Where can I learn more about comparison period and comparison date?

Visit the Account Schedules Formulas page of this blog. This page was developed to answer the many questions I have gotten in my account schedules classes about what else you can do using comparison period. The entire page is full of screen shots, just like the ones you see above, so you can reference them or even copy them exactly, as you create your own account schedules.

 

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.


Week Four Recap: Account Schedules 101

repeatHere’s the week 4 recap on the NAV Account Schedules 101 series.  Follow the links below to view the posts for each of the lessons we covered this week or view the entire series here.

NAV AS 101 Lesson 8: Formatting

NAV AS 101 Lesson 9: Formulas

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.



NAV AS 101 Lesson 9: Formulas

Formulas in Microsoft Dynamics NAV Account Schedules are the hardest concept for most users to grasp, because their use is just not intuitive. There are a few basic concepts that can help get you started.

When getting started with a row setup, you need to choose either Posting Accounts or Formula in the Totaling Type column. These two options indicate whether you will be pulling numbers from general ledger accounts (posting accounts) or calculating against other rows (formula). There is another option Total Accounts that lets you pull numbers from total accounts from your chart of accounts instead if you are using total accounts instead of or in addition to posting accounts. Once you have defined your option in the Totaling Type column, then you need to populate the Totaling column with either general ledger accounts or formulas, depending on what you have chosen.

f1

If you are a dimension user, you need to expand your selections to specific columns available in the row setup for dimensions. These are Dimension Totaling columns. The two global dimensions will always be available for use, but any other dimensions will appear with a label that is Dimension 3 Totaling or Dimension 4 Totaling (etc.) until you define which specific dimensions you want account schedules to use by defining them in analysis views. By filtering on dimensions in these columns, you can report at a deeper level and break down the segments of general ledger accounts to their dimension components in your financial reporting.

f2

When getting started with a column layout, you need to choose either a definition of time like Net Change or Balance at Date or Formula in the Column Type column. These two options indicate whether you will be pulling numbers from general ledger accounts (posting accounts) or calculating against other columns (formula). If you have chosen Formula as your Column Type, you need to fill in the formula. The same options for Dimension Totaling are available here, just as they are for the row setup.

f3

All the rest of the information you need about account schedule formulas happens in the Comparison Period Formula and Comparison Date Formula columns. There’s so much information there, we need to address it in a post of it’s own. Stay tuned, it’s coming up next!

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.


NAV AS 101 Lesson 8: Formatting

Formatting options for Microsoft Dynamics NAV are pretty basic, but there are a few tricks you should know to get the most out of what you have. Because very little has changed with formatting options in newer versions I’ll show some basics in NAV 2009 Classic, but highlight some expanded features in NAV2009 RTC and NAV2013 so you know the full range of what you can use.

This is an example showing the row setup, screen view, Excel export, and printed views with no formatting of any type assigned. It’s pretty ugly no matter which way you look at it.

no format

Let’s make this look better by applying some basic formatting options. We’ll bold the total line, underline the line above the total, italicize the second line, and choose show opposite sign on all the revenue lines, including the total line, so our sales are reflected as a positive number instead of as a credit balance. Looking better already, but there are a few things going on that need to be explained.

Underline – Can’t see it on the screen view, appears across the entire page including row descriptions in Excel export, and shows just under the numbers in the printed view.

Italic – Can’t see it on the screen view, but can see it on both the Excel export and printed view.

2form

We can improve on what’s going on with the underline with a few additional options. There is an option in the totaling type column for underline, and after NAV2009 SP1, there is an option for double underline. The screen shots below show the following differences. Notice they don’t make much of a difference at all on the screen view or Excel export versions, and have the largest impact on the printed version. NAV2013 has a new additional checkbox for double underline, but it still behaves the same way, only showing up on the printed version.

B – Just the underline checkbox is checked. If you want the underline situated tightly to your numbers without extra space in the printed view, this is the way to do it.

C – The underline checkbox is checked and the totaling type of underline is selected. This gives a wide double underline in the printed view only.

D – The totaling type of underline is selected. If you want a little bit of space below your numbers before the underline appears in the printed view, this is the way to do it.

E – The totaling type of double underline is selected.

u1u2u3u4

Similar to what we see with the different underline options, the show option also has limited utility. Let’s choose No instead of Yes on the second line of our example and see what we get. As you can see, the show option only hides a line in the printed view. There is actually a show option that is also available in the column layout, but again, it only hides a column in the printed view.

show

There is one additional formatting option that only exists in the column layout – the rounding factor. Selecting this option, just like show, only allows the changes to go through to the printed version, but if you are using that option you can round to a factor of 1, 1000 or 1000000.

colform1colform2

One last option here that I haven’t put together an example for is the New Page option. Use this option if you need to insert a page break anywhere in your report. I’ve seen lots of companies use this to produce a two page balance sheet. I’ve also seen companies build some pretty large multi-page account schedules in one named report and break each page by using this option. I think it is easier to maintain and update short simple reports if at all possible, so I recommend creating a few (or many!) separate account schedules instead of doing this. It’s just too easy to miss something, or at least spend a lot of time looking for, something you need to update in a large multi-page report kept in a single account schedule.

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.


Week Three Recap: Account Schedules 101

repeatHere’s the week 3 recap on the NAV Account Schedules 101 series.  Follow the links below to view the posts for each of the lessons we covered this week or view the entire series here.

NAV AS 101 Lesson 6: Row Setup

NAV AS 101 Lesson 7: Column Layout

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.


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.

1

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

rtc

 

Classic Client _________                                                   

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

2

 

 

 

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

classic

 

 

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.