ORDER LOAD SUMMARY AUTOMATED

Process Overview

Fields Description
Process Name Order Load Report & Summary
Segment REL
Process Family: Lenovo India
Process User(s): Wyzmindz solutions pvt ltd
Process Purpose: Its Helps Client to Summaries and overview of SI order which have been loaded.

Version History

Version Tag Prepared By Date of Update/Revision Validated By Reviewed by Approved by Revision Details
v1.0 Ritesh 22-01-2024 Navneeth Sushruth Vasista Vishwanath Mosale SOP Document Creation
v1.1 Sharanya 10-12-2025 Navneeth Sushruth Vasista Vishwanath Mosale Updated Process flow chart and RACI Matrix

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 Order Load Report is designed to provide a clear and comprehensive view of the weekly order loading status. It enables tracking of week-wise loading, monitoring the conversion of orders from un-executable to executable, and validating the overall progress of SI orders loaded into the system

1.2 Scope

  • Geographic Scope: India.
  • Business Segment: REL
  • Process Frequency: Daily
  • Core Activity: A detailed summary of weekly order loading activities.
  • Key Distributors Covered: Lenovo

1.3 Business/Segment Overview

  • The Order Load Report plays a crucial role in the order processing workflow. It provides: A detailed summary of weekly order loading activities.
  • Visibility into orders that are initially marked as unexecutable and their subsequent conversion to executable after validation
  • Enhanced decision-making support by highlighting order Loading and RSM Commits.

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 T Renganathan Sales Operations Head - Commercial Trenganathan@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 Orderload Report & Summary Sharanya J Sharanya J Navaneeth K Lenovo Ind Pvt Ltd, Wyzmindz – Lenovo Process Team

1.7 Access Required

  • Outlook - For receiving reports and communications.
  • Share point Access
  • AP Data Access
  • Access to MS Office Suite

1.8 Overview of Tools

Sl No Tool Name URL Description
1 Qliksense https://owb.lenovo.com/ It’s a website used to extract data for our usage.
2 CDMS (Channel Data Management System) https://cdms.lenovo.com/DCSDM/ Lenovo’s tool used to upload and collect data.

1.9 Legends

  • MTM - Machine Type Model (Product Number)
  • ST– Sell thru
  • OB – Opening Backlog
  • FL – Fresh Load
  • PUB – Public
  • EA ACQ – EA Acquisition
  • GCC ACQ – GCC Acquisition

1.10 Data Dependencies

  • AP Data Report of the same day.
  • Data Mart application - To Upload RM Mapping

1.11 Schedule of Reporting

  • Daily

1.12 Process Flow Chart

Process Flow Chart

1.13 Steps of Procedure

  • Open the Order Load generated report which we will get from coverage Qv on mail ID.
  • Hide “Summary” and “PC Comparison” sheet.
  • Go to “OB_FL” column and select “FL”.
  • Then, go to “Order_Creation_Date” column and Select Previous quarter date.
  • Go to “Order_Week” column and change all the resultant data of this column as “OB”. Also, change as “OB” in “OB_FL” column.
  • Remove the filter.
  • Then, select “ FL” and “OB” one by one in “OB_FL” column and check the “Order_Creation_Date” column.
  • OB (Opening Backlog) – Order which are not billed in previous quarter and moved in this quarter.
  • FL (Fresh Load) – Order which are loaded in current quarter.
  • Go to “Segment” column and check if there is a blank if yes, refer MDM dump and tag respectively
  • In Segments if there is no blank go to short segment and select blank tag short segment based on segment column
  • Go to segment_leader_Final column select blank and tag with respect to Segment.
    • =XLOOKUP (“SOMTMITEM Quantity”, “SOMTMITEM Quantity”, “Fcrad”)
  • Select “No” in “Fcrad Check” column and check the “Fcrad” column. If any “Yes” will be there, then select “Yes” in “Fcrad” column and compare “Exe/Un-Exe” column with “Conversion” column.
  • Select “Yes” in “Fcrad Check” column and check the “Fcrad” column. If any “No” will be there, then select “No” in “Fcrad” column. Compare “Exe/Un-Exe” column with “Conversion” column and check any conversion happened or not. (Conversion means, Unexecutable in Conversion column and Executable or Blank in “Exe/Un-Exe” column or Vice-versa)
  • If conversion has happened, then we have to select only conversion cases and copy the SO# for those particular cases.
  • Remove the filter.
  • Select “Unexecutable” in “Conversion” column and “Executable” & “Blank” in “Exe/Un-Exe” column. Then, check that SO#. If that will be not there then add the sum of the “CA” column in below yellow table which is available in “Pivot” sheet for that segment.
  • Next name another column next to Fcard check as DT/AIO—open PCSD file in brand select DSK copy material and product category column to the new sheet.
  • In DT/AIO column take lookup based on Material from OL and Material from the new sheet and return value would be product category If this category belongs to AIO of any series just make it AIO and remove the filter, deselect this AIO and #NA and Name the rest other category as DT
  • Go to brand_old select Visual and Visual Standalone and Write Visuals in AIO/DT column.
  • Change the data source of first Pivot table based on “Data” sheet and include “Exe/Un-Exe” column in Filter. This pivot table showing Overall Coverage number.

Steps

  • Pivot selection should be:
    • Exe/Un-Exe – “Executable” & “(Blank)”
    • FCrad – No
    • RSM – (All)
    • V_Brand – “DESKTOP, MOBILE WS, NOTEBOOK, THINK STATION”
    • OB_FL – (ALL)
    • Segment_Leader_Final – (All)
  • We have to arrange the name of Segment_Leader_Final as per there Segment which are mentioned in below table in Pivot sheet of report. Sample Pivot table with sequence of segment leader final mention below.

