Better upfront payables management can catch billing errors sooner, which can prevent duplicate payments and overpayments. Do not even get us started on late fees. If you like to make duplicate payments and overpayments, please e-mail us; we'll send you an invoice. For everyone else, there are a variety of ways to improve profit by reducing administrative costs, but that's another book altogether. The AP department can become a profit center of sorts, but only if you monitor and manage your payables by using features that already exist in GP, and by entering/posting daily.
The reports we will build in this chapter are all about managing payables.
What we will build:
Too often we find that Microsoft Dynamics GP users are not using features such as holds and prepayments due to a concern that it creates complexity. In an attempt to achieve the same results as these features, these users simply wait to enter and/or post transactions and payments. This method works against the company and creates more work for the users.
There are two pain points: the inability to easily see which invoices and/or vendors are on hold and which credit memos/returns or prepayments need to be applied.
Our goal is to create a simple dashboard that easily shows what needs to be applied and what is on hold, all in one report. Monitoring this report each day will allow users to spotlight the documents that need attention first.
There are two parts to these steps. The first part is technical, containing steps that involve work in SQL Server. The second part is the actual building of the report. You may choose to obtain assistance with the technical part, especially if you do not have access to SQL Server.
We have warned you before, this first part involves working in SQL Server. If you are not experienced in working in Microsoft SQL Server, please request help from your IT department or ask your Microsoft GP partner to assist you. You may corrupt your data and completely break your system if you are not careful. You should also have an appropriate SQL Server password.
For directions on how to create a view, follow the technical matter step for the first report in Chapter 2, Business Intelligence for the General Ledger.
Create the following View in SQL:
CREATE VIEW view_BI_PM_Open AS SELECT VM.VNDCLSID AS [Vendor Class], OP.VCHRNMBR AS [Voucher Number], OP.VENDORID AS [Vendor ID], VM.VENDNAME AS [Vendor Name], CASE OP.DOCTYPE WHEN 1 THEN 'Invoice' WHEN 2 THEN 'Finance Charge' WHEN 3 THEN 'Misc Charge' WHEN 4 THEN 'Return' WHEN 5 THEN 'Credit Memo' WHEN 6 THEN 'Payment' WHEN 7 THEN 'Schedule' END AS [Document Type], OP.DOCDATE AS [Document Date], OP.DOCNUMBR AS [Document Number], CASE OP.DOCTYPE WHEN 5 THEN - CURTRXAM WHEN 6 THEN - CURTRXAM ELSE CURTRXAM END AS [Current Amount], CASE VM.HOLD WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS [Vendhor Hold], CASE OP.HOLD WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS [Transaction Hold], OP.DUEDATE AS [Due Date], OP.PORDNMBR AS [PO Number], OP.TRXDSCRN AS Description FROM dbo.PM20000 AS OP INNER JOIN dbo.PM00200 AS VM ON OP.VENDORID = VM.VENDORID WHERE (OP.VOIDED = 0) AND (OP.CURTRXAM <> 0) GO GRANT SELECT ON view_BI_PM_Open TO DYNGRP
Let's prepare and connect to the data.
We performed a mock-up of what we wanted in Excel and have included a screenshot here:
Let's make a connection to the GP data:
Let's build the first PivotTable report:
Let's add slicers:
The finishing touches are as follows:
This Excel report shows all Open documents with a balance. This means ANY invoice, return, credit memo, payment, and so on, with an unapplied balance. This report is also used to reveal only those invoices or vendors that have been placed on hold. Finally, this report reveals credit memos, returns, and/or (pre) payments that have not yet been applied (or fully applied) to an invoice. This will allow the AP staff to fully utilize GP features, keeping the financial data current, and still make sure they are not overpaying or forgetting to pay something.