OEM REPORT

Process Overview

Field	Details
Process Name	OEM Report
Segment	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	Validated By	Reviewed by	Approved by	Revision Details
V 1.0	Sharanya J	13-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

The OEM report is used to track and validate OEM-related Sales Orders received from the client. It helps in coverage reporting, performance tracking, and ensuring correct taggings for analytics and business decisions.

1.2 Scope

• Geographic Scope: India.
• Business Segment: REL
• Process Frequency: Weekly once on Wednesday before 12AM
• Core Activity: To check the OEM revenue
• Key Distributors Covered: Lenovo

1.3 Business/Segment Overview

• The OEM business segment focuses on managing and monitoring Sales Orders that originate through Original Equipment Manufacturers. This segment plays a critical role in ensuring accurate order classification, seamless data alignment, and timely reporting across multiple internal systems. By validating Sales Orders against OEM mappings, business models, and regional classifications, the segment ensures that each transaction is correctly attributed to its respective category—such as Relationship (REL) or Small & Medium Business (SMB).
• Through systematic consolidation, verification, and enrichment of data, the OEM segment supports downstream processes such as coverage reporting, performance analytics, and stakeholder decision-making. The outcome is a more structured and reliable view of OEM-related business activity for the current quarter and overall operational planning.

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 |Amit Puranik |India OEM Lead |apuranik@lenovo.com| |2 |Kevin Guang1 Shi |AP HQ Workstation Ops |shiguang1@lenovo.com|

1.6 RACI Matrix

RACI Matrix – WyzMindz Solutions Pvt Ltd India

Report / Process	Responsible	Accountable	Consultation	Informed
OEM 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

No	Tool Name	URL	Description
1 Qliksense https://owb.lenovo.com/	It’s a website used to extract data for our usage.

1.9 Legends

• OEM – Original Equipment Manufacturer
• MTM - Machine Type Model (Product Number)
• ST– Sell thru
• SO - Sell Out
• NA – Not Available
• BLOG – Backlog
• REL – Relationship
• MM – Mid Market
• SM – Small Market
• FL – Fresh Load
• OB – Opening Backlog

1.10 Data Dependencies

• AP Data- Coverage Over all
• Week Loading File sent by Amit Puranik

1.11 Schedule of Reporting

• Frequency: Every Wednesday before 12 AM

1.12 Process Flow Chart

1.13 Steps of Procedure

• First, consolidate the SO numbers sent by the client in the OEM List file.
• Open the OEM Order Loading file (sent by client) and the OEM List base file.
• Check whether each SO Number from the OEM Order file exists in the OEM List base file using VLOOKUP.
• If it exists, no need to add those SO numbers to the base file.
• Select only the “#N/A” results in the OEM Order file. Copy the “End Customer Name” and “Sales Order No” columns and paste them into a new workbook.
• Remove duplicates based on “Sales Order No”.
• Open today’s AP Data file.
• Apply VLOOKUP next to the “Sales No” column of the AP Data file using “Sales Order No” from the new workbook.
• Formula: =VLOOKUP(Sales No, Sales Order No:Sales Order No, 1, 0)
• Deselect “#N/A” in the new column.
• Copy the “Sales No”, “End Customer Name” and “Business Model” columns into a new workbook.
• Filter “SMB” in the “Business Model” column and copy the “Sales No”.
• Remove the filter and check if the same Sales No also has “Relationship” as Business Model. If yes, delete the SMB data for that SO number. Repeat this for all SMB-related SO numbers.
• Remove duplicates based on “Sales No”.
• Copy the “Sales No” and “End Customer Name” and paste them under the “Sales Order No” and “Ecn” columns of the OEM order list base file.
• Enter the current quarter in the “Frm” column of the base file.
• Copy the “Business Model” column from the new workbook and paste it under the “Seg” column.
• Apply the same format from the previous data to the newly added data.
• Save the file as OEM Order List – Current Quarter (e.g., OEM Order List – Q4’2324).
• This updated base file will now be used to prepare the OEM Report.

Steps for OEM Report Preparation:

