Procurement is an important part of any business. Regularly analyzing this data is easier with the use of Power BI. It ensures that you are making the best use of your company’s resources. Here are a couple of things to look at when analyzing your purchasing department – Cost and Delivery Time Analysis.
Cost Analysis
There are several things to consider when analyzing Inventory Costs. Obviously, the unit cost of the item, but we also have to look at delivery, handling, storage, deposits, and item returns from customers. In the Power BI analysis below, I’m simply looking at the Item’s Current Cost from Dynamics GP Inventory Item Card vs. the Average Purchasing and Per Order Unit Costs.
Delivery/Lead Time Analysis
Delivery/Lead times can be important to companies, particularly when perishable and large goods are concerned. Delivery/Lead times that start to creep higher will hamper your Item Resource Planning Process. Your staff needs to analysis delivery/lead times and plan accordingly to prevent stock outs, limit storage area, and cash resource requirements.
Putting It All Together
I first created the below SQL view in my Dynamics GP company.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[vw_POPTransactions] AS WITH CTE as (Select [PONUMBER] ,[ORD] ,[POLNESTA] ,[POTYPE] ,[ITEMNMBR] ,[ITEMDESC] ,[VENDORID] ,[VNDITNUM] ,[VNDITDSC] ,[NONINVEN] ,[LOCNCODE] ,[UOFM] ,[UMQTYINB] ,[QTYORDER] ,[QTYCANCE] ,[UNITCOST] ,[EXTDCOST] ,[INVINDX] ,[REQDATE] ,[PRMDATE] ,[PRMSHPDTE] ,[REQSTDBY] ,[COMMNTID] ,[DOCTYPE] ,[POLNEARY_1] ,[POLNEARY_2] ,[POLNEARY_3] ,[POLNEARY_4] ,[POLNEARY_5] ,[POLNEARY_6] ,[POLNEARY_7] ,[POLNEARY_8] ,[POLNEARY_9] ,[DECPLCUR] ,[DECPLQTY] ,[ITMTRKOP] ,[VCTNMTHD] ,[BRKFLD1] ,[JOBNUMBR] ,[COSTCODE] ,[COSTTYPE] ,[ORUNTCST] ,[OREXTCST] ,[LINEORIGIN] ,[FREEONBOARD] ,[ODECPLCU] ,[Product_Indicator] ,[Source_Document_Number] ,[Source_Document_Line_Num] ,[RELEASEBYDATE] ,[Released_Date] ,[Purchase_IV_Item_Taxable] ,[Purchase_Item_Tax_Schedu] ,[Purchase_Site_Tax_Schedu] ,[PURCHSITETXSCHSRC] ,[BSIVCTTL] ,[TAXAMNT] ,[ORTAXAMT] ,[BCKTXAMT] ,[OBTAXAMT] ,[Landed_Cost_Group_ID] ,[SHIPMTHD] ,[LineNumber] ,[ORIGPRMDATE] ,[FSTRCPTDT] ,[LSTRCPTDT] ,[RELEASE] ,[ADRSCODE] ,[CMPNYNAM] ,[CONTACT] ,[ADDRESS1] ,[ADDRESS2] ,[ADDRESS3] ,[CITY] ,[STATE] ,[ZIPCODE] ,[CCode] ,[COUNTRY] ,[PHONE1] ,[PHONE2] ,[PHONE3] ,[FAX] ,[ADDRSOURCE] from POP10110 UNION ALL Select [PONUMBER] ,[ORD] ,[POLNESTA] ,[POTYPE] ,[ITEMNMBR] ,[ITEMDESC] ,[VENDORID] ,[VNDITNUM] ,[VNDITDSC] ,[NONINVEN] ,[LOCNCODE] ,[UOFM] ,[UMQTYINB] ,[QTYORDER] ,[QTYCANCE] ,[UNITCOST] ,[EXTDCOST] ,[INVINDX] ,[REQDATE] ,[PRMDATE] ,[PRMSHPDTE] ,[REQSTDBY] ,[COMMNTID] ,[DOCTYPE] ,[POLNEARY_1] ,[POLNEARY_2] ,[POLNEARY_3] ,[POLNEARY_4] ,[POLNEARY_5] ,[POLNEARY_6] ,[POLNEARY_7] ,[POLNEARY_8] ,[POLNEARY_9] ,[DECPLCUR] ,[DECPLQTY] ,[ITMTRKOP] ,[VCTNMTHD] ,[BRKFLD1] ,[JOBNUMBR] ,[COSTCODE] ,[COSTTYPE] ,[ORUNTCST] ,[OREXTCST] ,[LINEORIGIN] ,[FREEONBOARD] ,[ODECPLCU] ,[Product_Indicator] ,[Source_Document_Number] ,[Source_Document_Line_Num] ,[RELEASEBYDATE] ,[Released_Date] ,[Purchase_IV_Item_Taxable] ,[Purchase_Item_Tax_Schedu] ,[Purchase_Site_Tax_Schedu] ,[PURCHSITETXSCHSRC] ,[BSIVCTTL] ,[TAXAMNT] ,[ORTAXAMT] ,[BCKTXAMT] ,[OBTAXAMT] ,[Landed_Cost_Group_ID] ,[SHIPMTHD] ,[LineNumber] ,[ORIGPRMDATE] ,[FSTRCPTDT] ,[LSTRCPTDT] ,[RELEASE] ,[ADRSCODE] ,[CMPNYNAM] ,[CONTACT] ,[ADDRESS1] ,[ADDRESS2] ,[ADDRESS3] ,[CITY] ,[STATE] ,[ZIPCODE] ,[CCode] ,[COUNTRY] ,[PHONE1] ,[PHONE2] ,[PHONE3] ,[FAX] ,[ADDRSOURCE] from POP30110) Select a.POPIVCNO, a.IVCLINNO, a.POPRCTNM, a.RCPTLNNM, b.VENDORID, b.VENDNAME, b.VNDDOCNM AS [Invoice No], b.receiptdate as [Invoice Date], f.Released_Date, DATEDIFF(day, f.Released_Date, c.receiptdate) as Lead_Time, --d.PONUMBER, f.PONUMBER, --d.RCPTLNNM, --f.ORD, c.VNDDOCNM as [Bill of Lading], c.receiptdate as [Date Received], d.ITEMNMBR, d.ITEMDESC, d.VNDITNUM, d.VNDITDSC, a.QTYINVCD, e.QTYSHPPD, e.QTYREJ, f.UNITCOST From POP10600 a INNER JOIN POP30300 b ON a.POPIVCNO = b.POPRCTNM INNER JOIN POP30300 c ON a.POPRCTNM = c.POPRCTNM INNER JOIN POP30310 d on a.POPRCTNM = d.POPRCTNM and a.RCPTLNNM = d.RCPTLNNM INNER JOIN CTE f on d.PONUMBER = f.PONUMBER and d.RCPTLNNM = f.ORD INNER JOIN POP10500 e on a.POPRCTNM = e.POPRCTNM and a.RCPTLNNM = e.RCPTLNNM
Next, I imported the data into my Power BI Desktop ‘pbix file’ and created DAX measures for Total Lead Time, Total Purchasing Unit Cost, Average Lead Time, Average Purchasing Unit Cost, and Lead Time Weekly Moving Average. The result is the Power BI report below that compares:
- Item Current Cost vs. Average Purchasing Unit Cost and Order Detailed Costs
- Average Lead Time vs. per order lead times
Future Dashboard developments
In the coming months, I plan to add additional Power BI reports associated with my Dynamics GP data. The report will include
- an updated inventory procurement analysis using the lead time analysis from this post and sales forecasts.
Stay tuned for the future developments of the reports and an overview of their functionality. For help with building reports like this, please contact KTL Solutions where we can help kick start your organization into becoming data driven by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP and CRM needs.
BARRY CROWELL, MBA | BI/EDW Solution Architect
Barry is a Microsoft SQL Certified Professional with a strong knowledge of the Microsoft’s BI Stack (SSIS, SSRS, SSAS and Power BI). He has architected, developed and deployed clients’ Business Intelligence needs using Microsoft’s BI Stack and/or Solver’s BI360. His solutions have included SSIS ETL tools, SSRS reports and dashboards, Excel dashboards, Power BI reports and dashboards, and SSAS cubes. He has performed implementations as the lead consultant and/or end-user project manager. Barry has over 20 years of experience working in accounting and the Microsoft Dynamics GP industry, and in various industries such as housing authorities, universities, Tribal governments, and casino hospitality. He possesses a Bachelor’s Degree in Accounting and Business Administration from Black Hills State University and a Master’s in Business Administration from La Salle University. Barry’s experience in both the IT consultant field and experience as an accountant gives him the ability to understand the issues from both the IT and Finance prospective, and provide a solution that fits the needs for all parties involved.
