Get the skills you need to produce robust financial reporting using the software you already own! New NAV2013 account schedules training now available!
Posted: August 14, 2013 Filed under: Uncategorized | Tags: Account Schedules, financial reporting, general ledger, learning, NAV 2013, NAVUG Academy, training Leave a commentLooking for training on NAV account schedules? There are new classes now available. NAVUG Academy distance learning is offering two half day instructor led online sessions on September 20th. There is also a full day classroom session on October 21st held as part of the pre-conference training for NAVUG Forum, held in sunny Tampa, Florida. Detailed descriptions of all three classes can be found below, or simply click on the title of the session you want to register!
Account Schedules: Basics – half day course, 9:00am to 1:00pm, (9/20)
Microsoft Dynamics NAV account schedules are a powerful tool used to create custom financial statements directly from your general ledger. In this in-depth training session, you will learn the fundamental principles of account schedule construction. This course is open to any NAV user.
Using hands-on exercises, you will have the opportunity to apply what you learn during class to a basic trial balance and master the three basic account schedule building blocks: row setups, column layouts, and analysis views. You’ll discover how to filter your financial reporting by dimensions, layer in budgeted information, and produce reports by exporting to Excel in the most efficient way possible!
In this class all hands-on exercises will be done using NAV2013. Almost all account schedule functions are applicable to lower versions of NAV, so you will still benefit from the training if you use a version below NAV2013.
Functionality of account schedules is virtually the same, but navigation and look and feel is significantly different. This class is being taught only in the NAV2013 version. If you are using the Classic Client and will not be moving to NAV2009 RTC or NAV2013 soon, please wait to take a Classic class. If you are an experienced Classic account schedule user who wants to learn more about NAV2009 RTC or NAV2013, it is recommended that you begin with the Account Schedules: Basics class simply to get used to the new navigational paths required.
Learning Objectives:
• Understand basic construction of row setups and column layouts
• Learn how to build, update, and apply analysis views
• Use filters, formulas, and formatting
• Incorporate dimensions and budgets
• Integrate account schedules to Excel
Instructor: Kerry Rosvold
Audience: Finance
Level: Intermediate
Prerequisites: Intermediate to advanced level of experience as a NAV financials end user
Account Schedules: Beyond the Basics – half day course, 2:00pm to 6:00pm, (9/20)
If you already understand the three basic account schedule building blocks and you’ve got a firm grasp on dimension and budget filtering (or if you’ve already taken Account Schedules Basics), you are ready to expand what you know with this class! This course is open to any NAV user.
Using hands-on exercises, you will have the opportunity to apply what you learn during class to a simple balance sheet and income statement. You’ll discover how to design and use multiple column layouts with the same row setup base and expand time periods reported beyond the basics. Move past simple dimension filtering and incorporate dimensions into row setups and column layouts! Learn how to use more advanced formulas like base for percent and see how to use account schedules as an alternative way to reporting from the NAV budget tool.
In this class all hands-on exercises will be done using NAV2013. Almost all account schedule functions are applicable to lower versions of NAV, so you will still benefit from the training if you use a version below NAV2013.
Functionality of account schedules is virtually the same, but navigation and look and feel is significantly different. This class is being taught only in the NAV2013 version. If you are using the Classic Client and will not be moving to NAV2009 RTC or NAV2013 soon, please wait to take a Classic class. If you are an experienced Classic account schedule user who wants to learn more about NAV2009 RTC or NAV2013, it is recommended that you begin with the Account Schedules: Basics class simply to get used to the new navigational paths required.
Learning Objectives:
• Build essential financial statements, including an income statement and balance sheet
• Extend knowledge of additional time periods used in column layouts
• Expand use of dimensions into account schedules
• Use account schedules to report on all budgeting information
Instructor: Kerry Rosvold
Audience: Finance
Level: Intermediate
Prerequisites: Intermediate to advanced level of experience as a NAV financials end user; have taken Account Schedule Basics or equivalent prior experience with account schedules
Account Schedules and Analysis Views – full day course, 8:00am to 5:00pm, (10/21)
Microsoft Dynamics NAV account schedules can be a powerful tool used to create custom financial statements and analysis reports directly from your general ledger. Attend this all day training session to get a firm foundation on the basic principles of account schedule construction and learn how to build all of your essential financial statements. Using hands-on exercises, you will have an opportunity to stretch beyond the basics and discover how to build multiple row and column layouts, how to create dynamic reports using dimensions, how to incorporate budgets into your schedules, and how to leverage the power of integration to Excel to make your reports even more flexible. Additional time will also be spent on analysis reports and analysis by dimensions.
Microsoft Dynamics NAV version: This class will be taught in NAV 2013, and backward compatible with NAV 2009 RTC. If you are using NAV 2009 Classic, you will benefit from the class as the concepts and approaches are the same, however the navigation and user interface is different. These differences will be addressed during class.
Learning Objectives:
• Build essential financial statements, including an income statement and balance sheet
• Understand construction of row setups and column layouts
• Use filters, formulas, and formatting
• Integrate account schedules to Excel
• Incorporate dimensions and budgets to account schedules
• Extend principles to use of analysis reports and analysis by dimensions
Instructor: Kerry Rosvold, Augsburg Fortress Publishers
Audience: Finance
Level: Intermediate
Prerequisites: Intermediate to advanced level of experience as a NAV financials end user
nChannel lists Dynamics NAV Financials as a top Microsoft Dynamics NAV Blog
Posted: April 30, 2013 Filed under: Uncategorized | Tags: Account Schedules, budgets, Dynamics, financial reporting, Microsoft, NAV 1 CommentnChannel gave Dynamics NAV Financials a really nice mention in their recent listing of top Microsoft Dynamics NAV blogs.
Here’s a blurb from the article:
About the Blog: Kerry uses her extensive experience in Dynamics NAV to give readers an in-depth look at account schedules, financial reporting, dimensions, budgets and more. Readers can also follow the blog on Facebook, follow Kerry on Twitter and subscribe via email in order to receive notifications of new posts (the blog is updated often, with some days generating multiple posts).
For the full article, listing twelve other really fantastic NAV blogs and communities, click here!
Blogiversary Top 20 (#1) Account Schedules Formulas Page
Posted: April 29, 2013 Filed under: Uncategorized | Tags: account schedule formulas, Account Schedules, cheat sheet, comparison date formula, comparison period formula, Dynamics, Microsoft, NAV, 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!
The most viewed blog entry in the Dynamics NAV Financials first year isn’t an entry at all, but an entire page! Check out the Account Schedules Formulas Page found here to see what 15 people a week come to the blog to use.
I developed this page in response to a question I would get in my classes on account schedules. “Do you have a cheat sheet for all the formulas you can use in Account Schedules?” I’ve shied away from this in the past, because it’s a complex question. You’ve got to take into account comparison period versus comparison date, column type used, use of filters, and like all Microsoft products, there is often more than one way to accomplish the same result. But I think I’ve got us a start. To my knowledge, there are bits and pieces of this out there, but this is the most comprehensive guide to account schedule formulas that I know of.
If you know of more, please submit them here. As long as I can test it to prove it works in the way described, and it’s different enough from already listed formulas, I’ll add it to the list and make sure to put a shout out to the contributor on the blog.
I believe the power of community is an amazing thing. We continually make each other better by sharing information. I’m looking forward to seeing what our amazing NAV community can accomplish!
Blogiversary Top 20 (#3) Basic column layout options for NAV Account Schedules
Posted: April 25, 2013 Filed under: Uncategorized | Tags: Account Schedules, balance at date, Classic Client, column layout, comparison date formula, comparison period formula, financial statement, fiscal year, general ledger, NAV, net change, RTC 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!
Column layouts in NAV account schedules are what you build to add more flexibility to your financial statements. You can have an unlimited number of column layouts to match together with your already existing row setups. In general, most basic row setups contain general ledger numbers and column layouts contain dates. As an example, when I produce my income statement, I will have one row setup that reflects a summarized income statement and at least four different column layouts that reflect different configurations of month to date, year to date, comparisons against budget, and twelve month trended views.
Just like with row setups, there are many available options in the column layouts. This large variety of options can sometimes be overwhelming to the new account schedule user. I’ll show you which columns to choose in the column layouts for account schedules as a beginning point, and go through some simple explanations of how they are used.
Fields to start with:
Column No. – The column number is completely optional, but highly recommended. This simple element of the column layout will eventually be one of the key features of your account schedule, allowing you to calculate and organize with ease.
Column Header – This is where you’ll define, in words, what you’re showing in each column of your report. Keep it short; there is a 30 character limit.
Column Type: Net Change, Balance at Date, or Formula – There are actually seven options to choose from here, but I recommend that you limit yourself to these three when you’re just getting started. 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. Formula allows you to perform calculations in a column.
Ledger Entry Type – This column will allow you to define what type of ledger entries you will show. This is where you can choose actual general ledger entries or budgeted general ledger entries.
Formula – If you’ve chosen Formula as the Column Type, this is where you’ll put the formula.
Comparison Period Formula – This column allows you to define date formulas that are used to calculate the amounts shown. I generally recommend that beginning account schedule users start out using the comparison period formula field instead of the comparison date formula field. The comparison period formula field references the accounting periods set up in the fiscal year, so this option seems the most consistent, and is especially necessary for companies that may not follow a calendar fiscal year. Common data labels used in this field are CP for current period or -1CP for previous period and FY for fiscal year or -1FY for prior year.
Shown below are both the design view and the user view of a column layout for a summarized income statement showing year to date and prior year to date information in the columns to demonstrate the use of these six basic options in an account schedule.
Role Tailored Client
Classic Client
For more information on row setups, please see these posts: Basic row setup options for NAV Account Schedules and Complete row setup options for NAV account schedules.
If you’re just beginning to use account schedules, see Getting started with a new account schedule.
Blogiversary Top 20 (#6) Getting started with a new account schedule
Posted: April 22, 2013 Filed under: Uncategorized | Tags: Account Schedules, Classic Client, NAV, RTC 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
If you are just getting started with account schedules, you need some basic information to get going. This post will show you where to find account schedules in NAV, how to create a new one, and how to name them. We’ll cover both the role-tailored client (RTC) as well as the classic client.
Where can you find them?
If you’re using NAV with the RTC, choose Departments, then General Ledger from the Financial Management menu, then choose the Account Schedules link under the Reports and Analysis and Analysis & Reporting menus.
If you’re using NAV 2009 and prior versions with the classic client, choose Financial Management=>General Ledger=>Analysis & Reporting=>Account Schedules.
How do you create a new account schedule?
In the RTC, creating a new account schedule is made easy with the New button located on the action pane. If you’re a keyboard shortcuts person, you can use Ctrl-N, or if you prefer to use menu options, go to Actions=>New.
In the classic client, use F3 to create a new record for the account schedule, or select New from the Edit menu.
How do you name a new account schedule?
Once you’ve created a new report using the RTC, you’ve got 10 characters available for the Name field, and 80 characters available for the Description field.
The classic client has the same parameters.
Both clients will allow you to use any combination of numbers, letters, or special characters.
Keep in mind, that regardless of whether you are using RTC or the classic client, the names you use for the description field will be the default names used when you print account schedules directly out of NAV.
Blogiversary Top 20 (#7) 15 days of NAV dimensions
Posted: April 19, 2013 Filed under: Uncategorized | Tags: Account Schedules, Convergence, CustomerSource, dimensions, Dynamics, NAV, NAV 2013, 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!
March Madness may mean basketball for some folks, but for me it means Convergence! Getting a chance to participate as a speaker at Microsoft Dynamics Convergence is a great opportunity to meet new NAV users I haven’t met before. I’m always looking to expand my network of knowledgeable professionals, and one of the ways I do that is by sharing information.
I’m lucky enough to be giving a concurrent session this year called Tips & Tricks for Working with Dimensions in Microsoft Dynamics NAV and I thought I would put together a series for the blog on the same topic to get the information out to a wider audience. For the next 15 days, there will be a brand new post on one of these dimension topics. Enjoy!
Day 1 4 questions to ask when deciding how to use NAV dimensions in your business
Day 2 5 reasons you need to use NAV dimensions
Day 3 The finance professional’s perspective on NAV dimensions
Day 4 Why finance and IT need to work in partnership on a NAV dimension strategy
Day 5 Viewing NAV dimensions on postings: where can you see them?
Day 6 NAV default dimensions and value postings applied to master data
Day 7 NAV default dimensions and value postings on the chart of accounts
Day 8 NAV dimension combinations for additional accuracy
Day 9 NAV dimension priorities
Day 10 Resolving NAV dimension errors
Day 11 NAV dimensions in account schedules
Day 12 NAV dimensions in budgets and consolidations
Day 13 Communicating to IT about NAV dimensions and NAV2013 dimension sets
Day 14 Add NAV dimensions as your business changes
Day 15 Learning more about dimensions from Microsoft Dynamics Customer Source
BONUS View Convergence 2013 session on Microsoft Dynamics NAV dimensions here
This posting is one of the Top 20 Most Viewed in the last year! Follow this link to see the entire list.
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 (#14) Video Demo: How to export NAV account schedules to Microsoft Excel
Posted: April 10, 2013 Filed under: Uncategorized | Tags: Account Schedules, dimensions, Excel, export to excel, financial statement, learning, NAV, NAVUG, NAVUG Academy, training 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!
There are some quick and easy ways to publish NAV Account Schedules to Microsoft Excel but some of them end up with some pretty ugly formatting unless you know a few secrets on how to make them look better. This short demo video shows you how to create a nicely formatted NAV Account Schedule in Microsoft Excel that only requires formatting the first time you set it up. Each time you replicate the report after that, you can simply update the report with new numbers and the formatting will remain the same every time.
While this demo shows how to create a financial report with three monthly tabs, you can easily extend this technique in a couple of different ways. I use this same technique to produce my monthly financial statement package. I have eighteen pages in the package I produce each month, and each page is a different account schedule. When I start a new month, I copy the Excel workbook and rename it with the new month, then use exactly this same technique to produce the new month’s reports. By using this technique, the new copied workbook acts as a “shell” to receive my new numbers. From month to month, I am able to maintain consistent formatting in my reporting while producing my financial reports in the most efficient way possible. I can apply the same principles to my monthly team budget reports. In this case, I can produce the same actual versus budget report over and over with a new dimension filter applied for each team, one per each Excel worksheet in the workbook.
If you’d like to learn more about account schedules, please visit my 2013 Classes page. This short video demo comes from my Account Schedules Basics class which I teach through NAVUG Academy. This class offering is just one of many classes that are being offered by NAVUG Academy in 2013 to help you get more out of your use of NAV at your company.
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!