Key Performance Indicator Example
This is an example of a key performance indicator (KPI) that measures revenue goals for project managers. One project manager, Eric Nelson, has a different goal than all other project managers.
The following tables identify:
-
Entries made on the Key Performance Indicators grid on the Key Performance Indicators tab in Analysis Cubes Configuration
-
Entries made on the Specific Goals grid on the Key Performance Indicators tab in Analysis Cubes Configuration
-
Measures and dimensions selected from the Excel PivotTable Field List for a custom report
-
The resulting Excel report
The following table identifies the entries made in the Key Performance Indicators grid on the Key Performance Indicators tab in Analysis Cubes Configuration. In this grid you enter one goal for all project managers.
| Key Performance Indicators Grid Entries | ||
|---|---|---|
| Field | Entry | Notes |
| Measure | Revenue | This is the Revenue measure from the Revenue - Project Measure group. This KPI is tracking revenue. |
| Measure Type | Numeric/Amount | Revenue is tracked as a monetary amount. |
| Dimension | Project Project Manager | Revenue is being measured for project managers. |
| Timeframe | Fiscal Period | The time frame for measuring revenue is a fiscal period. (We will select a specific fiscal period in Excel.) |
| Default Goal | 200,000 | The default revenue goal for all project managers is $200,000 per year. |
| Status Method | Percent of Goal | The Default Goal Low Value and the Default Goal High Value amounts will be entered as a percentage of the goal. |
| Default Goal Low Value | 80 |
A project manager's goal is considered to be met if revenue is at least $160,000. $160,000 is .80 * $200,000. The goal is not met if revenue is less than $160,000. |
| Default Goal High Value | 120 |
A project manager's goal is considered to be met if revenue is $240,000 or less. $240,000 is 1.20 * $200,000. The goal is exceeded if revenue is more than $240,000. A project manager's goal is considered met if revenue is between $160,000 and $240,000. |
| Trend Comparison | Year to Year |
Trend information is calculated by comparing years. We will select the corresponding Fiscal Year dimension from the More fields folder in the Fiscal Periods dimension group in the Excel PivotTable Field List, to match the Year to Year setting in this Trend Comparison field. |
| Trend Low Percent | 3.00 | If the change between one year (the time frame we selected) and the next year is a decrease, and the decrease is more than 3.00%, then the trend is considered to be moving downward. If the decrease is less than the percentage entered in this field, the trend is neutral. |
| Trend High Percent | 4.00 | If the change between one year (the time frame we selected) to the next year is an increase, and the increase is greater than 4.00%, then the trend is considered to be moving upward. If the increase is less than the percentage entered in this field, the trend is neutral. |
| Label | Revenue - Project Project Manager | This is the name of the KPI. The default name is the KPI's measure and revenue expressed as measure - dimension. |
Specific Goals Grid Entries
The following table identifies the entries made in the Specific Goals grid on the Key Performance Indicators Tab of Analysis Cubes Configuration Form in Analysis Cubes Configuration. In this grid you enter Eric Nelson's goal, which is different than the other project managers' goal.
| Specific Goals Grid Entries | ||
|---|---|---|
| Field | Entry | Notes |
| Dimension Record | Eric Nelson | Eric's revenue goal is different than the $200,000 entered for all other project managers, so a separate goal is entered for him in the Specific Goals grid. |
| Year | 2011 | The goal for Eric applies for the year 2011 only. |
| Annual Goal | 150,000 | Eric's revenue goal is $150,000. |
| Goal Low Value | 80 |
Eric's goal is considered to be met if his revenue is at least $120,000. $120,000 is .80 * $150,000. The goal is not met if revenue is less than $120,000. |
| Goal High Value | 120 |
Eric's goal is considered to be met if his revenue is $180,000 or less. $180,000 is 1.20 * $150,000. The goal is exceeded if revenue is more than $180,000. Eric's goal is considered met if his revenue is between $120,000 and $180,000. |
| Columns 1-12 | Click the Distribute Annual icon on the Specific Goals grid toolbar. | After you click the Distribute Annual icon, the annual goal amount of 150,000 is divided by 12 and 12,500 is entered in each column 1 – 12. |
Selections from the PivotTable Field List In Microsoft Excel
From the Excel PivotTable Field List, select the following dimensions and measures:
| PivotTable Field List Selections in Excel | |
|---|---|
| Dimensions and KPI | Notes |
| Dimension: Project Project Manager | This is from the Projects folder in the Projects dimension group in the Excel PivotTable Field List. |
| Dimension: Fiscal Year | This is from the More fields folder in the Fiscal Period dimension group in the Excel PivotTable Field List. |
| KPI: Revenue - Project Project Manager |
This is from the KPIs group in the Excel PivotTable Field List. This is the KPI that we created in Vision Analysis Cubes Configuration. Select the following check boxes beneathRevenue - Project Project Manager in the PivotTable Field List:
|
Excel Report Results
| Project Manager | Revenue Value | Goal | Status | Trend | Revenue Value | Goal | Status | Trend |
|---|---|---|---|---|---|---|---|---|
| 2010 | 2011 | |||||||
| Sara Day | 208,000 | 200,000 | 
|
|
242,000 | 200,000 | 
|
|
| James Bartlett | 158,000 | 200,000 | 
|
|
198,000 | 200,000 |
|
|
| Emily Carlton | 205,000 | 200,000 |
|
|
195,000 | 200,000 |
|
|
| Mike Post | 198,000 | 200,000 |
|
|
201,000 | 200,000 |
|
|
| Eric Nelson | 119,000 | 150,000 |
|
152,000 | 150,000 |
|
|
Status symbols in Excel:
= Goal has been exceeded
= Goal has been met
= Goal has not been met
Trend symbols in Excel:
= Trending up
= Trending neutral
= Trending down