Blogiversary Top 20 (#13) Basic row setup options for NAV Account Schedules
Posted: April 11, 2013 Filed under: Uncategorized | Tags: Account Schedules, balance at date, Classic Client, financial statement, net change, row setup, RTC, totaling type 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!
The perpetual problem of the new NAV user is when you get started in a new area of the application, there are way too many options to choose from. How many times have you opened up a new form, only to find twenty or more columns displayed as the default, and many more lurking behind the show columns menu? While we appreciate having all options for all people at some point, new users can find this especially daunting when trying to just get started with something new.
I’ll show you which columns to choose in the row setup for account schedules as a beginning point, and go through some simple explanations of how they are used.
Fields to start with:
Row No. – The row number is completely optional, but highly recommended. This simple element of the row setup will eventually be one of the key features of your account schedule, allowing you to calculate and organize with ease.
Description – This is the one place you have to communicate, in words, what you’re showing on each line of your report.
Totaling Type: Posting Accounts or Formula – This field tells NAV what you’re going to do here. You’re either going to pull data from your general ledger posting accounts or calculate a formula.
Totaling – Which general ledger accounts you want to pull or what formula you want to calculate.
Row Type: Net Change or Balance at Date – The key here is knowing what type of accounts you’re reporting on. If you’re using income statement accounts (Revenue/Expense), then you need to use net change. If you’re using balance sheet accounts (assets/liabilities), then you need to use Balance at Date.
Shown below are both the design view and the user view of a summarized income statement, showing the use of these five options.
Role Tailored Client
Classic Client
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 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!
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!
Tis the (Budget) Season! Three handy NAV account schedule tips to get you through
Posted: October 23, 2012 Filed under: Uncategorized | Tags: Account Schedules, budgets, column layout, NAV, row setup, tips and tricks Leave a commentI 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!
This posting is one of the Top 20 Most Viewed in the last year! Follow this link to see the entire list.
Expand your use of dimensions in NAV account schedules by using analysis views
Posted: October 2, 2012 Filed under: Uncategorized | Tags: Account Schedules, analysis views, budgets, Classic Client, codeunit 410, column layout, date compression, dimensions, Dynamics, Microsoft, NAV, row setup, RTC, update Leave a commentIf I had to pick only three basic elements to Microsoft Dynamics NAV account schedules, I’d have to choose row setups, column layouts and analysis views. Of course, row setups give you access to basic general ledger accounts and column layouts give you options on how to show that data in different time periods. So what do analysis views do for us? Analysis views give us the ability to reach into our dimensions, past the two globals, and into the list of shortcuts, allowing us to combine any four dimensions we want at a time in any account schedule.
For illustration, I’m showing you an account schedule from NAV2009 Classic, which shows the dimension filters on an account schedule where no analysis view has been applied. 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 greyed out, unable to be used.
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.
There are a few things you need to know about analysis views before you start using them:
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, this is the best way to do this.
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.
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 one separately. Alternatively, you can choose to schedule codeunit 410 Update Analysis View as a regularly scheduled maintenance item in order to automate this process.
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 different time frame than your compression setting. Choosing date compression of none will allow you to drill down to full transactional detail.
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.
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.
Basic row setup options for NAV Account Schedules
Posted: July 5, 2012 Filed under: Uncategorized | Tags: Account Schedules, balance at date, Classic Client, financial statement, net change, row setup, RTC, totaling type 1 CommentThe perpetual problem of the new NAV user is when you get started in a new area of the application, there are way too many options to choose from. How many times have you opened up a new form, only to find twenty or more columns displayed as the default, and many more lurking behind the show columns menu? While we appreciate having all options for all people at some point, new users can find this especially daunting when trying to just get started with something new.
I’ll show you which columns to choose in the row setup for account schedules as a beginning point, and go through some simple explanations of how they are used.
Fields to start with:
Row No. – The row number is completely optional, but highly recommended. This simple element of the row setup will eventually be one of the key features of your account schedule, allowing you to calculate and organize with ease.
Description – This is the one place you have to communicate, in words, what you’re showing on each line of your report.
Totaling Type: Posting Accounts or Formula – This field tells NAV what you’re going to do here. You’re either going to pull data from your general ledger posting accounts or calculate a formula.
Totaling – Which general ledger accounts you want to pull or what formula you want to calculate.
Row Type: Net Change or Balance at Date – The key here is knowing what type of accounts you’re reporting on. If you’re using income statement accounts (Revenue/Expense), then you need to use net change. If you’re using balance sheet accounts (assets/liabilities), then you need to use Balance at Date.
Shown below are both the design view and the user view of a summarized income statement, showing the use of these five options.
Role Tailored Client
Classic Client
This posting is one of the Top 20 Most Viewed in the last year! Follow this link to see the entire list.
Use insert accounts for a fast start on building a trial balance
Posted: June 11, 2012 Filed under: Uncategorized | Tags: Account Schedules, Classic Client, export to excel, general ledger, insert accounts, NAV, row setup, RTC, trial balance Leave a commentYou’re diving into using account schedules – you’ve figured out where they are, created a brand new one, given it an easy short name and a longer descriptive name and now you’re ready to get started. Now what? You need to begin with the row setup; the foundation of all account schedules.
The row setup defines what you’ll see on the lines of your report. Generally you’ll want to define general ledger accounts, or groups of general ledger accounts, in the row setup when you’re just getting started. Later, you’ll want to add some column layouts but let’s focus on getting that foundation built first.
The easiest way to get going with a new row setup is to put together a trial balance. Your trial balance should be a listing of all your general ledger accounts, one line each, which will show the balance of each account on a particular date, generally a month or period end. If you add them all up, all the accounts should balance to zero, which is one way to prove that your books are in balance.
Role Tailored Client Instructions
If you’re using the role-tailored client (RTC), choose Edit Account Schedule from the action pane.
You’ll get a new screen with many blank rows ready to be used. From this screen, choose Actions=>Functions=>Insert Accounts.
This opens up your G/L Account List. Use your mouse to select all the rows you want to add to your row setup.
Once you’ve highlighted them all (they should turn light blue), hit OK, and all the accounts you’ve selected will be added to your row setup, one row per account.
At this point, don’t make any changes to the row setup. Let’s see if it matches the canned trial balance that you can print directly out of NAV. Choose the Overview button (shown in the above screenshot). Select a column layout. If you don’t have one yet, hang in there, we’ll cover that in an upcoming post. For now, I’m going to choose a simple one that shows balance only. Select Show Matrix from the action pane.
This will show all the accounts you selected in your row setup, now in your account schedule.
To prove out the balance, dump the account schedule into Excel. Do this by choosing Actions=>Functions=>Export to Excel. On the next window choose the option to Create a New Workbook. Hit OK, and your schedule will export to Excel. Watch out: If you’ve used Begin or End-Total lines in your chart of accounts, you’ll need to remove these lines. These are calculated fields, and should not be a part of your trial balance.
Let’s run the canned trial balance from NAV as a comparison to see if we’ve done this right. Follow this trail to get to the report: Departments=>Financial Management=>General Ledger=>Reports=>Financial Statement=>Trial Balance. Select Actual Balances as your report column and make sure the date filter is set to the same date you used when running the account schedule. Compare the report to the account schedule. Each line should match between the two reports and the total should be zero on both, proving your trial balance is indeed in balance.
Classic Client Instructions
For those who use the classic client, let’s run through the same steps. Starting from Financial Management=>General Ledger=>Analysis & Reporting=>Account Schedules, choose your new account schedule from the list.
Hit OK, and you’ll get a new screen with many blank rows ready to be used. From the Functions button on the bottom right, choose Insert Accounts.
This opens up your G/L Account List. Simply click on the blank cell immediately to the left of the No. field to select all accounts quickly.
Once you’ve highlighted them all (they should turn dark blue), hit OK, and all the accounts you’ve selected will be added to your row setup, one row per account.
At this point, don’t make any changes to the row setup. Let’s see if it matches the canned trial balance that you can print directly out of NAV. Hit OK, and then choose Overview from the Account Schedules button found on the bottom right.
Select a column layout from the Column Layout Name field. If you don’t have one yet, hang in there, we’ll cover that in an upcoming post. For now, I’m going to choose a simple one that shows balance only.
This will show all the accounts you selected in your row setup, now in your account schedule.
To prove out the balance, dump the account schedule into Excel. Do this by choosing Export to Excel from the Functions button on the bottom right.
On the next window choose the option to Create a New Workbook. Hit OK and your schedule will export to Excel. Watch out: If you’ve used Begin or End-Total lines in your chart of accounts, you’ll need to remove these lines. These are calculated fields, and should not be a part of your trial balance.
Let’s run the canned trial balance from NAV as a comparison to see if we’ve done this right. Follow this trail to get to the report: Financial Management=>General Ledger=>Reports=>Financial Statement=>Trial Balance. On the Options tab, select Actual Balances as your report column and make sure the date filter is set to the same date you used when running the account schedule. Compare the report to the account schedule. Each line should match between the two reports and the total should be zero on both, proving your trial balance is indeed in balance.