MM ST CLAIMS DSR

Process Overview

Field	Description
Process Name	AP MM DSR
Segment	SMB
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	Sanjay Gowda	12-12-2025	Sushruth Vasista	Vishwanath Mosale	SOP Document Creation

Distribution & Access List

Designation	Read	Write	Approval
Analyst	Yes	No	No
Team Lead	Yes	Yes	No
Manager	Yes	Yes	Yes

1.1 Purpose

The AP MM ST DSR process is designed to deliver a comprehensive, accurate, and audit-ready view of MM sell-through transactions for claims and rebate validation. The primary purpose of this process is to consolidate distributor-level DSRs, coverage data, pricing inputs, and master data to validate billing types, pricing (GRP/AGRP), partner classification, and customer details.

1.2 Scope

• Geographic Scope: India.
• Business Segment: SMB Segment
• Process Frequency: Every Tuesday
• Key Distributors Covered: Rashi, Redington, Savex, Supertron & Ingram

1.3 Business/Segment Overview

This process supports Lenovo India’s MM operations by integrating data from multiple distributors, coverage systems, pricing lists, and master data sources into a single standardized Claims DSR. Managed by the MIS and reporting teams, the activity involves extensive data reconciliation, pricing validation, partner and customer mapping, and compliance checks across billing models such as B2B, Pricelink, and Program billing. The finalized DSR is reviewed and approved by finance and operations stakeholders, enabling accurate claim settlements, minimizing revenue leakage, and ensuring transparency and control over sell-through–based claims within the MM business.

1.4 Responsibility and authority

• Analyst: Sanjay Gowda
• Process Owner: Nadeem Budihal
• Process Manager: Dayanand U
• Approval authority: Vishwanath Mosale

1.5 Escalation matrix: (Wyzmindz and Lenovo)

Escalation Matrix - Wyzmindz Solutions Private Ltd

Level	Name	Designation	Mail
1	Sushruth Vasista	Manager	sushruth.vasista@wyzmindz.com
2	Vishwanath Mosale	Director	vishwanath.mosale@wyzmindz.com

Escalation Matrix – Lenovo india Private Ltd

Level	Name	Designation	Mail
1	Marlene Pinto	Manager	mpinto4@lenovo.com

1.6 RACI Matrix

RACI Matrix – WyzMindz Solutions Pvt Ltd India

Report / Process	Responsible	Accountable	Consultation	Informed
AP MM DSR	Sanjay Gowda	Sanjay Gowda	Dayanand. U	Lenovo Ind Pvt Ltd, Wyzmindz Solutions pvt Ltd India

1.7 Legends

• Ask all Distys (T1s) to share the final DSR for month/quarter closer.
• Ask KC Darshan (BPRM) for Rebate List.
• Ask Final Pricelist from Dejo Mathew and consolidate with MTM and Channel Price (We should ask monthly).
• Ask Final Service Pricelist from Hareesh Reddy.
• There are 5 T1(Distributors) we receive Final DSR from on monthly basis.

RASHI - “[External] CC Report June 2023” on CCREPORTS ID

REDINGTON - “[External] CC-Sellout Report - AMJ'2023” on CCREPORTS ID

SAVEX - “[External] Lenovo Sales Report_Commercial” on CCREPORTS ID

SUPERTRON - “[External] Sale Report(CCDSR) JUNE l'23 ” on CCREPORTS ID

INGRAM - “[External] LEMB POS OCT” on CCREPORTS ID

Rebate List from KC Darshan.

1.8 Access Required:

