How to estimate a project (using Microsoft Project)

Almost anyone who works in an industry that sells services will sooner or later be asked to create an estimate. Most people have limited skills in this area: they’re trained to perform some kind of specialist work like software development or report writing, while estimating is typically left to specialists. Where there are no specialists (pre-sales estimators, for example), projects can be off course even before being sold. In most services firms, incorrectly estimated work has a costly and negative impact. Vague estimates directly impact the bottom line, but they also affect your reputation.

So why are we so bad at estimating project effort? Most people can say (approximately) how long a task will take them to perform. What’s much harder is to differentiate between the ideas of ‘effort’ and ‘duration’ (most people actually don’t know the difference, so it’s important to explain the definition of what we’re asking them to provide).

Effort is the amount of work that has to be performed to complete the task (regardless of who performs the task, how many people perform the task, and what unrelated other tasks the performers may also have to work on). Duration can only be calculated once we determine who will perform the task, how many people are going to perform the task, and whether they’re available to perform the task at a reliable level of availability.

There are different ways of creating estimates for projects involving more than one person. A common approach is to ask everyone to provide their task estimates in a spreadsheet and then consolidate the information. Spreadsheets have the advantage that they’re ease to use, and most people have Excel available to them. The big drawback of using a spreadsheet-based project estimation process is that a spreadsheet is essentially a ‘flat’ tool that doesn’t easily lend itself to telling the ‘story’ of the project. The story is the narrative of how the activities will unfold on the project’s time line.

Why is the narrative important for estimating? In one sense, it isn’t. Many flat estimates work quite well as long as they’re created carefully. But using a project management tool like Microsoft Project to create an estimate—and being able to automatically use its built-in scheduling capabilities—has one key advantage: we can easily include estimating variables that are based on parameters related to the project schedule. One example might be: the project manager needs to spend a few hours every week compiling and sending out a status report during the entire lifetime of the project. If you were using Microsoft Excel for your estimate, you’d first have to do some back-of-the-napkin calculations to figure out how long your project will take before including another, unconnected estimate line item for “weekly status reports.” MS Project, in the other hand, can be set up to maintain these tasks automatically and calculate the correct values, even if you subsequently change the project’s schedule (for example, by adding to the core work estimate at a later stage).

Using a series of screen shots from Microsoft Project 2010, I will take you through a simple but solid project estimating process from end to end. The subject matter of what we’re going to estimate is basically irrelevant—we’ll use a very simplified software development project, but you can use this process to estimate any team-based project where the primary activity is work.

What we’re trying to determine by creating this estimate is:

• How many hours of effort will this project take?
• How much will it cost to deliver this project?
• Approximately how long will it take?

There are many aspects of project estimating that I won’t be able to cover in this simplified outline. But I’m hoping that you’ll be able to build on the basic idea by trying it for yourself and playing around with MS Project (it can be somewhat forbidding at first but Google has a lot of useful answers).

(You can click on any of the images below to see them bigger.)

Let’s get started. Open MS Project. At the bottom left of the screen, switch it into Auto Schedule mode:

Next, select the “Project” tab and click on Project Information.

Your only objective here is to pick a reasonable start date for the project. Set it to some future date (pick a Monday):

Okay, now that we’re done setting up our basic project, let’s switch to the Resource Sheet. Our objective here is to create a definitive list of everyone who’s going to work on the project. Start by selecting the leftmost button on the “Task” tab:

Fill in the columns as indicated below. In the Resource Name column, give them names or roles (1). Initials can be helpful, so fill those in, too (2). Finally, fill in some billing rates (or cost to company, it doesn’t really matter) (3).

Switch back to your Gantt chart (using the same drop-down menu you used to get to the Resource Sheet). Now, let’s add two additional columns that we’ll need for our estimating to work.

Highlight the Duration column in the header and right-click (1). Then, select Insert Column from the context menu (2). In the ensuing column name selector, type Work. This inserts the Work column. Next, highlight the Work column, right-click and insert another new column. This one is called Cost. (Both Work and Cost are predefined entities in Microsoft Project, so nothing else needs to be defined.)

Your worksheet should now look something like this:

Now we’re ready to enter some data into our estimate. Start with the name of the project: “My Software Project.” Hit Enter and move to the next line. Start typing in some tasks in the order that they’ll occur. You can see the ones I added below:

Ignore all the durations and dates for now. Instead, highlight the tasks underneath the project name task (1), and indent them to group them (2) as shown in this screenshot:

To make things a little easier to see, let’s hide away some of the columns we don’t need right now. (This is an optional step. You don’t have to do this if you have a large screen and can comfortably see all the columns plus the Gantt chart on your screen.) Highlight the columns labelled Start, Finish and Predecessors by clicking on their headers (1), then right-click  and select Hide Column (2):

