Export data from supplier scorecard

The data used to calculate supplier scorecards can be exported in both a high level report and a more in depth level report.  

High-level export

At the top of the supplier scorecard there are two options for exporting: pdf or cvs.  

The pdf export is the same look and feel as what is displayed on the screen.  

The csv (excel) export will contain the high level metrics displayed on the screen and the additional metrics displayed under "More details".  

Both exports will follow the filters applied (selected report range and evaluation date).  

Detailed-data export

At the bottom of the supplier scorecard page you can create a report of all the scorecard data.  This report will generate and be available to download from the Reports tab.  

To download navigate to the Reports site under Reporting. 

Detailed data report pivot table

After downloading the report you can recreate the scorecard calculations by using a pivot table.  

You can follow the step by step pivot table instructions below or make a copy of the google doc (link below) and add your data to the first tab.  

https://docs.google.com/spreadsheets/d/1BvqZ33diifzJzAG2-QkVM0PDVOf6pbhnhSqAKKaBDSE/copy 

Pivot table instructions

1. Select the entire dataset 

2. Create a pivot table 

3. When you have selected the pivot table option in excel, click on the option to create a pivot table on a new spreadsheet. (NOTE: Please make sure that you have selected all of your data from the export otherwise the calculations will not match the supplier scorecard).

4. Your pivot table you will need to reference the metric calculation field in order to determine the steps on how to recreate the calculations from the scorecard. 

5. Adjust pivot table view by going to the design tab > subtotals button > “remove subtotals” >then go to report layout button > set report layout to “show tabular form”

Recreating the calculations on the pivot table 

Average change per PO

Due date change

Price variance

Closed PO spend

Closed PO lines

Supplier response rate

Supplier response days

Average change per PO Calculations

Average change per PO Calculation - Sum of numerator divided by the distinct count of metric ID

1. 1. Drag numerator value and metric id to the field values and filters
   2. Change metric id calculation to count instead of sum
   3. Filter 0 values from the numerator value filterCalculate average change per po by dividing sum of the numerator value by the count of metric id
   4. The excel formula to do that in this example is =C4/D4

Due date change calculation

Due Date Change Calculation - Sum of numerator

1. 1. Drag numerator value into the value section of the pivot and remove any filters used from previous calculations 

Price Variance Calculations

Price Variance - Sum of numerator divided by the sum of denominator

1. 1. Filter the metric name to select the price variance metric
   2. Drag and drop relevant field values, in this case you want the sum of numerator value and denominator value in the values section of pivot
   3. Apply calculation to determine the price variance, and adjust the data type value to percentage. 

Closed PO spend calculations

Closed PO spend - Sum of numerator

1. 1. Filter metric name in the pivot table to spend/lines
   2. Drag and drop the relevant field names into the pivot values section
   3. Closed PO Spend is the sum of the numerator value

Closed PO lines calculations

Closed PO Lines - Sum of denominator

1. 1. Filter metric name in the pivot table to spend/lines
   2. Drag and drop the relevant field names into the pivot values section
   3. Closed PO Spend is the sum of the denominator value

Supplier response rate calculations

Supplier response rate - Average of numerator 

1. 1. Filter the metric name to “Supplier Response Rate”
   2. Drag and drop the numerator value and change the numerator value field calculation to average
   3. Change the data type of the numerator value to percentage by selecting the cell with the numerator value and clicking on the percentage sign

Supplier response days calculations

Supplier Response Days - Sum of numerator divided by the sum of denominator

1. 1. Filter the metric name to select the Supplier Response Hrs metric
   2. Drag and drop relevant field values, in this case you want the sum of numerator value and denominator value in the values section of pivot
   3. Divide the numerator by the denominator to get the supplier response hours
   4. Take the value and divide it by 24 to get the Supplier Response Days.