• Open the “Coverage Overall” report received from Coverage QV on the Wyzmindz mail ID.
• Delete the “Summary” sheet.
• In the “Data” sheet, delete the “MTM Type” column.
• Apply the LEFT formula on the “Material” column to extract the first 4 characters for “MTM Type”.
• Formula: =LEFT(Material, 4)
• Paste the values of the “MTM Type” column.
• Insert two new columns after “MTM Type” and name them “OEM” and “Temp”.
• Open the latest “OEM Order List” file.
• Map OEM using MTM Type with the “OEM MT” column from the OEM Order List file.
• Formula: =VLOOKUP(MTM Type, OEM MT:Product, 2, 0)
• Map Temp using SO_No with Sales Order No from the OEM Order List file.
• Formula: =VLOOKUP(SO_No, Sales Order No:Ecn, 2, 0)
• Break the links.
• Select “Nano IOT Fanless” in the “OEM” column and replace all “Nano IOT Fanless” in the “Temp” column with the OEM column values.
• Formula: =OEM column
• Remove the filter.
• Select “#N/A” in the “OEM” column but deselect “#N/A” in the “Temp” column.
• Replace “#N/A” in the “OEM” column with the values from the “Temp” column.
• Formula: =Temp column
• Remove the filter.
• Paste values in the “OEM” column.
• Select “#N/A” in the “OEM” column and delete those rows (except header).
• Delete the “Temp” column.
• Filter the “RELATIONSHIP” values in the “T&R” column.
• Map the following columns using “Install @no” from the Coverage report and “DMU ID” from “Sheet1” of the latest RM Mapping file:
• FTF → =VLOOKUP(Install @no, DMU ID:FTF_Sales_Manager, 9, 0)
• Segment Leader Final → =VLOOKUP(Install @no, DMU ID:FTF_Sales_Manager, 7, 0)
• ISR → =VLOOKUP(Install @no, DMU ID:FTF_Sales_Manager, 8, 0)
• Sub Segment → =VLOOKUP(Install @no, DMU ID:FTF_Sales_Manager, 5, 0)
• Break the links.
• Remove filters.
• Map a “Temp1” column next to “Ship_to_City1” using SO_No with SO_No from the same sheet.
• Formula: =VLOOKUP(SO_No, SO_No:FTF, 35, 0)
• Add a “Check” column to compare “FTF” and “Temp1”.
• Formula: =FTF=Temp1
• Filter “False” in the “Check” column.
• Copy the “OEM” column into a new workbook and save it as OEM Check.
• Remove duplicates from this file.
• In the OEM report file, remove filters.
• Take each OEM one by one from the “OEM Check” file and check the mapping of “FTF”, “Segment Leader Final”, “ISR”, “Segment” and “Sub Segment”.
• If any row has “-” or Blank, replace it with the correct mapped value.
• Repeat this process for all OEMs in the “OEM Check” file.
• After all corrections, ensure that the “Check” column has only TRUE values.
• Delete “Temp1” and “Check” columns.
• Insert a new column next to “Ship_to_City1” and name it “REL/SMB”.
• If “Sub Segment” = REL, update “REL/SMB” as “REL”.
• If “Sub Segment” is Blank, check “T&R” column. If it is “LEMB” or “SOSB”, update “REL/SMB” as “SMB”.
• Insert a new column “FL/OB”.
• If the date is before the current quarter → mark as “OB”.
• If the date is in the current quarter → mark as “FL”.
• Create a pivot table from the “Data” sheet and name the sheet “Pivot”

1.14 Output Overview

• This pivot table shows the total coverage number

• Pivot Selection should be.
  • FL/OB – (All)
  • Bill_Blog – (All)
  • REL/SMB – (All)
  • SO_No – (All)
  • Material – (All)
  • Name of End Customer – (All)
• Again, insert a pivot table based on “Data” sheet in “Pivot” sheet next to the Coverage Pivot table. This pivot for “OB” number

• Pivot Selection should be.
  • FL/OB – Only “OB”
  • Bill_Blog – (All)
  • REL/SMB – (All)
  • SO_No – (All)
  • Material – (All)
  • Name of End Customer – (All)
• Again, insert a pivot table based on “Data” sheet in “Pivot” sheet next to the “OB” Pivot table. This pivot for “FL” number.

• Pivot Selection should be.
  • FL/OB – Only “FL”
  • Bill_Blog – (All)
  • REL/SMB – (All)
  • SO_No – (All)
  • Material – (All)
  • Name of End Customer – (All)
• Save the file as “OEM Data Current Quarter Today Date”.

Ex:- OEM Data Q3'2526 Nov-05

1.15 Validation checklist

• Add the SO# in the OEM List file when we received from client.
• Check all the OEM Name should be tag correctly.
• Check the tagging of FTF, Segment Leader Final, ISR and Sub Segment only for Rel and Tablet
• Check the REL/SMB tagging
• Check the FTF based on Sales No
• Check the FL/OB tagging
• After refreshing the pivot table, most of the time that should be increase from last week number

1.16 Communication

• To: Kevin Guang1 Shi shiguang1@lenovo.com; Yui Suzuki13 ysuzuki13@lenovo.com; Ramesh R rameshr@lenovo.com; India OEM India_OEM@LenovoBeijing.onmicrosoft.com; T Renganathan trenganathan@lenovo.com; Irfan Khan s2 is2@Lenovo.com
• Cc: Sushruth Vasista svasista@lenovo.com; Ramesh R rameshr@lenovo.com; Victhorria Chan vyun2@Lenovo.com; Sun Alex salex1@lenovo.com; Subhransu Sekhar Patra3 spatra3@Lenovo.com; Amit Puranik apuranik@lenovo.com; Navaneeth K nk@lenovo.com

1.17 Repository Details

"D:\KT Videos\OEM DATA.webm"