The next step in our estimating process is to tell MS Project who is going to be working on each task. Obviously, it wouldn’t make sense to have everyone work on every task. Different people specialize in different activities. Certain tasks, however, need to be performed by groups of people—meetings, for example. In the Resource Names column, there’s a drop-down menu you can use to populate each task with the correct resources from your Resource Sheet. In my example below, the kick-off meeting task is highlighted, and it makes sense for the whole project team to attend this meeting, so I’ve highlighted everyone.

Other tasks, lower down, have only a single resource assigned. You can see my first 6 project tasks with resources assigned here:

Don’t be alarmed by the obviously incorrect ‘estimates’ that MS Project is creating while you do this. It’s guessing how long tasks will take because we haven’t given it the correct information yet.

Next, using the Duration column (I know this seems counter-intuitive), type in the amount of time each task is going to take. You can use ‘2h’ for ‘2 hours’ and ‘4d’ for ‘4 days’ (mixing and matching between hours and days is fine). For example, our team kick-off meeting is going to be 2 hours long, so I entered ‘2h’ into the Duration column.

Notice how MS Project automatically calculates the Work column and displays 12 hrs there? This is in fact correct because six people are going to attend this 2-hour meeting. Notice also that Project automatically calculates the total cost of the team kick-off meeting using everyone’s cost as set up in the Resource Sheet. Finally, take a look at the roll up task at the top—we called it “My Software Project” and moved all other tasks below it. The roll up task is starting to show the overall cost of the project. Now we’re getting somewhere.

Now, I suggest that you take a few moments and repeat the above steps by creating a second section in your project and entering some more tasks, grouping them into two distinct project phases (in my example, a Requirements Phase and a Construction Phase), adding the correct resources into the Resource column and providing estimates in the Duration column. You can use the indenting function to group things correctly.

Once you have completed this exercise, you’ve successfully created your first project estimate in MS Project. We will now move on to create some rudimentary scheduling so that we can learn how to add a task that spans the entire length of the project, but if you wanted to stop here, you could. MS Project has correctly calculated the hours required to complete your project, and the project cost.

The next step is to ensure that our tasks are linked. This simply means telling MS Project that one task follows another. Since we took great care earlier to enter our tasks in the correct order, this won’t be difficult. Simply highlight all the tasks in your Requirements Phase section of the estimate (1), and use the Link button as shown (2):

Do this again for the tasks in the Construction Phase.

Next, let’s relate the two phases to make them follow one another. Highlight just the lines that say Requirements Phase and Construction Phase (1) and click on the Link button (2):

Finally, let’s use MS Project to help calculate an “overhead” task such as weekly project management activities. Tasks like this aren’t actually difficult to calculate—you could just eyeball the Gantt chart and see how many weeks the project will take and then enter that value into its own task line item—but it’s better to automate this so that you can change things later and the overhead task will automatically remain correct.

What we want to do is create a so-called “hammock task” (it’s called that because it spans the length between two poles, the project start and end dates).

First, enter an ordinary task. Call it “Weekly status activities:”

Next, let’s bring our Start and End columns back (we hid them earlier). If you didn’t hide yours, you can ignore this step. Navigate to the far right of the worksheet where you will see a column called “Add New Column.” Click it and enter Start. Repeat this to also get back your End column.

Now, double-click on your Weekly status activities task. You’ll get the following dialogue box. Enter the resource name (Project manager):

Now, enter the units that the PM is going to spend working on this task. I think 10% is probably about right—for a typical 40 hour week, the project manager will be spending about 4 hours per week on status activities. Enter a percentage here that makes sense to you.

The final step is to make our hammock task span the entire length of the project. This is done by linking the start and end dates of the overall project to the start and end dates of the hammock task. In your worksheet, navigate to the first task of the project (the kick-off meeting). Highlight the date in the Start column, right-click and select Copy:

Now, navigate back to your hammock task (Weekly status activities), select it, right-click and select Paste Special. In this dialogue, select Paste Link on the left (1) and then hit OK (2):

Repeat this process in order to copy a link to the project end date into the hammock task’s end date (the end date in my example is the end date of ‘Deploy to server’). You will now see the hammock task span the entire length of the project, and it will correctly calculate the overall hourly estimate and cost of this overhead activity.

That’s the basic process for estimating project work effort in Microsoft Project. It’s not really complicated once you’re familiar with the flow of tasks. It is absolutely crucial to follow the correct entry sequence, however. MS Project’s automatic calculating capabilities are very powerful but can also seem unpredictable (especially if you don’t use it daily). It’s crucially important to correctly configure separate Work and Duration columns, for example, and to enter information in the right sequence to get reliable results. As a reminder, the correct workflow for an estimate is:

1. Set up the Resource Sheet
2. Enter tasks in the the sequence they’ll be performed
3. Who will perform each task? — Add the correct resources to tasks
4. Enter estimates (in hours or days) into the Duration column.

Good luck with your estimates, and let me know your ideas and experiences in the comments below.