SOP FOR PREPARATION OF CONSUMER SELL THRU DSR - FINANCE
Input Data Matrix
The list below provides details of input required To Prepare Consumer ST DSR For Claims Submission:
| SL No | Required Inputs | Received From | Date | Purpose |
|---|---|---|---|---|
| 1 | Consumer ST Daily DSR | Consumer Report consumerrepo@lenovo.com | On 8th day of every month for the previous months of the same QTR. (Consider after clearing all the exceptions or manual upload) | Data used for claims submission |
| 2 | Tablet ST DSR | Tablet Report tabletreport@lenovo.com | On 8th day of every month for the previous months of the same QTR. (Consider after clearing all the exceptions or manual upload) | To move the MIXX Transactions NOTE: Retain these transactions in Tab DSR as well |
| 3 | Services Transactions | Services Ops Team | On 10th day of every Qtr as this is a Qtr program only | Manually include the Services billing shared for LMB & ND partners only |
| 4 | Omni Channel DSR | LES Team | On or before 7th of every month. | To move all the transactions to Cons ST DSR |
| 5 | Consumer T2 Rebate List | Consumer BPRM Team | After the closure of the previous month. Before 2nd of current month. | To Map the LPP codes for T2 Rebated Partners This is done to identify any differences in LPP codes mapped in LPP master & Rebate List |
| 6 | LPP Master Data | Consumer BPRM Team | After the closure of the previous month. Before 2nd of current month. | To map LPP codes that are not listed in T2 Rebate List |
| 7 | MTM Master | Consumer Product team | After the closure of the previous month. Before 2nd of current month. | To map all the details related to MTM BIZGROUP, SPECIFICATION, MTM TYPE, SRP, FORM FACTOR, SPTYPE |
| 8 | Issue Tracker | Wyzmindz (Internal Tracker) | 9th of every month | To check if all the issues received from Sales or Ops team are addressed and resolved. |
Consumer ST DSR Preparation & Validation:
Step 1: Consider “Daily Consumer Sell thru DSR” published on 8th of current month for the previous month. Below is the Snap Shot
Step 2: Delete “Summary, Pivot & Consumer S&P” Sheet.
Step 3: Validate No of Line Items, Qty & Inv Value using DM Vs QV Output
a. Select Qtr as “Q4/2019” & SUB_SEGMENT as “Consumer” in DM Comparison & QV Comparison
DM Out Put:
QV Output:
b. After selecting data as explained in step a we should get the out put as below snap shot all the values should be “0”.
Step 3: Rearrange the headers as per Claims Format.
Note
Few New columns needs to be included in Consumer ST Claims DSR(Qtr, Managed BP, CA, GTN Invoice Date, INR/USD, GTN Revenue, Dummy 1, Dummy 2, Dummy 3, Dummy 4) Refer attached excel file for header format & Sequence.
📊 Consumer ST Claims DSR(Excel)
Step 4: Under Header “InvDt” select the date for the months for which Claims DSR is getting prepared .
Note
(eg. For preparing the claims submission for the month of Feb’20 the date has to be >or= 1st Jan’20 & <or= 29th Feb’20). Remaining transactions has to be deleted form the DSR.
Screen Shot in Next Sheet
a. For eg. Select March’1920 transactions. And delete all the march transactions in the DSR.
Process For Data Consolidation:
The core data is added with mutiple other data for claims purpose only.
1. MIIX Transactions from “Tablet ST DSR” is moved to the respective month’s “Consumer ST DSR”
Step 1: Transactions from Tablet for the Specific list of MTM (Gowtham Mail Dte: 13/feb/19) is included into consumer ST claims DSR only.
a. Please find the below list of MTM for MIIX transactions, which has to be moved from current month “Tablet ST DSR” to “Consumer ST DSR”
| MTM | Biz Group |
|---|---|
| 80SG00BUIH | MIIX Tablet |
| 80U100J7IH | MIIX Tablet |
| 80U100JBIH | MIIX Tablet |
| 80XF00DBIN | MIIX Tablet |
| 80XF00DFIN | MIIX Tablet |
| 80XF00G1IN | MIIX Tablet |
| 80XF00FYIN | MIIX Tablet |
| 80XF00GCIN | MIIX Tablet |
| 81H3004RIN | MIIX Tablet |
b. Consider “Tablet ST Claims DSR” based on above mentioned MTM lookup up the transactions. Replicate the transactions with the mentioned MTM’s in Consumer ST DSR.
c. Once the MIIX transactions are replicated in the “Consumer ST DSR”, validate the Transactions from Tablet ST DSR to check if all the related transactions has been moved,(Count of Line Item,Qty or CA,Overal Inv Value) d. T1 & T2 LPP Codes should be mapped based on consumer segment only & not tablet segment.
2. Transactions from “Partner incentive on services” is moved to the “Consumer ST DSR”
a. Consider only “LMB” & “ND/Distributor” transacrions only as shown below.
📊 Pattner Incentive Redemption Program(Excel)
b. Cross Verify the MTMs shared in the data received, MTM Length should not be more than 10 digits and also it should be in MTM format Exc Names or Decription should not be there in MTM Column,
c. There will be 2 MTM columns in the Data Provided one is “MTM Base Machine” & the other is “Warranty Pack Number”, while updating in DSR consider MTMs under “Warranty Pack Number” only and check for Format or length suggested in above step.
d. Column “Selected Offer” should be updated as Descriotion in DSR.
e. Invoice Date in claims DSR should be updated based on “REG DATE” in Services data and Invoice No Should be updated based in “TICKET NO” in Services Data.
f. Cross verify T2 LPP codes shared by the partner in the Services dump shared.
3. Move all the “Omni Channel” transactions received from Saket Verma(sverma6@lenovo.com) to “Consumer ST DSR” refer attached file for Column mapping
a. Name Disty as “NDSL” as show in the below snap shot.
b.All the details related to MTM should be mapped from MTM master received from Gowtham.( BIZGROUP, SPECIFICATION, FORM FACTOR, SRP, MTM Type)
c. Map T1 & T2 LPP code based on consumer segement only.
Consumer ST Claims DSR Preparation:
The DSR prepared for claims submission is same as the format that is required for the upload on DCSDM.
Step 1: Consider Consumer ST DSR which has been consolidated as per previously explained steps.
Step 2: Following are the mapping done for the New Columns that are manually included for claims DSR Preperation.
a. Under “Qtr” Column mention Qtr name Ex. “1920-Q3” (eg. For Quarter 3 update as Q3) FY-Fiscal Year (eg. For FY’2019 update as “1920”, For FY’2020 Update as “2021”)
b. Under “Managed BP” map it as “Yes or No” based on the following considerations.
- Managed BP will be mapped as “Yes” or “No” based on the T2 Rebate List.
- If Partner status in the T2 Rebate List is mentiones as “Rolled out or Uploaded” map the partners as “Yes”.
- For all other partners map as “No”.
c. Under Header “CA” replicate the numbers which are there in “Qty” Column refer below points to update “CA”
- If “BIZGROUP” is Base Machines & Consumer Visulas(Refer below table for the names) CA will be “Qty = CA”
| BIZGROUP |
|---|
| CONSUMER NB |
| CONSUMER VISUALS |
| THINKPAD |
| CONSUMER DT |
| THINK CENTRE |
| SB DT |
| LENOVO B / V |
| THINKBOOK |
| Idea Tablet |
| Think Station |
- For all other BIZGROUP “CA = 0”
d. Under header “GTN Invoice Date” we need to replicate the data from the header “Invoice Date” and the date format has to be changed manually to “YYYY-MM-DD”.(Eg. 2020-03-06 for 6th March 2020).
e. Under Header “INR/USD” update as “INR” as default for all the transactions.
f. Under Header “GTN Revenue” values should be updated from “SRP_Revenue” & “InvVal”.
- If SRP value are available for the transactions then “GTN Revenue=SRP_Revenue”(SRP is mandatory for all the Base Machines & Consumer Visuals if not available please contact Gowtham to get the SRP gowtnava@lenovo.com)
- If SRP values are not available for the transactions considered as SnP or any other option then “GTN Revenue=InvVal”.
g. Leave it blank under header “Dummy 1”, “Dummy 2”, “Dummy 3” & “Dummy 4”
Step 3: Align the T1 LPP Code for each T1 partner as per the Consumer T1 LPP Master File received from “Consumer BPRM Team” on a monthly basis for previous month closure.
Note
If Disty is Lenovo T1 LPP code will be consider as T1 LPP code = Sold to code.
Step 4: Consider the T2 Rebate Master file & LPP Master received from BPRM Team to map the T2 LPP Code in “Consumer ST DSR”
📊 Q1 2223 T2 Master file as on 1st June(Excel)
📊 2022-05-11 BP Master Data T & C(Excel)
Attached the file of T2 Rebate & LPP master.
Note
For all the Rebated partners LPP code has to be Mapped as per the T2 Rebate list.
a. There will be 8 BP Categories “Shown in the below snap shot” if T2 partner name is not available in both T2 Rebate master & LPP Master then the partner categopry will be tagged as “One Time Partner”
b. T2 LPP Code will be mapped as per “City, State & Pan India Level” refer below table on basis of BP category
| BP Category | Mapping Level |
|---|---|
| LFR | Pan India |
| ONLINE | |
| E-COMMERCE | |
| RD | State |
| S&P RD | |
| LES | |
| LMB | City |
c. Map all the T2 LPP code for T2 partner available in T2 Rebate master based on Name & as explained in step b.
d. Map T2 LPP code from LPP master for all the T2 partners not availble in T2 Rebate master
Note
LPP code is mandatory for all “RD, LES, LMB, LFR, Online, E.Commerce” partners
e. If T2 partner is not avalible in both the files then Cust Code = T2 LPP Code & BP Category will be tagged as “One Time Partner”
Step 5: Updating MDM & othere Colums Related to MDM.
a. MDMs will be available in the rebate list shared for both T1 and T2 and same will be updated in LPP Code Column as well , suposse if there are any LPP instead of MDM in LPP Code Column we need to map MDM based on Rebate list by Vlookup from Rebate list to LPP Code column from DSR to get the MDM.
b. There are 3 More Columns which are related to MDM “T2_BP_Type”, “T2_BP_Sub_Type” & “T2_D365” for T2 and same 3 columns will be available for T1 MDM as well with Name starts from T1 instead of T2 below are the details that needs to be updated on these fields.
| BP Cat | BP_Type | BP_Sub_Type | D365 |
|---|---|---|---|
| T1/Distributor | T1 Distributor | ||
| IF NDSL Under Disty | T2 Retailer | etailer | AP eCommerce |
| RD/Visual RD | T2 Retailer | Regional_Distributor | |
| S&P RD | T2 Retailer | Regional_Distributor | AP S&P Partners |
| Online/E.Com | T2 Retailer | etailer | AP eCommerce |
| LFR | T2 Retailer | Storefront | AP Large Format Retailer |
| LMB | T2 Retailer | Storefront | AP Large Multi Brand |
| LES | T2 Retailer | Storefront | AP Lenovo Exclusive Store |
| OTP |
There are 4 new columns added based on the AP request as below
Values to be updated as suggsted below for the above 4 New Columns.
| New Coumns | AP_Subsegment | AP_TS | AP_CTO | AP_RTM |
|---|---|---|---|---|
| Details to be updated | Retail | TS | NON-CTO | InDirect |
Validation Process
| SL No. | Headers as displayed in Claims DSR(In Same Sequence) | Mandatory Field as defined by AP | Mandatory Field Format as defined by AP | Validation Points |
|---|---|---|---|---|
| 1 | Qtr | Yes | YYYY-Q# | No Blanks |
| 2 | ROL | No | As per DSR | No Blanks |
| 3 | DISTY | Yes | As per DSR | No Blanks |
| 4 | T1 Sold TO Code | yes | As per DSR | 1. No Blanks 2. Sold to Code format as per SI DSR |
| 5 | T1 LPP Code | Yes | As per T1 LPP Master received from Nagarjuna | 1. No Blanks 2. LPP Codes has to be based on instructions |
| 6 | CustCode | Yes | 1. As per Disty shared 2. For SI=ST Transaction Sold to Code = Cust Code | No Blanks |
| 7 | ContrNo | Yes | No | 1. Contract # has to be available for all verticals except for "One Time Partner" 2. Contract # has to be based on Consumer Segment only |
| 8 | T2 LPP Code | Yes | 1. For Rebated Partners - LPP Code has to be based on Rebate List and as per respective vertical 2. for retail Transactions LPP Code has to be as per Consumer Segment 3. For One Time Partner : LPP Code will be = to Cust Code | 1. No Blanks 2. LPP Codes should be related to Consumer Segment 3. LPP Codes should be based on Vertical 4. Partner Name,vertical & Contract # has to match with LPP Master based on LPP code aligned in DSR |
| 9 | Managed BP | Yes | For all rebated partners tag under "yes" else "No" | No Blanks |
| 10 | ConsBPType | No | For all rebated partners tag under "Active" else "Inactive" | No Blanks |
| 11 | New BP Category | Yes | Valid Category are RD/Alpha RD/LFR/Online/E-Comm/LES/LMB/One Time Partner | 1. No Blanks 2. Cross validate against LPP Master/rebate list for Partner Name/Contract # & Vertical tagging under Consumer Segment |
| 12 | New Tablet Tagging | No | No | No |
| 13 | BPName | Yes | As per DSR | 1. No Blanks 2. Cross validate against LPP Master/rebate list for Partner Name/Contract # & Vertical tagging under Consumer Segment |
| 14 | CITY | Yes | Based on GTN Format | 1. No Blanks |
| 15 | STATE | Yes | Based on GTN Format | 1. No Blanks 2. Quarterly Confirmation on State vs Territory vs Region Mapping file |
| 16 | CorrectedTerritory | Yes | Based on GTN Format | 1. No Blanks 2. Quarterly Confirmation on State vs Territory vs Region Mapping file |
| 17 | Region | Yes | Based on GTN Format | 1. No Blanks 2. Quarterly Confirmation on State vs Territory vs Region Mapping file |
| 18 | Tier | No | No | No Blanks |
| 19 | ACM | No | No | No Blanks |
| 20 | MTM | Yes | No | characters has to be 10 digits, less than or greater than that cross validate once |
| 21 | CATEGORY | No | No | Blanks can be there |
| 22 | SPTYPE | Yes | No | Ensure to confirm Stand Alone MTM list from product Team |
| 23 | BIZGROUP | No | No | No Blanks |
| 24 | PRODTYPE | No | No | Blanks can be there |
| 25 | FAMILY | No | No | Blanks can be there |
| 26 | ProcessorType | No | No | Blanks can be there |
| 27 | OSDOS | No | No | Blanks can be there |
| 28 | SPECIFICATION | No | No | Blanks can be there |
| 29 | SERIES | No | No | Blanks can be there |
| 30 | FORM FACTOR | No | No | Blanks can be there |
| 31 | Qty | Yes | No transactions with value "0" | No Blanks |
| 32 | CA | Yes | 1. For all transactions where Biz Group is product & Visuals then Qty = CA 2. for S&P, CA = 0 default | 1. values has to be >/< 0 for DT/NB 2. 0 for S&P line items |
| 33 | InvDt | Yes | No | 1. No Blanks 2. Invoice Date should belong to date range during running quarter |
| 34 | GTN Invoice Date | Yes | "yyyy-mm-dd" | 1. No Blanks 2. Invoice Date should belong to date range during running quarter 3. At Transactions level comparision between Invoice Date & GTN Date has to be "True" |
| 35 | Week | No | No | No Blanks |
| 36 | INR/USD | Yes | Mapped as Default "INR" | 1. No Blanks 2. Cross check for SI=ST transactions |
| 37 | InvNo | Yes | As per DSR | No Blanks |
| 38 | InvVal | Yes | As per DSR | 1. No Blanks 2. If 0 then Hard Bundle MTM |
| 39 | Zero GTN | yes | Default "No" | No Blanks |
| 40 | GTN Revenue | yes | 1. SRP Revenue = GTN Revenue for all the transactions where SRP revenue is available 2. InvVal = GTN revenue for all the transactions where SRP Revebue is not avaiable or "0" | 1. No Blanks 2. No "0" |
| 41 | SRP | No | As per product team input | Mandetory for DT/NB & Visuals |
| 42 | SRP_Revenue | No | SRP*qty | No Blanks |
| 43 | Premium Prod | No | No | Blanks can be there |
| 44 | MTM Type | yes | Entry/Mainstream/Premium | Blanks only for S&P line Iteam & Retail Transactions |
| 45 | Segment | yes | Consumer | No Blanks or different Segment |
| 46 | SNO | No | No | Blanks can be there |
| 47 | Dummy 1 | No | No | Blanks can be there |
| 48 | Dummy 2 | No | No | Blanks can be there |
| 49 | Dummy 3 | No | No | Blanks can be there |
| 50 | Dummy 4 | No | No | Blanks can be there |
| 51 | T1_LPP_ID | Yes | As per T1 LPP Master received from BPRM Team | 1. No Blanks 2. LPP Codes has to be based on instructions |
| 52 | T1_BP_Type | Yes | Default “T1 Distributor” | Blanks can be there |
| 53 | T1_BP_Sub_Type | Yes | Blanks can be there | |
| 54 | T1_D365 | Yes | Blanks can be there | |
| 55 | T2_LPP_ID | Yes | 1. For Rebated Partners - LPP Code has to be based on Rebate List and as per respective vertical 2. for retail Transactions LPP Code has to be as per Consumer Segment 3. For One Time Partner: LPP Code will be = to Cust Code | 1. No Blanks 2. LPP Codes has to be based on instructions |
| 56 | T2_BP_Type | Yes | As per the table below | Blanks can be there |
| 57 | T2_BP_Sub_Type | Yes | As per the table below | Blanks can be there |
| 58 | T2_D365 | Yes | As per the table below | Blanks can be there |
| 59 | AP_Subsegment | Yes | Default “Retail” | No Blanks |
| 60 | AP_TS | Yes | Default “TS” | No Blanks |
| 61 | AP_CTO | Yes | Default “NON-CTO” | No Blanks |
| 62 | AP_RTM | Yes | Default “InDirect” | No Blanks |
| New BP Category | T2_BP_Type | T2_BP_Sub_Type | T2_D365 |
|---|---|---|---|
| LFR | T2 Retailer | Storefront | AP Large Format Retailer |
| RD | T2 Retailer | Regional_Distributor | |
| ONLINE | T2 Retailer | etailer | AP eCommerce |
| LES | T2 Retailer | Storefront | AP Lenovo Exclusive Store |
| E-COMMERCE | T2 Retailer | etailer | AP eCommerce |
| LMB | T2 Retailer | Storefront | AP Large Multi Brand |
| VISUALS RD | T2 Retailer | Regional_Distributor | |
| S&P RD | T2 Retailer | Regional_Distributor | AP S&P Partners |
Process For Approval & Upload:
Once the DSRs are prepared for Claims submission, the DSR is validated at WyzMindz end and shared with the Lenovo Ops Heads for Approval.
The approval mail is shared in the below format.
After the approval from Ops Head is shared, the DSR is validated for one last round for standard columns like “Qtr”, “Qty”, “CA”, “GTN Invoice Date” & “GTN Revenue” before uploading the DSR onto DCSDM.
Below is the procedure for uploading the approved DSR onto DCSDM.
Step 1: Logon to DCSDM using the link http://cdms.lenovo.com:8083/DCSDM/
Step 2: Login with the user credentials.
Step 3: After login in the using the user credentials. Below page will be displayed.
Step 4: From the Drop down, please select the table onto which the DSR needs to be uploaded. For this case we need to select “MONTHLY INDIA CONS ST DSR CLAIMS”.
Step 5: Please choose the file from the destination where the DSR is saved.
Step 6: Please select the file that needs to be uploaded and click on open at the bottom right of the dialogue box.
