QTD BILLING REPORT

Process Overview

Field Details
Process Name QTD Billing
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 19-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 preparation, validation, and reporting of QTD Billing data by correctly mapping segments, segment leaders, RSMs, and FCRAD values.
  • This process enables reliable billing insights, consistent reporting across GA and KA, and alignment with AP data for management review and decision-making.

1.2 Scope

  • Geographic Scope: India.
  • Business Segment: REL
  • Process Frequency: Every Monday
  • Core Activity: To Review Billing Insights
  • Key Distributors Covered: Lenovo

1.3 Business/Segment Overview:

  • The QTD Billing report offers a structured view of billing performance aligned to the organization’s business hierarchy, specifically the Segment Leader and Sales Manager structure. It enables management to monitor billing trends, assess ownership and accountability, and support informed decision-making through consistent and accurately tagged billing data across the sales organization.

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

1.6 RACI Matrix:

Report / Process Responsible Accountable Consultation Informed
QTD Billing 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:

1.9 Legends:

  • ST– Sell thru
  • SO - Sell Out
  • NA – Not Available
  • FCrad – Future Crad
  • QTD -

1.10 Data Dependencies:

  • AP DataReport

1.11 Schedule of Reporting

  • Frequency: Every Monday.

1.12 Process Flow Chart:

Process Flow Chart

1.13 Steps of Procedure:

  • Open the QTD Billing report for the required period and ensure the latest raw data is available before starting any changes.
  • Navigate to the Short_Seg column and thoroughly review the column to identify any blank values.
  • If blank Short_Seg values are found:
    • Refer to the Order Load report.
    • Identify the corresponding records and map the following fields accurately:
    • Segment
    • Segment_Leader
    • Segment_Leader_Final
    • RSM
    • This mapping activity must be performed only for EA OL and EA ACQ segments.
  • This step is applicable only when new accounts are identified in the data.
  • If there are no new accounts, but blanks still exist in Short_Seg:
    • Review the Segment_Leader column.
    • Map the Short_Seg values based on the available Segment_Leader information.
  • After completing Short_Seg validation, move to the Brand column.
    • Select the CA option from the filter.
      • Deselect the value 0.
      • Ensure all remaining numeric values are set to 0 to standardize brand tagging.
  • Review the Segment_Leader_Final column carefully.
    • If any blanks are observed, equate the entire Segment_Leader_Final column with the Segment_Leader column to ensure consistency.
  • Proceed to the RSM column and apply a filter for TBH entries.
  • For records where RSM = TBH:
    • Refer to the Sales Manager column.
    • Update the RSM values based on the following logic:
    • Sales Manager Pranay Khera → Update RSM to VIDULA
    • Sales Manager Sandeep Arora → Update RSM to KARAN BISHT
    • Sales Manager Rakesh Sivaraman → Update RSM to SHASHI PRAKASH SINGH
    • Sales Manager Shivprasath → Review the Sales Manager mapping and tag the RSM accordingly.
  • Apply a filter on Short_Seg for the following segments:
    • EA OL
    • EA ACQ
    • SA
  • Within the filtered data, check the RSM column for any blank values.
  • For blank RSM entries under EA OL, EA ACQ, and SA:
    • Refer to the Sales Manager column.
    • Assign the appropriate RSM based on the mapped Sales Manager.
  • If the Sales Manager value is TBH:
    • Update the Rof SM field as TBH-2 to clearly indicate the temporary status.
  • Apply the following specific RSM mappings to ensure standardization:
    • Bhuvan Gera → RSM should be VIKAS BHARADWAJ
    • Sambit Kanungo and Supriya Karia → RSM should be SAMBIT KANUNGO
    • Open accounts → RSM must be tagged as DHEERAJ MEHTA
  • Perform a validation check to ensure correctness of RSM tagging:
    • For EA OL, EA ACQ, and SA segments, the RSM column must not contain any blanks.
    • For all other segments, the RSM value must strictly be “-” (hyphen).
  • Create a new helper column in the QTD Billing report.
    • Concatenate Sales_Order, Material, and Billed_Quantity into a single column.
  • Open the Order Load report and create a similar helper column.
    • Concatenate SO_No, MTM, and Quantity into one column.
  • In the QTD Billing report, perform an XLOOKUP:
    • Lookup value → QTD Billing concatenated column
    • Lookup array → Order Load concatenated column
    • Return array → FCRAD column from Order Load
  • Verify that the XLOOKUP is successful for all records and ensure no #NA errors are present.
  • Once FCRAD mapping is completed:
    • Break all formulas.
    • Copy and paste the data as values with formatting to lock the results.
  • Navigate to the Summary sheet in the QTD Billing file.
  • Change the pivot table data source to include the updated data range.
  • Refresh the pivot table to reflect the latest changes.

