Measure performance at a glance with Key Performance Indicators

 

by Kara Hiltz

Application:

Microsoft Excel 2013/2016

Operating System:

Microsoft Windows

Download:

http://download.elijournals.com/excel/201602/KPIs.zip

It’s important to know whether you’re meeting business goals and targets so that you can make adjustments and maximize your potential. Excel 2016’s Power Pivot offers a way to set and measure Key Performance Indicators (KPIs), which allow you to see in seconds whether you’re hitting the targets you set.

To create and display Key Performance Indicators (KPIs) in PowerPivot, we’ll:

  • Learn more about what KPIs involve and how businesses can use them.
  • Create a Power Pivot data model.
  • Add base and target measures to our data model.
  • Generate a Key Performance Indicator.

When you set goals, monitoring your progress is essential to staying on track. By comparing current data to its related goal, you can find areas where you need to improve in order to reach your target. You can also see areas in which you’ve already reached your goal. Key Performance Indicators, or KPIs, give you a quick visual snapshot of where you stand when compared with a goal, as shown in Figure A.

Excel 2013 and 2016’s Power Pivot analysis features allow you to set up KPIs that will help you visualize where you are on the path to a specific target. We’ll show you how it’s done.

A:

ime17b03A

Our KPI quickly tells us that this product did not meet the company’s standards for focus group opinions, and will probably go back to the development phase.

What are Key Performance Indicators?

Simply put, KPIs allow you to measure how close you are to achieving a goal. In our example, we want to analyze responses of a 20-person focus group for a product. Our goal is that 70 percent or more of the focus group participants score the product as a 4 or 5 in all six categories. There are three basic parts to a Key Performance Indicator:

  • Base value: The base value is a calculation that results in a value. Depending on your data, it may result in a value that reflects gross profit or sales. In our example, the base value is the total number of 4 and 5 responses in a focus group category.
  • Target value: A target value is the value representing your goal. This value may be a calculated value, such as when you want a sales rep’s total sales to equal two percent of the company’s overall sales. The target value may also be an absolute value, such as the average score a class receives on a test serving as the target value for individual student scores. In our example, the target value is 70 percent of the number of focus group participants.
  • Status thresholds:Excel allows you to define status thresholds to show how close or far from the target data is. Status thresholds determine what color Excel uses for its visual aid. In our example, we’ll use 20 percent as our lower threshold and 80 percent as our higher threshold, meaning that green indicates at least 80-percent progress toward the target, yellow is between 21- and 79-percent progress toward the target, and red indicates 20-percent or less progress.

Download:To follow along with us, you can download and extract the file FocusGroupResults.xls from the URL provided at the beginning of this article.

Excel 2013 vs. Excel 2016

You access the Power Pivot and Power View features differently depending on your version of Excel.

In Excel 2013, you need to install the Power Pivot add-in to use these analysis tools. When you go to the File tab, click on Options. In the Excel Options dialog box, choose Add-ins on the left. Choose COM Add-ins from the Manage dropdown list and click Go. Select the Microsoft Office Power Pivot in Microsoft Excel 2013 check box to enable the add-in and click OK. Now you’ll see the Power Pivot tab in your Ribbon.

In Excel 2016, the Power Pivot features have been incorporated into the application. They’re no longer an add-in. But you may have to enable the features. Go to the File tab and click on Options. In the Excel Options dialog box, choose Advanced on the left. Scroll down to the Data area, and then select the Enable Data Analysis Add-ins: Power Pivot, Power View, And Power Map check box. Click OK to return to Excel, where you now see a Power Pivot tab in your Ribbon.

Create a Power Pivot data model

We’ll show you how to set up all three parts of a KPI (base value, target value, and status thresholds) so you can use this powerful visual aid to gauge progress toward your business goals. But first you need to set up your Power Pivot data model.

To generate a data model:

  1. Go to the Power Pivot tab. (If you don’t see the Power Pivot tab, check out the box titled “Excel 2013 vs. Excel 2016” for help.)
  2. In the Data Model area of the Power Pivot tab, click on the Manage button. Excel will open Power Pivot for Excel as a separate window, but it will be blank.
  3. Return to your worksheet and, with any cell in your data table selected, click the Add To Data Model button on the Power Pivot tab. Excel will ask you to confirm the data range of your table through the Create Table dialog box.
  4. Click OK to confirm the range of data you want to add to the data model. Your Power Pivot windowwill populate with your data, as shown in Figure B.
  5. Switch to the Home tab in the Power Pivot window and click the PivotTable button.
  6. In the Create PivotTable dialog box, select the New Worksheet option button and click OK to generate a PivotTable in your workbook.
  7. Using the Field List on the right, click on Table 1 to view the columns available and select all of them to add them to your PivotTable.

Your PivotTable should resemble the one you added to the data mode[...]

 
Join NowClose
Return to the ExcelSkillsSociety's homepage