The cells are named in the same way as the simple example and can be used in the exact same way. In column F, we use a VLOOKUP to get the value from the query returned by the NF function. In column D, we determine the year/period combo appropriate for each value we need to look up. To make the output easier to read, we’ve split the formula into two columns. With a little more Excel magic, we can calculate the key dates we need: So now we have periods and starting dates. (This isn’t an Excel blog, so I won’t get into the details of why this works.) Next, we combine the two to get a year-period combination. Second, we figure out the year and period based upon the row returned. So, first, we ask users for the starting fiscal year that matches the first period returned in the report. But let’s assume we don’t have that authority. We could accomplish this by changing the names in NAV. 200601 for the first period in fiscal 2006). But we need periods and years to make this useful.Įventually, we want each period-ending date to map to a year-period combination (e.g. Using Accounting Periods Instead of Dates With a 4-4-5 Calendar (since I think in SQL, I originally created a solution using SQL which is at the bottom of the post ). And trying to remember when each period closed last year (or previous years) isn’t something you want to do.įortunately, there’s a way to fix that problem with a solution that works entirely in Excel.

(I’ve included the names I typically use.)īut what if you use a 4-4-5 calendar? Simple Excel formulas won’t do the trick. In the screenshot below, I’ve put in some simple formulas that give you most of the dates you need for basic financial reports. For example, once users enter a year and period, you can calculate the appropriate starting and ending dates for current year and last year. Now, if you’re operating on a calendar year, figuring out the start and end dates is fairly obvious. I’ve used named cells for the starting and ending dates (which makes writing the report easier). The second example will only work if you have a 4-4-5 calendar of your own or you set one up.)Īs you can see, we’ve asked Jet to return the balance of account 41300 from to. (Cronus doesn’t have a 4-4-5 calendar, but it provides a starting point. To figure this out, let’s start with a basic financial statement I built over fictional Cronus data. Popular golf events for private jet travellers in 2018 will include The Masters in Augusta the Ryder Cup 2018 near Paris and the British Open in Carnoustie, Scotland. But did the period end on the 22 nd, 23 rd, or 24 th? It’s easy enough to know that January ended last year on the 31 st, as it did this year. But it’s also different from pretty much every other ERP financial reporting system I’ve used.Īnd it can be especially challenging when you have a 4-4-5 or other non-monthly calendar. And like all the analysis pages within NAV, it’s driven by dates, not accounting periods. The fundamental financial report function in Jet is GL.

Jet calendar 2018