Monthly Reporting for 4-4-5 Accounting Periods

Monday, May 8, 2017 - By Jon Gardner

..using Snippets and working with Accounting Period GB table in NAV

The purpose of this post is not to go into the reasons, benefits or otherwise of  the 4-4-5 Accounting conventions sufficing to say that it does cause NAV/Jet clients a few problems when trying to create periodic/monthly reports.  The issues arise primarily because 4-4-5 accounting uses Period No’s rather than Calendar Months – and converting Period No’s to the dates needed to drive Jet reporting does sometimes require a little bit of creativity….

The Easy Way – Use a Snippet!

The easy way around this is very often to create a Snippet in Jet – simply manually create the period headers, start and end dates for the chosen year and save as a snippet!  For each timeline report simply sweep-in the snippet as you start the new report and everything is in place (especially the start and end dates) for your report.  Simple – as far as it goes…..

The Better Way

The snippet way works well  – create a snippet for each financial year and away you go!  The alternative, which perhaps gives more flexibility when doing analysis (eg. this year v last year), is to use Jet to convert the financial years and periods into easy start and end dates.

Unfortunately it seems that no two NAV partners set up the Accounting Period GB table in exactly the same way; there doesn’t seem to be a set convention for this, so depending on your own setup there may be some variations.  So here’s the data for this example –

The issue in this case (in our experience very typical) is that there is no relationship between the Period No. and the Year.  How much easier this exercise could have been if the Period No. field had been populated with Year-Period syntax rather than just 1..12 (NAV Partners please note!!)

Anyway, we have to work with the data we have.

In building the report we have just one menu option/filter – Financial Year. On our Options Sheet we have the Lookup expression set up as follows –

=NL(“Lookup”,”Accounting Period GB”,”Period Name”,”Period Type”,”Year”)

The value field in the Lookup has been named as DFILTER – so Cell C5 in the report itself picks this up as

• =NP(“Eval”,”=DFILTER”)

In cells C6 & C7 – both hidden in this report – there are two expressions to return the data parameters for the financial year we require –

• =NL(“First”,”Accounting Period GB”,”Period Start”,”Period Name”,$C$5)
• =NL(“First”,”Accounting Period GB”,”Period End”,”Period Name”,$C$5)

At the top of the report itself  there is another hidden cell – Cell D11 – in which there is a manually input 0 (zero).  Row 12 therefore now reads as follows –

• Cell D12: =+D11+1
• Cell E12: =NL(“Rows”,”Accounting Period GB”,”Period Start”,”Period Type”,”Month”,”Period Start”,NP(“DateFilter”,$C$6,$C$7))
• Cell F12: =NL(“First”,”Accounting Period GB”,”Period End”,”Period Start”,$E12,”Period Type”,”Month”)  

The Rows function in Cell E12 drives the report by returning the month start dates within the range that were calculated previously in Cells C6 & C7. As the rows are replicated down the page the Period No. column (Column D) increments by one for each row and the Period End date (Column F) is calculated based on the Start Date in Column E.

As with all things Excel there is always more than one way of doing it, and depending on the setup of the Accounting Period GB table one may have to use variations of the above.  If you get stuck, don’t have the Accounting Period GB table (WW & US releases of NAV have the Accounting Period table which is set up slightly differently), have any questions or feedback please do not hesitate to contact us by clicking here – Contact Us