In software development, planning is a necessary component regardless of whether one takes a traditional waterfall methodology or adopts a more agile mindset, such as SCRUM. As a project manager or scrum master, you often have to estimate how long a software release or product will take to develop. Given a set increment, such as a two-week sprint or calendar month, and the number of development units you have, it is possible to plan out a software project and estimate how much work can be completed. This estimation work is one of the many things that Microsoft Project excels at, however not everyone has Microsoft Project. Instead, you can use Microsoft Excel to do this type of work and achieve similar results.
Calculating Development Capacity:
To start with we have Development Units. This is a measurement of dedicated development hours which is what most project managers deal with in Microsoft Project, or you can substitute hours for Agile Story Points. For my example, I will utilize story points, but either hours or points will work.
Next we need to know the number of development resources that we will have available to us by Sprint or by Month. For this example, I am going to use 12 months (January through December).
The next value that we will need is how many units a single developer can complete in a given month.
Inputting these values into a table, will give us an estimated capacity for the project.
What we need next is the set of stories and their corresponding unit values. For this, we start with 49 sample stories, each with a different unit value, in this case story points. Our stories will be completed sequentially in Excel, and their story points will be totaled as we progress. This summary is used to calculate what month the work will take place in. If we are over the sum capacity of the 12 months, then we would have exceeded our percentage capacity and note this as “Over Capacity”.
We either can remove stories or increase the number of developers for a given month.
To do this in Excel, I created a Calendar worksheet with a table that listed out by month, the number of developers, the estimated velocity, and incremented story points. The calendar table does simple addition and subtraction. You can change the number of Developers and outside of the table, there is a value of Story Points per Developer that can also be changed.
In cell A4, we have 32 story points which we have already completed at the beginning of March. For the month of March we have an estimated capacity of 16 story points. Note, I have set the Story Points Per Developer value to 4 for this example. With the Calendar worksheet completed, we have a good overview of what our capacity for the next 12 months will be. We can now move on to story planning.
Next I added a Story Planner worksheet with another table that lists out the planned stories and estimated points. The Story Summary incrementally sums up the story points.
For the Estimated Month, a VLOOKUP formula evaluates the story points to the values in the calendar table. The IFERROR is used to display Jan instead of #NA values.
The Capacity column calculates a percentage and if over 100%, displays a warning message, to let me know we are over our story points.
The formula uses the IF function to display the percentage if it is less than 1, or display Over Capacity message. You can also apply percentage formatting to the column to make it appear like in the example above.
=IF((D2/Calendar!$A$14)<1, (D2/Calendar!$A$14),"Over Capacity")
Microsoft Excel is a very versatile tool and this tutorial illustrates some simple planning techniques that can be done using a couple of tables and some basic formulas. I have made a version of the Excel file, to use as a starter template: