Friday, January 17, 2014

Dynamics GP Excel Dashboard available now!

 

This comes to us from one of Dynamics GP’s finest, the infamous Mark Polino! Jump to  www.DynamicAccounting.net/Analytics to get more details.

This is the truly painless way to get started with Excel dashboards, have someone else do it for you Smile. Imagine, an Excel dashboard with all of the bells and whistles ready to go. We’ve all seen and heard how cool dashboards are, and wish we had the time to figure out how to create them. Well, the time has been reduced to just a few minutes. Seriously, the dashboard is easy to install; and, honestly, I do mean easy.

Simplicity is a word that appeals to me, and Mark’s Excel dashboard falls into that category. One of the things I like to point out during my development/tool classes is that you should ALWAYS look around to see if anyone else has already created the thing you are wanting to create. So many times, the answer is yes.  This is one of those yes times.

If you want to get started, but can’t carve out the hours it takes to develop this anew, this is for you.

Though the dashboard is perfectly fine just like it is, you have the option to change it however you want. We all know that changing something is far easier than creating it from scratch.

Can you say plug and play?  I am among the laziest people out there. I know the macro language so well because it saves me time and clicks.  If I can save clicks, I’m all for it! This is my kind of dashboard; just connect it to your data source and you’re ready to go. And fear not, if data sources are not your area of expertise, Mark makes that easy too.

So what’s available? Mark has released a dashboard for the Sales series. Here’s what it includes:

● Top Customers with corresponding costs               
● Top Items with corresponding costs               
● Top Salespeople, Territories and Sites               
● Sales over time with forward looking projection               
● Sales mix over time by item class               
● A full range of data filters to control information

Click the link and check it out.

How many of these do you think Mark can develop in 50 minutes?   

Until next post!

Leslie          

2014 Payroll Tax Tables–how to fill out manually

I’ve been getting quite a few calls lately about how to get the new 2014 tax tables if they can no longer get them from Microsoft. These folks are on versions 9 or 10, so they are out of luck on the download. Scandalous though it may be to remain on an unsupported version, they still have to update their tax tables. Fortunately, you can easily change the tax rates yourself using Dynamics GP.

After applying the current tax tables (as of 01-16-2014), this is what the windows look like:

FICAM 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button2014 FICAM no tear

 FICAS 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
image

EFICM

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
2014 EFICM no tear

EFICS 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] buttonimage

FED SINGLE 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status SINGLE
Push the [Tables] buttonPayroll Tax Tables FED SINGLE

FED MAR 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status MAR
Push the [Tables] buttonPayroll Tax Tables FED MAR

FED NRA 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NRA
Push the [Tables] buttonPayroll Tax Tables FED NRA

If you can’t read the screen shots, I also have the numbers for both the 2014 and 2013 in the tables below:

FICAM 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$200,000

$0.00

1.45%

$0.00

$200,000

$0.00

$0.00

2.35%

$0.00

$0.00

$0.00

$0.00

0.00%

$0.00

FICAS 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$117,000

$0.00

6.20%

$0.00

$0.00

$0.00

$0.00

0.00%

$0.00

EFICM 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$0.00

$0.00

1.45%

$0.00

$0.00

$0.00

$0.00

0.00%

$0.00

EFICS 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
 
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$117,000

$0.00

6.20%

$0.00

$0.00

$0.00

$0.00

0.00%

$0.00

FED SINGLE 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status SINGLE
Push the [Tables] button
 
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$2,250.00

$0.00

0.00%

$0.00

$2,250.00

$11,325.00

$0.00

10.00%

$2,250.00

$11,325.00

$39,150.00

$907.50

15.00%

$11,325.00

$39,150.00

$91,600.00

$5,081.25

25.00%

$39,150.00

$91,600.00

$188,600.00

$18,193.75

28.00%

$91,600.00

$188,600.00

$407,350.00

$45,353.75

33.00%

$188,600.00

$407,350.00

$409,000.00

$117,541.25

35.00%

$407,350.00

$409,000.00

$0.00

$118,118.75

39.60%

$409,000.00

$0.00

$0.00

$0.00

0.00%

$0.00

