PAN INDIA WATERFALL

Process Overview

Field Details
Process Name Pan India WaterFall
Segment Consumer
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 Kiran Biradar 21-12-2025 Dayanand U 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 purpose of this SOP is to define a standardized process for preparing, validating, and reporting PAN India Consumer Sell Thru (ST) and Sell Out (SO) data. This includes DSR data cleansing, pivot creation, workflow chart updates, commit integration, and quarter-to-date validation to ensure accuracy and consistency across all regional and segment-level outputs. This procedure supports reliable reporting for North, South, East, and West regions and across LFR, Online, and SLBD segments, enabling timely and accurate business decision-making.

1.2 Scope

  • Geographic Scope: India.
  • Business Segment: Consumer Segment.
  • Process Frequency: Weekly basis.
  • Core Activity: Preparation, consolidation, validation, and reporting of PAN India Consumer Sell Thru (ST) and Sell Out (SO) data using DSR pivots, workflow charts, and commit-updated waterfalls.

1.3 Business/Segment Overview

This process supports the Consumer segment by tracking weekly Sell Thru and Sell Out performance against commitments, enabling accurate predictability and timely business review.

1.4 Responsibility and authority

Role Name
Analyst Kiran Biradar
Process Owner Jospha Jeevitha J
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
1 Lenovo Ops Team Consumer Segment Ops Team

1.6 RACI Matrix

RACI Matrix – WyzMindz Solutions Pvt Ltd India

Report / Process Responsible Accountable Consultation Informed
Data download Kiran Kiran Dayanand Lenovo Ind Pvt Ltd, Wyzmindz – Lenovo Process Team

1.7 Access Required

  • Outlook Mail from Consumer ID

1.8 Overview of Tools

  • Microsoft Excel is required for data and report format and dynamic formulas.
  • Microsoft Macro for copy, pasting values from Waterfall.

1.9 Data Dependencies

  • PAN India Consumer Sell Thru (ST) DSR published by Divya Lakshmi
  • PAN India Consumer Sell Out (SO) DSR published by Divya Lakshmi
  • City Master file for State, Territory, and Region mapping
  • Client-provided ST and SO Waterfall files (including region-wise and segment-wise files)
  • PAN India ST and SO Workflow Chart files
  • WF Commits Update Robo (macro-enabled file)

1.10 Legends

  • DSR: Daily Sales Report used as the base source for ST and SO data
  • ST: Sell Thru
  • SO: Sell Out
  • QTR / QTD: Quarter-to-Date
  • Bizgroup: Business group classification in DSR
  • NB: New Business
  • DT: Direct Trade
  • LFR: Large Format Retail
  • Pivot: Excel Pivot Table used for data aggregation
  • Commit: Planned or committed value provided by the client and updated in waterfalls

1.11 Schedule of Reporting

Frequency: Weekly

1.12 Process Flow Chart

Chart 1: overview

PROCESS FLOW CHART

Pivot overview flow:

Pivot overview flow

1.13 Steps of Procedure

1.13.1 Open Source File

Open PAN India Consumer Sell-Thru DSR, published by Divya Lakshmi.

1.13.2 Bizgroup Filtering and Region Mapping

  1. In the [Bizgroup] column, apply filter and select:
    • Consumer DT
    • Consumer NB
    • Consumer Visuals
    • Visuals
  2. In the [Region] column, filter Blank values.
  3. Using the [CITY] column, populate:
    • [STATE]
    • [Territory]
    • [Region] Mapping should be done using the City Master file or a unique city list derived from DSR.
  4. Clear all filters.

1.13.3 New BP Category Rationalization

  1. Filter [New BP Category] for:
    • One Time Partner
    • RD
    • RFR
    • S&P RD
    • Visual RD
  2. Rationalize all selected values to “RD”.
  3. Remove the filter.

1.13.4 Pivot Sheet Creation

Create five pivot tables and rename the sheets as: - Sell Thru - Visuals ST - Yoga ST - Premium ST - Gaming ST

1.13.5 Sell Thru Pivot Processing

  1. In Sell Thru pivot, add to Rows:
    • Region
    • New BP Category
    • Bizgroup
    • ProdType
    • Week
  2. Filter Bizgroup to:
    • Consumer DT
    • Consumer NB
  3. Add Qty to Values and divide by 1000 using a calculated field.
  4. Open PAN India Consumer Sell Thru WF Chart (QQ’YYYY WW).
  5. Unhide the DSR sheet and paste the pivot output.

Validation: Validate totals with Pan India WOOST sheet under Quarter-to-Date (QTR) for the latest available week.

VALIDATION

