Blogiversary Top 20 (#16) Tips and tricks for a flawless budget load in NAV

Posted: April 8, 2013 | Author: kpeters | Filed under: Uncategorized | Tags: budgets, Excel, export to excel, NAV, 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!

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 | Author: kpeters | 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 (#18) Learning about Microsoft Dynamics NAV 2013: on demand learning from NAVUG is a fantastic resource!

Posted: April 4, 2013 | Author: kpeters | Filed under: Uncategorized | Tags: learning, NAV 2013, NAVUG, training, user group | 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!

As my company continues to move down the path to upgrading to NAV 2013, learning about the new version is high on our list of priorities. Yesterday, we gathered a group of 18 employees to view a webinar that had been recorded by the NAV user group (NAVUG). The live webinar had been done a month ago, but NAVUG records all of its webinars so user group members can view them on demand later.

I took the opportunity to view the webinar first to vet the material for appropriateness for our group. Being able to see the recording first allowed us to tailor the list of who we invited to view the webinar. Based on the content of the webinar, we invited NAV end users from the customer care, purchasing, finance, IT, warehouse, and marketing departments as well as our CFO and CEO. It was hugely convenient to be able to view the recorded webinar as a group because it allowed us to do this at a time that was convenient to us, we all got to consume the same learning experience at the same time, and we got a chance to discuss what we saw in the context of our own upgrade plans.

The webinar we viewed was titled “NAVUG 2013 Webinar Series: Microsoft Dynamics NAV 2013 – A Functional Preview” and we were excited to see some of the new charting and cash flow improvements, as well as to hear about dimension sets, database performance increases, and improved copy and paste. There were some smiles and ooh and aahs during the discussion of the web client and SharePoint clients for remote access and use of tablet technologies like the iPad and Surface. There was even a smattering of applause when the presenter showed some improved Excel integration features! Most importantly, all the people in the room got to pick up on the real world impact that NAV 2013 improvements will have on their jobs. Our company is still using the Classic Client, so for most of the people attending this was their first exposure to the Role Tailored Client and this webinar really got a lot of them thinking about what the “new” NAV would look like.

I can see already that NAVUG has two more sessions planned in their NAV 2013 Webinar Series. You can bet I’ll be viewing both of these, whether it is at the scheduled time for the webinar, or later, from the recorded session, and finding a way to share the info with other interested folks at my company.

NAVUG NAV 2013 Series: Introduction to ODATA Web Services: How to easily publish your NAV data 01/30/2013 11:00 AM (ET) NAV 2013 brings ODATA Web Services as a new way to access your NAV data from outside the system. Attend this session to find out how you can use ODATA to access your data from many other applications like Excel, SQL Reporting Services, Internet Explorer, smartphones and tablets.

NAVUG NAV 2013 Series: New Feature – Cash Flow 02/14/2013 09:30 AM (ET) We will demo how the new Cash Flow Forecast in NAV 2013 gives an efficient way to forecast short-term cash flow, enable better monitoring of cash receipts and cash disbursements and use the information to take preemptive steps.

For the cost of our annual corporate NAVUG membership, we were able to get 18 people at our company exposed to important concepts about the new version of the software that will be coming their way soon. We didn’t need to incur any travel costs or even any seminar registration fees, and we were able to schedule it at a time that worked for us. I know our company will continue to use the NAVUG library of recorded webinars as we continue to learn about NAV 2013. I hope you find this resource as useful as we have!

Blogiversary Top 20 (#19) The NAV accountant’s little helper: the reconciliation account check box

Posted: April 3, 2013 | Author: kpeters | 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 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!

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.

Blogiversary Top 20 (#20) Use show opposite sign to reflect net income correctly on an income statement using NAV account schedules

Posted: April 2, 2013 | Author: kpeters | Filed under: Uncategorized | Tags: Account Schedules, financial statement, income statement, net income, show opposite sign, 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!

One of the concepts I see students in my NAV account schedules class get stuck on is exactly how to use the show opposite sign function when constructing an income statement. It can be a bit mysterious why it behaves the way it does, and it seems to defy the rules of accounting and basic arithmetic all at the same time. Once you understand why it works the way it does, you’ll know how to use it in the future. From the example shown here, we see a very basic income statement.

Without using any numbers yet, let’s all agree on some basic calculations:

Revenue – Cost of Sales = Gross Profit $

Gross Profit $ / Revenue = Gross Profit %

Gross Profit $ – Operating Expenses = Net Operating Income (Loss)

Putting these basic calculations into the design view for this account schedule gives the result below. Notice we’re not going to use the show opposite sign function yet, or make any changes to the basic formulas we just defined.

When we look at the report view for this account schedule we see that we’ve got a significantly wrong result. The revenue is showing as a negative number and all of the calculations below it are incorrect.

Now, for the accountants out there, you may already have guessed what the problem is. When accounting for revenue, these numbers are assigned a credit value, which shows up as a negative. Most financial statement end users want to see this reflected as a positive number. This is where we need to use show opposite sign. Let’s add that in to the design view, as shown below.

When we look at our new result, you would think all would be correct. We’ve corrected the accounting problem by flipping the sign. However, when you look at the result, we get the following.

Why in the heck is that gross profit number not right? It seems we’ve got very simple math we’re dealing with here, what could be the problem? Many folks at this point go for the simple solution – just select show opposite sign on the gross profit $ line.

Unfortunately, this doesn’t work either, because you are flipping the sign on both the revenue value as well as the cost of sales value and you simply get the opposite of the same wrong number you got above.

The reason this is happening is because the show opposite sign function is doing exactly what it says it is doing, it is SHOWING the opposite sign. This is really only a presentational mask, and when calculations are done against the value, revenue is still calculating as a negative number, even though it appears on your report as a positive number. This little bit of arithmetic defying programming can make an accountant a little cranky, but it is easy to solve with a little mathematic trickery. In order to fix this, we need to put into place a two part solution. The first part we already know; we must select show opposite sign for our revenue accounts. The second part manipulates the Gross Profit $ and Gross Profit % as shown below, changing the value of the revenue account within the equation in order to calculate correctly.

The end result gets us correct results every time.

For those of you who are really paying attention, there is one more math mystery in the show opposite sign puzzle. Why have I chosen to show the calculation for Gross Profit % as -A3/A1 instead of simply using A3/-A1? Again, we’ve got to defy the programming with math tricks. Choosing A3/-A1 gives the error shown below. Get around it by moving the negative value to the other side of the equation and you’re good to go.

DynamicsNAVFinancials blog celebrates one year with top 20 postings

Posted: April 1, 2013 | Author: kpeters | 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!

20 Use show opposite sign to reflect net income correctly on an income statement using NAV account schedules

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

7 15 days of NAV dimensions

6 Getting started with a new account schedule

5 PowerPivot to the People

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 | Author: kpeters | Filed under: Uncategorized | Tags: Convergence, dimensions, Dynamics, Microsoft, NAV, tips and tricks, virtual convergence | Leave a comment

If 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!

Learning about NAV dimensions from Microsoft Dynamics Customer Source and MSDN (part 15 of 15)

Posted: March 29, 2013 | Author: kpeters | Filed under: Uncategorized | Tags: BREP, CustomerSource, dimensions, learning, manual, MSDN, training | Leave a comment

There are some great resources out there to help you learn about dimensions in NAV. The first resource I always recommend to other NAV customers is CustomerSource!

Assuming you are already a Microsoft Dynamics NAV customer, and you’re current on your BREP (Business Ready Enhancement Plan), you need to go to CustomerSource to get access to e-learning modules and training manuals that are just waiting for you to find them. If you’ve never visited here before, start at this link for some simple instructions.

If you want to search on your own, here is the place to start. On the left hand navigation bar, choose Documentation=>User Guides and then look for one of these two:

Overview of Training Manuals and Hands-On Labs for Microsoft Dynamics NAV 2009
Overview of Training Manuals for Microsoft Dynamics NAV 5.0

Once you get to these pages, you can search through a number of menus to get to exactly the topics you need without using the clunky CustomerSource user guide drop down choices, which are difficult to use at best.

Another good source are the Learning Plans for each version. On the left hand navigation bar, choose Training and Certification, then choose Learning Plans for Microsoft Dynamics from the main page. Choose your product and version, and you’ll have what you need. The Learning Plan for Microsoft Dynamics NAV 2009 is particularly good, providing links from the page directly to what you need. You will find sources for E-Learning, Instructor Led Training, and Training Materials. The link to the Learning Plan for Microsoft Dynamics NAV 5.0 actually points to a PDF version of the NAV 2009 Learning Plan; it looks like the 5.0 plan has been removed.

If, because you’ve taken a look at the Learning Plans and you know the Course Number for the training materials you want to find; here’s another quick way to get at them. At the top of the left hand navigation bar, there is a search box. Simply type in the course number, for example, 80050, and you’ll be taken directly to those training materials which you can immediately download.

But wait just a darn minute, where’s the information for NAV2013? I can tell you I’m as disappointed as you are. CustomerSource has not updated an overview document or a learning plan on their site for NAV2013. There are many comprehensive manual like documents out there for NAV2013 but they are really hard to find, and can be difficult to tell the difference between a manual and a white paper, especially if you are a customer and don’t understand the structure of which manuals are available without a table of contents to work from. So, for you folks who are new to to NAV with NAV2013, I’m sending you to another source, the Microsoft Developers Network. Don’t be afraid, there’s plenty there that end users can use as well.

Follow this path in the library: Servers and Enterprise Development=>Microsoft Dynamics=>Microsoft Dynamics NAV=>Microsoft Dynamics NAV 2013. There is lots of info here that will help you out, but if you need to get directly to dimensions, type this phrase into the search box: Set up dimensions to mark transactions. I’m using this source more and more often since I don’t have to mess around with any type of login or administration for my users, I can bookmark any page I want to get back to, and the search function is a world apart of improvement.

If you liked this post, check out the rest of our series, 15 Days of NAV Dimensions. Thanks for reading!

Add NAV dimensions as your business changes (part 14 of 15)

Posted: March 28, 2013 | Author: kpeters | 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 Dim ensions.