FED MAR 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status MAR
Push the [Tables] button
 
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$8,450.00

$0.00

0.00%

$0.00

$8,450.00

$26,600.00

$0.00

10.00%

$8,450.00

$26,600.00

$82,250.00

$1,815.00

15.00%

$26,600.00

$82,250.00

$157,300.00

$10,162.50

25.00%

$82,250.00

$157,300.00

$188,600.00

$28,925.00

28.00%

$157,300.00

$235,300.00

$413,550.00

$50,765.00

33.00%

$235,300.00

$413,550.00

$466,050.00

$109,587.50

35.00%

$413,550.00

$466,050.00

$0.00

$127,962.50

39.60%

$466,050.00

$0.00

$0.00

$0.00

0.00%

$0.00

FED NRA 2014

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NRA
Push the [Tables] button
 
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$9,075.00

$0.00

10.00%

$0.00

$9,075.00

$36,900.00

$907.50

15.00%

$9075.00

$36,900.00

$89,350.00

$5,081.25

25.00%

$36,900.00

$89,350.00

$186,350.00

$18,193.75

28.00%

$89,350.00

$186,350.00

$405,100.00

$45,353.75

33.00%

$186,350.00

$405,100.00

$406,750.00

$117,541.25

35.00%

$405,100.00

$406,750.00

$0.00

$118,118.75

39.60%

$406,750.00

$0.00

$0.00

$0.00

0.00%

$0.00

Now for the 2013 Tax Tables:

FICAM 2013

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$200,000

$0.00

1.45%

$0.00

$200,000

$0.00

$0.00

2.35%

$0.00

$0.00

$0.00

$0.00

0.00%

$0.00

FICAS 2013

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$113,700

$0.00

6.20%

$0.00

$0.00

$0.00

$0.00

0.00%

$0.00

EFICM 2013

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$0.00

$0.00

1.45%

$0.00

$0.00

$0.00

$0.00

0.00%

$0.00

EFICS 2013

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NA
Push the [Tables] button
 
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$113,700

$0.00

6.20%

$0.00

$0.00

$0.00

$0.00

0.00%

$0.00

FED SINGLE 2013

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status SINGLE
Push the [Tables] button
 
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$2,200.00

$0.00

0.00%

$0.00

$2,200.00

$11,125.00

$0.00

10.00%

$2,200.00

$11,125.00

$38,450.00

$892.50

15.00%

$11,125.00

$38,450.00

$90,050.00

$4,991.25

25.00%

$38,450.00

$90,050.00

$185,450.00

$17,891.25

28.00%

$90,050.00

$185,450.00

$400,550.00

$44,603.25

33.00%

$185,450.00

$400,550.00

$402,200.00

$115,586.25

35.00%

$400,550.00

$402,200.00

$0.00

$116,163.75

39.60%

$402,200.00

$0.00

$0.00

$0.00

0.00%

$0.00

FED MAR 2013

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status MAR
Push the [Tables] button
 
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$8,300.00

$0.00

0.00%

$0.00

$8,300.00

$26,150.00

$0.00

10.00%

$8,300.00

$26,150.00

$80,800.00

$1,785.00

15.00%

$26,150.00

$80,800.00

$154,700.00

$9,982.50

25.00%

$80,800.00

$154,700.00

$231,350.00

$28,457.50

28.00%

$154,700.00

$231,350.00

$406,650.00

$49,919.50

33.00%

$231,350.00

$406,650.00

$458,300.00

$107,768.50

35.00%

$406,650.00

$458,300.00

$0.00

$125,846.00

39.60%

$458,300.00

$0.00

$0.00

$0.00

0.00%

$0.00

FED NRA 2013

Administration | Setup | System | Payroll Tax
Enter or select the Tax Code
Push the [Filing Status] button
Scroll to Filing Status NRA
Push the [Tables] button
 
If Over But Not Over Tax Amount Tax Rate On Excess Over

$0.00

$8,925.00

$0.00

10.00%

$0.00

$8,925.00

$36,250.00

$892.50

15.00%

$9075.00

$36,250.00

$87,850.00

$4,991.25

25.00%

$36,900.00

