IT Build Sheet for SDLC Build phase - Worked Example

In a previous Post I have suggested a Build spreadsheet approach for planning and monitoring an IT SDLC build phase. In this post I take you through a Worked example to make things clearer.

Worked example

The example assumes a build phase from 06/01/2014 to 07/02/2014 although as you will see this is easily adjusted.

1 - Define Supply side and likely build phase duration

Firstly we create the SUPPLY side. Key points are:

• The worksheet (columns P onwards) has the days of the week and the base hours per day, 8 in this example and 0 at weekends. 
• The most complex element of the spreadsheet is the columns coloured in Blue where the availability period for build is defined through cell references. In this example, I defined the build phase as 06/01/2014 to 07/02/2014 (the use of cell references allows easy modification which is necessary as will become apparent)
• There are 3 developers Joe, John and Richard. Joe is only available 70% of the time and John 60% in this period as they have other duties. Richard however can be dedicated 100% to this work
• A sum of the cells P onwards for the period defined in Blue gives 140 hours supply for Joe, 120 hours for John and 200 for Richard

2 - Enter Demand side scope and bottom up estimates

In Step 2 we enter the Demand in terms of scope items. In this example, I have entered 9 modules with "expert judgement" estimates (both during Initiation and after Analysis & Design), the order in which the modules should be built, any dependencies / comments.

At this stage, nothing is allocated to a resource although if there were heavy skill set constraints you might need to do this. For this exercise, let us assume in the main, any resource could pick up any piece of work.

The Demand is consider "Unallocated" but a comparison of total Supply and Demand is carried out by a few simple spreadsheet calculations which shows that we are broadly in balance (actually a whole 4 hours spare supply!)

If the Supply wasn't in balance with Demand, assuming we aren't going to reduce estimates (not a recommended strategy), we need to either:

• descope Demand
• increase Supply (extend duration of build phase, add more resources, work overtime, have a higher allocation to the build etc)

3 - Allocate Demand to named resources

In Step 3 we allocate scope to named resources. In the example, I have started with the initial build order and you can see that in one case, I have split some scope between two resources. 

This is a good reason why the sheet should be handed to the development team lead as detailed knowledge of the technology will allow decisions to be made as to when work can be split between several people and when this is impractical. 

When the work is allocated out, the "Allocated" cell is changed from No to Yes so that the Demand isn't included twice in error (Allocated and Unallocated). You should note that you can check the Supply/Demand balance both at an individual level and overall (considering unallocated build scope).

Based on this setup (i.e. there is enough work allocated to each resource), this allows build work to commence. It is an individual choice whether you should allocate out all scope or keep some as unallocated. The logic of the latter approach is that things don't always go to plan and so you might need to change allocations during the build phase if everything is fully allocated.

4 - End of week 1 Monitoring & Control

In step 4 we look at monitoring of progress against plan. It is now the end of the first week (although you could monitor daily if you want). So build commenced on Monday 6 January 2014 and it is now Friday afternoon 10 January.

The Development team leader speaks with their team and the build sheet and establishes the picture. Estimates to Complete (ETC) should be captured from the start of next Monday i.e. ask "EXCLUDING the rest of day, how many hours effort do you need to complete this element of scope?"

• Joe has finished the build of scope id S01 (Module A) and is part way through the Unit testing although this has proved more problematic than originally thought and the best view is that this will be complete on Monday i.e. 8 hours remaining
• John has made reasonable progress on S03 but believes another minimum three days effort is required. The development lead believes this is a bit optimistic so notes 28 hours ETC
• Richard has completed the build of scope id S05 and believes he will complete the unit testing (S05UT) on Friday afternoon or first thing Monday morning. A cautious estimate of 2 hours for Monday is noted.

Armed with this data, the Development team lead can update the build sheet.

Firstly, a reduction in SUPPLY needs to be made as one week has elapsed.

Next, the DEMAND side can be updated.

The date started and date completed can be entered with ETCs updated in line with the meeting held (see above). I would keep the ETC as 0 for completed scope to identify who did the work. Once all the updates have been made then look at the difference between the Supply and Demand both at an individual resource level and also considering unallocated work. With this example, much of the scope has been left unallocated so the sheet shows 7 hours shortfall in resource for the current ETCs.

The next step is to decide what to do about the shortfall, possible strategies might be:

• organise some overtime to get back to balanced position (or better) as soon as possible
• add resource
• deliver build late (the least favourable option)

Conclusion

I hope this worked example shows the basics of how to use the Build sheet and the simplicity of the approach. I know from its use within multiple Projects I have run is that Development Team leads can operate it effectively and as a Project Manager it gives me visibility on progress and whether we can still hit the end phase milestone. You should be able to write your own version with some simple spreadsheet skills.