How to create a Microsoft Excel dashboard to monitor the progress of a software development project (part 3 of 3)
This article is the last part of a 3 posts series on software development project dashboards with Microsoft Excel. Episode 1 of the series discussed a software defect statistic dashboard, Episode 2 talked about a test progress and test success dashboard. Today’s post focuses on monitoring change requests, one of the biggest threats to complex software projects.
Change requests (CR) raised already during the development of the software lead to additional time and cost needed, threaten the project plan and the budget, bear the risk of additional defects and lead to an instable baseline of the software to be tested by Quality Assurance. That’s why you should definitely keep an eye on the development of change requests throughout the development phase of your software project.
Today’s post provides a minimalist dashboard for monitoring and reporting the actual status of change requests and their development over time. As always including the Microsoft Excel workbook for free download.
The Challenge
Create a one-page dashboard for monitoring the actual status of change requests raised against the project. A dashboard that can be used in an overall project reporting as well.
The Key Performance Indicators
According to Wikipedia a change request is
“… a document containing a call for an adjustment of a system; it is of great importance in the change management process.”
In the context of software development projects, a CR is a document submitted by one or several project stakeholders requiring additional features or functional enhancements to overcome weaknesses in the original design of the software.
Here are the main KPIs for CRs you should take care of:
- The number of CRs in general
The actual status of CRs for this week, a comparison to the status of the previous week and the development of CRs over time - CRs by status
A raised CR can always be assigned to a certain status:- “In investigation” means the CR document has been delivered and is in review by the software development.
- “Approved (in implementation)” are CRs that have been agreed to include, but not yet developed and delivered.
- “Included” CRs are already delivered and included in the actual version.
- “Future CRs” have been agreed on to be deferred to a later release of the software.
- “Rejected or cancelled” CRs are requests that will not be implemented or have been withdrawn by the stakeholders.
- CRs by departments / stakeholders
Monitoring the CRs by the departments / stakeholders that raised the CRs will give you a good impression of where most requests are coming from and which part of the original software design has the most weaknesses or starting points for necessary / desired changes. I recommend to have an eye especially on the CRs raised by IT department itself, because those are the ones which are probably necessary due to gaps in the original design. From this KPI you can learn a lot to avoid mistakes in future projects and releases.
The Visualizations
-
Overview of actual status for the selected weekA small table simply shows the most important KPIs of the selected week: total CRs last week, new CRs this week and total CRs this week divided by business departments and IT.
The horizontal waterfall chart underneath visualizes the distribution of all CRs by status. How many CRs are in investigation, already approved and in implementation, already included, i.e. delivered, deferred to future CRs (next release) or rejected/cancelled. I assume, many people would have used a pie chart for this, but I prefer this horizontal waterfall. From my point of view this looks clearer and it is easier to understand the proportions of CRs by status at a glance: -
Change Requests cumulated over time – total and by department
Two simple line charts visualize the development of CRs over time, in total and by department:
The Implementation
If you are are regular reader here on Clearly and Simply, you are probably already familiar with the way I am structuring my models: separate worksheets for the dashboard, the input data and the calculation. Business as usual.
The worksheet “control” takes advantage of some more complex functions like INDEX and MATCH, the formula calculating the ISO 8601 week numbers (as already used in the previous parts of this series), simple IF THEN clauses, array formulas and a way to do automatic sorting in Excel by formulas described here. No real innovative stuff, techniques you have probably already seen here or somewhere else.
The implementation of the dashboard doesn’t have real innovations either:
- A small table with the overall figures directly linked to the results on the worksheet “control”
- A spin button to “walk through the weeks”
- A horizontal waterfall chart (bars). Exactly the same technique as you would use for vertical waterfall charts (columns). As always, Jon Peltier provides a step by step tutorial how to do this.
- Dynamic line charts. Again, visit Jon Peltier’s PTS for a detailed how to tutorial. Many thanks Jon!
The Result
Here is a screenshot of the complete dashboard:The Download Link
And here is the Microsoft Excel workbook for free download:
Download Dashboard Software Change Requests (Excel 97 – 2003, 259K)
All data in this workbook is made up.
What’s next?
As already mentioned above, this was the last part of this series on software project dashboards. For sure, there are many enhancements, further software project KPIs and additional dashboards one could wish for. Nevertheless, this little series should get you started with developing you own software project dashboard reporting with Microsoft Excel according to your specific needs.
The next post here on Clearly and Simply will come back to Choropleth Maps once more. As already announced here, I am having one ace in left my sleeve regarding this topic. The next article will show some ideas on how to improve Choropleth Map with Microsoft Excel.
Stay tuned.