OK, maybe not mammoth saving but controlling quality and costs during the Return Material Authorizations (RMA) process has a multiplicative effect on growth in a company. Finding new ways to improve perfect order performance continually reduces RMA and increases customer satisfaction leading to more sales.
RMAs are a direct measure of product quality and a products’ nonconformance to customers’ specifications and requirements. They are issued for a wide variety of reasons. The RMA module within Dynamics GP is designed to provide detailed traceability for both customer and supplier returns. Before we get to designing our Power BI RMA dashboard let’s look at Dynamics GP RMA module.
Return Material Authorization Module
The Returns Management module for Microsoft Dynamics GP enables you to meet customer and vendor demands for product and part returns by streamlining tasks and improving your responsiveness to customer queries and complaints. Generate returns from historical customer invoices, service calls, or manually. Return an item to a vendor to fix it within your company and automatically transfer items from the returns warehouse to the main warehouse. Give your customers up-to-date information about the status of their returned items.
You can use Returns Management to enter, set up, and maintain your RMA and Return To Vendor (RTV) documents. If Returns Management is integrated with Service Call Management, an RMA is created automatically from a returnable parts line on a service call. If Returns Management is integrated with Sales Order Processing, you can select items directly from the historical Sales Order Processing invoice that was generated for a specific customer.
Dynamics GP’s RMA Life Cycle
When operating a returns warehouse, you sometimes interact with customers who need to return equipment for various reasons. When they bring you apart, you complete one of a few tasks: issue a credit, repair and return the item to the customer, or replace the item. You also may be accepting the item in exchange for an item you already provided to the customer. Once you’ve completed your transaction with the customer, you have a few more options: repair the item and return it to your inventory, return the item to the vendor, or scrap the item.
The following diagram outlines the life cycle of RMAs, from entry through completion. When Returns Management is integrated with the other modules of the Field Service Series (Service Call Management, Preventive Maintenance, Contract Administration, and Depot Management), many new options and paths become available.
You can create RMAs from two different points of access throughout Field Service:
- Manual entry in the RMA Entry/Update window
- From return lines for returnable items on a service call
Entry in the RMA Entry/Update window is the method described in this manual. Refer to the Service Call Management documentation for more information regarding service calls and returnable items.
RMA types Inside Dynamics GP
RMA documents are used to track an item return from your customers. The available RMA document types are as follows:
- Credit – Provide a credit to your customer’s account in Receivables Management for the value of the items the customer returned to you.
- Replacement – Provide the same item, or a similar item, as a replacement for your customer. You must receive the original item from your customer before you send the replacement item on a new order document in Sales Order Processing.
- Advance Cross–ship – Provide the same item, or a similar item, as a replacement for your customer. You can send the replacement item using a new Sales Order Processing order document prior to receiving the original item from your customer.
- Repair and Return – You, or your vendor, will repair the item that is received from the customer. Your customer will receive the item after it’s been repaired.
- None – The customer’s original item is picked up by your field service technician and returned to your returns warehouse. This type of RMA document was designed to integrate directly with Service Call Management.
Analyzing Our RMA Data with Power BI
With all the RMA data we will be using the following three visuals and associated measures:
- Rate of Return – This is an incredibly useful KPI in a distribution center, especially when segmented by cause for return. Identifying causes for returns — damage, late delivery, inaccurate product description, wrong item shipped, etc. — helps warehouse managers address underlying issues, and make necessary improvements.
Number of Units Returned/Number of Units Sold = Rate of Return. - Perfect Order Rate – This KPI measures how many orders your warehouse successfully delivers without incident: the correct item, shipped on time and received in good condition by the customer who ordered it. Lean practices help identify errors or inaccuracies before orders leave the warehouse.
Orders Completed Without Incident/Total Orders Placed = Perfect Order Rate - RMA Pareto Analysis – Done on the top 20% of factors that drive 80% of the returns. This will make cause troubleshooting more efficient, leading to permanent solutions to problems that may be causing RMAs to begin with. I’m not going to go into detail on how to build this chart in this blog post. You can find the steps on how to complete it here: https://powerbi.tips/2016/10/pareto-charting/.
First, we need to get our Dynamics GP RMA data into Power BI with the following SQL script:
WITH CTE
AS (SELECT
300 AS Company_Key,
b.RETDOCID AS [RMA Document ID],
b.LNSEQNBR,
a.Return_Record_Type,
b.RETREF AS [RMA Reference],
b.RETSTAT AS [RMA Status],
b.RETTYPE AS [RMA Type],
a.COMPDTE AS [DocDate],
a.USERID,
b.OFFID AS [Office ID],
b.LOCNCODE AS [Location Code],
a.CUSTNMBR,
b.SOPTYPE,
b.SOPNUMBE AS [Invoice Number],
b.SVC_RMA_Reason_Code AS [RMA Reason Code],
b.SVC_RMA_Reason_Code_Desc AS [RMA Reason Code Description],
b.ITEMNMBR,
b.QUANTITY,
b.UNITCOST AS [Unit Cost],
b.EXTDCOST AS [Extended Cost],
b.[Credit_SOP_Number]
FROM dbo.SVC05200 b (NOLOCK)
JOIN dbo.SVC05000 a (NOLOCK)
ON b.RETDOCID = a.RETDOCID
WHERE b.QUANTITY > 0
AND b.COMPDTE >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) – 4, 0) — Didn’t want everything so limiting to last 4 years of RMA data
AND a.COMPDTE >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) – 4, 0) — Didn’t want everything so limiting to last 4 years of RMA data
UNION ALL
SELECT
300 AS Company_Key,
b.RETDOCID AS [RMA Document ID],
b.LNSEQNBR,
a.Return_Record_Type,
b.RETREF AS [RMA Reference],
b.RETSTAT AS [RMA Status],
b.RETTYPE AS [RMA Type],
a.COMPDTE AS [DocDate],
a.USERID,
b.OFFID AS [Office ID],
b.LOCNCODE AS [Location Code],
a.CUSTNMBR,
b.SOPTYPE,
b.SOPNUMBE AS [Invoice Number],
b.SVC_RMA_Reason_Code AS [RMA Reason Code],
b.SVC_RMA_Reason_Code_Desc AS [RMA Reason Code Description],
b.ITEMNMBR,
b.QUANTITY,
b.UNITCOST AS [Unit Cost],
b.EXTDCOST AS [Extended Cost],
b.[Credit_SOP_Number]
FROM dbo.SVC35200 b (NOLOCK)
JOIN dbo.SVC35000 a (NOLOCK)
ON b.RETDOCID = a.RETDOCID
WHERE b.QUANTITY > 0
AND b.COMPDTE >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) – 4, 0) — Didn’t want everything so limiting to last 4 years of RMA data
AND a.COMPDTE >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) – 4, 0)) — Didn’t want everything so limiting to last 4 years of RMA data
SELECT
b.*,
rs.STSDESCR AS [RMA Status Description],
iv.ITMCLSCD AS [Item Class]
FROM CTE b (NOLOCK)
LEFT JOIN dbo.IV00101 iv (NOLOCK)
ON iv.ITEMNMBR = b.ITEMNMBR
LEFT JOIN dbo.SVC05500(nolock) rs
ON b.[RMA Status] = rs.RETSTAT
WHERE b.QUANTITY > 0
With the data now loaded into Power BI Desktop, I created relationships with my Inventory, Date and Customer dimension that I already had in Power BI Desktop from my previous blog posts.
Creating Our Measures
First, we need to create the measures we need to build the Rate of Return. Rather than create one big DAX measure we will be building several small measures that build upon each other. Here are the DAX formulas that we will be using for the Rate of Return measure:
- Return of Lbs CY
Returns Lbs. CY = CALCULATE( ABS( SUM(Returns[Total Return Lbs]) ) )
- CY
Lbs. CY = SUM(Sales[ExtendedWeight])
- Rate of Return Rate of Return = DIVIDE ([Returns Lbs. CY], [Lbs. CY], Blank() )
- Monthly Average Rate of Return
Monthly Avg. Rate of Return = AVERAGEX( DATESINPERIOD( Dates[Date] , LASTDATE( Dates[Date] ), -30, DAY), [Rate of Return] )
With our calculation now complete, we create a line chart with Date on the Axis and Monthly Avg. Rate of Return for the Value as seen in the bottom left line chart visual of our Returns Analysis report screenshot below.
Up next, our Perfect Order Rate measure. Once again, we will build several small calculations and add them together to create our Monthly Avg. Perfect Order Rate. Here are the DAX formulas that I used for this:
- Total Invoice Count – Since my data includes line level detail for each Sales Order type in the Sales Table I’ll count all lines invoice and evaluate each line later to make sure it was a perfect order.
Total Invoices Count = CALCULATE( COUNTROWS(‘Sales’), FILTER(‘Sales’,’Sales'[SOPTYPE] = 3))
- Perfect Order – Now we will determine if the order was fulfilled before the customer’s required date.
Perfect Order = CALCULATE(COUNTROWS (‘Sales’), FILTER(‘Sales’,’Sales'[SOPTYPE] = 3), FILTER(‘Sales’,[Document Date]<=’Sales'[Req Ship Date]))
- Perfect Order Rate
Perfect Order Rate = DIVIDE( [Perfect Order],[Total Invoices Count], BLANK())
- Monthly Average Perfect Order Rate
Monthly Avg. Perfect Order Rate = AVERAGEX( DATESINPERIOD( Dates[Date] , LASTDATE( Dates[Date] ), -30, DAY), [Perfect Order Rate])
Ready to Do Even More with Dynamics and Power BI?
As a full-service technology consulting company, KTL Solutions works with business leaders every day in helping them lead their organization into becoming data-driven organization by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP and CRM journey. Need help with your journey?
