Requirements part 5 - a very useful spreadsheet

I think there might actually have been one or two more posts on requirements than 5. No matter the end of the tunnel is in sight.

I've written quite a bit about what not to do with requirements and conversely some useful salves for common problems. What I haven't done (until today) is state clearly my approach to managing requirements (and more besides) or provide the tool to get the job done.

To date, we've talked about MoSCoW analysis (yuk!), pair-wise comparison, cost of compliance / non-compliance, KANO analysis and quite a bit more besides. We've never talked about UML or a bunch of other stuff, but ultimately (as you'll see) that might not matter too much.

Consider the illustration below - a veritable soup of inputs relating to requirements. Equally, a customisable and completely transparent score card that can be constructed and agreed by stakeholders early in the process.

What we're starting to get towards here is an approach to requirements prioritisation and management that can be highly customised to suit any situation. 

Get the stakeholder buy-in right, get the score card right and the rest will follow.

In the example below, I've used the following scoring elements - you however can use what you like.

• MoSCow - exactly what it says on the tin. MoSCow does have its place albeit do remain cognisant of the limitations previously discussed.
• Kano - see last post. A quick and easy way of assessing non-monetary value
• Contribution to the business plan - if it doesn't contribute, should you be doing it?
• Compliance - do we have to have this to meet regulatory requirements?
• Senior stakeholder flag - if the the budget holder wants it in taupe, then let's have that right out in the open from the get go.

Your score card might incorporate the elements illustrated above or be something complete different. You might have specific organisational imperatives which mean you incorporate none of the elements above - fine. The approach is no less valid.

So - you've got a score card - what next? Excel that's what. What you're seeing below is the scorecard above incorporated into Excel.

It's not too busy a spreadsheet but it has got a couple of tricks up its sleeve.

First, the fields are 'constrained' and aligned with the scorecard.

And, we've got a a simple but long formula to do the scoring calculation. Note the red highlight. We don't put the scoring in the formula itself - we use a lookup to a table elsewhere. This is important and we'll discuss this more later.

=IF(C2="Must have",Lookups!$B$1,(IF(C2="Should have",Lookups!$B$2,(IF(C2="Could have",Lookups!$B$3,(IF(C2="Won't have",Lookups!$B$4)))))))+IF(D2="Dis-satisfier",Lookups!$D$1,(IF(D2="Satisfier",Lookups!$D$2,(IF(D2="Delighter",Lookups!$D$3)))))+IF(E2="Key",Lookups!$F$1,(IF(E2="Required",Lookups!$F$2,(IF(E2="Aligned",Lookups!$F$3)))))+IF(F2="Yes",Lookups!$H$1,(IF(F2="No",Lookups!$H$2)))+(IF(G2="Yes",Lookups!$J$1,(IF(G2="No",Lookups!$J$2))))

I've uploaded the spread sheet here and you can play to your heart's content.

Some things to bear in mind.

1. You aren't constrained to 'sum' the scores. Multiplication has its place particularly as it opens up using a zero to effective nullify a requirement
2. You aren't constrained to using this just for requirements - the same approach works very well for (say) assigning a risk rating to server moves in a data centre migration
3. I've used significantly larger spreads sheets both in terms of the number of criteria used and the scores which correspond to those criteria. Undoubtedly there's a limit but I haven't found it. If you do (and good luck with that) you can always split the formula in two and sum the output.
4. Don't limit yourself to linear scoring - in point of fact, you're going to need to justify very carefully the use of linear scoring (i.e 1,2,3,4,5,6 as opposed to 1,3,8,20). Most things (I think) will benefit from a non-linear scoring approach.
5. Make sure you get your criteria right from the get go, the scoring only needs to be 'about' right as we re-tune that later on.
6. Weighting - don't think you can't apply a specific weighting to one or more elements on the scorecard - in point of fact this is just another way of playing with the scoring but don't rule it out.
7. If you're smart enough you can probably use a custom list in SharePoint to do this.

Finally, getting the scoring in the scorecard right from the get go is tough. So tough in fact that I discourage you from trying. Stakeholders can get a bit cagey too as, while they see the merit in the approach, they tend to be less certain about getting tied down by a scorecard that they (quite understandably) can't appreciate the fullest implications of at the outset.

Fine tuning the scoring is the subject of the next post.

Proportionality in controls

Early identification of the 'pinch' points and putting more effort towards controlling those elements is, in my opinion, time well spent.


In the example below, we're 5 months in on a project, the customer isn't too sensitive to cost, but is extremely sensitive to delays.

Doubtless there are a few ways of tracking quality. I've often thought that if you're handing over  project deliverables sequentially, CPI and SPI sort of give you a 'QPI' or quality performance index since your customer is signing off acceptance of products as you go. 


With IT projects you do involve the customer in test and review activities throughout the design and development work, but sign-off tends to be a bigger bang type activity. The PM of any IT project needs to be fully cognisant of the customer's wants, needs and any deficiencies as an ongoing process. This activity is encompassed within testing and defect management.


I'll do a post at a later date on the range of metrics relating to defect management which I like to capture but here I've singled out something I've always been very keen on. See the bottom of the three charts above. Something not entirely clear is that that the graph isn't cumulative. Each month the number of defects identified that month is plotted against the number of defects fixed that month. At a glance, this shows whether or not you have an unsustainable, worsening or improving state with regard to defect identification and resolution.

Note the control charts above, not only are these easily maintained and communicable, they illustrate trends and the tolerances to which the project is expected to adhere. Incidentally, I'm a fan of publishing this sort of material directly to the project team - it encourages shared ownership and good alignment of decisions throughout the project team.

As can be seen above we've got more tolerance on cost than time. Take the cost (CPI) plot, we went from a good start, to holding steady, followed by three consecutive periods of deteriorating performance. With the benefit of hindsight it would have been useful to have a more frequent reporting interval - if we'd had fortnightly reporting - somewhere between month 2 and month 3, it would have become apparent that corrective action was required, and consequently far more easy to justify.

While the time (SPI) plot serves to illustrate this point very well, what we could have seen in advance was the very limited tolerance on schedule would have benefit from more regular reporting for precisely the reason above. The emergence of a trend would have happened far more quickly.


These sort of indicators are one approach to helping ensure that within any given project, benefits are maximised and risk is minimised. The best indicators are those that inform timely decision making before things go off the rails, not simply reporting on the fact that they have.

Requirements, part 3

So far, I've had a good whinge about the MoSCoW method of requirements prioritisation, and suggested the alternative method of paired comparison. In this post I'm going to talk a little about the life-cycle of a requirement and why the management of requirements is pivotal to testing and product acceptance. In a future post, I'll come back to the topic of requirements prioritisation to provide a middle ground between MoSCoW and paired comparison.

 First, let's revisit the method of achieving quality I put forward in a previous post.

It's probably time to add a few more specifics around this, and that in turn will provide an entry point into several subsequent posts.

1. Requirements - this is the subject of this post and (at time of writing) two others besides. The scope of this activity extends to the elicitation, prioritisation, specification and management of the requirements
2. The QRA will be the topic of a subsequent post. For those impatient to get to grips with this, read Rex Black's material here. In short it's an analytic approach to prioritising the testing effort with a view to minimising risk
3. Development and implementation - we will get to this at some future point. As project manager you should be planning, directing and controlling this activity.
4. Testing - a big big topic but I'll endeavour to distil some valuable points during the course of future posts. I'm a big fan of V-Model development and sowing test activities throughout all development activities. Crucially; every development activity has a corresponding test activity. I would also keep in mind IEEE 829, a standard approach to test plan management. Links here and here Plenty more to come on this topic.
5. Defects have a management life-cycle all of their own. This is to ensure that efforts to resolve the defects are suitably prioritised, that defects when resolved are confirmed as fixed and a whole host of other activities. I'll post a couple of articles on this in the future.
6. Lastly the quality log. Technically, it is not strictly required however, in isolation the defect log (a component of defect management) paints an overly bleak picture. (Try having a defect log of 600 things which don't work - it doesn't make for happy project boards either). Incidentally, it helps too with product acceptance as opposed to the defect log (recording things which don't work) it provides a central repository encompassing all the things that do work.

So with this all said, let's focus on the role requirements play at each stage above. First, there's the requirements themselves. The QRA cannot be produced without a clearly understood set of requirements and do please note that this tool is sometimes crucial in placating legitimate stakeholder anxieties. Undoubtedly it is possible to start development and implementation activities without a detailed requirements specification. Doubtless too, this is one of the principal causes of project failure. You can certainly build something, but you cannot achieve quality without a clear understanding of what is required. Testing specifically is the activity of identifying defects and defects are defined as requirements that haven't been met. You cannot test a system if you don't know what the requirements for that system are. Defect management - the process of managing defects through the defect life-cycle cannot be undertaken without a detailed understanding of requirements.

So at every stage of this activity, requirements are playing a principal role. I think that'll probably suffice in terms of the 'big picture'. I have endeavoured to provide a solid basis upon which to build in future posts. These will include posts on how I typically approach the prioritisation of requirements, an explanation of the QRA, a complete review of the defects management life-cycle and more besides.

Requirements, testing and quality

I'm a big fan of testing, test methodologies and approaches. It's a fantastically rich and mature discipline and offers a lot to the project manager. I think the value of a project manager will almost certainly be amplified by even the most rudimentary knowledge of the discipline.


One approach I've become comfortable with is illustrated below.

Here's a link to a read only Google Doc of the illustration. Please help yourself. Here's a link to an editable version - if you can come up with something you think is better or interesting, please amend it as you see fit. If you do edit it, add some comments to the blog explaining your rationale. 


I'm only going to touch on each component briefly now. In practice, with the exception of the quality log, the components are in themselves substantial topics. But I intend to make this something of a recurrent theme and I'll try and support the process with a few useful resources along the way.


Hopefully, most of the elements illustrated are reasonably clear. Requirements, a big topic in itself, will almost certainly be the starting point for the majority of projects. I'll come back to the quality risk analysis (QRA) later. Development and implementation are condensed - they're not my focus here. We undertake testing* and either verify a requirement has been met, recording this in the quality log or find that it hasn't been met and raise a defect. 

*In practice, testing is an ongoing activity throughout the project life-cycle, but for my purposes here, it is illustrated it as a single element.

In practice, the quality log might seem a superfluous overhead. It can however prove to be quite a useful document, illustrating what has been achieved. By comparison, the defect log which, on its own, can paint a picture that isn't wholly representative.


The quality risk analysis was something I came across in Rex Black's book, Critical Testing Processes. Equally, he covers it in reasonable detail here. I include the abstract below.


Testing any real-world system is potentially an infinite task. Of this infinite set of possible tests, test managers need to focus on the most significant risks to system quality. These are the potential failures that are likely to occur in real-world use or would cost a lot if they did occur. This article describes practical ways to analyze the risk to system quality, providing guidance along the way to achieving effective and efficient testing.


I'll return to the topic of quality risk analysis and include a useful resource to support its use in due course. However, it would seem an opportune time to leave you with a very succinct and almost universal definition of quality namely; meets requirements, fit for purpose.