SERVICE COVERAGE

Process Overview

Fields Description
Process Name Services Coverage 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 Raksha S 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

The purpose of this report is to update, validate, and prepare the weekly Services Coverage and Services Upsell Dashboard by consolidating data from multiple source files - AP Services Scorecard, Commercial Backlog, REL QTD Billing, QTD Bookings, RM Mapping, and FTF-Region mappings.

1.2 Scope

  • Geographic Scope: India.
  • Business Segment: REL
  • Process Frequency: Tuesday/ Wednesday
  • Core Activity: To manage weekly services reporting operations, including data preparation, revenue updates, PR% calculation, coverage updates, and performance validations across segments
  • Key Distributors Covered: Lenovo

1.3 Business/Segment Overview

  • Pending.

1.4 Responsibility and authority

  • Analyst : Raksha S
  • 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 Pending. Pending. Pending.

1.6 RACI Matrix:

RACI Matrix – WyzMindz Solutions Pvt Ltd India

Report / Process Responsible Accountable Consultation Informed
Services Coverage 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 corporate website (Supporting Documents will be stored there in the future) Website: www.example.wyzminz

1.8 Overview of Tools

|No |Tool Name| URL |Description| |1| Lenovo Sharepoint website |Update the SharePoint link||

1.9 Legends

Field Meaning
AP

1.10 Data Dependencies

  • AP Services Scorecard FY2526Q3W07
  • QTD Bookings_FY2526Q3W07
  • Q32526 REL QTD Billing Report 26112025
  • RM Mapping Q3'2526 – Nov 18
  • Base File- Services Q3'2526
  • Commercial Backlog as on 26th Nov'2025_Final

1.11 Schedule of Reporting

  • Pending :

1.12 Process Flow Chart

Process Flow Chart

1.13 Steps of Procedure

  • Open “Services Coverage Base file” and go to “Calculation” sheet.
  • Link of “AP Services Scorecard” & “Commercial Backlog as on 22nd May'2023_Final file” is provide by client.
  • Download both files from that link.
  • Open “AP Services Scorecard” file and go to “REL Segment View”.
  • Copy the “INDIA TOTAL” value of “SVS REV” column which is in “QTD SERVICES BOOKINGS (NET OF GTN)” section.
  • Paste below the “Serv Rev” cell in “Calculation” sheet of “Base File”
  • Again, Go to “REL Segment View” sheet of “AP Services Scorecard” file.
  • Copy the %age of “INDIA TOTAL” of “ACTUALS” which is in “QTD SVS PEN RATE” section.
  • Paste this percentage value below to the “PR%” cell in “Calculation” sheet of “Base File”.
  • Go to “RM Mapping” sheet and delete the previous data.
  • Copy and paste the data from latest “RM Mapping” file according to header.
  • Go to “Coverage” sheet of “Base File” and Unhide the last two columns.
  • Open the “Commercial Backlog” file. Go to “Data” sheet and unhide the last column.
  • Copy the data from “Data” sheet and paste in “Coverage” sheet of “Base File” one by one as per header.
  • Those columns which are not present in “Commercial Backlog” file, keep that column as blank in “Base File”.
  • Don’t paste data in formulated column, we have to copy and paste that formula and drag till last.
  • In “Commercial Backlog” file, “Service Revenue $” column is formulated, and we have to do value paste the data of this column in “Base File”.
  • Open “REL QTD Billing” Report of last Monday and insert a column as “FL/OB”.
  • Select current quarter date in “Sales_Order_Creation” column and put “FL” in “FL/OB” column.
  • Then, select previous quarter and put “OB” in “FL/OB” column.
  • Open “Base File” and go to “Overall Dashboard” sheet.
  • For updating “Hardware revenue FL $K” column, we have to insert a pivot table in “REL QTD Billing” Report based on data sheet.

Steps

  • Pivot selection should be
    • Bill/Blog – (All)
    • FL/OB – FL
    • Short_Seg – (All)
    • Sales_Manager – (All)
    • RSM – (All)
    • Calculated Field – ‘Net_Revenue’/1000
  • Update the value from pivot table to “Hardware revenue FL $K” column according to the “Sales Manager” and “RSM” in “Overall Dashboard” sheet of “Base File” – Image -1
  • For updating “Hardware revenue $K Only Billing” column, change the selection of pivot table which we added in “ Rel QTD Billing” Report. – Image 2