Steps

  • Change the data source of second pivot table and include “Exe/Un-Exe” column in Filter. This pivot table showing the current week loading.

Steps

  • Pivot selection should be
    • Exe/Un-Exe – “Executable” & “(Blank)”
    • FCrad – No
    • RSM – (All)
    • V_Brand – “DESKTOP, MOBILE WS, NOTEBOOK, THINK STATION”
    • OB_FL – (ALL)
    • Order_Week – current week
    • Segment_Leader_Final – (All)
  • We have to arrange the name of Segment_Leader_Final as per there Segment which are mentioned in below table in Pivot sheet of report. Sample Pivot table with sequence of segment leader final mention below.

Steps

  • Insert a pivot table next to the third pivot table. This Pivot will show the Unexecutable quantity segment leader final wise.

Steps

  • Pivot selection should be
    • Fcrad – No
    • OB_FL – (All)
    • Exe/Un-Exe – Unexecutable
    • RSM - (All)
    • V_Brand – Desktop, Mobile WS, Notebook & Think Station
    • Segment_Leader_Final – (All)
  • You will see the pivot table below.

Steps

  • Insert a pivot table next to the above pivot table. This Pivot will show the “Conversion” quantity segment leader final wise.

Steps

  • Pivot selection should be
    • Conversion – Unexecutable
    • FCrad – No
    • OB_FL – (All)
    • RSM – (All)
    • Exe/Un-Exe – Executable & Blank
    • V_Brand – Desktop, Mobile WS, Notebook & Think Station
    • Segment_Leader_Final – (All)
  • You will see the pivot table below.

Steps

  • Copy and paste the two below tables from the “Pivot” sheet of previous day report.

Steps

  • In segment column of above table,
  • GA = GTC + GAB
  • KAB = Sum of all the segment excluding GA(GAB+GTC)
  • INKA = KAB - SLBD
  • In SMO Excluded Last Week column, we must update the Total Coverage number of Last Monday from the Total coverage pivot table of last Monday order load report (Red Highlighted in below pivot) as per Segment.

Steps

  • We need to update “OB”(Opening Backlog) number once in initial days of every quarter. It should be constant for that quarter.

  • We need to update “FL”(Fresh Load) number as per segment by putting below formula.

    • =Today Coverage number – OB number

  • Now, we need to update “Total” column of “PC” table as per segment by putting below formula.

    • =Sum of OB number & FL number of PC table.

  • Now, we need to update the “Week Commit”.

  • For updating week commit open the “Master_Waterfall Final” file.
  • Go to “D365” sheet and copy the whole data in different excel workbook.
  • Insert a pivot based on this sheet.

Steps

  • Pivot selection should be.
    • Week_Ref – Current week
    • Probability – “50%” & “80%”
    • Segment – (All)
  • You will see the pivot table.
  • Update the Week commit from above pivot table as per respective segment.

Steps

  • We need to update the “Week Actual” column of “PC” table by putting below formula as per respective segments.
    • =Total Coverage from PC Table – Last Week coverage from yellow table

Steps

  • Now, we need to update the “RTG” column of “PC” table by putting below formula as per respective segments.
    • =Week Commit – Week Actual

Steps

  • For updating “Conversion” column, we have to refer last pivot table of same sheet which is for Conversion number as per respective segments.

Steps

  • For updating “Unexecutable” column, we have to refer third pivot table of same sheet which is for “Unexecutable” number as per respective segments.

Steps

Steps

  • Click on “Copy Link” option

Steps

1.14 Output Overview

  • For the Output we separate it as KAB and GAB
  • Save two separate files as Q32526 Order Load Summary 09122025 – KA and Q32526 Order Load Summary 09122025 – GA
    • For KA the file will have the following output

Steps

  • For GA the file will have the following output

Steps

  • KA will have Visuals Sheet

Steps

1.15 Validation checklist

  • Take NOTEBOOK, DESKTOP, THINK STATION, MOBILE WS in "V_Brand" Column and Check the "CA" column.
  • "CA" column should not column contain zero value.
  • Apart from NOTEBOOK, DESKTOP, THINK STATION, MOBILE WS, SMO & TABLET in V_Brand, CA should be zero.
  • Check the "Segment" column, If we are getting Blank then we have to tag based on DMU from MDM Dump or AP Data or on Client Confirmation
  • Check the "Sales_Manager" colummn, if we are getting blank then tag based on DMU from MDM Dump or AP Data or on Client Confirmation
  • Check "Segment_Leader" & "Segment_Leader_Final" column, If we will get blank then tag this as per Segment from REL Mapping file. Both the columns should be same.
  • Check the "ISR" column, If we are getting Blank then we have to tag based on DMU from MDM Dump or AP Data or on Client Confirmation.
  • Check "Short_Seg" column, it should be same as "Segment" column.
  • Check the "RSM". RSM Tagging based on Sales Manager. RSM is only for EA Acq, EA OL and SA Segments.
  • Check the "FCRAD" column from AP Data, if found any wrong FCRAD.
  • Check the order conversion based on "FCRAD Check". If any conversion found add manually in Conversion column of Summary Table available in "Pivot" sheet.
  • In Pivot sheet, Check the Total of below Summary table and "Grand Total" of first pivot table, both the number should be match segment wise.
  • Week loading should be match with the last pivot table in "Pivot" sheet.
  • Conversion of Summary table should me match with conversion pivot table (4th Pivot table).
  • Unexecutable of summary table should be match with Unexecutable pivot table (3rd Pivot Table)
  • Check Last Week Coverage number with Last week Order load report (Monday report)).
  • Today Coverage numbers should match with Total of summary Table as well as Grand Total of first pivot table

1.16 Communication

1.17 Repository Details

• "D:\KT Videos\RM Mapping.mp4" – Will be updated in SharePoint