LOST DEALS

Process Overview

Fields	Description
Process Name	Loss Deals Report
Segment	Relationship [REL]
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	Raksha S	03-12-2025	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 extract and clean D365 opportunity data, apply required mappings, identify loss deals, and generate a structured Loss Deals report that highlights lost opportunities, key reasons, and segment/brand-wise analysis for business review.

1.2 Scope

• Geographic Scope: India.
• Business Segment: REL
• Process Frequency: Sunday
• Core Activity: To Summarize the Table data from India D365 sales opportunity data
• Key Distributors Covered: Lenovo

1.3 Business/Segment Overview

• Pending.

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	Pending.	Pending.	Pending.
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
Loss-Deals Report	Raksha S	Raksha S	Navaneeth K	Lenovo Ind Pvt Ltd, Wyzmindz – Lenovo Process Team

1.7 Access Required

• Outlook & Webmail Access
• Access to Ms Office Suite
• Access to Coperations@lenovo.com Mails
• Access to Co-Data/D365 Qlikcloud

1.8 Overview of Tools

• NA

1.10 Data Dependencies

• Funnel dump (Downloaded from D365/Co-Data)
• Funnel Tagging File
• Services Offerings File
• Mapping File

1.9 Legends

• Pending

1.11 Schedule of Reporting

• Frequency: Pending

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.

• You will see the below table.

• Filter Business Group field with IDG.

• Filter Region field with INDIA.

• Filter Fiscal Quarter field with Previous 6 Weeks, Current Quarter and next 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 and save as Funnel Dump.
• Open the Funnel Base File “Base File Q3'2526 (Funnel) v1”.
• Copy the Data till Transaction Indicator in Dump and value paste in Data sheet of base file.
• Next the Transaction Indicator column as the column as 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 file.
• Map Services Category next to the Segment column using MTM column with PN column from Services Offerings file and named this column as Services Category.
  • =IFERROR(VLOOKUP(MTM , ‘PN’ : ‘Services Offering Category’,3,0), “-”)
• Map Services Offering Category next to the Services Category column using MTM column with PN column from Services Offerings file and named this column as Services Offering Category.
  • =IFERROR(VLOOKUP(MTM , ‘PN’ : ‘Services Offering Category’,4,0), “-”)
• Map Tower next to the Services Offering Category column using Services Offering Category column with Services Offering Category column from Services Offerings file and named this column as Tower.
  • =IFERROR(VLOOKUP(Services Offering Category , ‘Services Offering Category’ : ‘Offering Bucket’,5,0), “-”)
• Select Device as a Service in Solution Area column and Update DAAS in next to the Tower column. Named this column as DAAS.
• Remove the filter.
• Map Offering Bucket next to the DASS column using Services Offering Category column with Services Offering Category column from Services Offerings file and named this column as Offering Bucket.
  • =IFERROR(VLOOKUP(Services Offering Category , ‘Services Offering Category’ : ‘Offering Bucket’,7,0), “-”)
• Select all the REL segment in Segment column and select “-” in Updated Opp Owner column.
• Check Field 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 ACQ”, “EA OL” & “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 ACQ”, “EA OL” , “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.

• Save this file as India Pipeline and put the today’s date.
• Now, Open India Pipeline today’s file.
• Deselect all the REL segment in Segment column and delete the whole resultant data.(Ctrl + “-”)
• Remove the filter.
• Deselect “DT” , “DTWS”, “NB”, “NBWS”, “PCG S&P”, “Service”, “SMO” & “Visual” in Brand column and delete the whole resultant data. (Ctrl + “-”)
• Remove the filter.
• We need to keep 0% probability cases. So, we must deselect 0% in “Probability” column and delete the whole resultant data. (Ctrl + “-”).
• Now go to “Fiscal Quarter” column and deselect Current Quarter. Delete the whole resultant data. (Ctrl + “-”).
• Remove the filter.
• Insert a column “Loss Deal Status” next to the “Offering Bucket “column.
• In this column, Map Loss Deal Status next to the “Offering Bucket “column using Status Reason column with “Loss Deals Reasons Tagging file.
  • =IFERROR(VLOOKUP(Status Reason , ‘Loss Deals Reasons Need to be considered’ : ‘Loss Deals Reasons Need to be considered’,2,0),”NO”)
