Using Excel for IT resource estimation

I am big fan of Excel, and I know it has a ton of features. I’ve been using it for more than two decades and still discover new things it can do… All backward compatiable. Such a wonderful product…

This post is about a template and couple of neat little tricks you can use to save good time in your resource estimation exercises.

Here is the scenario: You are sending a proposal to a client for application management services for a period of 3 years. That is, you will be giving some technical experts to support queries and resolve issues for their business users. Since it is a new product for the client you expect heavy support requirement during the inital 6 months of use, and then gradually reducing over the remaining time. So how do you estimate resource requirements for this? Even with Excel, if you do it manually, it is difficult and confusing. Here are a couple of tricks, explained in some detail.

First: The template

A bit of a busy template… Let me try to break it down.

1) On top are the legend and a small table to calculate how many hours are in a man-month. Please take note of the legand color scheme as it is important to understand the template. The man-month thing is to accomodate variation in work-hours and work-days

2) Section A is an estimate of effort percentage. As noted in assumption, the first six months will require the most effort. This maxiumum effort is considered as 100%. The following months and years shows smaller effort compared to this peak effort. As can be understood, this model is very flexible and allows custom efforts each month to (say) allow for peak seasons.

3) Section B is to derive number of support tickets based on one peak ticket volume (in the first month). Every cell in this table is this peak ticket volume multiplied by the percent effort for that month. Note: It is also possble to work with higher ticket volume than the peak month by tweaking the effort percentage (say, to 125%)

4) Section C is the breakup of a months ticket in to EASY, MEDIUM and HARD tickets. It also allows to capture work hours required for each type of ticket on average. This and section B are the inputs to next section (D)

5) Section D then converts the monthly support ticket estimate to monthly support hours estimate based on easy/medium/hard breakup (Section C)

6) Section E. Each monthly support hours is then divided by hours per man-month to arrive at number of resources/ head count/ people required each month

Neat trick # 1

All the formulas can leave even master Excel users a but cross-eyed… Excel provides two nice ways to understand the formulas in use. First method may be familiar to most: Click on FORMULAS and then on SHOW FORMULAS.

It will change the template to something horrendous as shown here. However, I believe it is fairly usable

There is a second method. And yes, it is more human friendly for the same purpose. But there is something to understand before seeing this second method.

Every such model has key inputs (here, the pivot value) and key outputs (here, average resource per month). Data and calculations flow from the input to output like a near logic circuit. Excel provides a nice way to visualize and understand this logic flow.

To get this, first click on the target output value (here, average resource per month) and then click on FORMULA > TRACE PRECEDENTS

Every time you click this button, you will see blue lines forming. Keep clicking this button until nothing seems to happen anymore. In this template, you will end up like this:

Beautiful isnt it? This is the logic circuit we’ve put together! Play around with this to understand more. Oh.. to remove these blue lines after you’re done with them, click the button REMOVE ARROWS in the same ribbon (below the TRACE buttons). Now time for next neat trick

Neat trick # 2

Many a times, you will be in the unfortunate position of tweaking your estimates from hard gained experience to make your final proposal more saleable for your sales team, and more buyable for your customers. This means we have to start with average resource per month, and arrive at a number of tickets per month (or any of the other combinations). You can do this two ways.

1) Trial and error. Keep changing numbers till the result is acceptabe to all parties. This is often messy but a good method because it uses human intelligence implicitly

2) Brute force. Get Excel to do the heavy lifting. Not the best, but certainly the fastest. This is part of TOOLS menu which is not very apparent in modern Excel. But thanks to backward compatiability, if you are a keyboard warrior like me, this is very accessible. Here’s how to use it: Click on the goal cell (average resource per month) and then press ALT + T + G in quick succession (Tools > Goal seek). You will be presented with a nice little window like this:

Our average resource per month is now 15. Your sales guy is adamant that the client will not accept any more than 12 people. So how will that affect your tickets per month (for example). Set the “To value” cell to 12 (the ask from sales) and then set the “By changing cell” to the pivot value cell.

Did you see the magic? Spoiler: The pivot value of ticket now changes from 300 to 240 tickets for Jan of Year 1.

Hope this helps a lot of you. In case you want a copy of the template, drop me a mail via “Contact me” and I will send you one.

Thanks and best of luck…