$87,850.00

$183,250.00

$17,891.25

28.00%

$89,350.00

$183,250.00

$398,350.00

$44,603.25

33.00%

$186,350.00

$398,350.00

$400,000.00

$115,586.25

35.00%

$405,100.00

$400,000.00

$0.00

$116,163.75

39.60%

$406,750.00

$0.00

$0.00

$0.00

0.00%

$0.00

 

That should do it!

Until next post

Leslie

Sunday, January 12, 2014

There is an error with at least one vendor record, print the customer/vendor record for details

This is an error I thought I’d add to our ‘When Things Go Wrong’ section. My thanks goes to Alaa Ramadan, who both identified the error and discovered it’s solution.  You can find the thread here.

The error presented itself when the user was attempting to complete a refund check transaction in the RM module. (Sales | Transactions | Refund Checks).

As it turned out, the associated vendor record had been put on hold thus preventing the refund check creation.  Clearing the hold checkbox on the Vendor Maintenance window solved the problem.

Thanks Alaa!

Until next post,

Leslie

Wednesday, January 8, 2014

Form W-2 Common Errors - Codes for Retirement Plans

This information comes straight from our friends at the IRS, it describes common mistakes made on Forms W-2. I have run into many of these as our clients begin to prepare their annual filings.

If you find that you are missing any of these items, be sure to modify the payroll card rather than just editing the W-2. If you need to change the W-2 (and W-3), you can re-create the year-end wage file.

If you have already loaded the 2014 tax tables, change the FICA table to reflect the 2013 amounts before re-creating the files. Change them back when you are done. Listed below are the rates for 2013 and 2014
Code Year Rate Limit
EFICM 2013 1.45% no limit
EFICS 2013 6.2% $113,700
FICAM 2013 1.45% up to $200,000
FICAM 2013 2.35% over $200,000
FICAS 2013 6.2% $113,700
       
EFICM 2014 1.45% no limit
EFICS 2014 6.2% $117,000
FICAM 2014 1.45% up to $200,000
FICAM 2014 2.35% over $200,000
FICAS 2014 6.2% $117,000

Depending on how many you have to change, you could also edit the Year-end wage files.  Those files are:

Physical Name Technical Name
UPR10100

UPR_Year_End_WORK_HDR

UPR10101

UPR_Year_End_WORK_Wage

UPR10103

UPR_Year_End_WORK_Pension

UPR10104

UPR_Year_End_WORK_Special

UPR10105

UPR_Year_End_WORK_State

UPR10106

UPR_Year_End_WORK_Local

UPR10107

UPR_Year_End_WORK_Other

Here are the IRS rules:

You must ensure that the information on Form W-2, Wage and Tax Statement, and Form W-3, Transmittal of Wage & Tax Statements, you issue to your employees contains correct retirement plan information because:

  • employees need accurate information to determine the correct deductions and credits on their tax return, and
  • IRS agents use the information from these forms to determine whether employers are complying with income and employment tax reporting requirements.

Common mistakes
During Form 5500 examinations and EPCU projects, IRS agents found employers using incorrect codes in Box 12 of Form W-2, for example:

  • code D for 401(k) elective deferrals incorrectly included 403(b), 457, or non-qualified amounts.
  • code E for 403(b) contributions but did not have a 403(b) plan.
  • code H to incorrectly report health benefits; code H is for elective deferrals to a 501(c)(18)(D) tax-exempt organization plan. (In fact, a recent Employee Plan Compliance Unit project found that only 6 % of employers who used this code actually contributed to a 501(c)(18) plan.)
  • code S for a SIMPLE 401(k); the correct code for a SIMPLE 401(k) is code D.

Common codes used for Box 12

Letter code:
Used for:
Description:

D
401(k) contributions
Elective deferrals to a 401(k) cash or deferred arrangement, including SIMPLE 401(k)s

E
403(b) contributions
Elective deferrals made under a 403(b) salary reduction agreement

F
408(k)(6) contributions
Elective deferrals made under a SARSEP

G
457(b) contributions
Elective and nonelective deferrals made to a 457(b) deferred compensation plan

