PAST DUE FUNNEL
Process Overview
| Field | Details |
|---|---|
| Process Name | Past Due Funnel |
| Segment | REL |
| Process Family: | Lenovo India |
| Process User(s): | Wyzmindz solutions pvt ltd |
| Process Purpose: | To systematically enrich and cleanse pipeline data by mapping sales ownership, RSM, segment, and offering attributes from multiple reference files, ensuring data accuracy and consistency. |
Version History
| Version Tag | Prepared By | Date of Update/Revision | Validated by | Reviewed by | Approved by | Revision Details |
|---|---|---|---|---|---|---|
| v1.0 | Sharanya J | 03-12-2025 | Navneeth | Sushruth Vasista | Vishwanath Mosale | SOP Document Creation |
Distribution and Access List
| Designation | Read | Write | Approval |
|---|---|---|---|
| Analyst | Yes | No | No |
| Team Lead | Yes | Yes | No |
| Manager | Yes | Yes | Yes |
1.1 Purpose
To ensure accurate and consistent preparation of the India Pipeline report by mapping correct ownership, segment, RSM, and offering details from multiple source files. This process supports reliable pipeline visibility and decision-making through standardized filtering, validation, and summary reporting for past data.
1.2 Scope
- Geographic Scope: India.
- Business Segment: REL
- Process Frequency: Every Friday
- Core Activity: Pipeline Data Standardization
- Key Distributors Covered: Lenovo
1.3 Business/Segment Overview
This Helps Client to Summaries and overview of Pipeline data for historical and current quarter. It provides a structured view of pipeline performance to help leadership track progress, prioritize opportunities, and make informed business decisions.
1.4 Responsibility and authority
- Analyst : Sharanya J
- Process Owner : Navaneeth
- Process Manager : Dayanand
- Escalation Manager : Sushruth Vasista
- Approval Authority : Vishwanath Mosale
1.5 Escalation matrix: (Wyzmindz and Lenovo)
Escalation Matrix – WyzMindz Solutions Pvt Ltd India
| Sl No. | 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
| Sl No. | Name | Designation | Email ID |
|---|---|---|---|
| 1 | Anand M Lanka | Head - India Order Fulfilment & Order Center | analanka@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 |
|---|---|---|---|---|
| Overall Coverage Report | Sharanya J | Sharanya J | Navaneeth K | Lenovo Ind Pvt Ltd, Wyzmindz – Lenovo Process Team |
1.7 Access Required
- Outlook - For receiving reports and communications.
- AP Data Access
- Access to MS Office Suite
1.8 Overview of Tools
| Sl No | Tool Name | URL | Description |
|---|---|---|---|
| 1 | Codata | https://codata.lenovo.com/ | It’s a website used to extract funnel. |
1.9 Legends
- ST– Sell thru
- SO - Sell Out
- NA – Not Available
- FCrad – Future Crad
- RSM – Regional Sales Manager
- SM – Sales Manager
- MDM – Master Data Management
1.10 Data Dependencies
- Funnel Dump
- MDM Dump
1.11 Schedule of Reporting
- Frequency: Every Friday.
1.12 Process Flow Chart
1.13 Steps of Procedure
- Open Co-Data/D365 https://codata.lenovo.com/fe/#/homePage.
- Login with “nk” ID.
- Click on D365-AP Opportunity & Activity Data Details.
- Click on D365 Opportunity Details.
- Filter Business Group field with IDG.
- Filter Region field with INDIA.
- Filter Fiscal Quarter field with Current Quarter and Previous 3 Quarters.
- Right click on table and select Option (3dots icon).
- Click on Download as…
- Then Again click on Data.
- Then Click on Export.
- Then click on Click here to download your data file.
- Open the file once downloaded.
- Next the Transaction Indicator column there is a Manager Forecast 2 and, in this column, apply a formula for bring the data of Manager Forecast column.
- =Manager Forecast
- Open Funnel Tagging file.
- Map Updated Opp Owner next to the Manager Forecast 2 column using Field Sales column with Sales Manager column from Funnel Tagging file and named this column as Updated Opp Owner.
- =IFERROR(VLOOKUP(Field Sales, ‘Sales Manager’ : ‘Updated Name’,5,0), “-”)
- Map RSM next to the Updated Opp Owner column using Updated Opp Owner column with Sales Manager column from Funnel Tagging file and named this column as RSM.
- =IFERROR(VLOOKUP(Updated Opp Owner, ‘Sales Manager’ : ‘RSM’,4,0), “-”)
- We have SBU_Code along with Segment based on that SBU_Code.
- Map Segment next to the RSM column using Sbu_Code column with Sbu_Code column from segment table of Funnel Tagging file and named this column as Segment.
- =VLOOKUP(Sbu_Code, ‘Sbu_Code’ : ‘Segment’,2,0)
- Open Services Offerings sheet from the Base file.
- Map OH L1 next to the Segment column using MTM column with PN column from Services Offerings file and named this column as OH L1.
- =IFERROR(VLOOKUP(MTM , ‘PN’ : ‘Services Offering Category’,3,0), “-”)
- Map OH L2 next to the OH L1 column using MTM column with PN column from Services Offerings file and named this column as OH L2.
- =IFERROR(VLOOKUP(MTM , ‘PN’ : ‘Services Offering Category’,4,0), “-”)
- Select Device as a Service in Solution Area column and Update DAAS column as “DAAS”.
- Remove the filter.
- Map Offering Bucket column using OH L2 column from Services Offerings sheet and named this column as Offering Bucket.
- =IFERROR(VLOOKUP(Services Offering Category , ‘Services Offering Category’ : ‘Offering Bucket’,4,0), “-”)
- Select all the REL segment in Segment column and select “-” in Updated Opp Owner column.
- Check Field Sales and Inside sales column. If we are getting any Field Sales, then update that name in Updated Opp Owner column.
- = Field Sales
- Then, Check Inside Sales column. If we are getting any Inside Sales, then update that name in Updated Opp Owner column.
- = Inside Sales
- Remove the filter.
- Select “EA” & “SA” segment in Segment column and check whether we are getting “NA” in RSM or not. If we will get “NA”, then select “NA” in RSM column and update it based on Updated Opp Owner.
- Remove the filter.
- Deselect “-”, “EA” , “SA” & “SMB” and check whether we are getting any name in RSM column. If we will get any name, then we have to select that name and change it as “NA”.
Note
- RSM is only for “EA” & “SA” apart from these two, RSM should be “NA” for other REL segments.
- Save this file as India Pipeline and put the today’s date.
- Now, Open India Pipeline today’s file.
- Select “Open” in “Status” column.
- Go to “Fiscal Quarter” column and select current quarter and all the previous quarter.
- Go to “Week” column and select current week(-1) and all the previous week.
- Select the resultant data from starting column to “Updated Opp Owner” column and paste in the new excel workbook.
- Open “Mapping” File.
- Map Opportunity Owner next to the Updated Opp Owner column using Opportunity Owner column with Sales Manager column from first table of Mapping file and named this column as Opportunity Owner.
- =IFERROR(VLOOKUP(Opportunity Owner , ‘Sales Manager’ : ‘Sales Manager’,1,0),“-”)
- Open latest “MDM Dump” file.
- Map MDM Dump next to the Opportunity Owner column using L2 MDM ID column with L2_MDM_ID column from MDM Dump file and named this column as MDM Dump.
- =IFERROR(VLOOKUP(L2 MDM ID , ‘L2_MDM_ID’ : ‘IDG_FTF_Rep’,24,0),“-”)
- Next to the MDM Dump column for correct name using L2 MDM ID column with L2_MDM_ID column from MDM Dump file. Mapping this only for reference of correct name.
- =IFERROR(VLOOKUP(L2 MDM ID , ‘L2_MDM_ID’ : ‘IDG_FTF_Rep’,25,0),“-”)
- Select the incorrect name in “MDM Dump” column and correct that name by referring next column
- After correcting all the names, paste the value of “MDM Dump” column and delete the next column.
- Next to the MDM Dump column name the blank column as “Sales Manager”. We have to update this column by referencing “Updated Opp Owner”, “MDM Dump” & “Opportunity Owner”.
- Deselect “-” from “Updated Opp Owner” column and update “Sales Manager” column same as “Updated Opp Owner” column by applying below formula.
- =Updated Opp Owner
- Remove the Filter.
- Select only blank in “Sales Manager” column and deselect “-” in “MDM Dump” column.
- Update “Sales Manager” column same as “MDM Dump” column by applying formula.
- = MDM Dump
- Remove the filter.
- Again, select blank in “Sales Manager” column and deselect “-” in “Opportunity Owner” column.
- Now again, update “Sales Manager” column same as “Opportunity Owner” column by applying below formula.
- = Opportunity Owner
- Remove the filter and do value paste in “Sales Manager” column.
- Check the RSM whether we are getting “NA” or not for “EA” & “SA” segment. If we will get “NA” for these segments, then we have to update as per “Sales Manager”.
- Deselect “-”, “EA”, “SA” & “SMB” in “Segment” column. All RSM should be “NA”. If we will get any name, then make those name as “NA”.
1.14 Output Overview
- Insert a pivot table based on “Data” sheet and named this pivot sheet as “Opp ID”.
- Pivot Selection should be.
- Fiscal Quarter – (All)
- Probability – (All)
- Brand – DT, DTWS, NB, NBWS
- Segment – Only REL segments (Deselect “-” & “SMB”)
- RSM – (All)
- Opportunity Owner – (All)
- Opportunity ID D365 – (All)
- Sales Manager – (All)
- Week – (All) [Sort by A to Z]
- Again, insert a pivot table based on “Data” sheet and named this pivot sheet as “Sales Manager”.
- Pivot Selection should be.
- Fiscal Quarter – (All)
- Probability – (All)
- Brand – DT, DTWS, NB, NBWS
- Segment – All REL segment (Deselect only “-” & “SMB”)
- Sales Manager – (All)
- Week – (All) [Sort by A to Z]
- Map Email IDs of Sales Manager next to the Pivot table using Sales Manager column of pivot table with Sales Manager column from Mapping file.
- =IFERROR(VLOOKUP(Sales Manager , ‘Sales Manager’ : ‘Email IDs’,4,0), “-”)
- We need to put these email ids in “To” of the mail while sending this report.
- After putting the mail ids in mail, we have to delete these mail ids from the “Sales Manager” sheet.
- Save the file as “Past Due Funnel” and put the today date.
1.15 Validation checklist
- Check Status Column, it should be "Open" only.
- Fiscal Quarter should be current quarter and all the previous quarters.
- Week should be all the weeks till previous week
- Check the segment, Sales Manager and RSM tagging
- Refresh both pivot tables. Brand should be only DT,DTWS,NB,NBWS and Segment should be only the REL Segment.
1.16 Communication
Altamash Shariff mshariff2@lenovo.com; Dia Paul1 dpaul1@Lenovo.com; Prashant Singh50 psingh50@Lenovo.com; Arunoday Singh45 asingh45@lenovo.com; Lalit Behl lbehl@lenovo.com; Sandesh I Pardeshi spardesh@lenovo.com; Hemang Majmudar hmanjmudar@lenovo.com; Shantanu Garg sgarg@lenovo.com; Rumpa Das4 rdas4@lenovo.com; Mohamed Rikaash mrikaash@lenovo.com - Cc: T Renganathan trenganathan@lenovo.com; Sushruth Vasista svasista@lenovo.com; Vidula Dalvi vdalvi@Lenovo.com; Shivprasath C M47 sm47@Lenovo.com; Saurabh Agrawal ASAURABH@lenovo.com; Vivek Sharma vsharma8@lenovo.com; Stephen Sequeira ssequeira@lenovo.com; Vikas Gupta8 vgupta8@Lenovo.com; Vikas Bhardwaj vbhardwaj@lenovo.com; Dheeraj Mehta dmehta@lenovo.com; Bhanupratap Singh bsingh@lenovo.com; Mukesh Nihalwani mnihalwani@lenovo.com; Vaishali Taresh Patel vaishali@lenovo.com; Sameer Varshney svarshney@lenovo.com; Naveen Kejriwal nkejriwal@lenovo.com; Sundar P mp7@Lenovo.com; Yash Goyal1 ygoyal1@lenovo.com; Irfan Khan s2 is2@Lenovo.com; Bhavya G1 bg1@Lenovo.com; Kishore Kumar V3 kv3@lenovo.com
1.17 Repository Details
"D:\KT Videos\Funnel Past Due.mp4"