Tracking KPIs using Microsoft Excel dashboard

KPIs or Key Performance Indicators come into picture when the organisation want to gauge the work estimates versus achieved output rations. It is a reliable source of information for the Project Managers to make informed decisions and plan strategies based on the goals to be achieved. The KPIs may vary depending upon the industry type. I am hereby going to demonstrate how I drafted reports while working for a product company.

As we were a product company, our KPIs were modules of the product which were uniquely designed to address the client needs. We had numbers on usage and traffic visiting these modules. It was the deteriorating numbers in case of usage that directed us in aligning activities focusing on training for the users. I shall focus on KPI metrics dashboard here.

NOTE: This blog is for the beginners looking forward to a quick solution to be presented to the top management team members. I am planning a second blog where the KPI plan will be completely pictorial and dataset would be as large as 300 records.

Find answers for below pointers before jumping on to work. I am mentioning the reasons for my dashboard creation

• Purpose – Improve module usage
• Audience – Top level Management and Project Manager
• Actionable KPIs – Restrict your KPIs to three in number per client
• Type of charts to be used – Bar Chart

We will be working on data set collected from the data analytics teams where we have mention of the clients and parallel module usage percentages in a tabular format. Refer to the image shared below for better understanding:

You will notice the colored icons next to each cell. These icons can be added by selecting the range of cells with numbers and clicking on the “Conditional Formatting feature” under “Home” tab. Select “New Rule’ from the drop down menu and follow the below steps to achieve the same result.

• Select the rule type – Let it be the default selection stating “Format all cells based on their values”
• Under “Edit rule description” select “Format Style” as “Icon Set”
• Under “Icon Settings” option, you can add value as per your need. Make sure you change the “Type” to “Number”
• Click “OK” to save your settings.

Adding a Bar chart is simple. You can add one by selecting the data to be depicted on your chart by clicking on “Insert tab”. You can change the chart settings using Design, Layout and Format tabs. I had to change the layout and chose to go ahead with horizontal bars in the chart. You can select each element on your chart and change font style, colour and also the colours of the bars representing the clients.

We took steps given below based on our analyzed data shared above:

Client A – The only KPI which ran the client account was the Fee Reminder which was not in use as the enhancements asked by the client following the phase 1 implementation was placed later in the Product Backlog. The Product Manager planned it for the next sprint and started working on Concept designs to be approved by the client.

Client B – The Relationship Manager came to us with serious data issues where the database needed update in results as the data mismatch on ERP and our software created escalations from students resulting in resistance to rely on our tool. The API integration teams came into action at once and fixed it on priority.

Client C – It faced the challenge with modules like Service Centre and Anti Ragging. The Relationship Manager immediately conducted meetings with respective departments and reported new requirements explaining the need of reporting tools and dashboards to track live status.

Client D – We decided to conduct a quick call with the Administration Department and understood the challenges faced by their team while using this module and started working accordingly. The Growth Manager was asked to prepare User Manuals and share a plan to facilitate training with respective team members.

This example was small in scale but had a huge impact in terms of client retention and improved usage of the product. Stay tuned for the next blog on KPI Dashboard using dataset with over 300 records.