• Break the link.
• Insert a pivot table based on Data sheet and named this sheet as “Seg Summary”.

• Select below options,
• Pivot Selection should be.
  • Brand – DT, DTWS, NB, NBWS, SMO
  • Loss Deal status – Yes
  • Segment – (All)
  • Opportunity Owner – (All)
  • Opportunity ID D365 – (All)
  • Status Reason – (All)
  • Competitor Name – (All)
  • Confirmation Comments – (All)
  • Customer Name EN – (All)
• You will see the pivot table below.

• Insert a pivot table based on Data sheet and named this sheet as “Pivot”.

• Pivot Selection should be.

  • Brand – DT, DTWS, NB, NBWS, SMO
  • Loss Deal status – Yes
  • Segment – (All)
  • Account Name – (All)
  • Updated Opp Owner – (All)
  • Status Reason – (All)

• Copy the two tables from “Summary” sheet of last delivered report and name this sheet as “Summary” or move whole “Summary” sheet from last shared to todays report.

• Now, insert pivot table for updating the above tables based on “Data” sheet.
• First, we have to insert pivot table for updating the DT, NB, WS & SMO column of “PC - Quantity in K Units” table.

• Pivot Selection should be.

  • Brand – “DT”, “NB”, “DTWS”, “NBWS” & “SMO”
  • Loss Deal status - Yes
  • Segment – (All)

• Next to the pivot table, Divide the values of all the resultant values of pivot by 1000 but for update the “WS” column of the table, first we have to summarize the values of “DTWS” & “NBWS” then divide by 1000.

• Update the values as per segment & brand in highlighted columns of below mentioned table from above calculated values.

• Check the Total value of “CA” from table to calculated Total value of pivot table.
• For update the “# Accounts” column of “PC - Quantity in K Units” table, insert a pivot table and select the “Add this data to the Data Model” option.

• Pivot Selection should be.

  • Segment – (All)
  • Brand – (All)
  • Loss Deal status – Yes

• Update the “# Accounts” column of “PC - Quantity in K Units” table as per segments.

• For updating “PC - Large Cases” table, again insert a pivot table.

• Pivot Selection should be.
  • Brand – “DT”, “DTWS”, “NB”, “NBWS” & “SMO”
  • Loss Deal status – Yes
  • Account Name – (All)
  • Segment – (All)
  • Updated Opp Owner – (All)
• Do “Repeat all Item Labels” and Select “Do Not Show Subtotals” option.
• You will see the pivot table below.

• Copy the data of pivot table from “Account Name” to “Total” column and value paste next to the Pivot table.
• Sort the “Total” column by “Largest to Smallest”.
• Copy the top 4 cases and value paste in “PC - Large Cases” table of “Summary” sheet.
• Divide the value of “Qty in K(Units)” by 1000.
• You will the Table below.

• Insert a pivot table based on Data sheet and named this sheet as “Analysis Pivot”. While inserting Pivot Table select “Add this data to the Data Model”.

• Pivot Selection should be.
  • Brand – “DT”, “DTWS”, “NB”& “NBWS”
  • Fiscal Quarter – (All)
  • Loss Deal status – (All)
  • Status Reason – (All)
  • Segment – (All)
• Format the pivot as per previous week pivot table.
• Right click on Segment, select “Subtotal Segment”.
• Delete all the pivot sheets and keep only “Summary”,” Pivot”,” Analysis Pivot”, “Seg Summary” and “Data” sheet.
• Save this file as “Loss Deals” and put current quarter and today’s Date. Ex:- Loss Deals Q3'2525 Nov-10

