tech stack rank

Tech Stack Ranking
CS Data
•	Scheduler – Reports – Operations – Customer Survey Result
•	Copy/paste into CS Data tab
•	Convert the number from the “How likely is that you would recommend our serv” column text to number
•	Copy/Paste formulas down
•	Add Month in Column A
NPS Tech Stack Tab
•	Pivot off of CS Data tab
o	Report Filter:  Month
	Filter on Current Month Only
o	Row Labels:  Tech
o	Values
	Count of Case ID
	Sum of Detractor
	Sum of Neutral
	Sum of Promoter
	Sum of Response
o	Copy/Paste % caluclations
•	Hardcode the pivot table
•	First sort table by Response %
o	Do not include “Blank” techs or Grand Total
o	If their response rate is less than 70% highlight in red
•	The sort the pivot not highlight in red by NPS Score
•	Rank the non-red part of the pivot table by NPS Score (see rank formula used in previous month)
•	Then Rank the red part of the pivot by NPS Score (see rank formula used in previous month)
•	Above the Rank – calculate the Mid Score (See formula used in previous month)
•	Currently I have to manually bring in the employee ID’s but our programmer for scheduler is adding employee id to this report so we will no longer have to do that.  He said it should be completed sometime the week of June 7th.
Tech Weekly Hours
•	Scheduler – Reports – Operations – Tech weekly hours
•	Timeframe is for 2 week periods
•	Copy/Paste into Tech Weekly Hours Tab
•	Convert everything to numbers
•	Add two columns
o	Payperiod – which is the title from the report that gives the dates
o	Month – add current month
On Time %
•	Pivot off the Tech Weekly Hours Data
o	Report Filter:  Month –filter on current month
o	Row Labels:  Tech Name & UHSid
o	Values:   Sum of Cdays, Sum of OnTime
•	Hardcode pivot table
•	Add % on time calculation – see previous month
•	Sort % on time – largest to smallest
•	Rank – see calculation from previous month
•	Above the rank – calculate the mid score (see formula used in previous month)

Productivity Tab
•	Reporting Central – Surgical Services Reports – Quality Indicators
o	Parameters – current month
o	Region & Division – Empty
o	District & Technologist – Null
•	Use this as the base for summary
•	Copy/Paste into Productivity tab
•	Convert employee ID to number
•	Sort by % of applied time (largest to smallest)
•	Rank – see formula in previous month

District Growth
•	I will save a file called “Matt Pivot” to your folders that you will use to calculate this
•	Pivot off of the “Matt Pivot” Data
o	Row labels:  UHSSSDISTRICT
o	Column Labels:  Month – filter on Current Month and PY Month (ex.  Apr 2015 & Apr 2014)
o	Values:  Count of CaseID
•	Remove Grand Total
•	Hardcode Pivot table
•	Add Growth calculation- see previous months calculation
•	Sort by Growth % - largest to smallest
•	Rank – see previous months calculation
•	Abot the rank calculate the mid score – see previous months calculation


Stack Rank
•	Copy headers from previous month and paste in between last month and YTD data
•	Copy/Paste Technician and Employee ID from the “Productivity tab”
•	Vlookup  the district based on the employee ID from the “Look Up” tab
o	If N/A:
	I will save a file called “Qualified Earnings” for you to view
	Try vlookuping by employing id to bring in district
	In this file you will be able to see the “Job Profile” or title.  If it says ESWL Specialist add this to the lookup list and input “Removed” into the district because we only want to look at Laser Technicians
	If you still cannot find the employee contact me and I will look in workday
	Once you have found the correct district for these N/A’s add  them to the lookup list
o	Once all Districts are populated, in the column to it’s right use the formula =left(select district immediately to it’s left, 3)
o	This will give you just the number to the district – we’ll use this for the district growth vlookup
o	Hardcode these and convert to number
•	Filter on the “District” to only see “REMOVED” items
o	Delete this lines
•	Sort by District to remove the blank spaces
•	NPS
o	Vlookup based on employee ID into NPS Tech Stack to bring in Ranking
o	Add “IFERROR” to formula to say if there is an error return the “Mid Score”
	See formula  in April
•	Productivity
o	Vlookup based on employee ID into Productivity tab to bring in Ranking

•	District Growth
o	Vlookup based on the district number into the District Growth tab to bring in Ranking
o	Add “IFERROR” to formula to say if there is an error return the “Mid Score”
	See formula  in April

•	Total Score
o	Sum the Rankings of NPS, On Time, Productivity, District Growth

•	Sort by Total Score – smallest to largest
•	Color code base on Ranking
o	Top 25% are in Green
o	Middle 50% are Yellow
o	Bottom 25% are in Red

•	YTD Stack Rank
o	Add any new technicians from current month that are not on the YTD Summary
o	Insert column for new month between previous month and Avg
o	Vlookup based on employee ID to bring in the current month ranking
o	Adjust average to include YTD data
o	Sort by average rank – smallest to largest
•	Color code base on Ranking
o	Top 25% are in Green
o	Middle 50% are Yellow
o	Bottom 25% are in Red