AP MM ST DSR

Process Overview

Fields Dexcription
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 Legends

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

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

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

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

Rebate List from KC Darshan. Legends

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

Flowchart

1.10 Process Description

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

Process Description

  • 1.3 Click on Quotation. Process Description

  • 1.4 Filter Business Unit with PCSD. Process Description

  • 1.5 Filter Region with IN and Country with IN, IN12 & IN30. Process Description

  • 1.6 Right click and download as Data.

  • 1.7 Save the Downloaded file as LBP Extract with current date in XLSB extension to avoid loading time.
  • 1.8 Delimit Contract Number.
  • 1.9 Add new column next to it with concatenation of Contract Number and Quotelines Product Number.
  • 1.10 Select Quotelines Channelprice entire column and highlight it with Yellow.
  • 1.11 Similarly, Quotelines Sales Price.
  • 1.12 Create a pivot.
  • 1.13 Add Quotelines Product Number and CTO MTM in rows.
  • 1.14 Convert to Classic pivot and remove the subtotals (Design-Subtotal-Do Not Show SubTotals).
  • 1.15 Filter CTO MTM with only Blanks.

Process Description

  • 1.16 Open Final DSR received from each T1s.
  • 1.17 Add new column at the beginning with concatenation of Invoice number, MTM, QTY & CinContract#.

  • 1.18 Open Consolidated Base file. Process Description

  • 1.19 Similarly, add new column in beginning with concatenation of Invoice number, MTM, QTY & CinContract#.

  • 1.20 Then add one more column next to it.
  • 1.21 Check for any new transactions using vlookup function.
    • =vlookup(A2, “Final DSR concatenated column”,1,0).
  • 1.22 If found any new transactions, add them in base DSR.
  • 1.23 Similarly, check in Overall Coverage file that if any Lenovo billings have been missed.
  • 1.24 Check with all data i.e., Contract numbers, End customer name and invoice date.
  • 1.25 Similarly, check with all T1’s DSRs.
  • 1.26 If any mismatches were found, update them.
  • 1.27 Remove the column with the formula apart from concatenated column.

  • 1.28 Open current date’s Coverage report Process Description

  • 1.29 Add new column next to Contract No with concatenation of Contract No and Material.

  • 1.30 One more column next to Local_Currency with Unit Price with calculation of (Local_Currency_Value/ Billed Quantity).
  • 1.31 Open Consolidated Coverage file of last 4 qtrs.

Process Description

  • 1.32 Go to Base DSR.
  • 1.33 Add two columns next to cin_contractno i.e., As per Coverage and Remarks1.
  • 1.34 Go to last column (after ASM column) and add columns with headers as followed.
  • 1.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.36 Update Unit as inv_value/QTY.
  • 1.37 Filter bill_through column only with Pricelink.
  • 1.38 Go to As per Coverage check with below formula with Coverage file.
    • =VLOOKUP (A2, “[COVERAGE REPORT]! Contract NoMaterial: T&R”,20,0)
  • 1.39 Filter #NA and redo the same step with consolidated coverage of last 8 qtr file and check for available transactions.
  • 1.40 Break the link.
  • 1.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.42 Select mapped values from As per Coverage and update it as Yes, else update with No.
  • 1.43 Keep blanks for Program billings.
  • 1.44 Filter As per Coverage with Yes, update topseller column with B2B else TS.
  • 1.45 Filter As per Coverage with Yes, update bill_through column with B2B, for No, Pricelink and keep Program as it is.
  • 1.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.47 Filter bill_through - B2B & Pricelink.
  • 1.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.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.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.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.52 Open CC DSR Template.

Process Description

  • 1.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.54 Go to T1 LPP Code sheet.
  • 1.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.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.57 Filter #NA and update it with T1 Sold to Code.
  • 1.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.59 Add new column next to T2 GSTIN (Base DSR) i.e., PAN (=Mid (T2 GSTIN,3,10))
  • 1.60 Go to DSR template and PC Managed sheet.
  • 1.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.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 update it with T1 Sold to Code
  • 1.63 Filter data in T2_MDM_ID beginning with “PA” and update T2_BP_Type with “T2 Reseller” keep remaining as blanks.
  • 1.64 Filter bill_through with B2B and update AP_TS with Non TS else TS.
  • 1.65 Filter t2_name with Direct Billings by Lenovo and update AP_RTM with Direct else Indirect.
  • 1.66 Filter bill_through with B2B and update Bid Type with Frontend and for Pricelink Backend and for Program keep it blanks.
  • 1.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.68 Open Last 4 qtrs Reseller names (D:\Backup\Desktop\DSR for AP\FY 2324\Q1'2324).
  • 1.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.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.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.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.73 For GRP & AGRP, we need to extract it from Coverage, LBP, Pricelist and Service pricelist.
  • 1.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.75 Filter bill_through with Program, Biz group with Services and t1_name as Rashi.
  • 1.76 Open Service Pricelist received from Sharika.
  • 1.77 Update GRP as per below formula-
    • =VLOOKUP (MTM, [Service pricelist] Part Number: Q2FY2324 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.78 Open Pricelist received from Dejo and consolidate it in a separate file with MTM and Channel Price.
  • 1.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.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.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.82 Go to AGRP Column and filter with 0, then go to SMB Revenue column and update as per below formula
    • =GRP * QTY
  • 1.83 Go to AGRP Column and filter without 0, then go to SMB Revenue column and update as per below formula
    • =AGRP * QTY
  • 1.84 Check if the total sum of SMB Revenue and Total invoice value as their difference should be less than 7%.
  • 1.85 Share it from CCreports ID
    • To receipeints & CC recepients from the previous Week