Topic: ‘Purchasing’

(Part 4) of series Dynamics GP 10 and 1099s. 

IRS Regulations, 3^rd party applications and Dynamics GP 10.  Read the rest of this entry »

Dynamics GP 10 and 1099s (Part 2) Tracking the W-9.  Tracking W-9s can be a tedious task in itself.  Make a list of 1099 vendors and mark off W-9s received. Send out W-9 notifications to vendors, receive some W-9s back, send out another notification, receive a few more and the cycle continues. 

Knowing that collecting W-9s  is a project here are some tips you can use to track W-9 received using Dynamics GP’s user defined fields and Smartlist. Read the rest of this entry »

So you implemented or upgraded to Dynamics GP 10.  Year End is approaching and you are wondering what to do about tracking W-9s and processing 1099s.  What are the options?

This 4 part blog series titled “Dynamics GP 10 and 1099s”  is an overview of the different options for those who upgraded this year and those who implemented, for the first time, Dynamics GP 10.  In this series, we will look at Dynamics GP 10 1099 functionality, W-9 information tracking, reviewing and updating the Vendor’s 1099 box, IRS rulings and other 3^rd party product add-ins to help you decide the most effective approach to handling your 1099s.  Read the rest of this entry »

Sales Tax collection and remittance is an often covered issue but Use Tax is often overlooked or ignored. In these difficult times, governments are increasing collection efforts and we are definitely hearing about increased use tax audits. While Microsoft Dynamics GP doesn’t provide specific use tax funtionality beyond it’s built in sales tax strengths, the sales tax features can be used quite effectively to accomodate use tax calculation, reporting and allocation.

To improve awareness of these features, I’ve created The Complete Guide to Use Tax Functionality in Microsoft Dynamics GP 10. The guide provides specific instructions and screenshots detailing how to setup Dynamics GP for various use tax scenarios using only default system functionality.

At iSight last week, I presented a high speed session titled 50 Dynamics GP Tips in 50 Minutes. Attendees got the presentation and notes on a USB drive but in case you’ve lost your drive or just need to point a colleague to the presentation, you can get the presentation with related notes here.

It’s often helpful to have an idea of the volume of transactions in a department. For example, it’s nice to know how many AP Vouchers and Checks get processed each week to understand the load on the AP department. This also provides a pretty objective benchmark for AP employees. To make this happen, we start with a SQL View that agregates the Open and Historical AP transactions by week.

This code uses a Friday to Thursday week with the expectation that checks are cut on Friday so the typical AP entry cutoff is end of day Thursday. If your week doesn’t work like that, the code is easy to change. After that we can query the view using a Smartlist, Excel, SQL Reporting Services or Crystal Reports. My preference has always been Excel because this data really lends itself to a graph.

The code to build the view looks like this:

Create View Metrics_APVoucherCountWeek as

–Get Open AP
Select
–Get end of week
Case
when datename(weekday,docdate) = ‘Friday’ then Docdate
when datename(weekday,docdate) = ‘Saturday’ then dateadd(Day,6,docdate)
when datename(weekday,docdate) = ‘Sunday’ then dateadd(Day,5,docdate)
when datename(weekday,docdate) = ‘Monday’ then dateadd(Day,4,docdate)
when datename(weekday,docdate) = ‘Tuesday’ then dateadd(Day,3,docdate)
when datename(weekday,docdate) = ‘Wednesday’ then dateadd(Day,2,docdate)
when datename(weekday,docdate) = ‘Thursday’ then dateadd(Day,1,docdate)
else ’01/01/1900′ end as WeekEndDate, DocDate, VchrNmbr, DocType, Bchsourc, Mdfusrid
from pm20000

–Get History AP
Union

Select
Case
when datename(weekday,docdate) = ‘Friday’ then Docdate
when datename(weekday,docdate) = ‘Saturday’ then dateadd(Day,6,docdate)
when datename(weekday,docdate) = ‘Sunday’ then dateadd(Day,5,docdate)
when datename(weekday,docdate) = ‘Monday’ then dateadd(Day,4,docdate)
when datename(weekday,docdate) = ‘Tuesday’ then dateadd(Day,3,docdate)
when datename(weekday,docdate) = ‘Wednesday’ then dateadd(Day,2,docdate)
when datename(weekday,docdate) = ‘Thursday’ then dateadd(Day,1,docdate)
else ’01/01/1900′ end as WeekEndDate, DocDate, VchrNmbr, DocType, Bchsourc, Mdfusrid
from pm30200

You only have to run the view code once to build the view named Metrics_APVoucherCountWeek.

From there, a query on the count of AP Vouchers by user looks like this:

–SQL Query for APVoucherCount
–Use Weekenddate in where clause to limit range
–This is ALL AP Voucher transactions.
–Uses Metrics_APVoucherCountWeek View

Select WeekendDate, Mdfusrid as APUser, Count(VCHRNMBR) as VoucherCount
from Metrics_APVoucherCountWeek
–Exclude Payments
Where Doctype<>6
group by WeekendDate, mdfusrid

And the output looks like this:

AP Metrics Ouput Example

You can see that it would be pretty easy to graph the voucher count per user per week. I have prebuilt AP queries available for AP transactions, AP payments and AP Transactions excluding PO related items. We’ve also got PO and Fixed Asset metrics available, all you have to do is ask.

This is my shameless inducement to get you to leave a comment. If you want more queries for the metrics, leave a comment on the blog with an email and I’ll get them to you. No, we’re not going to spam you to death if you ask for the metrics. We just want to know that people are paying attention.