How to create Microsoft Excel dashboards to monitor the progress of a software development project (part 1 of 3)
When it comes to manage software development projects, you have to monitor a lot of different quantitative and qualitative metrics in order to answer the main question:
“Where are we?”
As in any other project you have to take care of the usual suspects in project controlling like the completion rate of tasks, the milestones and quality gates, the budget adherence, etc. In software development projects, however, there are a couple of very important specific additional facets to be monitored closely:
- The actual status and the trend of software defects
- The test progress, test coverage and test success
- The actual status and the development of change requests
Today’s article is the first of a 3 post series on how to create minimalist, dynamic software project dashboards with Microsoft Excel; this time a software defect monitor dashboard including the Microsoft Excel workbook for free download.
Create a one-page dashboard for monitoring the actual status of software defects in order to have an overview of the software quality at a glance. A dashboard that can be used in your overall project reporting as well.
The Key Performance Indicators
The main metric we care about in this part of the series is the number of software defects. According to Wikipedia a software defect (aka software bug) is
“… the common term used to describe an error, flaw, mistake, failure, or fault in a computer program or system that produces an incorrect or unexpected result, or causes it to behave in unintended ways.”
Usually software defects are categorized by severity, e.g. assigning a defect to one of the following classes:
- Severity 1: Critical
Critical defects result in a total failure of the software or unrecoverable data loss. A workaround is impossible. - Severity 2: Major
Major defects result in massively impaired functionality. A workaround does not exist or is impracticable. - Severity 3: Minor
Minor defects result in non-critical failures of the system. A satisfactory workaround exists. - Severity 4: Cosmetical
Cosmetical defects are of low or very low impairment. A reasonable workaround exists.
Different strokes for different folks. Other projects and other companies may use different naming conventions (like showstopper / severe / medium / minor, or others), but usually there are 4 or maximum 5 categories to classify the severity of a software defect.
The actual status of a software bug can be classified into one of the following 3 main categories:
- Assigned
The defect was detected by Quality Assurance or during other tests (like the User Acceptance Test), assigned to and accepted by the software development team. - Resolved
The defect was detected and assigned to the software development. The bug fix has already been delivered, but it was not yet retested by Quality Assurance. - Closed
The defect was detected and assigned, development delivered the bug fix and Quality Assurance already successfully retested the fix.
Again, terminology may differ across different software projects or companies. Furthermore there are a some more possible status like “in discussion”, “in review”, “rejected”, “duplicate”, etc. Though, in order to keep the showcase lean and simple, I am restricting myself to these 3 defect status.
The Visualizations
- Software defects by severity and status for the actual week
The snapshot of actual number of defects by status and severity is probably the first thing to look at. A simple table enhanced by a stacked bar chart and explanatory texts allows a quick overview: - Software defects by status over time – cumulated and week on week
An area chart showing the cumulated values of software defects over time and an additional line chart displaying the trend of assigned, resolved and closed defects visualizes all relevant information on the development of software bugs during the project:At the beginning of the project we expect the number of assigned defects to grow faster than the number of defects resolved or even closed. Towards the end of the project, however, we should see the cumulated number of assigned defects in the area chart to level off and the gaps between the areas to decrease. With regards to the line chart, this means the orange (resolved) and green (closed) line should tend to be above the red line (assigned) towards the end of the project.
- Average resolution and conclusion times – actuals and development over time
The average resolution time and average conclusion time are very interesting additional measures regarding the status of software defects. The average number of days between the date of assignment and the date of resolution shows how long software development needs on average to fix a defect. The span between the date of resolution and the date of conclusion reveals the performance of Quality Assurance regarding retesting the delivered fixes. Finally, the period between the date of assignment and the date of conclusion can be used as one of the basic inputs on an estimation to complete.
The visualization is pretty minimalistic: a table displaying the needed average number of calendar days by severity and a line chart showing the evolution of the 3 metrics over time.
The Implementation
As already discussed in a previous post, I am always trying to implement a workbook structure that strictly separates the data input sheet (“data”), the consolidation / calculations (“control”) and the dashboard. This case is no exception.
Hence, you will find all formulas for consolidating the data on the worksheet “control”. There are some advanced formula techniques like array formulas, formulas using SUMPRODUCT and one complex formula calculating the ISO 8601 week numbers. I will not provide a detailed how-to tutorial on these formulas, but the following links might help you understand the used techniques:
- For understanding the concept of array formulas you may want to check out Chip Pearson’s great website
- Charley Kyd has a very good article on his website ExcelUser regarding SUMPRODUCT
- An explanation on how to calculate week numbers is available in a previous post here on Clearly and Simply
Agreed, all these formulas require advanced skills in Microsoft Excel, but they are no rocket science.
The implementation of the used visualizations, however, should be self-explanatory: 2 tables directly linked to the results on the control sheet, a stacked bar chart, an area chart and 2 line charts. Microsoft Excel standard functionality and charts, no tricks and hacks necessary.
The Result
Here is the final result, a screenshot of the complete dashboard:
A variety of other metrics may be added, like an estimation to complete, the defect fix fault ratio (i.e. the number of bad fixes compared to the number of delivered fixes), etc., but I guess this little showcase should get you going.
The Download Link
Here is the Microsoft Excel workbook for free download:
Download Dashboard Software Development Defect (Excel 97 – 2003, 168K)
All dates and numbers in this workbook are made up.
What’s next?
Monitoring the software defects of your project is only half the battle won. Another extremely important thing to track is the progress and performance of your Quality Assurance and testing. The next article will discuss a Microsoft Excel dashboard to monitor the test progress of a software development project.
Stay tuned.