• Outlook & Webmail Access
• Access to ccreports@lenovo.com Mails (to get latest DSR’s)
• Access to WyzMindz ID Mails (to get Coverage report)
• Lenovo ID (To share with OPS HEAD for approval)
• Access to Co-Data to download LBP Dump (https://codata.lenovo.com/fe/#/login?hash=/homePage)

1.9 Flowchart

1.10 Process Description:

• 1.10.1 Open Co-Data portal & login.
• 1.10.2 Click on AP IO – LBP Dashboard.

• 1.10.3 Click on Quotation.

• 1.10.4 Filter Business Unit with PCSD.

• 1.10.5 Filter Region with IN and Country with IN, IN12 & IN30.

• 1.10.6 Right click and download as Data.
• 1.10.7 Save the Downloaded file as LBP Extract with current date in XLSB extension to avoid loading time.
• 1.10.8 Delimit Contract Number.
• 1.10.9 Add new column next to it with concatenation of Contract Number and Quotelines Product Number.
• 1.10.10 Select Quotelines Channelprice entire column and highlight it with Yellow.
• 1.10.11 Similarly, Quotelines Sales Price.
• 1.10.12 Create a pivot.
• 1.10.13 Add Quotelines Product Number and CTO MTM in rows.
• 1.10.14 Convert to Classic pivot and remove the subtotals (Design-Subtotal-Do Not Show SubTotals).
• 1.10.15 Filter CTO MTM with only Blanks.

• 1.10.16 Open Final DSR received from each T1s.
• 1.10.17 Add new column at the beginning with concatenation of Invoice number, MTM, QTY & CinContract#.
• 1.10.18 Open Consolidated Base file.

• 1.10.19 Similarly, add new column in beginning with concatenation of Invoice number, MTM, QTY & CinContract#.
• 1.10.20 Then add one more column next to it.
• 1.10.21 Check for any new transactions using vlookup function.
  • =vlookup(A2, “Final DSR concatenated column”,1,0).
• 1.10.22 If found any new transactions, add them in base DSR.
• 1.10.23 Similarly, check in Overall Coverage file that if any Lenovo billings have been missed.
• 1.10.24 Check with all data i.e., Contract numbers, End customer name and invoice date.
• 1.10.25 Similarly, check with all T1’s DSRs.
• 1.10.26 If any mismatches were found like T2 names/GSTIN/Contract#/End Customer names, update them.
• 1.10.27 Remove the column with the formula apart from concatenated column.
• 1.10.28 Open current date’s Coverage report

• 1.10.29 Add new column next to Contract No with concatenation of Contract No and Material.
• 1.10.30 One more column next to Local_Currency with Unit Price with calculation of (Local_Currency_Value/ Billed Quantity).
• 1.10.31 Open Consolidated Coverage file of last 4 qtrs.

• 1.10.32 Go to Base DSR.
• 1.10.33 Add two columns next to cin_contractno i.e., As per Coverage and Remarks1.
• 1.10.34 Go to last column (after ASM column) and add columns with headers as followed.
• 1.10.35 Unit, GRP, AGRP, Remarks2, T1_MDM_ID, T1_BP_Type, T1_BP_Sub_Type, T1_D365, T2_MDM_ID, T2_BP_Type, T2_BP_Sub_Type, T2_D365, AP_Subsegment, AP_TS, AP_CTO, AP_RTM, SMB Revenue, Bid Type, T1 Sold to Code, Backend Type, Partner Acquisition Status, End Customer, Relationship Status and RAD 9
• 1.10.36 Update Unit as inv_value/QTY.
• 1.10.37 Filter bill_through column only with Pricelink.
• 1.10.38 Go to As per Coverage check with below formula with Coverage file.
  • =VLOOKUP (A2, “[COVERAGE REPORT]! Contract NoMaterial: T&R”,20,0)
• 1.10.39 Filter #NA and redo the same step with consolidated coverage of last 8 qtr file and check for available transactions.
• 1.10.40 Break the link.
• 1.10.41 Check for any transactions available in them, usually we’ll get LEMB/SOSB/Consumer/REL transactions from Coverage. If found many REL, get confirmation from T1s & OPS head to move them to REL.
• 1.10.42 Select mapped values from As per Coverage and update it as Yes, else update with No.
• 1.10.43 Keep blanks for Program billings.
• 1.10.44 Filter As per Coverage with Yes, update topseller column with B2B else TS.
• 1.10.45 Filter As per Coverage with Yes, update bill_through column with B2B, for No, Pricelink and keep Program as it is.
• 1.10.46 Filter bill_through - B2B and update Remarks1 column with B2B-Coverage, for Pricelink - TS-Unavailable in Coverage and for Program billings TS-GRP Billing.
• 1.10.47 Filter bill_through - B2B & Pricelink.
• 1.10.48 Filter Blanks from Endcustomer DMU, update it with below formula (lookup array from LBP Extract)
  • =VLOOKUP (cin_contractno, “LBP Extract Contract Number: End Customer DMU”,20,0)
  • Break link and clear filter (End Customer DMU).
• 1.10.49 Filter Blanks from Account MDM IDs, update it with below formula (lookup array from LBP Extract)
  • =VLOOKUP (cin_contractno, “LBP Extract Contract Number: End Customer L2 MDM ID”,22,0)
  • Break link and clear filter (Account MDM IDs).
• 1.10.50 Filter Blanks from remarks, update it with below formula (lookup array from LBP Extract)
  • =VLOOKUP (cin_contractno, “[LBP Extract]! Contract Number: End Customer Name”,23,0)
  • Break link and clear filter (remarks).
• 1.10.51 Filter Blanks from Quote Type, update it with below formula (lookup array from LBP Extract)
  • =XLOOKUP (cin_contractno, “LBP Extract-Contract Number”, “LBP Extract- Quote Type”,0)
  • Break link and clear all filters.
• 1.10.52 Open CC DSR Template.

• 1.10.53 Go to T1 Sold to Code column and update it with-
  • =VLOOKUP (Invoice No, “Overall Coverage! Billing_Doc: Sold to Customer”,5,0)
  • If #N/A, update with below formula
  • =VLOOKUP (cin_contractno, “LBP Dump! Contract Number: T1D L2 MDM ID”,28,0)
  • If #N/A, update with below formula
  • =VLOOKUP (t1_name, “[DSR Template] T1 LPP Code! t1_name: Sold to code”,2,0)
• 1.10.54 Go to T1 LPP Code sheet.
• 1.10.55 Go to Base DSR and to t1_lpp column and update it with below formula.
  • =VLOOKUP (t1_name, “[DSR Template] T1 LPP Code! t1_name: T1 LPP”,3,0)
  • Filter #NA and update it as per T1 Sold to Code.
• 1.10.56 Go to T1_MDM_ID and update it with
  • = VLOOKUP (t1_name, “[DSR Template] T1 LPP Code! t1_name: T1 MDM”,4,0)
• 1.10.57 Filter #NA and update it with T1 Sold to Code.
• 1.10.58 Go to T1_BP_Type and update it with
  • = VLOOKUP (t1_name, “[DSR Template] T1 LPP Code! t1_name: T1_BP_Type”,5,0)
  • Remove #NA (keep them blank)
  • Break link and clear all filters.
• 1.10.59 Add new column next to T2 GSTIN (Base DSR) i.e., PAN (=Mid (T2 GSTIN,3,10))
• 1.10.60 Go to DSR template and PC Managed sheet.
• 1.10.61 Go back to DSR and t2_lpp column and update it as per below formula-
  • =VLOOKUP (PAN, “[DSR Template] PC Managed! Pan: BP LPP Code”,6,0)
  • Filter #NA and update with-
  • =VLOOKUP (t2_name, “[DSR Template] PC Managed! BP Name: BP LPP Code”,5,0)
  • Again filter #NA and update with T1 Sold to Code.
• 1.10.62 Go to T2_MDM_ID and update it with-
  • =VLOOKUP (PAN, “[DSR Template] PC Managed! Pan: MDM ID”,8,0)
  • Filter #NA and update with-
  • =VLOOKUP (t2_name, “[DSR Template] PC Managed! BP Name: MDM ID”,7,0)
  • Filter #NA and add two columns next to it & update each with -
  • =VLOOKUP (cin_contractno, “[LBP Dump] Sheet1! Contract Number: T2R L2 MDM ID”,32,0)
  • =VLOOKUP (cin_contractno, “[LBP Dump] Sheet1! Contract Number: T2R Name”,33,0)
  • And check if the T2R names matches as per the BP reported T2 Names, if it matches, then update the T2R L2 MDM ID in T2_MDM_ID else update it with T1 Sold to Code.
  • Filter #NA and update it with T1 Sold to Code
• 1.10.63 Filter data in T2_MDM_ID beginning with “PA” and update T2_BP_Type with “T2 Reseller” keep remaining as blanks.
• 1.10.64 Filter bill_through with B2B and update AP_TS with Non TS else TS.
• 1.10.65 Filter t2_name with Direct Billings by Lenovo and update AP_RTM with Direct else Indirect.
• 1.10.66 Filter bill_through with B2B and update Bid Type with Frontend and for Pricelink Backend and for Program keep it blanks.
• 1.10.67 Filter endcustomername with GRP Billings/Billed in GRP/ Pricelink/DOA/any T1’s names or Blanks and update End Customer column with No and if the transaction is billed to any customer, then update with Yes. – Logic is to update if the transaction billed have an actual end customer.
• 1.10.68 Open Last 4 qtrs Reseller names (D:\Backup\Desktop\DSR for AP\FY 2324\Q1'2324).
• 1.10.69 Go to Base DSR and Partner Acquisition Status and if the partner is have not billed in last 4 qtrs update it with Yes and if billed then update it as No i.e., if t1_name gets mapped as per below formula then No else Yes.
  • =VLOOKUP (t2_name, [Last 4 qtrs Reseller names] Reseller Names,1,0)
• 1.10.70 Go to ISR column and update blanks as per-
  • =VLOOKUP (Account MDM IDs , [DSR Template]ISR Mapping! L2_MDM_ID: New Allignment,3,0)
  • Delete #NA.
• 1.10.71 Similarly, go to account manager update blanks as per-
  • =VLOOKUP (Account MDM IDs , [DSR Template]ASM|CM Mapping! L2_MDM_ID: CM/ASM,5,0)
  • Delete #NA and ASM.
• 1.10.72 Check bpcategory based on Pan and BP name as per new rebate list received from KC Darshan, if any changes found in BP category, then update as per new rebate list.
• 1.10.73 For GRP & AGRP, we need to extract it from Coverage, LBP, Pricelist and Service pricelist.
• 1.10.74 Filter bill_through with B2B, go to GRP and update pricing as per below formula-
  • =VLOOKUP (cin_contractno&mtm, [Coverage]Contract No Material: Unit Price,12,0)
  • Filter #NA in GRP and update as per historical Coverage data.
  • =VLOOKUP (cin_contractno&mtm, [Consilidated Coverage] Contract No Material: Unit Price,12,0)
  • Update AGRP as 0 and Remarks beside AGRP as Coverage.
• 1.10.75 Filter bill_through with Program, Biz group with Services and t1_name as Rashi.
• 1.10.76 Open Service Pricelist received from Sharika.
• 1.10.77 Update GRP as per below formula-
  • =VLOOKUP (MTM, [Service pricelist] Part Number: Q2FY2526 Pricing excluding taxes (Distributor to Partner),3,0)
  • Update AGRP as 0 and Remarks as Service Billing – Sharika’s file.
  • Filter #NA as well as clear T1-name filter and update them as per invoice value and Remarks as Service Billing – Invoice value.
• 1.10.78 Open Pricelist received from Dejo and consolidate it in a separate file with MTM and Channel Price.
• 1.10.79 Filter bill_through with Program and filter Biz group without Services update it with below formula-
  • =VLOOKUP (MTM, [Pricelist] MTM: Price,2,0)
  • Check with all months of current qtr.
  • Filter mapped values and update Remarks as Pricelist.
  • If found any #NA, then update as per it as per unit price and Remarks as Invoice value as no price in Pricelist. And keep AGRP as 0
• 1.10.80 Filter bill_through with Pricelink, go to GRP and update as per below formula-
  • =VLOOKUP (cin_contractno&mtm, [LBP Extract] Contract NumberQuotelines Product Number: Quotelines Channelprice,8,0)
  • Go to AGRP and update as per below formula-
  • =VLOOKUP (cin_contractno&mtm, [LBP Extract] Contract NumberQuotelines Product Number: Quotelines Sales Price,10,0)
  • Check for any abnormal values like 9999 or 999999 or may be much higher the unit price. If found, update it as per unit value(invoice) and update the Remarks as AGRP from LBP and GRP as per Invoice value as abnormal.
  • Check for #NA, update GRP as per unit price(invoice) & AGRP as 0 and Remarks as As per Invoice value as no price in LBP.
• 1.10.81 After updating GRP and AGRP, check the Remarks for any unavailability of prices and updated with different prices like all Pricelink transactions should be extracted from LBP, B2B from Coverage and Program billings from Pricelist.
• 1.10.82 Go to AGRP Column and filter with 0, then go to SMB Revenue column and update as per below formula
  • =GRP * QTY
• 1.10.83 Go to AGRP Column and filter without 0, then go to SMB Revenue column and update as per below formula
  • =AGRP * QTY
• 1.10.84 Check if the total sum of SMB Revenue and Total invoice value as their difference should be less than 7%.
• 1.10.85 Filter CIN transactions in cin_contractno column and check with finance on the GRP & AGRP and update in the DSR.
• 1.10.86 Connect with Finance team on the missing prices from Pricelist & LBP data.
• 1.10.87 Also check if any SI transactions were missed to include from Coverage file.
• 1.10.88 Once everything is done, we need to update the DSR in Claims format.
• 1.10.89 Open Claims ST DSR and copy the columns from DSR to Claims file as per below table.

Base DSR	Claims DSR	Base DSR	Claims DSR	Base DSR	Claims DSR
Current QTR (2526-Q4)	QTR	ca	CA	Endcustomer DMU	End Customer DMU
rol_month (mm-dd-yyyy)	ROL	invoice_date	INVOICE_DATE	Blank	Dummy3
Update M1/M2/M3 basis ROL	ROL_M	invoice_date	GTN Invoice Date	Blank	Dummy4
t1_name	DISTY	inv_value	InvVal	Unit	unit_price
T1 Sold to Code	T1 Sold to Code	"NO"	Zero GTN	GRP	lenovo_cleared_price
t1_lpp	T1 LPP	SMB Revenue	GTN Rev	AGRP	Approved GRP
Partner Acquisition Status	Partner Acquisition Status	"INR"	INR/USD	Backend Type	Backend Type
manage_type	Managed	week	WEEK	Blank	Slab Based Program
Blank	FA_FR	weeknew	WEEK_WF	Blank	Unique Billing
Formulated	BP_TYPE	invoice_no	INVOICE_NO	T1_MDM_ID	T1_MDM_ID
dmu	DMU	GRP	GRP	T1_BP_Type	T1_BP_Type
t2_lpp	T2 LPP	bpcategory	CC_BPCATEGORY	Blank	T1_BP_Sub_Type
bpcontractno	CONTRACT_NO	Formulated	INV_MTM	Blank	T1_D365
bpname	RESELLER_NAME	Blank	ZD_FD	T2_MDM_ID	T2_MDM_ID
city	CITY	If BILL_THROUGH is "B2B", update "B2B" else "TOPSELLER"	TOP SELLER	T2_BP_Type	T2_BP_Type
state	STATE	cin_contractno	CIN_CONTRACTNO	Blank	T2_BP_Sub_Type
Territory	Territory	bill_through	BILL_THROUGH	Blank	T2_D365
newregion	REGION	Blank	LGP Applicable	Blank	AP_Subsegment
India II	INDIA_I_II	End Customer	End Customer	AP_TS	AP_TS
rcm	RCM	endcustomername	ENDCUSTOMERNAME	AP_CTO	AP_CTO
mtm	MTM	remarks	REMARKS	AP_RTM	AP_RTM
Blank	SPTYPE	"MM"	Segment	Blank	End Customer Startup
"YES" If the Family is T&X for Thinkpad else "No"	PREMIUMPROD	Blank	special_bid	Quote Type	Quote Type
qty	QTY	T2 GSTIN	GST No

• 1.10.90 For Category, BIZGROUP, PRODTYPE, FAMILY, PROCESSOR_TYPE, OSDOS, SPECIFICATION & SERIES, map the details from MTM Master based on MTM.
• 1.10.91 Map CustCode from DSRs shared by Disty with concatenation of Invoice Number, MTM, QTY & Contract#. Leave blanks for Lenovo billings & Supertron.
• 1.10.92 Share with Finance team post approval from Marlene.