Week six recap: Account Schedules 101

repeatHere’s the week 6 recap on the NAV Account Schedules 101 series.  Follow the links below to view the posts for each of the lessons we covered this week or view the entire series here.

NAV AS 101 Lesson 13: Printing Account Schedules

NAV AS 101 Lesson 14: Exporting to Excel

 Don’t forget to visit the Account Schedule Formulas and Account Schedule Examples pages if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Microsoft Dynamics NAV.


NAV AS 101 Lesson 14: Exporting to Excel

Using the Export to Excel function is the single most important time-saver when using NAV account schedules because once you create your first Excel workbook and set all the formatting you’ve created a “shell” that never needs to be set up or changed again. Each month, when you produce your reports for the next fiscal cycle, all you need to do is update that month’s numbers. Not only does this save you a huge amount of time, but your reports also look consistently the same month after month.

This post has video demonstrations showing three different ways to use the export to Excel function. The videos are all done using NAV 2009 R2 Classic, but the functions work exactly the same in all versions of NAV. The only thing that is different is how you get to the Export to Excel function in each version of the software.

NAV 2009 Classic – Choose Functions button at bottom right then choose Export to Excel

NAV2009 RTC – Choose Actions button at top left then choose Functions then choose Export to Excel

NAV2013 – On the Ribbon choose Export to Excel

Produce the same report for three different months

Produce the same report for three different dimensions

Produce financial statement package with three different reports, for two different months

Correction: Oh no! In NAV2013, we’ve lost the ability to update the workbook and still retain formatting. See my post on Account Schedule bugs here for more details on the new recommended best practice on how to deal with this change. I’ll get a new video posted out here with a demo of how to do this later this year.

This posting is part of the NAV Account Schedules 101 series.  Find the entire list of lessons here.

Don’t forget to visit the Account Schedule Formulas and Account Schedule Examples pages if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Microsoft Dynamics NAV.


NAV AS 101 Lesson 13: Printing Account Schedules

printerYou’ve got a few options to get account schedules out of NAV. You can print them, export them to Microsoft Excel, or you can even simply copy and paste the results out of NAV and into Excel.

I generally recommend companies choose to use the export to Microsoft Excel function because it allows maximum efficiency with maximum flexibility to produce their financial statements.  We’ll go over that in the next post in this series.

If you just need a quick dump to Excel so you can play with the numbers a bit, you can simply select all the rows you have on-screen, right-click and copy, and then right-click and paste into Excel. Be warned that you can only copy what’s actually showing on the screen, so if everything is not showing, you’ve got to rearrange your screen a bit to make this work well.

In addition, you can choose to produce your financial reports using XBRL, but I’m not going to consider that an end-user friendly function for the purposes of this post. See your partner to help you get set up if your company wants to do that.

If you must print, here are some tips to help you make those reports look as good as possible. You can print from two different places in NAV. Things are a little bit different between NAV Classic versions and NAV RTC/2013 versions, so I’ll illustrate them all.

[Warning: some of the images in this post get mighty tiny. Click on any picture to bring it up in it’s very own window to save your eyesight. If you’re reading this one on your phone, you’re out of luck, sorry.]

NAV Classic users

If you elect to print from the account schedules overview screen, choose the function button on the bottom right of the screen and choose Print. This brings up the following configuration screen where you can set a few parameters, and then choose Print.

para

The result you’ll get will look like the screen shot below. There are a few things to note about the result. The information at the top of the page comes from a number of sources. Some of the fields you can control directly, like the row setup and column layout names. In this example, I would want to remove the words “hands on exercise” if I was going to print my schedules. Some of the other fields are coded into the report, so if you want them changed, you’ll need to go into the Object Designer or have your partner change that for you.

The last thing is that the default print setup only allows seven columns, including the description. In this example, I’ve got eight, so my report cuts off the difference column for my year to date numbers. In order to fix that, I can either reduce the number of columns I have, maybe by removing the spacer in the middle, or I can print from a different area.

bet

In Classic, go to Financial Management=>General Ledger=>Reports=>Financial Statement=>Account Schedule, Landscape. You’ll see now the report is oriented landscape instead of portrait, so now we can see the difference column that was missing.

p3

This comes in pretty handy when you’ve got a trended month report to print, which allows 14 columns, including the description, on your report.

p4

NAV Role Tailored Client (RTC) users

The RTC has made a few changes to printing account schedules, but not many. The most notable is printing from the overview looks significantly different. I would consider this as unusable as a financial statement. When you actually print it from the reports menu, it looks exactly the same as the Classic version, shown above, and you still need to choose between the portrait and landscape versions of account schedules.

o1

NAV 2013 users

In NAV2013, we’ve got one huge step forward and a couple small steps back with printing. The huge step forward is that we no longer have to choose between portrait and landscape and, we get the same report when printing whether we print from the overview or from the reports menu. We’ve lost the ability to do some small things, like putting brackets around negative numbers, and now the row numbers show up on the report, which I’m not a thrilled about.

r8r9

There’s one hidden bonus feature in 2013. If you click a little box in the setup labeled “show account schedule setup”, you get a report that shows your design view right next to your end-user view, which makes it super easy to compare your design and results. We’ve been able to see the layout previously by printing the account schedules layout report, but it’s nice that it’s been added as an option when printing the schedules, all in the same place.

f10

This posting is part of the NAV Account Schedules 101 series.  Find the entire list of lessons here.

Don’t forget to visit the Account Schedule Formulas and Account Schedule Examples pages if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Microsoft Dynamics NAV.


Week five recap: Account Schedules 101

repeatHere’s the week 5 recap on the NAV Account Schedules 101 series.  Follow the links below to view the posts for each of the lessons we covered this week or view the entire series here.

NAV AS 101 Lesson 10: Comparison Period v. Comparison Date

NAV AS 101 Lesson 11: Filtering

NAV AS 101 Lesson 12: Drilling Down

 

Don’t forget to visit the Account Schedule Formulas and Account Schedule Examples pages if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Microsoft Dynamics NAV.


NAV AS 101 Lesson 12: Drilling Down

3D white people. Worker with a jackhammerI don’t know what kind of questions you need to answer as a part of your job, but here are a few of the questions I need to answer in my job every day:

Why is that sales number so low?

Why is that sales number so high?

Where did the expense for “x” end up?

What exactly did that team spend all that money on?

Did we get invoiced for that thing yet?

Did that journal entry get done?

Where did that number come from?

I use account schedules to answer those questions and in most cases, I use the drill down feature to get the answers. Each version of NAV does this a little bit differently, but it’s a very intuitive feature to use no matter what the version.

  • CLASSIC:  Drill down using the arrow in the cell
  • RTC:  Right click to drill down
  • NAV2013: Double Click to drill down

The mantra you need to learn when using the drill down feature is “drill down to navigate”. In order to get through all the layers of a posting starting with the highly summarized number you see in your account schedule you need to drill down at least a couple of times until you can see the numbers you are looking for. Sometimes you can tell what your answer is by seeing what account or set of accounts the number came from, but other times, you are looking for the entry point of the number – generally either an invoice (sales or purchase) or a journal entry. You can get to all of those juicy answers by drilling down starting from the account schedule, and once you get to the screen that shows you the Navigate function, you can navigate directly to the journal entries that were made and see them in detail or even call up a copy of the invoice you are looking for.

This posting is part of the NAV Account Schedules 101 series.  Find the entire list of lessons here.

Don’t forget to visit the Account Schedule Formulas and Account Schedule Examples pages if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Microsoft Dynamics NAV.


NAV AS 101 Lesson 11: Filtering

The primary difference between account schedules in Classic versions of NAV (NAV2009 and prior) and Role Tailored versions of NAV (NAV2009 and later) is mostly navigational. Most of the ways we construct row setups and column layouts have remained the same, but getting around the software is significantly different. Filtering is one of the areas where we see this the most.

When filtering an account schedule in Classic versions, you have some very basic options to select your dates, budget, dimensions, and very little else.

fil1

When filtering an account schedule in Role Tailored versions, you’ve suddenly got a whole new world of possibilities. 2009RTC makes things a little complicated, making us move through an additional screen on our way to the account schedule overview before we can apply additional filters once we get there. This has been my least favorite iteration of account schedules filtering because so many of the filters reset when you leave the screen, requiring you to continually redefine them when you come back or cheat the system by changing the work date in order to keep your sanity.

fil2

fil3

NAV2013 makes it all better with some great improvements and overall simplification. The new filtering eliminates the additional screen on the way to the overview, taking us directly there instead. The filtering is a nice improvement, keeping the frequently changed options up top and the set and forget filters on the bottom. The most important thing of all is that the next period and previous period buttons are back, allowing you to quickly change periods without having to type the full date in.

fil6

fil7

A nice new feature that lurks in the background are the previous and next column buttons. You’ll think you’ll never need them until you have an account schedule set up with more than 12 columns. The new freeze pane option is nice, but it decreases the number of columns available in your view. Use the shiny new next column button to see the rest of the info you need.

This posting is part of the NAV Account Schedules 101 series.  Find the entire list of lessons here.

Don’t forget to visit the Account Schedule Formulas and Account Schedule Examples pages if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Microsoft Dynamics NAV.


You’ve only got six weeks until June 30th. Who cares?

YOU should! June 30th is the early bird registration deadline for NAVUG Summit, held this year in St. Louis, MO from October 14-17. There is no single other conference I can possibly recommend more than NAVUG Summit for NAV users. Every year as we go through the process of preparing great content to share with conference attendees I am amazed at the deep passion all of the conference organizers show when constructing the session schedule. I’ve been attending this conference for seven years now, and speaking for six of those years, and I can tell you this year is already shaping up to be the best year yet.

If you’re looking to learn more about NAV from other experienced NAV users, to deepen your professional skill set with new knowledge that is directly applicable to your work, and to network with people who use the same software in their jobs that you do every day, NAVUG Summit is the one event you need to attend this year.

You’ve got six weeks left to save $200 before the registration goes up. Make your list of what you need to learn and who you need to meet. Prepare your arguments to attend this incredibly valuable conference. Talk to your coworkers about why they should come with you. Get registered.

I wouldn’t miss it. You shouldn’t either. See you there!