Affordable Health Care Reporting and Dynamics GP

By Sherlene Sorenson

Definition of a full time employee per the Affordable Care Act:

A full time employee is defined under the Affordable Care Act as an Employee who works 30 hours per week, per month on average.

Currently, Microsoft does not plan on creating any specific reports to calculate the average hours worked per employee, so reporting for the Affordable Care Act will need to be done with a custom report. As a result, we’ve addressed this issue using SmartList Builder, but it doesn’t do a clear-cut calculation.

For example: if Company X; issues checks at an odd time, not every employee listed in the report would always be paid for the same number of weeks during a specified timeframe. There still is a need for manual monitoring or at least looking more carefully than just taking the report at face value to see who meets the requirements. Please see below for instructions on how to use the attached SmartList Builder object that will work for GP10, GP2010 and GP2013. This object pulls data from the Payroll Transaction History table, and summarizes to one row per employee for the Check Date Range included.

Instructions:

  1. Save the attached SmartList Builder object somewhere that you can retrieve it while in GP.
  2. In GP>Microsoft Dynamics GP>Tools>SmartList Builder>Import, Import the SmartList Builder object.
  3. Open SmartList to include the new object.
  4. Grant access to the SmartList Builder object. Choose a Task to add access to, and you will find this Smartlist under Product: SmartList, Type: SmartList Object, Series: SmartList Objects. Mark the checkbox next to “Payroll Historical Trx-Paycodes-SUMMARIZED by Empee”.
  5. Re-open Smartlist. The new object will show in Smartlist>Payroll>Payroll Historical Trx-Paycodes-SUMMARIZED by Empee.
  6. Set up a Favorite with the following columns:
  • Employee ID
  • Last Name
  • First Name
  • Units to Pay (hours)
  • UPR Trx Amount (if dollars are desired)
  • Earliest Ck Date Included
  • Latest Ck Date Included
  • Hire Date
  • Date Inactivated Optional, if desired:
  • TRX Beginning Date (optional)
  • TRX Ending Date (optional)
  • Adjusted Hire Date
  • Last Day Worked
  • Employee Class
  • Type of Employment
  • HR Status
  • Inactive Employee
  1. Set up Search Restrictions Using the Check Date field, where Check Date is within the date range you want to test for. You may also want a restriction to exclude inactive employees from the results.
  2. Let the view generate until complete.
  3. Export the view to Excel.
  4. Add a column for Number of Weeks. Manually calculate the number of weeks based on Check Date Range or Transaction Date Range.
  5. Add another column with a calculation of Average Hours per Week (Total Hours / Number of Weeks).
  6. Examine the data for employees that fall on the outside extremes. It may be an employee that was hired or terminated or had a Leave of Absence in the middle of the date range. Also keep in mind that if extra checks are issued for pay advances or missed hours, it may throw off the results for the employee(s) involved.

Notes:

  1. This SmartList Builder object has a built-in restriction to not pull data older than 2012.
  2. This SmartList Builder object has a built-in restriction to pull only Paycode transactions (not Deductions, Benefits, Taxes, etc.).
  3. If you have paycodes that use Units or Hours that should not be included as part of Total Hours, there will need to be a Restriction added or a Calculated field added to the object in SmartList Builder to exclude inappropriate paycodes.

Visit our website to learn more about Microsoft Dynamics GP and our full product line.

Contact InterDyn BMI today!

Advertisements