Use Statistical Accounts to calculate meaningful Key Performance Indicators in Dynamics GP

It is not uncommon during financial statement design to create reports intended to monitor Key Performance Indicators. Dynamics GP's statistical account feature provides functionality, which expands this capability beyond simple financial ratios.

Statistical accounts (A.K.A. Unit Accounts) allow organizations to keep track of non-financial data in the General Ledger, like square footage, head count, machine hours, labor hours, quantities produced, inspected, rejected, sold, returned and/or serviced in summary or detail. 

Statistical accounts can be posted to without the need to balance the transaction, increasing or decreasing the balance of the account as necessary. These account balances can then be pulled into financial reports via FRx or Management Reporter and used in calculations to compute things like sales price and cost of goods sold per unit, cost of labor per hour, revenue per service call, etc.

In order to use this feature, Statistical Accounts must be created and maintained. Statistical Accounts are maintained in Financial > Cards > Financial > Unit Account

Unit Account setup is similar to Financial Account Setup; however, there are some additional settings available in this window.

Account Number is a required field, and can make use of the sub-account structure to track statistics at a departmental, divisional or product line level. 

Description is also a required field, so some value must be entered in this field. 

All other fields are optional. 

The Alias field can provide a shortcut to speed account entry.

Decimal Places allow for the configuration of from 0 to 2 decimal places for tracking statistical data.

Series allows for the selection of modules, in addition to the General Ledger, which will allow the entry of statistical data for this account. Options are Sales and Purchasing. It is typical for statistical information to be collected and imported or entered monthly.

There are also a pair of check boxes, one of which is only present in more recent releases of Dynamics GP (2013 & 2015).

The Inactive check box does precisely what it appears to do. Checking this box makes this account inactive, which prevents it from being used, but does not remove it from the system. One of the options in the Year-End close can automate the removal of inactive accounts, so be careful there.

The Clear Balance During Year-End Close check box was added in version 2013, and also does precisely what it appears to do. When this box is checked, the balance at Year-End Close is not treated like a typical Balance Sheet Account. Typically, Balance Sheet Accounts are zeroed and their balances are brought forward into the next year as beginning balances. This is not optimal when statistical/unit accounts are being used to track units of production or sales, where it is best if the beginning balance for the year be zero.

All other fields are used to view information about the account and cannot be edited in this window.

Once accounts are properly configured, statistical information can be tracked by posting entries to the General Ledger, which use posting date to assign transaction information to the appropriate fiscal period(s).

I have seen statistical accounts used to track numerous relevant statistics at many organizations (miles, gallons, trips, calls, etc.) The use of Unit Accounts in Dynamics GP is limited only by your imagination.

SQL 'sa' user cannot login to Dynamics GP 2015

Every now and again I see a post in the Dynamics GP community forum which tickles my fancy. Most recently, a forum user reported the following issue:

I have a new install of Dynamics GP 2015 in a terminal server environment, installed on SQL Server 2012 using SQL Server Authentication.  The 'sa' login works fine when launching SQL Management Studio but when attempting to login to GP I receive the following:

All other users can log in including DYNSA.  In addition, I'm not able to add companies in the new install of Management Reporter which I assume is related.

I was impressed when Jorge Mejia asked one question about logging into GP Utilities and the length of the 'sa' account's password.

There is a mismatch between the ‘keyable’ length for the login password between Dynamics GP Utilities login and Dynamics GP login windows.

The maximum length of the datatype for the password in the Dynamics GP Utilities login window is 30, while the maximum length of the datatype for the password in the Dynamics GP login window is 15, therefore you have to go for the shortest length.

Symptoms:

Cannot Log Into Dynamics GP using SQL Authentication

Can Log Into SQL Server Management Studio

Can Log Into Dynamics GP Utilities

Cannot Add New Company/Companies in Management Reporter

Diagnosis:

SQL 'sa' account password longer than 15 Characters

Resolution:

Change SQL 'sa' password less than 16 characters long.

Management Reporter Missing Account Analysis - Missing Building Blocks

One of the many differences between FRx and Management Reporter is the means by which users determine which accounts are missing from a Row Definition (formerly Row Format in FRx). In FRx one explicitly identified which reports should display missing and duplicate accounts by opening Report Options > Advanced > Exception Report and mark to report either or both missing and duplicate accounts.

In Management Reporter, this is not the case. Not being one to recreate the wheel. Crestwood Associates created a fine post on how to address these issues in Management Reporter. Here is a link to this article:

Crestwood Associates Blog Article on Missing and Duplicate Accounts in Management Reporter

One of the finer points of the Missing Account Analysis window, it is possible to suppress the reporting, which can be handy if the accounts listed as missing should not be on the report. Clicking on Exclude check box next to the Account or Report (I am less enthusiastic about this option, as Chart of Accounts change and grow over time) will suppress the account or report in the Missing Account Analysis window.

Consequently, the Missing Account Analysis window may, or may not contain all of the current Row Definitions and/or various Missing Accounts. If you are conducting a Missing Account Analysis and find you are completely missing a Row Definition, one of two things may be the cause. 

First, it is entirely possible for someone (even yourself) to have excluded the Row Definition from Analysis. In order to determine if this is the case, you should click on the Show excluded building blocks and values button. This will also reveal any accounts manually excluded from analysis. See image one below:

The second cause could be that recent changes to the Row Definition may not have been synchronized with the Missing Account Analysis window. If this is the case, then clicking on the Refresh button (see image one above) will update this information - it may take some time, if there are a significant number of changes to update.

A thorough Missing Account Analysis will require the Show excluded building blocks and values box be checked and the window be refreshed.

Redbeard (Harry Lee) Awarded Microsoft MVP - Dynamics GP

For the past 24 hours I have been wrestling with the decision of whether or not to post this article. Yesterday I received formal notification I had been inducted into the elite group of Microsoft Most Valuable Professionals for my contributions to the Dynamics GP community this past 12 months.

For the uninitiated, the Microsoft MVP Award recognizes the exceptional technical community leaders who actively share their high quality, real world expertise with others, or so the email reads…

This is the kind of news one typically wants to shout from the rooftops, and within a select group of friends, family and coworkers, I did just that. 

The reason for my hesitation to post this article is two-fold. In life when accolades arrive, it is best to act as if you have been there before and behave in a dignified manner. Secondly, I am acutely aware of my own imperfection and am still doing the math on whether or not I feel worthy of such an honor.

Last year, around this time, I didn’t know such an honor existed. What I did know was one of the greatest proponents of Dynamics GP Manufacturing and a bright light in Microsoft Dynamics GP Constellation of stars, Richard Whaley, fell from the sky, or was promoted (depends on your belief system).

Richard Whaley Tribute by Leslie Vail

Richard and I were not close friends, but we met numerous times, over the years, at various events, and excitedly discussed Dynamics GP and Manufacturing.  My immediate reaction, to Richard’s passing, knowing what I did, was there were going to be one huge pair of shoes to fill.

It would be too grand a statement to say my contributions to the Dynamics GP Community this past year have been a tribute to Richard, as they were much more proof of Richard’s ability to challenge and inspire people. I am both honored and humbled to be recognized as an MVP, and will work hard to be deserving of the award.