Overview

Tying resource costs to specific projects, initiatives, or line-items (in either abstract effort or in dollars) is a common and extremely challenging problem faced by many organizations. This document outlines how, through a careful use of basic scrum principles and some clever Google Sheets work, this reporting can be delivered.

By taking the sum of story points completed by a team in a fixed time period (generally a month) and dividing it by the cost to fund that team in the same period, we can convert each team's unique story point scaling into a common and useful number we can roll up into their parent objects (like epics, themes, or initiatives), and thus see in a real and impactful sense where investments are being made, when, and by approximately how much.

With the additional step of identifying work and resources as being capitalizable or not, we can extend this logic into a complete capitalization\operations expense report that requires very little additional effort on the part of product owners and developers.

Assumptions and Requirements

Scrum

This process relies on each ticket's story size to help determine how much it cost, and so all teams whose work is being measured would need to point all of their stories in order for this process to work.

Part of the Scrum Agile methodology involves teams assigning "story points" to each ticket in their refined backlog. This number is an abstract representation of the time and complexity the team think is needed to deliver on the story relative to their work generally.

Financials

This process requires that a cost-per-team be provided so that the cost-per-point can be derived for each team in that period.

Each team's story point scale can vary widely; while many teams choose to structure their story point scale around a fibonacci sequence and so would be basically similar, there is no guarantee that one team's 1-point story is the same time\complexity as a 1-point story on another. However if we can determine both the number of points completed by a team in a given period of time and the cost spent to fund that team over that same period, then we can use each team's cost-per-point rate to translate from each into a common and comparable dollar amount.

Work Location and Breakdown Structure

The last piece of the puzzle is figuring out where each ticket worked on by a team fits into the larger picture, and therefore where its cost should be accounted for. This means that:

• Every ticket needs to have complete linkages throughout the hierarchy all the way from most junior child to most senior parent. Missing links will stop the rolling up and result in a category of un-mappable costs.

• No work can be done by a team unless it is represented by a pointed ticket in their project. This ensures that our cost-per-point is accurate, because it includes all the work done and is using the correct rate.

Capitalization Definitions and Agreements

The majority of guidance around what work and whose time can be capitalized was written to support the "waterfall" project management approach, and does not lend itself well to how most modern development is done. Some common challenges:

• Activities denoted as being "research" or "design" are expressly not considered capitalizable. Many Agile methodologies call for these actives often and baked into other activities, making it difficult to tease out exactly how much time is spent on them.

• Certain roles are often denoted as expressly non-capitalizable, such as UX designers or project owners. In many organizations these hats can and are worn by people who also perform roles that would be  capitalizable.

The most important thing is that development\program management and accounting come to an agreement on what qualifies as CapEx or OpEx (and when) in some key areas, namely:

• Can we capitalize an entire story or (ideally) epic? In most cases, a user story or development task goes through several phases ("New", "Design", "Development", "QA", "Deployed" for example) however trying to determine how much time a given story spent in each status (and therefore how much of it can be capitalized using the most strict guidance) is a very heavy lift. An agreement to simply denote the entire story as capitalizable or not would save you this effort; raising this one hierarchical level to the epic will save you even more.

• Can we capitalize all of a person's time? If you have a person performing both capitalizable and non-capitalizable activities, then you will need to measure the allocations for each person across their roles in some way which can be incorporated into the reporting process.

Process Overview

Once these assumptions are codified into working agreements and you have a body of work in JIRA for a period to measure, you can begin to generate the report. For the purposes of this document, we will assume that the period being measured is one month, however by providing the relevant supporting data the template can support any time box.

Building the Report

Step 1: Building the Data Set

Story Level

The first dataset you will need is the low-level work (i.e. the stories, tasks, bugs, and anything else at that "standard issues" level) that was resolved in the month. The filter might look something like:

At minimum, you will need to make sure you have the following information in your export:

• Project

• Issue Key

• Epic Link

• Story Size

You may also want to add some other details to provide deeper analysis or to help you troubleshoot any oddities:

• Summary

• Resolution

• Resolution Date

• Issue Type

Epic Level

The second dataset provides information about the next layer up in the work-breakdown structure, the Epic. This represents a single team's expression of a project (i.e. all of the work a given team needs to do to deliver on a feature or goal in one phase) and so there is usually project-specific metadata on the epic that is important to incorporate, such was whether the epic is capitalizable or what quarter the team plans on beginning work.

At minimum, you will need to make sure you have the following information in your export:

• Epic Name

• Epic Key

• Capitalizable Flag

Roll-Up Reporting

The third dataset provides information about the highest layers of work, and is designed to let you roll up the lower level information by categories that are useful to your business. At minimum, this would be an "initiative" issue acting as a parent to epics, but it might also include:

• Mid-level objects like "Capabilities" 

• Super-high level objects used to group initiatives like "goals" and "themes"

• Information about the initiative\capability\goal\theme like:
  
  

  • Roadmap area (product, tech, maintenance)

  • Roadmap status

  • Project budget\spend allocation

  • Projected start and end dates

Many organizations choose to use a tool to manage this layer of work. Jira itself can handle most of the basics, although larger organizations often find a dedicated tool, such as Jira Align or Aha! more useful. At the end of the day, your data should have at least the following columns:

• Epic Key

• Initiative name

• Initiative key

Step 2: Merging the Data

A template for this report which contains the logic needed to drive this report can be found here. After making a copy, you will need to do a few things to initialize the sheet for your environment.

