EDI DSR vs CSD DSR Comparison – Consumer Segment
Process Overview
| Fields | Description |
|---|---|
| Process Name | EDI DSR vs CSD DSR Comparison – Consumer Segment |
| Segment | Consumer |
| Process Family: | Lenovo India |
| Process User(s): | Wyzmindz solutions pvt ltd |
| Process Purpose: | This report is instrumental in providing actionable insights, tracking performance, and supporting Lenovo’s sales strategies |
Version History
| Version Tag | Prepared By | Date of Update/Revision | Reviewed by | Approved by | Revision Details |
|---|---|---|---|---|---|
| V 1.0 | Sushruth Vasista | 22-01-2024 | Vishwanath Mosale | Vishwanath Mosale | SOP Document Creation |
| V 1.1 | Amruta Jamnis |
Distribution & Access List
| Designation | Read | Write | Approval |
|---|---|---|---|
| Analyst | Yes | No | No |
| Team Lead | Yes | Yes | No |
| Manager | Yes | Yes | Yes |
1.1 Purpose
This SOP provides a detailed process for comparing EDI (CSD) and DSR data for the Consumer segment on a daily basis. It aims to identify quantity mismatches, missing records, and duplicate entries to ensure data consistency between CSD Final Data Export and Daily Manual DSR reports. The report assists in maintaining accurate sales tracking and reporting for Lenovo Consumer products.
1.2 Scope
- Geographic Scope: India.
- Business Segment: Consumer Segment (covering carepack, Consumer DT, Consumer NB, Consumer SNP & Consumer visuals).
- Process Frequency: 1st to 15th Daily or till we finalize the DSR, 15th to 31st, twice a week
- Core Activity: To Compare Inv & MTM & Qty between CSD DSR Vs EDI DSR
- Key Distributors Covered: AEGIS, ELCOM, INGRAM, RASHI, REDINGTON, SAVEX, and SYSTECH.
1.3 Business/Segment Overview
This process supports Lenovo India's sales strategies by providing actionable insights and tracking performance. The primary focus is on managing channel sales data (3S) accuracy for Consumer products. The process covers both Consumer & SMB (Small/Mid-market) segment data. It identifies and resolves issues like quantity mismatches, missing records, and duplicate entries for the seven key distributors: AEGIS, ELCOM, INGRAM, RASHI, REDINGTON, SAVEX, and SYSTECH.
1.4 Responsibility and authority
- Analyst : Amruta Jamnis
- Process Owner : Jeevitha
- Process Manager : Dayanand
- Approval Authority : Vishwanath Mosale
1.5 Escalation matrix: (Wyzmindz and Lenovo)
Escalation Matrix – WyzMindz Solutions Pvt Ltd India
| Level | Name | Designation | Email ID |
|---|---|---|---|
| 1 | Sushruth Vasista | Manager | sushruth.vasista@wyzmindz.com |
| 2 | Vishwanath Mosale | Director | Vishwanath.mosale@wyzmindz.com |
Escalation Matrix – Lenovo India Pvt Ltd
| Level | Name | Designation | Email ID |
|---|---|---|---|
| 1 | Debashish | CONS Ops Head | dbhadra@lenovo.com |
| 2 | Saurabh Agarwal | Ops Director | ASAURABH@lenovo.com |
1.6 RACI Matrix:
RACI Matrix – WyzMindz Solutions Pvt Ltd India
| Report / Process | Responsible | Accountable | Consultation | Informed |
|---|---|---|---|---|
| EDI DSR vs CSD DSR Comparison -CON | Amruta Jamnis | Amruta Jamnis | Dayanand. U | Lenovo Ind Pvt Ltd, Wyzmindz Solutions pvt Ltd India |
1.7 Access Required
- Outlook/Webmail Access: For receiving DSR reports and communications.
- CSD (Channel Sales Data): For Exporting CSD data and performing resale tracking/corrections.
- Access to Daily Manual CON ST DSR
- Access to MS Office Suite
1.8 Overview of Tools:
Overview of Tools
| Level | Tool Name | URL | Description |
|---|---|---|---|
| 1 | CSD (Channel Sales Data) | https://owb.lenovo.com/ | It’s Lenovo’s centralized system for managing 3S (SI, ST & SO) across different Geo & BP & go with AP delivery. |
| 2 | CDMS (Channel Data Management System) | https://cdms.lenovo.com/DCSDM/ | Lenovo’s tool used to collect & track sales (SO) data from partners & stores. |
1.9 Legends
- MTM: Machine Type Model (Product Number)
- INVMTM: Concatenated field of Invoice Number and MTM
- NA: Not Available
1.10 Dependencies
- Final Data Export file (from CSD system)
- Consumer ST DSR file (Daily Sell Thru Report for Consumer segment) via email
- MTM Input Master file (for Biz Group mapping) from DCSDM page
1.11 Schedule of Reporting
The Consumer Segment Data Comparison process is structured around a daily flow to ensure timely data validation: - Frequency: 1st to 15th Daily or till we finalize the DSR, 15th to 31st, twice a week
1.12 Process Flow Chart
1.13 Steps of Procedure
Final Data Export File (CSD)
- Open the Final Data Export file (CSD). Concatenate ‘Invoice Number’ and ‘Product Number’ in a new column named ‘INVMTM’.
- E.g.: CONCAT (CS3, AD3)
- Add a new column ‘CON’ and map the ‘Biz Group’ from the MTM Input Master using VLOOKUP based on Product Number.
- After mapping, filter out and take only these 5 options: Carepack, Consumer DT, Consumer NB, Consumer SNP, Consumer Visuals. Then delete the others.
- In this comparison report, the key distributors considered are AEGIS, ELCOM, INGRAM, RASHI, REDINGTON, SAVEX, SYSTECH.
- Insert a Pivot Table for the Final Data Export file.
- Filters: Status (Select Released & Soft Error) Invoice Date (Monthly)
- Rows: Seller L3 MDM Name (Select All 7 Disty) - INVMTM - Invoice Number - Product Number
- Values: Sum of Quantity
Consumer ST DSR File
- Open Consumer ST DSR file. Concatenate ‘Invoice Number’ and ‘MTM’ into a new column named ‘INVMTM’.
- E.g. CONCAT (AH2, T2)
- Insert a Pivot Table for Tablet DSR file.
- Filters: Invoice Date (Monthly)
- Rows: Disty Name (Select All 7 Disty) - INVMTM - Invoice Number - MTM
- Values: Sum of Sales Quantity
- Check the sum of Sales Quantity for both the files for all distributors.
- Create a new Comparison File, name the first sheet as ‘Disty’ and name the second sheet as ‘CSD_Errors’ or ‘CSD_Dump’.
- Add columns: Disty, INVMTM, INV, MTM, CSD QTY, DSR QTY, Difference (DIFF), Match (True/False), Error Message.
- Copy the CSD data from Pivot and add to the Comparison File.
- Perform a VLOOKUP in the DSR Pivot file for INVMTM from the Comparison File and get INVMTM.
- Filter that new column in DSR and check for N/A or errors. If any errors are there, then add those data into the Comparison File next to the CSD data pasted before and make them bold.
- Unfilter the new column and map the DSR QTY in Comparison file using XLOOKUP from DSR.
- Calculate Difference = DSR QTY – CSD QTY. If Difference = 0, set Match = TRUE else FALSE ex:(=F2=E2).
- Filter out only FALSE data. Then add Error message column in CSD pivot into rows and map the error message from CSD file to the comparison file.
- If there is no data or if errors appear in CSD Qty column, change the status to Error in CSD pivot and check the Error Message and Quantity.
- If the Error quantity matches the DSR quantity, then update CSD and DSR quantities to be the same and manually change the match column into FALSE.
- If Error qty does not match with DSR qty, enter Not Available (NA) in CSD in the error message column.
- If there is no data or if error appears in DSR QTY, verify whether it is a Lenovo Billings or not by pasting the MTM and Invoice Number into CSD under the Resale Tracking tab.
- If the Buyer Name column is ‘Lenovo India Pvt Ltd’, then need to cancel the entry from CSD and enter “Lenovo Billings” in the Error Message column.
- If the record is not present in CSD, then enter NA (Not Available) in DSR in the Error Message column.
• Filter out only N/A or errors from error message column. • Check for duplicates by dividing DSR QTY and CSD QTY, if it’s a whole number then duplicate or else QTY mismatch in Error message column. • Even duplicates can be verified manually by cross-checking the INVMTM values in the CSD data.
• For Quantity Mismatch data, again check the data in CSD by pasting Invoice number and MTM’s. • This must be done only once for all 7 distributors. • Take one more sheet in the same comparison file and name it as CSD_Dump. Add all the errors from CSD data, where status must be Error, copy the data from CSD and add into the same sheet.
1.14 Output Overview:
The final output is the Comparison File which contains fully validated and reconciled data for all 7 distributors, showing matching status and the reason for any mismatch/error found: • Primary Output: Comparison File (Disty sheet & CSD_Dump). • Key Output Data Fields: Includes Distributor, INVMTM, CSD QTY, DSR QTY, DIFFERENCE (DIFF), MATCH (True/False) & ERROR MESSAGE. • Reconciled Data: The ‘Disty’ sheet holds the reconciled sales data. • Error Documentation: The ‘CSD_Dump’ sheet captures all CSD records that were flagged with an error status.
1.15 Validation checklist:
After completing the comparison, validate the following:
• Compare the CSD and DSR Sales Quantities Distributor-wise. • Validate Duplicates by dividing DSR Quantity by CSD Quantity – Whole number indicates Duplicates, and decimal number indicates Quantity Mismatch. • Quantity Difference (DSR QTY – CSD QTY) should be 0 for True Entries. • Verify error messages for accuracy and completeness. • Cross-check that all Invoice Dates are identical between both files. • Ensure all distributor sheets have been processed. • Review duplicate, errors and mismatch cases manually.
1.16 Communication:
AEGIS: support@aegisinfoware.com, d.shah@aegisinfoware.com, dbhadra@Lenovo.com, abhatnagar@lenovo.com, akumarj@lenovo.com, shamadol@lenovo.com, du@lenovo.com, kchudasama@lenovo.com.
INGRAM: Bibin.Chacko@ingrammicro.com, Sashmita.Panigrahi@ingrammicro.com, Abhishek.Mahapatra@ingrammicro.com, dbhadra@Lenovo.com, akumarj@lenovo.com, akumar33@lenovo.com, du@lenovo.com.
ELCOM: rajan@elcomonline.com, dbhadra@Lenovo.com, abhatnagar@lenovo.com, akumarj@lenovo.com, shamadol@lenovo.com, du@lenovo.com, kchudasama@lenovo.com.
!
RASHI: anil.vas@rptechindia.com, sachin.govalkar@rptechindia.com, dbhadra@Lenovo.com, abhatnagar@lenovo.com, akumarj@lenovo.com, shamadol@lenovo.com, du@lenovo.com, akumar33@lenovo.com, kchudasama@lenovo.com.
REDINGTON: viswanathan.swamy@redingtongroup.com, shoba.ss@redingtongroup.com, dbhadra@Lenovo.com, abhatnagar@lenovo.com, akumarj@lenovo.com, shamadol@lenovo.com, akumar33@lenovo.com, du@lenovo.com, kchudasama@lenovo.com.
SAVEX: aniket.bagwe@savex.in, amit@savex.in, prashant.punyarthi@savex.in, dbhadra@Lenovo.com, akumar33@lenovo.com, akumarj@lenovo.com, abhatnagar@lenovo.com, shamadol@lenovo.com, du@lenovo.com, kchudasama@lenovo.com.
SYSTECH: sridhar@systechitsolutions.co.in, dbhadra@Lenovo.com, abhatnagar@lenovo.com, akumarj@lenovo.com, shamadol@lenovo.com, du@lenovo.com, kchudasama@lenovo.com, akumar33@lenovo.com.
