DESK PROCESS FOR PREPARATION OF SM SELL THRU DSR – FINANCE
Input Data Matrix
The list below provides details of input required To Prepare SM ST DSR For Claims Submission:
| SL No | Required Inputs | Received From | Date | Purpose |
|---|---|---|---|---|
| 1 | SM ST Daily DSR | India SMB Ops | smbops@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) |
| 2 | SMB Rebate List | KC Dharshan kdharshan@lenovo.com | After the closure of the previous month. Before 2nd of current month. | To Map the MDM ID for T2 & T3 Rebated Partners |
| 3 | Consumer LES & LMB List | Rajesh SN rsn@lenovo.com | After the closure of the previous month. Before 2nd of current month. | To map MDM ID for retail partners |
| 4 | GRP List | Dejo dmathew@lenovo.com Kalpesh kchande@lenovo.com | After the closure of the previous month. Before 12th of current Month. | To Map GRP for the MTM which are not available in the GRP list uploaded on DM |
| 5 | Non TS MTM List | Dejo dmathew@lenovo.com 10th of every month To map the Non TS MTM in the DSR | ||
| 6 | Services Price List | Hareesh hkumbam@lenovo.com | After the closure of the previous month. Before 2nd of current month. | To map the GRP for Rashi services MTMs |
| 7 | Issue Tracker | Wyzmindz(Internal Tracker) | 11th of every month | To check if all the issues received from Sales or Ops team are addressed and resolved. |
SM ST DSR Preparation & Validation:
Step 1: Consider “Daily SM Sell thru DSR” published on 8th of current month for the previous month. Below is the Snap Shot
Step 2: Delete “Dashboard & DSR Summary” 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 “SMB” in DM Comparison & QV Comparison
DM Out Put:
QV Output:
b. After selecting data as explained in step above 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 SM ST Claims DSR(Qtr, Managed BP, GTN Invoice Date, INR/USD, GTN Revenue, Dummy 1, Dummy 2, Dummy 3, Dummy 4 and columns related MDM ID and AP Susegment) Refer attached excel file for header format & Sequence.
📊 SOSB ST DSR Claims Header Format(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 from the DSR.
a. For eg. Select March’2020 transactions. And delete all the march transactions in the DSR.
Process For Data Consolidation:
Consolidation Process does not happen in SBSO ST DSR, since there is no movement happens from other data to SBSO ST DSR.
Make sure if all the E.Com transactions moved from SI to ST DSR are relfecting in the ST DSR which will be automatically moved from backend from Lenovo IT.
DSR Preparation is explained in the next sheet.
SM 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 SM 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 “Accepted, Pending for acceptance, or Pending for approval” map the partners as “Yes”.
- For all other partners map as “No”.
c. 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).
d. Under Header “INR/USD” update as “INR” as default for all India ST transactions & USD for all the SLBD transactions, curently we have only India transactions in SM ST DSR.
e. 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 & Visual Standalone if not available please contact Product or Finance team)
- If SRP values are not available for the transactions considered as SnP or any other option then “GTN Revenue=InvVal”.(only for Standalone SNP MTMs)
f. Leave it blank under header “Dummy 1”, “Dummy 2”, “Dummy 3” & “Dummy 4”
g. Follow the below table to update the Values under “T1_BP_Type, T1_BP_Sub_Type, T1_D365 & T2_BP_Type, T2_BP_Sub_Type, T2_D365)
| New BP Category | T2_BP_Type | T2_BP_Sub_Type | T2_D365 |
|---|---|---|---|
| T1/ND/Distibutror | T1 Distributor | ||
| LES | T2 Retailer | Storefront | AP Lenovo Exclusive Store |
| SRD | T2 Reseller | Regional_Distributor | |
| LEP | T2 Reseller | T3_Reseller | AP Lenovo Exclusive Partners |
| LMB | T2 Retailer | Storefront | AP Large Multi Brand |
| LFR | T2 Retailer | Storefront | AP Large Format Retailer |
| NON REBATE - BREADTH | |||
| E-COMMERCE | T2 Retailer | etailer | AP eCommerce |
| ONLINE | T2 Reseller | AP eCommerce | |
| S&P SRD | T2 Reseller | Regional_Distributor | AP S&P Partners |
| SMO - SB | T2 Reseller | ||
| TES | T2 Retailer | Storefront | AP Think Experience Store |
Step 3: Align the T1 MDM for each T1 partner as per the SM rebate list Received by “KC Sharshan - kdharshan@lenovo.com” on a monthly basis for previous month closure.
Attached the file of SM MDM ID file.
Step 4: Consider the SMB Rebate file received from “KC Sharshan - kdharshan@lenovo.com” & LES & LMB list received from Rajesh SN to map the T2 MDM ID in “SM ST DSR”
Attached the file of SMB Rebate List & LPP master.
a. There will be 11 BP Categories “Shown in the below snap shot” if T2 partner name is not available in both Rebate List & LPP Master then the partner category will be tagged as “Non rebate Breadth for ST transactions”
b. T2 LPP Code will be mapped as per “State & Pan India Level” refer below table on basis of BP category
| New BP Category | Mapping Level |
|---|---|
| T1/ND/Distibutor | Pan India |
| E-COMMERCE | |
| ONLINE | |
| LES | State |
| SRD | |
| LEP | |
| LMB | |
| LFR | |
| S&P SRD | |
| SMO - SB |
Note
- MDM ID is mandatory for all “SRD, LES, TES, LEP, LMB, LFR, Online, E.Commerce, SMO, S&P SRD” partners
- Remove CC-T2, CC-ND, SMO-CC & ND-S&P” from SMB Rebate list.
c. If T2 partner is not avalible in both the files(SMB Rebate List & LES/LMB list) then Cust Code = T2 MDM ID & BP Category will be tagged as “Non Rebate Breadth” for all other ST transactions.
ST Transactions:
Validation Process
| SL No | Headers as displayed in Claims DSR(In Same Sequence) | Logic/Data Mapping - Description for populating respective headers | Mandatory Field Format as defined by AP | Validation Points |
|---|---|---|---|---|
| 1 | Qtr | Quarter format has to be in "YYYY-Q#" | YYYY-Q# | No Blanks, format has to be as defined |
| 2 | ROL | as per QV extract | No | No Blanks |
| 3 | DISTY | Name of all Disty | No | No Blanks |
| 4 | T1 Sold TO Code | 1. Based on Coverage file - Sold to Codes with max CA were selected and aligned.2. Sold to Codes are constant till date | No | 1. No Blanks 2. Sold to Code format as per SI DSR 3. Non Mandatory Field post discussion with AP team |
| 5 | T1 LPP Code | 1. Based on Inputs received from KC Dharshan 2. If the LPP Code is based on Territory then refer to Territory Column to align LPP Codes(LPP/MDM) As per defined LPP Format Refer KC Dharshan’s mail for validation | ||
| 6 | CustCode | 1. As per Disty reporting 2. For SI=ST transactions Sold to Code = Cust Code No No Blanks | ||
| 7 | ContrNo | 1. T2 Contract # for rebated partners has to be based on respective quarter Rebate List 2. For non rebated partners during running quarter if the contract # & category is already available do not change No 1. Contract # has to be available for all verticals except for "Non Rebated Breadth" partners 2. Contract # has to be based on SM Segment only 3. All LPP Code aligned has to be mapped for Contract # - Can be validated against Rebate List | ||
| 8 | T2 LPP Code | 1. For Rebated Partners – LPP/MDM Code has to be based on Rebate List and as per any speacial instruction received if any 2. For Retail Transactions LPP/MDM Code has to be as per SM Segment 3. For Non Rebated Breadth if LPP/MDM code is not available : LPP Code will be = to Cust Code 4. For all partners to whom LPP/MDM code is aligned do not change or remove unless requested or approved by segments ops LPP/MDM Code as per defined LPP/MDM Codes 1. No Blanks 2. LPP/MDM Codes should be related to SM Segment 3. LPP/MDM Codes should be based on Vertical 4. Partner Name,vertical & Contract # has to match with Rebate list based for all the LPP/MDM code aligned in DSR | ||
| 9 | Managed BP | For all rebated partners tag under "yes" else "No" "Yes", "No", "Blank" No Blanks | ||
| 10 | ConsBPType | For all rebated partners tag under "Managed" else "Unmanaged" No No Blanks | ||
| 11 | New BP Category | 1. Tagging as per Rebate List for all rebated partners 2. Transactions excluding above will be under "Non Rebated Breadth" Valid Category are SRD/Online/LFR/E-Comm/LMB/TES/LEP/SMO-SB/S&P SRD/Non rebated Breadth 1. No Blanks 2. Cross validate against rebate list for Partner Name/Contract # & Vertical tagging under SM Segment | ||
| 12 | New Tablet Tagging | Field Not Required No Field Not Required | ||
| 13 | BPName | As per Disty reporting No Special Characters like : ", ' 1. No Blanks 2. Cross validate against rebate list for Partner Name/Contract # & Vertical tagging under SM Segment for all LPP/MDM code aligned transactions | ||
| 14 | CITY | As shared by Disty No No Blanks | ||
| 15 | STATE | Mapping is based on SMB Segment City Mapping file Based on GTN Format 1. No Blanks 2. Quarterly Confirmation on State vs Territory vs Region Mapping file | ||
| 16 | CorrectedTerritory | Mapping is based on SMB Segment City Mapping file Based on GTN Format 1. No Blanks 2. Quarterly Confirmation on State vs Territory vs Region Mapping file | ||
| 17 | Region | Mapping is based on SMB Segment City Mapping file Based on GTN Format 1. No Blanks 2. Quarterly Confirmation on State vs Territory vs Region Mapping file | ||
| 18 | Tier | Based on City Mapping file No No Blanks | ||
| 19 | ACM | Based on RCM Maping file input received from "segment Ops" No Blanks only for transactions moved from SI | ||
| 20 | MTM | As per Disty reporting No Speacial characters characters has to be 10 digits, cross validate if characters are </> 10 | ||
| 21 | CATEGORY | Based on AP MTM Master No No Blanks | ||
| 22 | SPTYPE | Based on the inputs from product team No Ensure to confirm Stand Alone MTM list from product Team | ||
| 23 | BIZGROUP | Based on MTM Master No No Blanks | ||
| 24 | PRODTYPE | Based on AP MTM Master No Blanks can be there | ||
| 25 | FAMILY | Based on AP MTM Master No Blanks can be there | ||
| 26 | ProcessorType | Based on AP MTM Master No Blanks can be there | ||
| 27 | OSDOS | Based on AP MTM Master No Blanks can be there | ||
| 28 | SPECIFICATION | Based on AP MTM Master No Blanks can be there | ||
| 29 | SERIES | Based on AP MTM Master No Blanks can be there | ||
| 30 | FORM FACTOR | Left Blank No Blanks can be there | ||
| 31 | Qty | As per Disty shared As per Disty reporting No transactions with value "0" | ||
| 32 | CA | 1. For all transactions where Biz Group is product then Qty = CA 2. for S&P/Services/Visuals, CA = 0 default NA 1. values has to be >/< 0 for DT/NB 2. 0 for S&P line items | ||
| 33 | InvDt | As per Disty shared Dates has to be between the month closing DSR 1. No Blanks | ||
| 34 | GTN Invoice Date | Invoice Date = GTN Invoice Date "yyyy-mm-dd" 1. No Blanks 2. At Transactions level comparision between Invoice Date & GTN Date has to be "True" | ||
| 35 | Week | Based on Invoice Date mapping is through ROL Calendar NA No Blanks | ||
| 36 | INR/USD | 1. For Disty reporting tagging will be default in "INR" No 1. No Blanks 2. Cross check for SI=ST transactions | ||
| 37 | InvNo | 1. Based on Disty reporting No No Blanks | ||
| 38 | InvVal | 1. Based on Disty reporting No No Blanks | ||
| 39 | Zero GTN | Default tagging is under "No" unless specified with list of MTM that needs to get tagged under "Yes" "No"/"Yes" No Blanks | ||
| 40 | GTN Revenue | 1. SRP Revenue = GTN Revenue for DT/NB & Stand Alone S&P 2. For hard bundled Invoice Value = GTN Revenue No 1. No Blanks 2. If 0 then it has to be hard bundle S&P line items | ||
| 41 | SRP | 1. For SnS - GRP is as per Price List received from Product Team 2. For GRP Variance between date range - This mapping is automated and the mapping file is currently managed by Product Team(Dejo) - DSR gets mapped from this table for listed MTM 2. If not available get GRP values from Dejo & Kalpesh | ||
| No Mandatory for DT/NB | ||||
| 42 | SRP_Revenue | SRP*qty No No Blanks | ||
| 43 | Premium Prod | Non Mandetory Field No Blanks can be there | ||
| 44 | MTM Type | Map based on MTM master No No | ||
| 45 | Segment | Default SM SM No Blanks or different Segment | ||
| 46 | SNO | No No No | ||
| 47 | System Integration | No Mapping is done No No | ||
| 48 | Dummy 2 | No Mapping is done No No | ||
| 49 | Dummy 3 | No Mapping is done No No | ||
| 50 | Dummy 4 | No Mapping is done No No | ||
| 51 | T1_MDM_ID | 1. Based on Inputs received from KC Dharshan 2. If the LPP Code is based on Territory then refer to Territory Column to align LPP Codes(LPP/MDM) As per defined LPP Format Refer KC Dharshan’s mail for validation | ||
| 52 | T1_BP_Type | Default “T1 Distributor” for all the T1 reported transactions for the transactions moved from SI leave it “Blank” Yes Blanks can be there | ||
| 53 | T1_BP_Sub_Type | Blank Yes Blanks can be there | ||
| 54 | T1_D365 | Blank Yes Blanks can be there | ||
| 55 | T2_MDM_ID | 1. For Rebated Partners – LPP/MDM Code has to be based on Rebate List and as per any speacial instruction received if any | ||
| 2. For Retail Transactions LPP/MDM Code has to be as per SM Segment | ||||
| 3. For Non Rebated Breadth if LPP/MDM code is not available : LPP Code will be = to Cust Code | ||||
| 4. For all partners to whom LPP/MDM code is aligned do not change or remove unless requested or approved by segments ops LPP/MDM Code as per defined LPP/MDM Codes 1. No Blanks | ||||
| 2. LPP/MDM Codes should be related to SM Segment | ||||
| 3. LPP/MDM Codes should be based on Vertical | ||||
| 4. Partner Name,vertical & Contract # has to match with Rebate list based for all the LPP/MDM code aligned in DSR | ||||
| 56 | T2_BP_Type | Refer Table 1 on page 13 Yes Blanks Can be there | ||
| 57 | T2_BP_Sub_Type | Refer Table 1 on page 13 Yes Blanks Can be there | ||
| 58 | T2_D365 | Refer Table 1 on page 13 Yes Blanks Can be there | ||
| 59 | AP_Subsegment | Default “Channel” No No Blanks | ||
| 60 | AP_TS | To be tagged as TS and Non TS , for Non TS refer mail received from Dejo and update the column as Non TS based on the MTM and NP Category No No Blanks | ||
| 61 | AP_CTO | CTO & NON-CTO, check MTM if it contains CTO in it then “CTO” else “NON CTO” No No Blanks | ||
| 62 | AP_RTM | Default “InDirect” |
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” along with the corrections shared(if any) from the ops head 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 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 SB 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.
