Blogiversary Top 20 (#14) Video Demo: How to export NAV account schedules to Microsoft Excel
Posted: April 10, 2013 Filed under: Uncategorized | Tags: Account Schedules, dimensions, Excel, export to excel, financial statement, learning, NAV, NAVUG, NAVUG Academy, training Leave a commentWe’re celebrating our one year blogiversary by reposting the Top 20 Most Viewed in the last year, as determined by you, our readers! Follow this link to see the entire list. Enjoy!
There are some quick and easy ways to publish NAV Account Schedules to Microsoft Excel but some of them end up with some pretty ugly formatting unless you know a few secrets on how to make them look better. This short demo video shows you how to create a nicely formatted NAV Account Schedule in Microsoft Excel that only requires formatting the first time you set it up. Each time you replicate the report after that, you can simply update the report with new numbers and the formatting will remain the same every time.
While this demo shows how to create a financial report with three monthly tabs, you can easily extend this technique in a couple of different ways. I use this same technique to produce my monthly financial statement package. I have eighteen pages in the package I produce each month, and each page is a different account schedule. When I start a new month, I copy the Excel workbook and rename it with the new month, then use exactly this same technique to produce the new month’s reports. By using this technique, the new copied workbook acts as a “shell” to receive my new numbers. From month to month, I am able to maintain consistent formatting in my reporting while producing my financial reports in the most efficient way possible. I can apply the same principles to my monthly team budget reports. In this case, I can produce the same actual versus budget report over and over with a new dimension filter applied for each team, one per each Excel worksheet in the workbook.
If you’d like to learn more about account schedules, please visit my 2013 Classes page. This short video demo comes from my Account Schedules Basics class which I teach through NAVUG Academy. This class offering is just one of many classes that are being offered by NAVUG Academy in 2013 to help you get more out of your use of NAV at your company.
Blogiversary Top 20 (#15) Why NAV users should be using dual monitors
Posted: April 9, 2013 Filed under: Uncategorized | Tags: dual monitors, Excel, NAV, paperless, productivity, ROI Leave a comment
We’re celebrating our one year blogiversary by reposting the Top 20 Most Viewed in the last year, as determined by you, our readers! Follow this link to see the entire list. Enjoy!
My company began using dual monitors a few years ago for power users, and have been putting them into place for all users this year. Does this change make a real difference in user productivity?
Some real world examples of what my team uses dual monitors for:
- More than one NAV session at a time. As long as you have the licenses for it, you can have more than one NAV session open at a time. This is great for when you need to run a report that ties up your session for a while like the AR aging or AP aging or inventory valuations. Use one session to run these reporting hogs in, and another session to look up information for something else you’re working on.
- Answering email questions. Open up that email on one screen, and reference the information you need on another.
- Spreadsheet stretch. Sometimes when you have a ton of data to deal with, it sure helps to see it all in one place.
- Remote access. If you’re in more than one computer at a time, perhaps your laptop and also a computer back at the office via remote access, being able to see one on each screen is a big help instead of hitting Alt-Tab all day.
- Using the help menu or other documentation. Are you trying to figure out how to use something new? Put up the application on one screen and the help menu (or user guide) up on the other screen.
- Comparison. Whether you’re looking up more than one option on the internet or comparing what you have in your production versus development databases, using dual monitors is a great way to see what you options you have without having to switch between screens to remember what you saw.
- Connecting with your customer. When on the phone with a customer, our customer care team has NAV on one screen and a view of what our customer is looking at on our website on the other. This allows them to better answer the customer’s question about the product, and also make suggestions later as to how we can improve on the customer experience.
- Entry to NAV on one screen, information on another screen. If your work is transactional, and you need to enter invoices on one screen, but reference a document on another screen, this is a great way to use dual monitors. Don’t print out that document, just put it up on your second monitor.
- Paperless processing. Ultimately, dual monitors make paperless processing possible for my company. We’re working on a paperless initiative in our accounts payable area right now which couldn’t have happened without dual monitors. Getting users to reference documents on-screen has been an easy, natural transition and while we’re saving a tree or two, the better argument has been the time saved in not printing, organizing, and filing those documents.
Return on investment of dual monitors
If you’re not convinced yet, take a look at the ROI of dual monitors. Let’s say a second monitor costs $200. Estimates of productivity gains range anywhere from 10% – 50%. If you have an employee who generates $200,000 of revenue annually, at a conservative estimate of 10% productivity gain, this could equate to a $20,000 increase in revenue; a one hundred-fold payout on your initial investment.
Blogiversary Top 20 (#16) Tips and tricks for a flawless budget load in NAV
Posted: April 8, 2013 Filed under: Uncategorized | Tags: budgets, Excel, export to excel, NAV, tips and tricks Leave a commentWe’re celebrating our one year blogiversary by reposting the Top 20 Most Viewed in the last year, as determined by you, our readers! Follow this link to see the entire list. Enjoy!
Hopefully, you’ve completed your budget work for 2013 and all you need to do to finish is to load all that information to NAV so you can begin to report against your actual financial numbers. If you’ve done this before, you might already know that NAV’s budget tool can be a bit fussy and also a bit cryptic about why it won’t accept your carefully prepared data. Here are four quick tips to help you with getting that data into NAV quickly, correctly, and in one try.
1. Export first, then import. This is the single most important detail about getting budget data loaded into NAV. You can choose to export an existing budget or even choose to export a blank new budget. Exporting a budget as your first step establishes a working template you can populate your data into, including dimensions. As long as you start with this template, you are already most of the way there to a successful NAV budget load.
2. If you’re using dimensions, validate your data against the provided drop downs. Make sure all budget lines that use dimensions are only using valid dimension names. Any deviation from the allowable values that already exist in NAV can cause your budget load to error out or load without balancing.
3. Clear the formats from your numbers. Once you’ve copied and pasted or typed your numbers into your Excel template, use the Excel “Clear Formats” function on all cells that contain a numeric value to make sure they are all returned to a formatted status of general. NAV does not accept any other format than the one in the template, and use of other formats, including use of commas in the numbers, will cause the budget tool to give an error and keep you from loading your budget successfully.
4. Use the “Add Entries” option for a brand new budget load and the “Replace Entries” option for a subsequent version. The add entries option should only be used for a brand new budget load, otherwise the entries will be added on top of the already existing entries, doubling or tripling them. If you need to load a second or third version or some type of correction, always use the replace entries option instead of the add entries option. If you really get stuck with a bunch of errors, the best thing you can do is delete your budget and reload from scratch.
Take one last look through your data to make sure it’s accurate and exactly what you expected. You can use the budget tool to do this, or even better, put together a quick account schedule that shows your entries using “G/L Budget Entries” instead of “G/L Entries”. Make sure to look at your total balances, balances by fiscal period, and balances with dimension filters applied. Once you’re satisfied that all your budgeted data has loaded correctly and completely, you’re ready to produce financial reporting showing actual versus budgeted numbers!
Blogiversary Top 20 (#17) Tis the (Budget) Season! Three handy NAV account schedule tips to get you through
Posted: April 5, 2013 Filed under: Uncategorized | Tags: Account Schedules, budgets, column layout, NAV, row setup, tips and tricks Leave a comment
We’re celebrating our one year blogiversary by reposting the Top 20 Most Viewed in the last year, as determined by you, our readers! Follow this link to see the entire list. Enjoy!
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.
Happy Budgeting!
Blogiversary Top 20 (#19) The NAV accountant’s little helper: the reconciliation account check box
Posted: April 3, 2013 Filed under: Uncategorized | Tags: balance after posting, Classic Client, general ledger, journal entry, NAV, net change, posting test report, reconciliation account, RTC, tips and tricks Leave a commentWe’re celebrating our one year blogiversary by reposting the Top 20 Most Viewed in the last year, as determined by you, our readers! Follow this link to see the entire list. Enjoy!
There’s a tiny feature in NAV that I take for granted. It’s there, in the background, hiding in the setup, and it helps me out just a little almost every time I make a manual journal entry. Especially if I’m in a bit of a rush, or distracted, this option gives me one more quick but effective look at what I’m doing before I commit a journal entry to my books permanently. This nifty little thing can be added to your NAV setup at any time without any help from IT and won’t require any maintenance. The option I’m talking about is the Reconciliation Account check box you can find on your general ledger account card. Here’s where you can find it:
Role Tailored Client
Classic Client
For any account where you’d like this additional double check, check the Reconciliation Account box found on the General Ledger Account Card. Now go put together your journal entry.
Instead of posting directly from the entry, choose Actions=>Posting=>Test Report=>Preview (if you’re RTC) or just choose Posting=>Test Report=>Preview (if you’re Classic). NAV will put together a handy dandy print preview which will now include a section at the bottom labeled Reconciliation. This little section will show you which direction your entry is going in, what sign your ending balance will be, and what the actual balance after posting will look like. This all happens before you commit it as final to the system. This may be just the extra bit of help you need in order to keep you from making an error. If you like what you see, post that journal entry!
If you have an account where you’ve got a lot of daily activity, you may not find this as useful as you do with accounts with just a little bit of activity throughout the month because you’ll have a more frequently changing end balance. Decide what might be useful to you.
Next time you do a journal entry, give it a try. Go to the account card and check the box. Make your journal entry. Before you post, run the posting test report. Is the net change in your journal entry going in the right direction? Did you enter the correct account? Is your ending account balance the right sign? Most importantly, is the balance after posting what you expected? This little helper can give you a quick preview before you hit post and save you from having to reverse and correct an entry.
DynamicsNAVFinancials blog celebrates one year with top 20 postings
Posted: April 1, 2013 Filed under: Uncategorized | Tags: Account Schedules, budgets, Classic Client, Convergence, dimensions, Dynamics, Excel, export to excel, financial reporting, financial statement, general ledger, NAV, NAV 2013, NAVUG, payment terms, PowerPivot, tips and tricks, user group 2 Comments
It’s our blogiversary! I started this blog in April 0f 2012, inspired by the number of NAV end users I had met over the last year who had the same questions that just weren’t getting answered. What better way to get that information out to folks than a blog where I could write up the topic in an easy to understand way?
You’ve shown your support! Over that last year, we’ve had over 15,000 views, almost 100 followers via email, have people following us on our new Facebook page, and know that many of you follow postings through the RSS feed. People from more than 120 countries are reading DynamicsNAVFinancials! The blog has gotten enough attention to be picked up for syndication through Microsoft Dynamics Community and I was even asked to participate as one of the eleven official bloggers at Microsoft Dynamics Convergence 2013 last month.
Special thanks has got to go to the NAVUG. The user group has been especially supportive of me and of the blog, finding opportunities for me to speak to NAV users in person, in webinars, online classes, and through this blog. Without the support of the user group this would have been a much longer road and I am so grateful for the constant work they have done to get me in front of as many readers as possible.
Publishing over an entry a week in the last year, there’s a lot to choose from, but in the month of April, I’ll be re-running the top 20 postings determined by the most viewed entries. Enjoy, and thank you so much for reading! There would be no reason to do this without you!
19 The NAV accountant’s little helper: the reconciliation account check box
18 Learning about Microsoft Dynamics NAV 2013: on demand learning from NAVUG is a fantastic resource!
17 Tis the (Budget) Season! Three handy NAV account schedule tips to get you through
16 Tips and tricks for a flawless budget load in NAV
15 Why NAV users should be using dual monitors
14 Video Demo: How to export NAV account schedules to Microsoft Excel
13 Basic row setup options for NAV Account Schedules
12 Aging methods in NAV – which buckets are you looking for?
11 Complete row setup options for NAV account schedules
10 Learning about Microsoft Dynamics NAV 2013
9 Where can I learn more about NAV analysis reports and analysis by dimensions?
8 10 easy tips for payment terms success in Microsoft Dynamics NAV
6 Getting started with a new account schedule
4 Maximize your Microsoft Dynamics NAV 2009 Classic screen space with user level configuration options
3 Basic column layout options for NAV Account Schedules
2 NAV keyboard shortcuts – Classic to RTC
1 The Account Schedules Formulas Page
View Convergence 2013 session on Microsoft Dynamics NAV dimensions here
Posted: March 29, 2013 Filed under: Uncategorized | Tags: Convergence, dimensions, Dynamics, Microsoft, NAV, tips and tricks, virtual convergence Leave a commentIf you’ve enjoyed this month’s 15 days of NAV dimensions series, and would like to hear me speaking about dimensions, there is now a recording out on the Convergence website as well as on the Virtual Convergence website.
If you were a registered Microsoft Dynamics Convergence 2013 attendee, log in to the Convergence site and bring up the Schedule Builder. From here you can view a recording of any session that was listed as a concurrent or deep dive session, and you can even re watch the keynote and general sessions! You can search by any number of methods for my session, which was called Tips & tricks for working with dimensions in Microsoft Dynamics NAV, and ran on Thursday, March 21st at 2:30. Click the link to watch the session video.
If you were not able to attend Convergence this year, there is a Virtual Convergence that is available to the public. You’ll need to log in to register, but once there, you’ll have the same access as Convergence attendees to concurrent, deep dive, keynote, and general sessions. In order to find my session, search under the sessions menu, then Microsoft Dynamics NAV, then scroll down until you see the box that contains Tips & tricks for working with dimensions in Micro . . .
There are a lot of great sessions out there available for one year past the close of Convergence 2013, so I bet these will only be out there until the end of February 2014. Take some time to explore what else is out there and share with your coworkers! This is a great way to get information into your company about the ERP you have all chosen to run your businesses.
Enjoy!
Add NAV dimensions as your business changes (part 14 of 15)
Posted: March 28, 2013 Filed under: Uncategorized | Tags: dimensions, financial reporting, financial statement, global, NAV, shortcut Leave a comment
Setting up dimensions should not be something we exclusively do when we implement NAV for the first time at our businesses. Change is the norm in business, and I would be genuinely surprised to hear from anyone at this point that their business has not changed significantly in the last five years. So for finance and IT professionals, what changes when the business changes? Reporting requirements!
For NAV, that means opportunity for using dimensions differently or to add new dimensions. Hopefully you are happy with your global dimensions, the two most important dimensions for your company, and you’re ready to add some shortcut dimensions so you can expand your reporting capabilities. There are four main things you should keep in mind when adding a shortcut dimension.
1) Keep in mind timing and financial cutoff. If you choose to start collecting data on a new dimension today and today falls in the middle of a fiscal period, you’re going to create a disconnect in your financial data where you have data with the new dimension value and data with the blank dimension value in the same period. Don’t do it. Find out when the end of the fiscal period is, and start gathering the new data starting with the start of the new fiscal period. This doesn’t have to be the year-end, it could be a month, or whatever period you have at your company, but do take the time to plan this out, your finance department will thank you later.
2) Know that collecting a new dimension will not magically attach to your historic data. Assigning dimension data to your item or customer or vendor only begins the collection of that data on any new transactions generated since you assigned the dimension data. There is nothing out there that will magically attach this new requirement to old historic data. There are ways to go back and change the historic data, but this is generally beyond what you want to do manually. Involve someone experienced in SQL or call your partner for some help with this. And for goodness sakes, try this in a test system first. It’s always good to do a practice run on this kind of change, and should be mandatory if you’re planning to change a large amount of data. Remember that in many cases, it is perfectly ok to collect new data without catching up the history. Only you can decide what you need for your reporting.
3) Don’t abandon your pending data. Don’t forget there are things out there you created prior to assigning that new shortcut dimension. Sales orders, purchase orders, transfer orders and any other type of form that may be in process at the time you defined that new dimension will need to be caught up to the new requirements. If you forget this, NAV will remind you by throwing a dimension error when you, or your colleagues, try to post those documents to the system.
4) Consistency is the key. Make sure you set up this new dimension with the same level of consistency you’ve used with your other dimensions. Start with your master data, but follow through by adding the safety net of the chart of accounts, and remember to incorporate your choice of value postings.
Keep reading this month as we continue our series, 15 Days of NAV Dimensions.
Communicating to IT about NAV dimensions and NAV2013 dimension sets (part 13 of 15)
Posted: March 27, 2013 Filed under: Uncategorized | Tags: Account Schedules, default dimensions, dimension sets, dimensions, financial reporting, financial statement, NAV, NAV 2013 Leave a commentToday I’ve got some old news and some new news for you regarding dimensions, but first I need to re-emphasize that finance and IT must be of one mind when it comes to dimensions. As a finance professional, you’ll definitely have your perspective on how dimensions will help you to efficiently produce financial reports with control and consistency. But remember that finance and IT need to work in partnership on a NAV dimension strategy in order to be really successful. One way these two roles must work together is regarding how to get your hands on posted dimension data in NAV. If you’re using NAV only tools to get your data, you won’t need this information, but once you progress to using outside reporting tool packages, you need to know a little bit more to get your collective hands on that data.
Here’s the old news: if you are using any version of NAV prior to NAV2013, dimensions are kept in a separate table behind your main data. You can see this, though it may not be obvious to you, when you populate dimensions using Ctrl-Alt-D or when you view dimensions through the dimensions button from master data. This table is actually called the default dimensions table (table #352). If you’re using something like SQL for reporting, you’ll need to perform a join between this table and the table(s) to which the data are related. As of today, the large majority of NAV users are using versions prior to NAV2013, so this is important information to have if you’re to get all the data you want into your reports now.
Here’s the new news: With NAV2013, dimension sets are turning dimensions upside down, treating them in an entirely different way, in order to make the way dimensions are stored in NAV more efficient. We’ve all heard the horror stories of ginormous databases with performance problems due to overuse of dimensions. Those stories have always been frustrating to me because it seems we should be able to use dimensions in as robust a manner as we can handle. Well, Microsoft has made a great improvement with the creation of dimension sets. I’ll admit that explaining the mechanics of exactly how dimension sets work is beyond my technical capability, so I’ll hand that off to experts more talented than I. I did get to see a presentation by Jesper Lachance where he showed an example (which he allowed me to share in my recent Dimensions presentation at Microsoft Dynamics Convergence 2013) that shows a twenty fold decrease in number of data items stored by using dimension sets instead of the pre-NAV2013 method.
Faithie Robertson of Archerpoint has a fantastic article A Better Mousetrap! Dimension Sets in Dynamics NAV 2013 (Navision) which does a really great job of explaining exactly what a dimension set does differently.
If you’re looking for a textbook explanation, visit MSDN on their page Dimension Set Entries Overview.
Encore Business Solutions has an illustrated guide NAV 2013 – Dimension Sets.
You can see why, with dimensions being stored in a separate table, and with dimension sets showing up as a new improvement, everybody on the team needs to understand how dimensions work, where they’re kept, what your particular company conventions are, and how you’ll report against them accurately and effectively. I haven’t heard a lot of feedback yet from end users about how working with dimension sets is working for them and whether it is making reporting better or challenging in different ways. But, the feedback from programmers, developers, and database administrators has been a resounding: THANK YOU MICROSOFT!!!
Keep reading this month as we continue our series, 15 Days of NAV Dimensions.
NAV dimensions in budgets and consolidations (part 12 of 15)
Posted: March 26, 2013 Filed under: Uncategorized | Tags: Account Schedules, budgets, Classic Client, consolidation, dimensions, financial reporting, financial statement, global, NAV, shortcut Leave a commentNow that you know you can view dimensions on postings and in financial reporting though account schedules, let me show you how you can utilize dimensions in budgets. NAV budgets opens up a few more possibilities for you where dimensions are concerned. Take a look at this screen shot, using the classic client, that shows clearly what the available dimensions are in budgets.
If you look at the left side, you can see the persistent global dimensions of Department and Project which the test database for Cronus uses. Just like all areas of NAV, global dimensions are available everywhere, even in budgets. On the right hand side, you can actually see four more dimensions. These are shortcut dimensions and if you count, you can see you’ve got a total of six dimensions available with NAV budgets to use for your planning process. As long as you budget for a dimension then you can report actual versus budgeted against that dimension.
In addition, you can also see there is a field called business unit filter, which I’ve always counted on as a “bonus” dimension. This field becomes useful when you have multiple companies in NAV and use them to consolidate your financial statements. I’ve got a very simple setup where I have two companies and a consolidation company. When I consolidate my statements monthly, and when I load my budgets, I designate the business unit filter for each of the two companies so I can report on them individually as well as together, on a consolidated level. Because I use separate companies with the business unit filter I don’t need to use a dimension to designate company for my financial statements.
Keep reading this month as we continue our series, 15 Days of NAV Dimensions.
Dynamics NAV Financials 