H
501(c)(18)(D) contributions
Elective deferrals to a Section 501(c)(18)(D) tax-exempt organization plan (Included in the "Wages, Tips, Comp." amount in Box 1)

S
408(p) SIMPLE contributions
Deferrals made under a SIMPLE IRA plan

AA
Roth contributions
Designated Roth contributions under a 401(k) plan

BB
Roth contributions
Designated Roth contributions under a 403(b) plan

EE
Roth contributions
Designated Roth contributions under a governmental 457(b) plan (a tax-exempt organization’s 457(b) can’t have a designated Roth account)

See the Instructions for Forms W-2 and W-3 for a complete list of codes.

Form W-2, Box 13
The “Retirement plan” indicator in Box 13 shows whether an employee is an active participant in your company’s plan. If this box is checked, it lets the recipient know that depending on their filing status and modified adjusted gross income, they may not be entitled to a full deduction for their traditional IRA contributions. You should check the retirement plan box if an employee was an “active participant” for any part of the year in:

  • a qualified pension, profit-sharing, or stock-bonus plan under Internal Revenue Code Section 401(a) (including a 401(k) plan).
  • an annuity plan under IRC Section 403(a).
  • an annuity contract or custodial account under IRC Section 403(b).
  • a simplified employee pension (SEP) under IRC Section 408(k).
  • a SIMPLE retirement account under IRC Section 408(p).
  • a trust described in IRC Section 501(c)(18).
  • a plan for federal, state, or local government employees or by an agency or instrumentality thereof (other than a 457(b) plan).

Active participant
Generally, an employee is an active participant if covered by a:

  • defined contribution plan (for example, a 401(k) plan) for any tax year and is credited with any contributions or forfeitures, or
  • defined benefit plan for any tax year that the employee is eligible to participate.

Don’t check the retirement plan box if your company only has non-qualified or 457(b) plans.

Form W-3, Box b
Form W-3, Box b has checkboxes to specify the type of employer filing the form. You should check the appropriate box if you are a:

  • non-governmental tax-exempt 501(c) organization;
  • state or local government or instrumentality;
  • state or local government or instrumentality and have received a determination letter from the IRS indicating that you are also a 501(c)(3) tax-exempt organization; or
  • federal government entity or instrumentality.

Otherwise, you should check the “None apply” box. Only check one box.

Page Last Reviewed or Updated: 22-Nov-2013

Until next post!

Leslie Vail

Thursday, December 19, 2013

When things go wrong - 'Object reference not set to an instance of an object'

This must be one of my most hated error messages. Usually, I see it in Integration Manager, but this time it has shown up in Management Reporter.

Jake Friedl recently  posted a list of things to check when you receive this error. Thank you Jake! This is a great list:

  1. Check for missing ISO codes.
  2. Start GP and log into any company as 'sa'.
  3. Go to Microsoft Dynamics GP -> Tools -> Setup -> System -> Currency.
  4. You will need to select every single currency from the Currency ID lookup.
  5. Once selected, confirm a code is entered in the ISO Code field.
  6. Check for missing functional currencies.
  7. You will need to log into every single company in GP to check the following.
  8. Go to Microsoft Dynamics GP -> Tools -> Setup -> Financial -> Multicurrency.
  9. Confirm a currency is selected in the Functional Currency field.
  10. Check for a blank Budget ID.
  11. Start SQL Management Studio and log in as a SQL sysadmin.
  12. Run the following query against each GP company database: select * from gl00200
  13. If you find a blank value in the BUDGETID column, back up the GP database and clear the value with: delete gl00200 where BUDGETID= ''
  14. After going through the above, you will need to re-create the data mart integration if you are using the data mart provider:
  15. Close all instances of MR and back up the MR database.
  16. Start the Configuration Console and click on the integration under ERP Integrations.
  17. Click the Disable Integration button and wait a moment for that to take effect.
  18. Click the Remove link in the top-right.
  19. Stop the MR services at the top level of the Configuration Console and then delete the DDM database from SQL.
  20. In the Configuration Console, start the MR services and then click File -> Configure and deploy a new DDM integration.
  21. Enable the integration and wait for the initial load to complete.

Until next post!

Leslie