1.13.6 Visuals ST Pivot Processing

  1. In Visuals ST pivot, add to Rows:
    • Region
    • Week
  2. In Filters, add:
    • Bizgroup → Consumer Visuals, Visuals
    • SPTYPE → Stand Alone
  3. Add Qty ÷ 1000 as a calculated field.
  4. Open PAN India Consumer Visuals ST WF Chart (QQ’YYYY WW).
  5. Unhide DSR sheet and paste values.

Validation: Match QTR totals with Pan IndiaVSLST sheet.

VALIDATION

1.13.7 Yoga ST Pivot Processing

  1. In Yoga ST pivot, add to Rows:
    • Region
    • Week
  2. Apply filters:
    • Series → contains “Yoga”
    • Bizgroup → Consumer NB
  3. Add Qty ÷ 1000 as a calculated field.
  4. Open PAN India Consumer Yoga ST WF Chart (QQ’YYYY WW).
  5. Unhide DSR sheet and paste values.

Validation:Confirm QTR totals with Pan IndiaYGST sheet.

VALIDATION

1.13.8 Premium ST Pivot Processing

  1. In Premium ST pivot, add to Rows:
    • Region
    • New BP Category
    • Week
  2. Apply filters:
    • Bizgroup → Consumer NB
    • MTM Type → Premium
  3. Add Qty ÷ 1000 as a calculated field.
  4. Open PAN India Consumer Premium ST WF Chart (QQ’YYYY WW).
  5. Unhide DSR sheet and paste values.

Validation:Verify QTR totals with Pan IndiaPMST sheet.

VALIDATION

1.13.9 Gaming ST Pivot Processing

  1. In Gaming ST pivot, add to Rows:
    • Region
    • Week
  2. Apply filters:
    • Bizgroup → Consumer NB
    • Form Factor → Gaming
  3. Add Qty ÷ 1000 as a calculated field.
  4. Open PAN India Consumer Gaming ST WF Chart (QQ’YYYY WW).
  5. Unhide DSR sheet and paste values.

Validation: Match QTR totals with Pan IndiaGMST sheet.

VALIDATION

1.13.10 Steps of Procedure (SO)

1.13.10.1 Open Source File

Open PAN India Consumer Sell-Out (SO) DSR, published by Divya Lakshmi.

1.13.10.2 Bizgroup Filtering and Region Mapping

  1. In the [Bizgroup] column, apply filter and select:
    • Consumer DT
    • Consumer NB
    • Consumer Visuals
    • Visuals
  2. In the [Region] column, filter Blank values.
  3. Using the [CITY] column, populate:
    • [STATE]
    • [Territory]
    • [Region] Mapping should be done using the approved City Master file or a unique city list derived from SO DSR.
  4. Clear all filters.

1.13.10.3 New BP Category Rationalization

  1. Filter [New BP Category] for:
    • One Time Partner
    • RD
    • RFR
    • S&P RD
    • Visual RD
  2. Rationalize all selected values to “RD”.
  3. Remove the filter.

1.13.10.4 Pivot Sheet Creation

Create five pivot tables and rename the sheets as: - Sell Out - Visuals SO - Yoga SO - Premium SO - Gaming SO

1.13.10.5 Sell Out Pivot Processing

  1. In Sell Out pivot, add to Rows:
    • Region
    • New BP Category
    • Bizgroup
    • ProdType
    • Week
  2. Filter Bizgroup to:
    • Consumer DT
    • Consumer NB
  3. Add Qty to Values and divide by 1000 using a calculated field.
  4. Open PAN India Consumer Sell Out WF Chart (QQ’YYYY WW).
  5. Unhide the DSR sheet and paste the pivot output.

Validation:Match totals with Pan India WOOSO sheet under Quarter-to-Date (QTR) for the latest available week.

1.13.10.6 Visuals SO Pivot Processing

  1. In Visuals SO pivot, add to Rows:
    • Region
    • Week
  2. In Filters, add:
    • Bizgroup → Consumer Visuals, Visuals o SPTYPE → Stand Alone
  3. Add Qty ÷ 1000 as a calculated field.
  4. Open PAN India Consumer Visuals SO WF Chart (QQ’YYYY WW).
  5. Unhide DSR sheet and paste values.

Validation:Validate QTR totals with Pan IndiaVSLSO sheet.

1.13.10.7 Yoga SO Pivot Processing

  1. In Yoga SO pivot, add to Rows:
    • Region
    • Week
  2. Apply filters:
    • Series → contains “Yoga”
    • Bizgroup → Consumer NB
  3. Add Qty ÷ 1000 as a calculated field.
  4. Open PAN India Consumer Yoga SO WF Chart (QQ’YYYY WW).
  5. Unhide DSR sheet and paste values.

Validation:Match QTR totals with Pan IndiaYGSO sheet.

