NAV AS 101 Lesson 17: Budgets with Account Schedules

The single most important thing to know about using budgets with account schedules is: if you want to report on it, you have to budget on it first! Account schedules allows very flexible options for reporting almost any financial measure you can thing of as actual versus budget, but you must set up that level of detail inside your budget in order to be able to eventually report at that level of detail. Keep in mind that you may not want to budget at the same level of detail as the financial transactions you gather in your general ledger, especially when dimensions are involved. If you have very detailed dimensions set up at the item or even customer level, you almost certainly will not budget at that level unless you have very few items or customers. You will more likely choose to budget at a higher more summarized level like product grouping, or department or project.

Setting up account schedules to use budget entries is very simple. When designing any column layout, simply choose budget entries instead of general ledger entries when filling out the ledger entry type field.

NAV budgets open up a few more possibilities for you where dimensions and account schedules are concerned. Take a look at this screen shot, using the classic client, that shows clearly what the available dimensions are in budgets.

budgetdimsIf 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 your account schedules. Remember, you’ll need to apply an analysis view to your schedule in order to look at any four dimensions at a time, even when looking at budgets.

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 using account schedules. 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.

While I do use the budget feature in NAV, I don’t dwell there very long. I use the tool inside NAV to load my budget entries once a year, and after that I leave it alone. Instead of viewing the plan in the NAV budget, I view all budget related entries with account schedules. I find this gives me the most flexibility in switching back and forth between different column layouts that I already use with my actual numbers, and in constructing new ways to compare budget to actual results for my end users.

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 16: Analysis Views

Now that you know you can use dimensions with account schedules, how do you do it? You need to create an analysis view. Analysis views give us the ability to reach into our dimensions, past the two globals, and into the full list of dimensions, allowing us to combine any four dimensions we want at a time in any account schedule.

If you don’t use an analysis view, you’ll still be able to get to your global dimensions as shown below. 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 grayed out, unable to be used.

1

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.

2

There are six things you need to know about analysis views before you start using them:

1.  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 every time you open the report, this is the best way to do this.

av1

2.  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.

av2

3.  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 view separately.  Alternatively, you can choose to schedule codeunit 410 Update Analysis View as a regularly scheduled maintenance item in order to automate this process.

4.  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 a different time frame than your compression setting. Choosing date compression of none will allow you to drill down to full transactional detail.

av3

5.  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.

6.  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.

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 15: Dimensions with Account Schedules

dimensions gridUsing dimensions along with account schedules is when you really start to get powerful with your reporting. So far, we’ve only looked at general ledger accounts in rows and periods of time in columns, which is great for financial reporting. However, once you incorporate dimensions you can move beyond just financial reporting and produce other types of reports.

There are three things you need to know about using dimensions with account schedules.

1.  If you are using global dimensions, you can get to them from everywhere in NAV, including account schedules. The company shown below uses Department and Project as their global dimensions, so the account schedule filtering options shown are: Department, Project, Dimension 3, and Dimension 4. If you don’t have dimensions defined, you’ll just see Dimension 1, Dimension 2, Dimension 3, and Dimension 4 as your available options.

1

2. You can use dimensions in rows or in columns or in both rows and columns at the same time! Look in the screen shot above – you have the option of producing totaling by dimension for the same dimensions you have defined in your filters. Think of the possibilities: for this company you could simply see departments in all the rows and a cross section of projects in the columns, effectively giving you a one screen report of projects by department. The general ledger accounts are still there in the design of the rows, and the dates are still there in the filtering of the overall report, but you’ve got two more ways to expand your reporting. Now you’ve moved beyond financial reporting and expanded into reporting on operational areas.

3. You can change the dimensions you report on with account schedules. You are not limited to just the global dimensions, you can actually report on four dimensions in any combination, effectively getting you access to any of the unlimited number of  dimensions you can have. In order to do this, you need to define an analysis view, which we’ll go over in the next post, but for now, take a look at how that is different, both in the filtering as well as in the dimension totaling areas of account schedules. This example has an analysis view defined for four different dimensions, which would allow the company to look at entirely different things with their reports.

2

For a much more in depth discussion of dimensions, take a look at this series devoted to just that topic: 15 Days of Dimensions.

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.