Steps Steps

  • Pivot selection should be:
    • Bill/Blog – only “Bill”
    • FL/OB – (All)
    • Short_Seg – (All)
    • Sales_Manager – (All
    • RSM – (All) -Calculated Field – ‘Net_Revenue’/1000
  • Update the value from pivot table to “Hardware revenue $K Only Billing” column according to the “Sales Manager” and “RSM” in “Overall Dashboard” sheet of “Base File”.
  • If we will get “-”, “OPEN” or any new name in pivot table, we must put that value in “NA” or add in total of that “Sales Manager” of respective “segment”.
  • Put the value of “OTHERS” segment in “Billables” section.
  • Grand Total of “Hardware revenue FL $K” column should be match with Grand total of QTD Billing report pivot table.
  • Grand Total of “Hardware revenue $K only Billing” column shouldn’t be match with Grand total of QTD Billing report pivot table because it is taking value from “Calculation” sheet.
  • Open previous week “Service Upsell Dashboard” file and go to “Qtd Cvg PR%” sheet.
  • Insert a new pivot table in “REL QTD Billing” report for updating “Qtd Cvg PR%” sheet of “Service Upsell Dashboard” file.

Steps

  • Calculated field: -
    • PR% CA = CA/1000
    • Field2 = ‘Net_Revenue’/1000000
  • Change the pivot table into “Classic Pivot Table”.
  • Remove the sub-total and do “Repeat All Item Labels”.
  • Go to “Qtd Cvg PR%” sheet of “Service Upsell Dashboard” file and delete the previous data from 4th row.
  • Copy and paste the data from pivot table to “Qtd Cvg PR%” sheet according to header.
  • “PR%” column contain formula. Copy the from previous data and paste in today data and drag the formula till end.
  • Copy the format from previous data and paste into the today data.
  • Delete the previous data rows except header.
  • For updating “Service Revenue $M” column we must insert a pivot table in today “Services Coverage” report after preparing the report.

Steps

  • No filter needed in any column.
  • Calculated Field (Field3) = ‘Service Revenue $’/1000000
  • Avoid the repetition of “Install @no.”, we have to follow these steps: -
    • Right click on Pivot table.
    • Click on “Field Setting”.
    • Click on” Layout & Print” tab.
    • Select “Show item labels in tabular form” option.
    • Click on check box of “Repeat item labels” option.
    • Click on “OK” button.
  • Apply VLOOKUP in “Service Revenue $M” column of “Service Upsell Dashboard” report based on “Install @no.” from pivot table of “Services Coverage” report.
  • Break the link in “Qtd Cvg PR%” sheet of “Service Upsell Dashboard” file.
  • Change “#N/A” of “Service Revenue $M” column with “-”.
  • Apply filter and go to “CA in K” column.
  • Apply Number Filter as “Greater Than or Equal To – 1”.
  • Go to “PR%” column and filter this column as per “RED” colour.
  • Sort the “PR%” column from Largest to Smallest and Again sort by colour.
  • Open “QTD Bookings_FY2324Q1W09” file. Go to “QTD Bookings” sheet.
  • Apply filter and select only “India” in “Region” column.
  • Select “Enterprise” & “Global” in “Segment for Pivot” column.
  • Select only “5” in “Product Hierarchy” column.
  • Copy and paste only the resultant data in new excel workbook.
  • Save it as “QTD Bookings”.
  • Go to “headers to Map” sheet of “Base File”.
  • Copy the header which are present in “AP Data” column and search in the header of “QTD Bookings” file and replace that header with header which are present adjacent to that header in “Cvg” column “header to Map” column.
  • Do one by one for all the header and change the header which required.
  • Change “Billed” to “Bill” and “Unbilled” to “Blog” in “Bill/Blog” column.
  • Save this file.
  • Open the “Commercial Backlog” report and go to “Data” sheet.
  • Copy the data and paste below the “QTD Booking” data in “Coverage” sheet of “Base File” as per header.
  • Don’t paste the formulated column data. We have to copy the formula from previous data and paste in today data and drag till end of data.
  • In “Serv Rev in INR” column, the formula should be: -
    • For QTD Booking data – “=coverage!AA2”
    • For Commercial Backlog Data – “BJ2*BL2”
  • Replace all the date which are present in “Billing Date” & “GTN Invoice Date” column of “Commercial Backlog” data with “Backlog”.
  • Open “FTF-Region” file and go to “ICO” sheet.
  • Select “ICO” in “New Segment” column in “Coverage” sheet of “Base File”.
  • Apply VLOOKUP in “Services RSM” column based on “New FTF” from “ICO” sheet of “FTF-Region” file.
  • Don’t remove the filter.
  • Break the link.
  • Select “Sudheer Ugargol” in “New FTF” column.
  • Again, Apply VLOOKUP in “Services RSM” column based on “Install @no.” from “ICO” sheet of “FTF-Region” file.
  • Remove the filter.
  • Deselect only “ICO” in “New Segment” column.
  • Apply VLOOKUP in “Services RSM” column based on “New FTF” from “Sheet1” sheet of “FTF-Region” file.
  • Break the link.
  • Replace “Zero” & “#N/A” with “-” in “Services RSM” column.
  • Replace “Blank” with “Zero” in “Local Currency Value” & “Services Revenue in $” column.
  • “CA” is equal to “Billed Quantity” in “QTD Booking” data.
  • All “Remarks” & “Repeat” columns should be “Blank”.
  • Open previous week “Services Coverage” report. Go to “Coverage” sheet and delete the previous data except 2 or 3 rows.
  • Copy the whole data from “Coverage” sheet of “Base File” and paste below the previous data in “Services Coverage” report.
  • Copy and paste the format from previous data to today data.
  • Delete the previous data rows except header.
  • Break the link.
  • Go to “Pivot” sheet and refresh the pivot table based on “Coverage” sheet.
  • Grand Total of pivot table should be increasing every week.
  • Save the file as “Services Coverage Q1'2324 Week 09”.

1.14 Output Overview

  • Services Upsell Dashboard – “Overall Dashboard” sheet.

Steps

  • Services Upsell Dashboard – “T2 and T3 Attainment” sheet.

Steps

  • Services Upsell Dashboard – “Top 5 T2 T3 performers” sheet.

Steps

  • Services Upsell Dashboard – “Qtd Cvg PR%” sheet.

Steps

  • Services Coverage report – Pivot sheet.

Steps

  • Services Coverage report – Coverage sheet.

Steps.

1.15 Validation checklist

  • Go to “Overall Dashboard” sheet and then Open Today “Services Coverage” report.
  • In “Pivot” sheet, select “FL” in “FL/OB” which is in filter of pivot table.
  • Open “Base File” and go to “Overall Dashboard” sheet.
  • Validate “Services revenue FL $K” column from pivot table of “Services coverage” report according to “Sales Manager” and “RSM” one by one.
  • If we will get difference, then value paste the value from pivot table to dashboard.
  • We must put the value of “OTHERS” segment of pivot table into “Billables” section of dashboard.
  • Go to “Overall Dashboard” sheet of “Base File” and Validate “Tower 1”, “Tower 2” & “Tower 3” of “Billing ($K)” & “Backlog ($K)” one by one.

Steps

  • For validating that, go to pivot sheet of “Services Coverage” report and the selection should be in “WW Summary Tower": -
  • For Tower 1 – “Support Services”
  • For Tower 2 – “Managed Services & Daas”
  • For Tower 3 – “Project Oriented Services”
  • Validate “Bill” for “Bill” and “Backlog” for “Backlog”.
  • Validate “Bill + Backlog” column of “Overall Dashboard” sheet with total of pivot table of “Services coverage” report. Only check the formula in dashboard, don’t do value paste the value from pivot table of “services coverage” report.

Steps

  • Go to pivot table of “Services Coverage” report and select “Non Tied” only in “Tied/Non Tied” filter and Validate with “Non Tied Bill” of “Noin Tied Attainment” in “Overall Dashboard” sheet of “Base File”.
  • Also, Validate “Non Tied Coverage” column of “Overall dashboard” sheet from Grand Total of pivot table .
  • Go to “T2 and T3 Attainment” sheet of “Base File” and Validate “Managed Services” , “Software & Cloud Services” & “Smart Support Solution” column of “T2 Bill” & “T2 Backlog” from “Pivot table of “Services Coverage” report.

Steps

  • The selection should be in “Service Tower” filter column :-
    • For Managed Services – only “Managed Services”
    • For Software & Cloud Services – only “Software & Cloud Services”
    • For Smart Support Solution – only “Managed Services”
  • Go to “Overall Dashboard” sheet of “Base File” and validate “T3 Bill” & “T3 Backlog” from pivot table of “Services Coverage” report.

Steps

  • Select “Project Oriented Services” in “Service Tower” filter in pivot table of “Services Coverage” report and validate from “Bill” to “T3 Bill” and “Backlog” to “T3 Backlog” column. Validate “T3 Bill+Backlog” column with “Grand Total” of pivot table.

Note

While validating the values if any new name came under any segment, then we have to put that under “NA” of that particular segment. If “NA” is not, then there then added that value under the total of that segment.

  • Open “Services Upsell Dashboard” report and go to “Overall Dashboard” sheet. Put the cursor in “A1” cell . Press “Ctrl + ‘-’ ” and Select “Shift cells up”.
  • Go to “Overall Dashboard” sheet of “Base File”. Copy the dashboard and paste in “Overall Dashboard” sheet of “Services Upsell Dashboard” file.

Steps

  • Change the Accounting format of “Grand Total(W/o Elcot)” of “Asp Target $M(On billing)” column as “$ English (United States)”.
  • Go to “T2 and T3 Attainment” sheet of “Services Upsell Dashboard” file. Put the cursor in “A1” cell. Press “Ctrl + ‘-’ ” and Select “Shift cells up”.
  • Go to “T2 and T3 Attainment” sheet of “Base File”. Copy the dashboard and paste in “T2 and T3 Attainment” sheet of “Services Upsell Dashboard” file.

Steps

  • Break the link.
  • Change the week number as last week in all the dashboard of “Base File” and “Services Upsell Dashboard” file.
  • In “Overall Dashboard” sheet of “Base File”, “Grand Total(W/o Elcot)” of “Bill + Backlog” column should be equal to the grand total of pivot table which is in “Services Coverage” report.
  • Save all the files.
  • We must send only “Services Coverage” file and “Services Upsell Dashboard” file.

Steps

1.16 Communication

Mode of Communication would be Email

1.17 Repository Details

NA.