1.13.10.8 Premium SO Pivot Processing

  1. In Premium SO pivot, add to Rows:
    • Region
    • New BP Category
    • Week
  2. Apply filters:
    • Bizgroup → Consumer NB
    • MTM Type → Premium
  3. Add Qty ÷ 1000 as a calculated field.
  4. Open PAN India Consumer Premium SO WF Chart (QQ’YYYY WW).
  5. Unhide DSR sheet and paste values.

Validation:Confirm QTR totals with Pan IndiaPMSO sheet.

1.13.10.9 Gaming SO Pivot Processing

  1. In Gaming SO pivot, add to Rows:
    • Region
    • Week
  2. Apply filters:
    • Bizgroup → Consumer NB
    • Form Factor → Gaming
  3. Add Qty ÷ 1000 as a calculated field.
  4. Open PAN India Consumer Gaming SO WF Chart (QQ’YYYY WW).
  5. Unhide DSR sheet and paste values.

Validation:Validate QTR totals with Pan IndiaGMSO sheet.

1.13.10.10 Commits Paste and Waterfall Update

  1. Create a Source Folder and place all files received from the client, including:
    • Etail SO WF
    • SO WF Charts
    • NE / SW PAN India Consumer Gaming SO WF
    • NE / SW PAN India Consumer Yoga SO WF
    • NE / SW PAN India Consumer Visuals SO WF
    • NE / SW PAN India Consumer Gaming ST WF
    • NE / SW PAN India Consumer Yoga ST WF
    • NE / SW PAN India Consumer Visuals ST WF
    • Etail ST WF
    • ST WF Charts
    • NE / SW PAN India Consumer Premium SO WF
    • NE / SW PAN India Consumer Premium ST WF
    • NE / SW PAN India Consumer Sell Out WF
    • SW PAN India Consumer Sell Out WF
    • NE / SW PAN India Consumer Sell Thru WF
    • SLBD Consumer WF
  2. In the Target Folder, ensure all 10 waterfall files are available for update.

1.13.10.11 Commit Update Using Macro

  1. Open WF Commits Update Robo.xlsm.
  2. Navigate to the “Path” sheet.
  3. Paste:
    • G2 cell value into A3 (Source Folder path)
    • G3 cell value into B3 (Target Folder path)
  4. In cell I3, select the required Week.
  5. Click the Validation button.
  6. After successful validation, click the Run button.

PAN INDIA CONSUMER COMMIT UPDATE ROBOT

1.13.10.12 Post-Run Confirmation

• Confirm the macro has pasted commit values (highlighted in blue) • Ensure commits are updated in all waterfall files in the Target Folder

PAN INDIA CONSUMER COMMIT UPDATE ROBOT

1.13.10.13 Commit Validation

  1. Open each updated waterfall file.
  2. Validate Quarter-to-Date (QTR) values against the corresponding client-received waterfall.
  3. Check region-wise QTR for:
    • North
    • South
    • East
    • West
  4. Ensure QTR values match for all business segments:
    • LFR
    • Online
    • SLBD
  5. Proceed only if all regional and segment-level QTR values are aligned.

1.14 Output Overview:

1.14.1 DSR-Based Outputs

The following outputs are generated from the PAN India Consumer DSR after data cleansing, mapping, and pivot preparation:

  • Sell Thru (ST) Pivot Output: Region, BP Category, Bizgroup, Product Type, and Week-wise Sell Thru data
  • Sell Out (SO) Pivot Output: Region, BP Category, Bizgroup, Product Type, and Week-wise Sell Out data
  • Visuals ST and SO Outputs: Region and Week-wise Sell Thru and Sell Out data for Visuals and Consumer Visuals
  • Yoga ST and SO Outputs: Region and Week-wise Sell Thru and Sell Out data for Yoga series under Consumer NB
  • Premium ST and SO Outputs: Region, BP Category, and Week-wise Sell Thru and Sell Out data for Premium products
  • Gaming ST and SO Outputs: Region and Week-wise Sell Thru and Sell Out data for Gaming form factor products

1.14.2 Workflow Chart Outputs

  • The pivot outputs are pasted into the respective PAN India Workflow (WF) Chart files, including:
    • PAN India Consumer Sell Thru WF Chart
    • PAN India Consumer Sell Out WF Chart
    • PAN India Consumer Visuals ST WF Chart
    • PAN India Consumer Visuals SO WF Chart
    • PAN India Consumer Yoga ST WF Chart
    • PAN India Consumer Yoga SO WF Chart
    • PAN India Consumer Premium ST WF Chart
    • PAN India Consumer Premium SO WF Chart
    • PAN India Consumer Gaming ST WF Chart
    • PAN India Consumer Gaming SO WF Chart Each file contains updated weekly and quarter-to-date values.

1.14.3 Commit-Updated Waterfall Outputs

