Account Schedule Formulas

This list was developed as a challenge to the NAV user community based on this blog posting.  If you have a formula you’d like to share, go to this link and submit it.  As long as I’m able to prove it works as described by replicating it and it is different from other formulas already listed, it will be added to the list and the contributor will be credited in a posting on the blog.  The knowledge we share as a community makes us stronger!  Thanks for participating!

[ Looking for even more information about how to use NAV Account Schedules? Try the NAV Account Schedules 101 series.  Find the entire list of lessons here. ]

__________________________________________________________________________________

Notes and Disclaimers

Any formula with ** in front of it is considered a Best Practice.

To get budgeted numbers on any of these options, change the Ledger Entry Type from G/L Entries to G/L Budget Entries.

All examples assume a calendar fiscal year for ease of illustration but, when using comparison period, can apply to fiscal years with non-calendar periods.

___________________________________________________________________________________

One Day of Activity     Date filter must be set to “1” or “DAY”.

**Current Period     If date filter set to May, gives May. If December, gives December.

Current Period (alternative)     Simply leave formulas blank. If date filter set to May, gives May. If December, gives December.

**Year to Date     If date filter set to May, gives Jan-May. If December, gives Jan-Dec.

Year to Date (alternative)     Simply leave formulas blank. If date filter set to May, gives Jan-May. If December, gives Jan-Dec.

Year to Date (alternative)     Simply leave formulas blank. If date filter set to May, gives Jan-May. If December, gives Jan-Dec.

For the Entire Year     Simply leave formulas blank. Gives Jan-Dec no matter what the date filter. Most commonly used for budget entries.

Remaining Amount for the Year     Simply leave formulas blank. Most commonly used for budget entries. Can also accomplish same result by using a formula to subtract year to date from entire fiscal year.

**Prior Year to Date     Gives balance one fiscal year prior to date in filter.

Prior Year to Date (alternative)     Gives net change of the same month one fiscal year prior to date in filter.

**Same Period of the Prior Year     Gives net change of the same month one fiscal year prior to date in filter.

**Net Change, 12 Months of Fiscal Year     Gives monthly activity, or net change, for twelve months.  Most commonly used for income statement accounts.

Net Change, 12 Months of Fiscal Year (alternative)    Gives monthly activity, or net change, for twelve months.  Most commonly used for income statement accounts.  A more risky method to use because of its dependency on the date filter being set for the last month of the fiscal year in order to calculate correctly for each month.

**Balance at Date, 12 Months of Fiscal Year     Gives balance of each month for twelve months.  Most commonly used for balance sheet accounts.

Balance at Date, 12 Months of Fiscal Year (alternative)     Gives balance of each month for twelve months.  Most commonly used for balance sheet accounts.

**Specific Period     In this example, gives the 8th period of the current fiscal year no matter what is in the date filter.

**Specific Multiple Periods     Gives a summarized calculation for the first six periods of the current fiscal year in one column.

**Specific Period of the Prior Year     Gives the 8th period of the current fiscal year no matter what is in the date filter.

**Quarterly Period     For each column, shows one quarter of net change.

**Three Years in Columns     Gives three years of activity.

**Last Seven Days     Date filter must be set to the last day of the week.

**All Days in a Month     Date filter must be set to last day of the month.  Must have separate column layouts for 31/30/29(leap year)/28 day months.