Load Your Teams

The 'Team Mapping' tab contains information about your team layout and is used to identify the correct rate for a given story. You will need to provide the following information for each team in your program:

• Project Key - the project key for the JIRA project owned by this team

• Team Name - the name of the team

• Vertical\Train - the group within the organization that this team belongs to

Set Up Your Team Rates and Reporting Period

The 'Team Rates' tab contains two key pieces of information: the team rates, and the report period.

Team Rates

This section (columns A, B and C) identifies the cost needed to run each team listed in the 'Team Mapping' tab. The 'labor only' rate should include only capitalizable expenses (generally, salaries of employees performing capitalizable work) and is used to generate the CapEx side of the report. The other number is used to generate the 'project accounting' numbers, and so should include any organizational expenses accrued. For example, many organizations include as project accounting overhead the cost of shared infrastructure and service teams.

Reporting Period

Cells L2 and L3 should contain the starting and ending dates of the period of time being measured in the report. Often these reports are generated monthly, and so the monthly expenses calculated by the sheet will match the monthly expenses you entered into columns B and C. However, the sheet can also be used to make projections with existing rates. For example, at the end of your PI planning week, you might want to validate your plan against your yearly goals. To do this, you enter your newly planned work and the rates you have handy, which are for the last month of your last PI. You then set the period in L2 and L3 to be your upcoming PI, and the sheet (in columns F and I) will re-calculate the correct rates to use over the new time period.

Step 3: Load and Verify Your Data

After you have entered and\or verified the team mapping, rates, and report period details, you can move onto loading your data into the report by copying...

• Your "Story level" data into the 'Period Stories' tab

• Your "Epic level" data into the 'Period Epics' tab

• Your "Roll Up Reporting" data into the 'Initiative Mapping' tab

Once the data is loaded, you need verify that there are no wrinkles or errors. The sheet has several layers of error checking built-in to help identify where in the data-chain the issue is:

• The "problem children" tabs are designed to identify and highlight problems with the imported data. It is primarily focused on missing linkages and field information.

• These linkage and missing information errors are expressed in the reporting tabs ("Period Initiatives" and the "Roll-Up" tabs) as "error message line items". These not only identify where in the chain the error is, but an approximate "size" of the error in dollars.

  • "Epic Unlinked to Feature" means that the Jira epic key is not mapped to an initiative on the "Initiative Mapping" tab. In the template sheet, the epic "EE-30 is an example of this. 

  • "Feature Unlinked to Initiative" means that the Jira epic key exists in the "Initiative Mapping" tab, but has no initiative data associated with it. In the template sheet, the epic "FF-39" is an example of this. 

  • "Missing High Level Area" means that the initiative has not been assigned a "High Level Roadmap Area" in column F of the "Initiative Mapping" tab. In the template sheet, the epic "DD-17" (the "Improve Site Stability" initiative) is an example of this. 

• Once this is done, verify that all of the calculated columns in the 'Period Stories' tab (to the left of the grey column "I") have no errors. These tend to be caused by problems in the team or rate mapping sheets.

Step 4: Using the Data

Once you have confirmed that everything is being correctly processed, the reporting tabs will contain different break-downs of the data:

• The "Period Initiatives" tab shows all of the initiatives which were contributed to by team work in the reporting period. 

• The "Roll-Up" tabs show the costs rolled up at the different levels of your WBS hierarchy.

Appendix

Making Modifications

This sheet assumes a particular way of working which may or may not map onto your organization. For example:

• It includes metrics at the initiative level such as "High Level Roadmap Area" or "Roadmap Status" that your organization does not use, but does not include ones you do report on.

• It assumes the work breakdown structure is three levels i.e. "stories and tasks" rolling up to "epics" rolling up to "initiatives". However your organization uses an intermediate object like "capability" between your epics and initiatives.

While it is not feasible to document how to address all of the potential changes you may need to make in order to utilize this approach, there are some architectural elements of the sheet which can help streamline the process.

Staying Dynamic

Aside from the imported data you upload and a one specific segment (the list of teams on the "Team Rates" page), there should be no hard-coded lists anywhere in the sheet. While this simplifies maintenance tremendously, it also makes it significantly easier to add new metics.

For example, let's say your organization does not use "High Level Roadmap Area" as an initiative-level metric but do look at "Requesting Division". You will need to rename some column headers and other identifiers, but by placing the values in "High Level Roadmap Area" column in the template, you will see your initiatives rolled up that way in the "High Level Roll Up" tab.

'Period Stories' is the Key

The "Period Stories" tab acts as the keystone of the sheet - the derived data in columns J+ in this tab are where all other data, from cost-per-SP to WBS location, is joined to the actual work-level issues. Each roll-up tab then groups this same data by different columns to provide the different views. By replicating this approach for your new metrics, you can incorporate new views without needing to build reporting tabs from scratch.

As example, let's say that your organization does have an intermediate object between "initiative" and "epic" called "capability". In order to incorporate this new layer, you will need to:

• Map epics to their parent capability

• Map capabilities to their parent initiative

• Build a reporting screen to show the costs per capability

Where you store the "epic-to-capabiity" and "capability-to-initiative" mapping information is not critically important; you could store both as part of the "Initiative Mapping" tab, split them across that and the "Period Epics", or even make a new "Capability Mapping" tab; the key is that this is then mapped back to the work-level issues in the "Period Stories" tab. Once this is done, you can clone an existing "roll-up" tab and re-point it to your new data column to see your new view.