After running the WF Commits Update Robo, the following outputs are produced:

  • Updated 10 Target Waterfall files with commit values pasted automatically
  • Commit values highlighted in blue color for easy identification
  • Region-wise commit updates for:
    • North
    • South
    • East
    • West
  • Other commit coverage:
    • LFR
    • Online
    • SLBD

1.14.4 Validation-Ready Outputs

All generated outputs are validation-ready and support:

  • Quarter-to-Date (QTR) reconciliation
  • Region-wise and segment-wise comparison with client-provided waterfalls
  • Final reporting and stakeholder review

1.15 Validation checklist

1.15.1 Source Data Validation

  • Confirm PAN India Consumer Sell-Thru DSR is the latest version published by Divya Lakshmi
  • Ensure data is complete for the latest available week
  • Verify no duplicate records exist after filtering

1.15.2 Bizgroup and Mapping Validation

  • Check [Bizgroup] contains only:
    • Consumer DT
    • Consumer NB
    • Consumer Visuals
    • Visuals
  • Validate [STATE], [Territory], and [Region] are populated for all records
  • Confirm city-to-region mapping is done using the approved City Master / DSR reference
  • Ensure no blanks remain in [Region] column

1.15.3 New BP Category Validation

  • Verify all applicable values are rationalized to “RD”
  • Confirm no legacy values remain:
    • One Time Partner
    • RFR
    • S&P RD
    • Visual RD

1.15.4 Pivot Structure Validation

  • Confirm all five pivot sheets exist:
    • Sell Thru
    • Visuals ST
    • Yoga ST
    • Premium ST
    • Gaming ST
  • Validate pivot data source range includes full dataset
  • Ensure pivots are refreshed after any data change

1.15.5 Calculated Field Validation

  • Confirm Qty ÷ 1000 calculated field is used in all pivots
  • Validate units are consistent across all outputs
  • Check for rounding or formula errors

1.15.6 Sell Thru Validation

  • Bizgroup filter includes only Consumer DT and Consumer NB
  • Pivot totals match Pan India WOOST sheet
  • Quarter-to-Date (QTR) values match up to the latest week

1.15.7 Visuals ST Validation

  • Bizgroup filter includes Consumer Visuals and Visuals
  • SPTYPE filter includes only Stand Alone
  • Pivot totals match Pan IndiaVSLST QTR values

1.15.8 Yoga ST Validation

  • Series filter contains “Yoga” only
  • Bizgroup filter is Consumer NB
  • QTR totals match Pan IndiaYGST sheet

1.15.9 Premium ST Validation

  • Bizgroup filter is Consumer NB
  • MTM Type filter is Premium
  • QTR totals match Pan IndiaPMST sheet

1.15.10 Gaming ST Validation

  • Bizgroup filter is Consumer NB
  • Form Factor filter is Gaming
  • QTR totals match Pan IndiaGMST sheet

1.16 Communication

Below mentioned image is body of the mail

PAN INDIA CONSUMER COMMIT UPDATE ROBOT

Mail ID’s

To: rajesubr@lenovo.com, dbhadra@Lenovo.com, jleslie@lenovo.com, jbasha@Lenovo.com, ngola@lenovo.com, gr3@lenovo.com, dnair@lenovo.com, abhatnagar@lenovo.com, akumarj@lenovo.com, vmalho@lenovo.com, pdas2@lenovo.com, ssingh15@lenovo.com, sdeshpande@lenovo.com, ssreekumar@lenovo.com, nmittal@lenovo.com, sjain9@lenovo.com, kchudasama@lenovo.com, smir1@lenovo.com, jmahajan@lenovo.com, knimje@lenovo.com, du@lenovo.com, svasista@lenovo.com, akumar33@lenovo.com, nnarkhede@lenovo.com, aramesh@lenovo.com, pkrishnamurt@lenovo.com, nk@lenovo.com, rajesubr@lenovo.com, sdeshpande@lenovo.com, vsharma7@lenovo.com, sverma6@Lenovo.com, aagarwal9@lenovo.com, sgautam2@Lenovo.com, pkrishnamurt@lenovo.com, nk@lenovo.com, smishra12@Lenovo.com, rdeshkar@lenovo.com, bm5@Lenovo.com, ss100@Lenovo.com, mgupta6@Lenovo.com, bchandraseka@Lenovo.com, kbiradar@Lenovo.com, pn6@Lenovo.com, shamadol@lenovo.com, ssreekumar@lenovo.com, mjain1@lenovo.com, fulfillment.c@wyzmindz.com

1.17 Repository Details

Field Details
Name Pan India WaterFall[All] - QQ'YYYY Wk[WW]
Type .xlxm
Path / URL [Local System]
Access Daya/Kiran
Owner / Team Daya
Last Updated 21-12-2025