Steps of Procedure

  • Apply the following pivot filters:
    • ROL_Week → ALL
    • FCRAD → ALL
    • ROL_Month → ALL
    • BILL/BLOG → BILL
    • Segment_Leader_Final → ALL
  • In the Brand filter, select only:
    • Desktop
    • Mobile Workstation
    • Workstation
    • SMO
  • Re-arrange the segment order in the pivot table as per the standard sequence:
    • GTC
    • GAB
    • SA
    • EA OL
    • ICO
    • PUB
    • Private Edu
    • SLBD
    • EA ACQ
    • GCC ACQ
  • Right-click on the pivot table and open PivotTable Options.
    • Set “For empty cells show” to 0.

Steps of Procedure

  • To validate the Grand Total, open the AP Data file.
  • In AP Data, apply the following filters:
    • CONRELSMB → REL
    • Brand → Desktop, Mobile Workstation, Notebook, SMO, ThinkStation
    • DATATYPE → Ships
  • Check the sum of Volume in AP Data.
    • Ensure this value matches exactly with the Grand Total displayed in the QTD Billing pivot table.
  • Create the KA version of the report:
    • Deselect Vivek Sharma and Stephen Sequeira (GA accounts).
    • Save the file separately as QTD Billing – KA.

Steps of Procedure

  • Create the GA version of the report:
    • Go to Short_Seg and deselect GTC and GAB.
    • Deselect the remaining non-GA data as required.
    • Refresh the pivot table.
    • Save the file separately as QTD Billing – GA.

Steps of Procedure

• While sending the report via email: - Copy the main summary pivot table and paste it directly into the email body. - Below the main pivot, paste another pivot table filtered for: - Brand = Workstation and Mobile Workstation - Include Sales Manager with subtotals for each manager. - Send separate emails for GA and KA, ensuring the correct version and corresponding pivot details are shared with stakeholders.

Steps of Procedure

Steps of Procedure

Steps of Procedure

Steps of Procedure

1.14 Output Overview

The output of the QTD Billing process is a validated and standardized billing report that presents accurate billing figures aligned to the Segment Leader and Sales Manager hierarchy. The report is delivered as separate GA and KA views, supported by refreshed summary pivots and reconciled totals, enabling clear performance tracking and management review.

  • In AP Data, apply the following filters:
    • CONRELSMB → REL
    • Brand → Desktop, Mobile Workstation, Notebook, SMO, ThinkStation
    • DATATYPE → Ships
  • Check the sum of Volume in AP Data.
    • Ensure this value matches exactly with the Grand Total displayed in the QTD Billing pivot table.

Output

Output

1.15 Validation checklist

  • Check the Short_Seg, Segment_Leader , Segment_Leader_Final, Sales_Manager, ISR. These all columns should be updated as per End_Cust_Code
  • Check the RSM column, it should be updated as per Sales_Manager. RSM is only for EA OL, EA Acq and SA segments.
  • Apart from NOTEBOOK, DESKTOP, WORKSTATION, MOBILE WS & SMO in Brand , CA should be zero.
  • Check the Grand Total of Pivot table with AP Data volume. Both should be equal.

