Aging methods in NAV – which buckets are you looking for?Posted: October 30, 2012 Filed under: Uncategorized | Tags: accounts payable, accounts receivable, aging, aging method, bucket, document date, due date, invoice date, NAV, overdue, payment terms, posting date, tips and tricks, transaction date Leave a comment
When running any type of financial report, it’s important to ask the right question in order to get the right answer. When running your accounts payable or accounts receivable aging reports, it’s especially important since there are three available options that will give you the same total answer but will break your transactions down into different aging bucket categories. Choosing the correct option for the question you actually want to answer is the key.
Question #1: How overdue is the invoice?
By choosing the Aging Method of Due Date, you are asking NAV to age each bucket on your aging in intervals based on the Due Date of the invoice. Remember the due date is based on a calculation that applies payment terms against the document date of the invoice.
Question #2: How many days have passed since the invoice was posted?
By choosing the Aging Method of Transaction Date, you are asking NAV to age each bucket on your aging in intervals based on the Posting Date. This is sometimes confusing to new users since on both the purchase and sales invoice forms, the two available dates are posting date and document date. The posting date should always be the date you want to post the invoice to your books and therefore the fiscal period the invoice will report in. For the purposes of your aging reports, posting date is equivalent to transaction date.
Question #3: How many days have passed since the vendor billed us – or – since we billed the customer?
By choosing the Aging Method of Document Date, you are asking NAV to age each bucket on your aging in intervals based on the Document Date. This one makes a little more sense to folks because at least the terms are the same. But darn us accountant types, we often call the document date the invoice date when we’re referring to it. Basically, this date should be the date your vendor has provided on the invoice, or from the customer side, the date you shipped and therefore the date you provided on the invoice to the customer. Any payment terms defined on the account will use the document date to calculate the due date.
An example of two invoices shown with all three aging methods
In a perfect world, our posting dates and document dates would all be the same. Let’s pick a perfect example with posting date of 11/01/10 and document date of 11/01/10 and payment terms of Net 21. This would calculate a due date of 11/22/10 on this invoice.
Then let’s pick an imperfect example. Let’s pretend someone at our company turned in that 11/01/10 invoice sometime in December, after the November books were closed. We still need to book this late invoice, so we’ll choose a posting date of 12/01/10, but since it was not the vendor’s error, we’ll use their invoice date of 11/01/10 in order to calculate the payment terms correctly.
Now, let’s look at an aging as of December 15th, 2010, all three ways. Take note that for all three methods, the balance due is exactly the same. The differences appear in how the aging buckets are defined and how transactions age into the different buckets.
Aging Method of Due Date
Key difference here is the aging buckets start calculating at the due date, so you’ll see column headers of current, up to 30 days, 31-60 days and over 60 days only when using this aging method.
Aging Method of Transaction Date
This aging is based on the posting date, the date that corresponds to the fiscal period you posted the invoice in. Note the late invoice with a 12/1/10 date shows as current.
Aging Method of Document Date
This aging has the same buckets as the one run with transaction date, but is based on the document date or invoice date.
This posting is one of the Top 20 Most Viewed in the last year! Follow this link to see the entire list.
Tis the (Budget) Season! Three handy NAV account schedule tips to get you throughPosted: October 23, 2012 Filed under: Uncategorized | Tags: Account Schedules, budgets, column layout, NAV, row setup, tips and tricks Leave a comment
I don’t know about your company, but my company is in full budget preparation mode. We’re working on getting our sales forecast and operating budgets put together in time for the end of year. Like most companies, we’re working on this predictive tool while we’re in the midst of finishing out the year, so of course, we get to make a few assumptions on how the year will end up. I don’t have a crystal ball, but I do have some pretty great account schedules that help folks at my company get a good solid look at where we ended up last year, what we planned for this year, and how we’ve done over a couple of years, and I’m going to share those three account schedule column layouts with you today. Hopefully these simple setups will give you some quick ways to wow your company and make their budget construction process easier.
Tip 1: Show 12 months of actual data
We ask our budget managers to give us a twelve month prediction, why shouldn’t we give them twelve months of data to work with? No matter what month you’ve just closed, you can quickly put together an account schedule that shows all of the completed months for the year (in this case, January-September) as well as October/November/December of the prior year. This way, if your activity is generally the same year over year, budget managers can get a quick gauge from this setup. Keep in mind this column layout isn’t dynamic, so it’s not a true rolling twelve months. For budgeting purposes, it’ll get you what you need.
Tip 2: Show 9 months of actual data and show what the plan is for the last three months of the year
Another way to provide a projection for the year is to show all the completed months for the year (again, January-September) and then show the budgeted plan for October/November/December. This method shows actual activity as well as three months of planned activity in order to project the total for the year. If activity this year is significantly different (higher or lower) from the year prior, you might depend on this view instead.
Tip 3: Show a whole bunch of history at a high level
Especially for sales forecasting, it sometimes gets difficult to look at too much data at once. One way to get past data analysis overload is to provide a few years’ worth of annual data. This column layout will give four years of data, helping you to compare not only year over year, but multiple year trending in your data.
All three of these tips should give you a few additional tools to help you make sense of a whole lot of data. They can be applied to almost any row setup you have whether that setup is related to revenue or expenses. If you’re looking for more formulas, visit the account schedule formulas page.
This posting is one of the Top 20 Most Viewed in the last year! Follow this link to see the entire list.
Challenge to the NAVUG Community: NAV Account Schedules formulas cheat sheetPosted: October 15, 2012 Filed under: Uncategorized | Tags: Account Schedules, challenge, cheat sheet, formulas, NAV, NAVUG, NAVUG Academy, tips and tricks, user group Leave a comment
I had the opportunity today to teach a full day class on account schedules to an amazing engaged group of people! I get the privilege of doing this in person once a year at NAVUG Forum thanks to NAVUG Academy. In the last year, I’ve branched out to teach this same class in webinar format, and boy has it been a blast. The number one question I get is, “Do you have a cheat sheet for all the formulas you can use in Account Schedules?”
I’ve shied away from this in the past, because it’s a complex question. You’ve got to take into account comparison period versus comparison date, column type used, use of filters, and like all Microsoft products, there is often more than one way to accomplish the same result. But I think I’ve got us a start with the page I’ve just put out on my blog.
I believe the power of community is an amazing thing. We continually make each other better by sharing information.
I CHALLENGE YOU TO SUBMIT A COMMENT WITH AN ACCOUNT SCHEDULE FORMULA THAT’S NOT YET BEEN LISTED.
As long as I can test it to prove it works in the way described, and it’s different enough from already listed formulas, I’ll add it to the list and make sure to put a shout out to the contributor on the blog.
I’m looking forward to seeing what our amazing NAVUG community can accomplish!
Ready, get set, network! NAVUG Forum is prime time for making connections.Posted: October 9, 2012 Filed under: Uncategorized | Tags: CardMunch, community, Forum, LinkedIn, NAVUG, networking, Qrious, user group 1 Comment
It’s less than a week before we get to show up in Seattle for NAVUG Forum to connect with other professionals who use NAV in their jobs every day, just like we do! I’m making my “gettin’ ready to network list” and thought I’d share. These are my networking essentials:
1) Get that LinkedIn profile updated. Put a new picture up there so folks will know what you look like today. I like to go professional for me, but I don’t mind seeing a casual picture out there of other folks, so long as it’s current and helps me to recognize them. Almost anything is better than a blank photo spot. Make sure your basic info is updated, and you’ve got your top 10 skills and expertise out there. Another good reason to do this is, for the first time ever, the user group will be using Qrious, a phone app that will allow you to scan QR codes on attendee badges to get you to their designated contact information. That could be a company website, a blog, a Twitter or Linked in profile, or whatever they direct you to.
2) If you’re not a regular Twitter user, brush off that account and make sure you’re ready to use it. A lot of fun things can happen in the NAVUG Twitter-verse during Forum; it’s a great way to know what’s going on, where folks are at, and what the big WOWs of the day have been.
3) Updated business cards. I’m going to go out on a limb and say that the old business cards are racing toward obsolescence, but they’ve got their place, and I still use them. They’re a quick way to get information into someone else’s hands and promote your company.
4) Spend some time reviewing contacts you’ve made in the past. I had an old dog-eared stack of business cards that graduated to a box because I couldn’t hold them all anymore. Once I got to that point, I got organized with CardMunch, an app that allows you to scan the card with your phone and then uses Mechanical Turk to transcribe the card into a contact record and locate the person’s LinkedIn profile, if they have one set up. You can even add notes to the record, just like writing on the back of a business card. I can flip through images of the actual cards, or the profiles I’ve gathered, when I’ve got a few extra minutes. Highly recommended.
5) Charging Cords! There’s no bigger bummer than to have your phone go dead in the middle of all the action.
6) A pack of your favorite breath mints. For real now, none of us are getting enough water, we’re eating stuff we don’t normally eat, probably not getting enough sleep, and we’re in close quarters all day. A mint never hurts, and it’s nice to be able to offer one to someone else.
7) The list of people you want to meet, even if you don’t know who they are yet. Every conference I make a list. Sometimes, I’ve got a person’s name on the list with a note “follow-up about the comment on ACH procedures with Lee”. Other times, I’ve got a note “find someone who knows about managing VAT with mixed standard and zero-rated items on consignment”. I’ll even throw in some “stop by the help desk and brainstorm that new idea on managing dimensions” or “informally poll finance users to see if they’d be interested in webinars on AP best practices”.
8) Adjust your attitude to be ready to interact. Lots of the folks who attend Forum are IT and Finance professionals. You’ve gotta admit, we don’t always have the best reputation for extroversion. Make it a part of your mindset to mentally get out from behind your computer (or your phone). Meal times and break times are prime time to get discussions going with a few people at a time. Take note of someone in a session who asked an interesting question, or who looked puzzled, or maybe who looked like they didn’t know anyone yet. Make eye contact, smile, and ask questions.
7) Comfy shoes. What do shoes have to do with networking? When I’m having my last conversation after talking with folks all day, I don’t want to be shifting around because my feet hurt and I’m sure not going to let sore feet get in the way of making it across the Expo hall one more time because there is one more person on my list I need to find.
8) Prepare to follow-up later. Don’t let all this prep go to waste! Even as you’re meeting new folks, remember that you’ll want to follow-up with people once you get back home. It’s always nice to hear from someone you met, and to establish a connection with someone who really understands what you do professionally. You never know now how you might be able to help them, or how they might be able to help you. The opportunity we all have to build an engaged community of NAV users who help each other exists because we all show up ready to connect, learn, and share.
See you in Seattle!
Expand your use of dimensions in NAV account schedules by using analysis viewsPosted: October 2, 2012 Filed under: Uncategorized | Tags: Account Schedules, analysis views, budgets, Classic Client, codeunit 410, column layout, date compression, dimensions, Dynamics, Microsoft, NAV, row setup, RTC, update Leave a comment
If I had to pick only three basic elements to Microsoft Dynamics NAV account schedules, I’d have to choose row setups, column layouts and analysis views. Of course, row setups give you access to basic general ledger accounts and column layouts give you options on how to show that data in different time periods. So what do analysis views do for us? Analysis views give us the ability to reach into our dimensions, past the two globals, and into the list of shortcuts, allowing us to combine any four dimensions we want at a time in any account schedule.
For illustration, I’m showing you an account schedule from NAV2009 Classic, which shows the dimension filters on an account schedule where no analysis view has been applied. The two global dimensions for CRONUS USA (Department and Project) are available to be selected on the dimensions filter tab and the remaining options are greyed out, unable to be used.
By selecting a different analysis view on the account schedule name page, you will have expanded options. Perhaps you’d like to apply an entirely different set of dimensions filters to your account schedule or limit the dimensions filters to only two of your shortcut dimensions. Any combination of four dimensions is available to you through analysis views.
There are a few things you need to know about analysis views before you start using them:
You can add a default analysis view to any account schedule. If you always want a certain account schedule to filter on a selected group of dimensions, this is the best way to do this.
Analysis Views must be updated. You can do this at any frequency you wish. Some companies choose to update once a month, some companies update once a day. Be aware that the process of updating pulls in any transactions that have been posted since the last update to your analysis view. This means if you post some entries during your close process, you’ll need to update your analysis view in order to show the change on your account schedule.
Updating can be done manually or it can be automated. To manually update analysis views, just hit the update button. You’ll need to do each one separately. Alternatively, you can choose to schedule codeunit 410 Update Analysis View as a regularly scheduled maintenance item in order to automate this process.
When you set up an analysis view for the first time, it can take a long time to update. Depending on the size of your database, if you don’t limit how far back your update goes, it could take a long time to update initially. Be careful by trying this out in a test system first. This process, which will normally take seconds when run daily, will take many hours if you don’t limit it and will cause table locks for other users.
If you test out an update and are afraid it will take up too much processing time, you have a couple of options. First, you can limit the start date of your update. One reason you might need a new analysis view is because you’ve added a new dimension. In this case, you really don’t need to go back to the beginning of time on your update. Choose the date you started gathering data on your new dimension as your starting date. You can also choose date compression. By compressing your data by day, week, month, quarter, period, or year, you limit how much detail you can see when you drill down. If you use this option, you’ll need to remember that this is a compressed view if you change column layouts to different time frame than your compression setting. Choosing date compression of none will allow you to drill down to full transactional detail.
Avoid using the update on posting button. This option updates your analysis view every time you post something to your system. Every sales order, every sales tax entry, every cash receipt, etc. will update real time. I’ve seen one small company use this option and it brought their system performance to a crawl.
Remember to include your budgets. If you make any changes to your budgets, you need to update that information on your analysis views as well.