We began production of “Flicker of Hope” by doing mass CSV imports of the first issues that would populate our Jira boards, which we’d later assign across the team. Early on, we began to realize that certain members of the team would end up getting inadvertently over-allocated on estimated hours in their tasks. It was a oversight we had made during sprint planning as we gauged the capabilities of our team and as designs change, a common characteristic of preproduction.

Jira – despite its idiosyncrasies – is good for many things. Effective data visualization is often not one of them. It was especially to configure fields to view the capacity of each person in a clear and easy-to-understand manner with the tools that Jira had to offer out-of-the-box. So, in the minutes before a meeting, I got an idea: Why not make a visualizer for sprint capacity inside Excel?

After creating a little prototype, I built upon it due to feedback from my professors. Jira can export a sprint or filter’s issues to CSV. Taking advantage of that, I created a workbook with a few sheets:

Dashboard: This is a front-end dashboard featuring a speedometer chart that I put together combining two doughnut charts. The speedometer’s pointer is determined by a percentage (total team’s capacity -the total amount of hours the team can work – divided by the team’s current capacity). That percentage is rounded and plugged into the chart. Currently, we’re running at 94 percent capacity.

This sheet features the entire team roster, with two cells for each person . The left cell is the current total of the original estimate of tasks for that specific person. Next to it is their total capaciy which can be configured in each of their respective cells. The cells for each person have color-coded conditional formatting to show status at a glance.

Green: The person is under capacity. We don’t want people running at 100% max capacity, so this is ideal.
Yellow: This person is at 100% capacity. Any more tasks will overallocate them.
Red: This person is overallocated. Usually if this is the case, I write in a comment or note to give details as to why someone may be overallocated. For instance, Hannah – one of our animators – frequently appears overallocated. However, her tasks feature tentative handoff to other animators or take shorter than expected, depending on the complexity of a mocap animation.

Each person has 30 hours in the sprint – 15 hours required each week minimum. Exceptions include leads have 20 hours to account for typical lead duties, people on the team that may have extenuating circumstances such as chronic illnesses, etc.

The color-coding of the cells is handled by formula-dependent conditional formatting, which checks if the number fed in from the Jira CSV export (found in another sheet, Sprint5_Import).

This tool helped me especially in giving me – the DD of our team – another tool to audit and sanity-check what we are committing forth onto our sprint when sprint planning is finished, giving me time to pull data, examine it, and work with respective leads in the event someone is overallocated. And since the data is fed in as a data connection in Excel, the chart can be updated in real-time, so as long as the corresponding CSV is replaced with a new one of the same name and fields.

Going the Speed Limit: Gauging Sprint Capacity Visually in “Flicker of Hope”