NAV AS 101 Lesson 1: Why do I use Account Schedules?

decesion_makingI use account schedules as the primary source of financial reporting at my company. With all the available choices out there, why do I use account schedules? I’ve got a whole list of reasons.

1)  I can custom build all of my financial statements, exactly how I want to see them.  

Especially when I talk with prospective NAV customers, I hear a lot of objections as to why NAV doesn’t come with “out of the box” financial statements. If you think about this for a bit, what part of your company’s financials will fit the definition of “out of the box”? Is your chart of accounts the same as someone else’s? What about the name of your accounts? Your numbering convention? Is the way you present your financial statements just like anyone else’s? If you built your financial statements using an out of the box solution, how long would it be before you began to customize them?

Why not build them the way you want them the first time and be able to customize them as your company changes?

2)  Account schedules tie directly to the general ledger.

Someone told me once that reporting from the general ledger was the best way to get to the truth. Since my financial statements have got to be accurate and consistent above all else, I like this idea. I know, that without a doubt, my account schedules tie back to my trial balance and my detailed transactional postings. I can prove it out over and over. I can use my account schedules to debunk some of the untruths that come out of some of our other reporting sources. Knowing I can get to the truth makes me trust the results I get from account schedules and gives me confidence in deeming them as the place to get exactly the right answer.

3)  Budgets integrate really well with account schedules.

I use the budgets area of NAV extensively. However, I only actually touch the budgets area once a year, when I populate them with our next years’ data. Budgets integrate to account schedules so fluidly that I have no reason to go back and forth between the two during the year as I track how we’re doing in comparison to budget or even as I look ahead to remind myself of what the plan was. I can get this information from account schedules and get all my financial information from one place.

4)  Dimensions along with account schedules are a powerful combination.

Account schedules without dimensions are like James Bond without Q. James Bond can certainly hold his own without all the gadgets, but come on, the things that Q adds are really cool! Adding dimensions in almost as many combinations as you can think of gives you added power in your account schedules and lets you stretch beyond mere financial reporting and expand into operational reporting.

5)  Drill downs

The drill down feature from account schedules is unsurpassed as a quick research tool. Any number you can produce in an account schedule that comes from the general ledger (instead of as the result of a formula) can get you to the source of the number by drilling down. That means you can find the exact source(s) of a highly summarized number whether it comes from an invoice or a journal entry, and you can track down where that number came from, when it happened, and even who the user was who created it.

6)  They export easily to Excel.

My monthly financial statement package is 18 pages, all produced out of account schedules. Each month, I export directly to Excel and produce reports that are consistently formatted and look the same every month. I’ve got good control over the process while still having the flexibility I need when we decide we want to make a change. All of this gets loaded up to SharePoint for the end users who use them, and no tree products are harmed in the production of our financials.

7)  Account schedules can be built and maintained by finance folks without IT help.

This is, and always has been, the big seller for me. I’m a DIY kind of person. I do my own landscaping, I bake my own bread (not all the time), I can build a fire while camping, and I painted my own living room. These same principles flow through to my business. I want to be able to do it myself. I love my IT colleagues, but goodness knows they have enough to do without having to produce my financials. Account schedules are easy enough to use that I don’t need to know a programming language, or how to accomplish a table join, in order to build them. All I need is knowledge of my chart of accounts, what the structure of my dimensions and budgets are, what the differences are between balance sheet and income statement accounts, and some simple formulas.

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 Account Schedules 101

I’ve decided to give away my account schedule class material here on the blog in this Series: NAV Account Schedules 101. Check back to see what’s been added, or choose to follow the blog to receive email updates (or subscribe to the RSS feed), so you don’t miss any of these when they come out. Most examples and screen shots will be shown in NAV2013, but I’ll make sure to tie back any significant differences to 5.0 and NAV2009 Classic and RTC. The list of topics we’ll be covering is below and you’ll see two or three new ones come out every week until we’re through the whole list.

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.

101v2

 

NAV AS 101 Lesson 1: Why do I use Account Schedules?

NAV AS 101 Lesson 2: Am I anti add-on?

NAV AS 101 Lesson 3: Where else can you learn about Account Schedules?

NAV AS 101 Lesson 4: Basic Elements of Account Schedules

NAV AS 101 Lesson 5: Getting Started

BONUS WEBINAR – I did a repeat of a NAVUG sponsored Convergence session from this last March titled, “Basic Financial Reporting with Account Schedules”. The one hour session covered some account schedule basic concepts, but also shows how to look at an income statement nine different ways with live demonstrations done in a NAV2013 database. The webinar is recorded and is available for viewing at this link out on Collaborate if you have a premium NAVUG membership.

NAV AS 101 Lesson 6: Row Setup

NAV AS 101 Lesson 7: Column Layout

NAV AS 101 Lesson 8: Formatting

NAV AS 101 Lesson 9: Formulas

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

NAV AS 101 Lesson 11: Filtering

NAV AS 101 Lesson 12: Drilling Down

NAV AS 101 Lesson 13: Printing Account Schedules

NAV AS 101 Lesson 14: Exporting to Excel

NAV AS 101 Lesson 15: Dimensions with Account Schedules

NAV AS 101 Lesson 16: Analysis Views

NAV AS 101 Lesson 17: Budgets with Account Schedules

NAV AS 101 Lesson 18: Account Schedules not balancing?

NAV AS 101 Lesson 19: Row numbering

NAV AS 101 Lesson 20: Sharing with your friends

NAV AS 101 Lesson 21: Account Schedule Bugs

NAV AS 101 Lesson 22: Dealing with Zeros

NAV AS 101 Lesson23: Copy and Paste

NAV AS 101 Lesson 24: When to change the sign

NAV AS 101 Lesson 25: What else can I do with Account Schedules?


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

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.

budgetload

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.

budgetload2

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.

budgetload3

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.

budgetload4

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!


Tips and tricks for a flawless budget load in NAV

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.

budgetload

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.

budgetload2

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.

budgetload3

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.

budgetload4

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!

This posting is one of the Top 20 Most Viewed in the last year! Follow this link to see the entire list.