1.14 Output Overview

• Attach the report to mail.
• Copy and paste below mentioned tables in mail body while sending the report.

1.15 Validation checklist

• Segment should be only the REL sub-segment.
• Probability should only be 0%.
• Brand should be DT, DTWS, NB, NBWS, PCG S&P, Service, SMO and Visual.
• Fiscal Quarter should be only current quarter.
• Check the Loss Deal Status column tagged correctly based on Status Reason column.
• Check all the Segment and RSM tagged correctly.
• The selection in Pivot table of "Pivot" & "Seg Summary" sheet are same, the selection should be "Yes" in Loss Deal status filter and IN Brand it should be DT, DTWS, NB, NBWS, SMO.
• Check the numbers in "PC - Quantity in K Units" table of "Summary" for all the brands Segment wise. Check the numbers of accounts in "# Accounts" column of same table.
• Check the top 4 accounts in "PC - Large Cases" table of "Summary" sheet.
• Select only Base machine(DT, DTWS, NB, NBWS) in brand of "Analysis Pivot" sheet pivot table. Check the format of the pivot table as per last week file.

1.16 Communication

Alok Kumar63 akumar63@lenovo.com; Amit Kaushik1 akaushik1@lenovo.com; Archana Sarfare archsarf@lenovo.com; Bhanupratap Singh bsingh@lenovo.com; Dev Thomas dthomas@lenovo.com; Donnel Varkey dvarkey@lenovo.com; Florence Margaret fmargaret@lenovo.com; Hiren P. Shimpi hshimpi@lenovo.com; Kumaran P kumaranp@lenovo.com; Mallikarjun kollari mkollari@lenovo.com; Neha Sabharwal1 nsabharwal1@lenovo.com; Pranab Pramanik ppramanik@lenovo.com; Rakesh Sivaraman rsivaraman@lenovo.com; Ranjit Gerard Sequeira rsequeira@lenovo.com; Ravikumar K Kanakarajan rkanakar@lenovo.com; Rini Jennifer G4 rg4@lenovo.com; Rohit Midha rmidha@lenovo.com; Sameer Varshney svarshney@lenovo.com; Vikash Kumar kvikash@lenovo.com; Saqib Jameel sajameel@lenovo.com; Sharika Dinesh sdinesh@lenovo.com; Shrihari R sr17@lenovo.com; Shruti Sharma28 ssharma28@lenovo.com; Stephen Sequeira ssequeira@lenovo.com; Vaishali Taresh Patel vaishali@lenovo.com; Venkat Ramani R vr@lenovo.com; Vidula Dalvi vdalvi@lenovo.com; Vikas Gupta8 vgupta8@lenovo.com; Vivek Sharma vsharma8@lenovo.com; 'fulfillment.c@wyzmindz.com'; Sushruth Vasista svasista@lenovo.com; Shivprasath C M47 sm47@lenovo.com; Pranay Khera pkhera@lenovo.com; Sandeep Arora2 sarora2@lenovo.com; Shashi Prakash Singh ssingh23@lenovo.com; Nilesh Prajapati nprajapati@lenovo.com; Mudit Shinghal mshinghal@lenovo.com; Sundar P mp7@lenovo.com; Mukesh Nihalwani mnihalwani@lenovo.com; Karan Bisht kbisht@lenovo.com; Naveen Kejriwal nkejriwal@lenovo.com; Khushaboo Choudhary1 kchoudhary1@lenovo.com; Monica Vasnani mvasnani@lenovo.com; Irfan Khan s2 is2@lenovo.com; Yash Goyal1 ygoyal1@lenovo.com; vivekananda Gupta pgupta12@lenovo.com; Kishore Kumar V3 kv3@lenovo.com; Bhavya G1 bg1@lenovo.com Sushruth Vasista svasista@lenovo.com; T Renganathan trenganathan@lenovo.com

1.17 Repository Details

• Attach required master file/Base file/Mapping files.