KPI Dashboard in Excel

You go to any organization; it is important to analyze based on their Key Performance Indicators (KPI). A separate dedicated team will be responsible for investigating and showing the results with visual effects. This article will show you how to create a KPI dashboard of individual sales employees in Excel. So, download the workbook to create a KPI dashboard in Excel with us.

Different companies have different KPI dashboards. For this article, we are taking into consideration the sales-driven organization. Its core revenue generation department is its sales team in a sales-driven organization. To just how the company is performing, it is important to look at the performance of each individual in the department.

Below is the example of creating a KPI Dashboard in excel.

The steps to create a KPI dashboard in Excel are as follows:

1. First, we need to create a “Target_Table” for each employee across 12 months.

In the above table for each individual, we have created a target for each month. We have applied table format in excel and named it Target_Table.

2. Similarly, we must create one more table called Sales_Table, which shows actual sales.

3. Similarly, we must create two more tables to arrive at the salary and incentive numbers.

Above is the table of individual salaries. So now, create a table for Incentives.

We are done with all the data inputs required to show in the dashboard. Next, we must create one more sheet and name it Dashboard – KPI.

4. Create the heading “Individual KPI Dashboard- 2019” in Excel.

5. Now, create employee names and build a drop-down list in Excel of employees.

6. Create a month-wise table to show Target, Actual, Variance, and Average Sale. And also to display Salary and Incentive Paid.

We have divided the data to be shown for the first six months and the later six months. So that is why we have added H1 total and H2 total rows.

H1 consists of the first six months total, and H2 consists of the second six months total. So, the whole is a combination of H1 + H2.

7. For Target and Actual, we must apply VLOOKUP from the Input Data sheet and arrive at numbers based on the name selection from the drop-down list.

To find Actual sales data, select Sales_Table in place of Target_Table in the VLOOKUP formula.

To find the variance, subtract actual sales data from target sales data.

To get an average of sales data, apply the below formula.

Similarly, we must do the same for the Salary Paid and Incentive Paid columns.

Then, find the Total Earned by adding Salary Paid and Incentive Paid.

Now, based on the selection we make from the drop-down list of the name, VLOOKUP fetches the Target, Actual, Salary Paid, and Incentive Paid data from the respective table which we have earlier in the article.

8. We need to create a cell for the Efficiency Level. Then, we need to divide the achieved amount by the target amount to arrive at the efficiency level.

9.Now, arrive at the return on investment result. But, first, we must divide the Actual amount by the Total Earned amount.

10. Now, a graphical representation of the data we have comes at. Below is the graph we have applied. We can create a chart based on the liking.

We have applied a line graph for “Target,” “Actual,” and “Average” numbers in the above chart. Therefore, this graph will represent the “Average” achieved and “Target” achieved against the “Target” numbers.

We have applied a Clustered Column chart in excel.

After all this, we need to insert a simple efficiency chart. Finally, we have applied the efficiency chart, and we can start using this KPI dashboard by downloading the Excel workbook from the link provided.

Based on the selection we make from the drop-down list, numbers will turn up accordingly, and graphs will show the differences.

Based on the requirement, we can increase the number of employees and fill the target data, actual data, and salary and incentive data accordingly.