1.16 Communication

For KA Report:

For GA Report:

Purnima Chhabra1 pchhabra1@lenovo.com; Shruti Muralidhar smuralidhar@lenovo.com; Dia Paul1 dpaul1@lenovo.com; Md Altamash Shariff mshariff2@lenovo.com; Toshika Agnihotri tagnihotri@lenovo.com; Suchismita Sengupta ssengupta2@lenovo.com; Sanchari Sen2 ssen2@lenovo.com; Sophia George1 sgeorge1@lenovo.com; Jacqueline Samson2 jsamson2@lenovo.com; Ayesha Nakshath S5 as5@lenovo.com; Vikas Gupta5 vgupta5@lenovo.com; Santhosh Shenoy sshenoy@lenovo.com; Hemang Majmudar hmanjmudar@lenovo.com; Indu Panikker pindu@lenovo.com; Shantanu Garg sgarg@lenovo.com; Rumpa Das4 rdas4@lenovo.com; Abhilash V Rao4 arao4@lenovo.com; Harshika Bhatt hbhatt@lenovo.com; Madhura Vasudevan mmurugan@lenovo.com; Archana Sarfare archsarf@lenovo.com; Stephen Sequeira ssequeira@lenovo.com; Vivek Sharma vsharma8@lenovo.com; Mohd Shahab Farooqui mfarooqui@lenovo.com; Kapil Lamba klamba@lenovo.com; Ganesh Kumar paramashivam gparamashiva@lenovo.com; Bhagirathi Praveen bpraveen@lenovo.com; Alok Kumar63 akumar63@lenovo.com; 'Mallikarjun kollari' mkollari@lenovo.com; Shailaja Puzakail spuzakail@lenovo.com; Hemant Tandon htandon@lenovo.com; Neetu Kulkarni2 nkulkarni2@lenovo.com; Sharika Dinesh sdinesh@lenovo.com; Shrihari R sr17@lenovo.com; Sudhir Choudhary schoudhary1@lenovo.com; Saqib Jameel sajameel@lenovo.com; Kapil Bhatia kbhatia@lenovo.com; Ravikumar K Kanakarajan rkanakar@lenovo.com; Amit Kaushik1 akaushik1@lenovo.com; Ravikumar K Kanakarajan rkanakar@lenovo.com; Shrihari R sr17@lenovo.com; Sachin Kinagi skinagi@lenovo.com; Sudhir Choudhary schoudhary1@lenovo.com; Saqib Jameel sajameel@lenovo.com; Alok Kumar63 akumar63@lenovo.com; Rohit Midha rmidha@lenovo.com; Kapil Bhatia kbhatia@lenovo.com; Naveen Kejriwal nkejriwal@lenovo.com; Sameer Varshney svarshney@lenovo.com; Bhavya G1 bg1@lenovo.com - Cc : Ravikumar K Kanakarajan rkanakar@lenovo.com; Shrihari R sr17@lenovo.com; Sachin Kinagi skinagi@lenovo.com; Sudhir Choudhary schoudhary1@lenovo.com; Saqib Jameel sajameel@lenovo.com; Alok Kumar63 akumar63@lenovo.com; Kapil Bhatia kbhatia@lenovo.com; Naveen Kejriwal nkejriwal@lenovo.com; Sameer Varshney svarshney@lenovo.com; Goonj Tuteja gtuteja@lenovo.com; 'Mallikarjun kollari' mkollari@lenovo.com; Monica Vasnani mvasnani@lenovo.com; Sushruth Vasista svasista@lenovo.com; Dayanand U du@lenovo.com; Navaneeth K nk@lenovo.com; T Renganathan trenganathan@lenovo.com

1.17 Repository Details:

  • "D:\KT Videos\QTD BILLING.mp4"