After my previous article about a structured process to develop a web application I received some requests from my readers which asked to me to dedicate a post about how to estimate times and costs of a web project.

In this articles I want to illustrate a simplified top-down process to estimate times and costs of a web process using a simple spreadsheet (in this example I used Google Spreadsheets but if you prefer you can use Microsoft Excel, OpenOffice Spreadsheet or a free online service such as Zoho or EditGrid).

Process main phases

In this simple top-down estimate process you can identify five main phases:

1. Define Activities
2. Define Task
3. Define Human Resources
4. Assign Human Resources to Tasks
5. Estimate times and costs

The process start with a general definition of macro-activities and with a detailed definition of tasks, human resources used, times and costs related to each task.

1. Define Activities

In this first phase you have to define the main activities which compose your project:


For example, in a generic web project you can identify the following main activities:

1. Requirements definition
2. Design
3. Implementation
4. Test
5. Release

In my spreadsheet I created a new sheet called Activityes and I added the following two columns:

A: WBS (work breakdown structure), the ID of each activity/task;
B: Activity name.

Next step is to detail each activity with a certain number of specific tasks.

2. Define Tasks

Each activity is composed from some tasks. Each task is a smaller piece of work which composes a main activity:


In the spreadsheet you can add new tasks adding new rows below related main activity. I suggest you to use a different format to highlight tasks from activities how I used in the following example:

1. Requirements definition
1.1 Define application scope
1.2 Define technical requirements

2. Design
2.1 Application Map
2.2 Database Entity relationship model
...

3. Implementation
3.1 SQL code
3.2 HTML code
3.3 CSS code
...

3. Define Human Resources

Next step is defining human resources in terms of category, seniority and hourly cost:


Each category has a specific hourly cost related to specific seniority. You can organize these information using a simple category/seniority matrix. For example if you have to estimate a big/medium size project you can identify the following categories:

- Analyst
- Programmer
- Project manager
- ...

and the following seniorities:

- Junior
- Senior
- ...

Now, define hourly cost for each category/seniority combination (in a more complex project you can also define a standard rate and an overtime rate for each combination). In the spreadsheet you can create the table above in a new sheet called Resources in the same spreadsheet. At this point you have two sheets:


A first sheet with activities and a second sheet with resources. In this way when you assign resources to tasks you can link the cost of a specific resource with a reference formula (=). This is a good practice because if you have to change the cost related to a specific combination category/seniority, you can do it only once in the sheet "Resources" and automatically all changes will be reported in all instances (task) which use that combination in the sheet "Activities"

4. Assign Human Resources to Tasks

Next step: assigning one or more resources to each task estimating the effort which a task requires. This is a very delicate activity because you have to calibrate the right combination between category and seniority of resources you want to use in your project in order to estimate correctly project times and costs.

In the spreadsheet, in the sheet "Activities" create the following three columns:

1. Num (number of resources assigned to a task)
2. Category
3. Seniority

This is the result:


You can add different resources to each task (different category or different seniority) simply adding a row below the task name (for example take a look at "Define application scope" where I added 1 analyst junior in the first row and 1 analyst senior in a new row below the task name).

5. Estimate Times and Costs

Now, for each resource, estimate the daily effort (Hours/day column), number of days (Days colum), get cost related to category/seniority combination from the sheet "Resources" using a reference formula (Hourly Cost column), and calculate Total costs:


For each task (row) Total Cost is equal to:


Take a mind some task could have specific costs which are indipendent from the number of resources you assign to that task. You can add this costs adding a new column to the left of the column Total Cost called "Additional Costs".

In this case Total Cost will be equal to:


That's all. Take a look at the spreadsheet or copy it in your Google Documents account to reuse it.

Take also a look at these posts:

- Structured process to develop a web application
- Google Spreadsheets Gantt Chart (Microsoft Project-like)
- Google Spreadsheets: formulas tutorial
- Google Spreadsheets Tips: Add custom charts
- Project Management: Excel Gantt Chart Template

I hope you'll find this post useful. If you have some suggestions about this process or if you want to suggest some interesting link related to this topic please add a comment, thanks!