Output: After this class you’ll have massaged the data so only contracts that are relevant to you are left.
At this point the only contracts that should be in our pivot table are the kinds that you are interested in, but there are probably hundreds of purchasing offices running down your pivot table. So let’s start to get rid of the low performers, (breaking in a customer takes real time and effort so we want to get the most out of that time and effort.)
Step 1: To do this, click on the down arrow next to “Row Labels” and then select “More Sort Options.” When the box comes up, click “Descending,” then in the drop down select”Count of unique_transaction_id,” then “Ok.”
This will cause the offices to organize themselves from largest to smallest.
Step 2: Select the data in the “Grand Total” column of the pivot table, but not the bottom cell that has the actual grand total.
Step 3: Go to the “Conditional Formatting” tab and select”Color Scales” then the first option.
This will cause the cells to shade themselves based on the size of the number in the respective cell. The darker the green, the more contracts there are. The darker the red, the fewer contracts there are.
We are obviously more interested in the greens than the reds.
Step 4: At this point, we want to remove offices that aren’t doing a lot in our area.
I recommend going to where the color shifts from pure yellow to orange , then selecting everything below that. Then right click, go to “Filter” and select “Hide Selected Items.”
Step 5: Go to your pivot table and in the search bar search for”Agency,” and add “ContractingOfficeAgencyID” to the top of the rows field which will cause all the offices to re-organize by their sub-agency (or service for DOD).
Step 6: The sub-agencies should already be arranged by size but if they aren’t, right click on any of the cells that have a sub-agency listed.
Right click, select “Sort” and click “More Sort Options.”Then in the resulting box select “Descending,” and”Count of unique_transaction_id.” Click Ok.
Step 7: Now that we have all the sub-agencies in size order, remove the bottom-half to bottom-third of the sub-agencies.
Be sure to leave yourself at least 10 sub-agencies which hopefully have at least 20 contracts each.
Step 8: Now let’s repeat the process at the full agency level.
Search for “Maj_Agency_Cat” and add it to the top of the rows window.
This will cause the sub-agencies to group together by agency. As before, if they aren’t already in order by size, right click an agency and sort it.
Step 9: Remove the bottom rung of low performing agencies.
Step 10: Another group of agencies to remove are the ones where there has been a big falloff in the number of contracts left over the last few years.
Even if an agency has a lot of contracts total, if they haven’t put much out in the last three years it’s a huge red flag. I recommend you remove them by right clicking on that agency and then filtering it out.
Output: At this point, I hope you have around five to ten agencies each with a smattering of high performing sub-agencies, and a couple high performing offices inside of them.