Sample estimation sheet

Project estimation with excel

Perform cost and time estimation with leveled resource without complex macros

Excel has proven to be a very versatile tool for solving a lot of different use-cases. This article focuses on using excel to perform quick project and cost estimations for projects that have well defined activities and the focus is on estimating the delivery costs based on different team models while achieving optimal allocation for duration of project.

The template shown above is available as download. Please note that this document does not contain any macros and has been created using out of box features. The rest of the article focuses on how the template works so that you can create one from scratch or an develop an alternate version that works for you.

The excel sheet has the following sheets

1. Guide — provides basic guidance on how to use the template
2. ResourcePlan — contains the allocations, cost estimates and resource leveling state
3. REF — has reference information used by ResourcePlan

Reference Data (REF)

The REF sheets contains the following details

1. List of activities that may be applicable to a project
2. Resource table which has name of team member and associated level of the person
3. Level table contains various Levels and associated cost per hour

REF sheet of workbook

The Level column (column F above) in Resource table has Data Validation setting that references the List of Levels (column H above) as shown below

Data Validation for Level in column F

Resource Plan

The ResourcePlan worksheet is used to capture the member allocation to various activities in two areas i.e. activity area (1) and allocation area (2) as shown below

Resource Plan worksheet

End-user would typically populate the activities(1), associated resource (1) and the % allocation during a particular week (2). All the other columns will be populated automatically with appropriate values. Color coding on the bottom half will show any resource that is over allocated in the given week.

Data validations

The following data have been setup on the worksheet

1. Activity ($B$2:$B$100) — Data validation is a List of values based on Activity column on REF worksheet (REF!$B$2:$B$50)
2. Resource ($C$2:$C$100)- Data validation is a List of values based on Resources column on REF worksheet (REF!$E$2:$E$50)

Category Format

The following columns and rows have been specifically category formatted

1. Allocation area ($J$2:$AK$100) has been identified as Percentage with 0 decimal places.

Conditional Formatting

Conditional formatting allows quick identification of cells that match specific conditions. The following conditional formatting rules were set up on the worksheet

1. Allocation area ($J$2:$AK$100) was setup to show content in specific color if the value of cell is > 0. This allowed for quick identification of how the various activities are spread over the duration of project.

Allocation area formatting rule

2. Leveling area ($J$103:$AK$153) — has two rules to identify the overallocated (value between 1.00001 and 1000) cells with red background and basic allocation (value between 0.0001 and 1.0001) cells with different background.

The reference to exact value is required due to the way “” (cells with empty string set as part of formulae) value is treated as a numeric value during conditional rule evaluation.

Reference

The Resource field ($C$103) uses formula (REF!E2:E50) to automatically populate all the resources defined in REF tab in that column below the cell.

This spilling effect removes the need to explicitly select the resources as part of setup.

Lookups

The lookups (more specifically VLookup) was used to automatically populate the Level and Price based on resource selection

1. Level ($E$2:$E$100) uses formula as IF(C2<>””,VLOOKUP(C2,REF!$E$2:$F$50,2,FALSE), “”) in $E$2 to lookup the Level corresponding to Resource selected in C2 using the resource/level table in REF sheet spread across $E$2:$F$50 and the value in the 2nd column (i.e. $F$2:$F50).
2. Price ($F$2:$F$100) uses formula as IF(E2<>””,VLOOKUP(E2,REF!$H$2:$I$50,2,FALSE), “”) in $F$2 to lookup the Price corresponding to Level value in E2 using the level/pricing table in REF sheet spread across $H$2:$I$50 and the value in the 2nd column (i.e. $I$2:$I50).

Note the use of $ to lock the values that should not change as the formula is copied across the entire column. For example in formula VLOOKUP(E2,REF!$H$2:$I$50,2,FALSE), only the E2 will change as the cell is copy pasted in the other cells of the column since all other cell references have been “locked” by applying the $

Note the use of IF to ensure that columns are not auto-populated with error values (#NA#, #VALUE#, 0, etc) in case the input value has not been set.

Calculations

The costs are calculated based on simple formula IF(F2<>””,$D2*F2*5*8,””) across Planned Cost and Calculated cost

Complex calculations

The Calculated Duration and resource leveling for week, use a bit complex formula for calculation

1. Calculated Duration uses IF(SUMIF($J2:$AK2,”>0")>0,SUMIF($J2:$AK2,”>0"),””) to identify and sum all the row cells that have a value greater than zero. Note use of $ before column name but not before number to lock the column as the formula is copied to entire column.
2. Resource leveling area uses the formula IF(SUMPRODUCT(($C104=$C$2:$C$100)*J$2:J$100)>0, SUMPRODUCT(($C104=$C$2:$C$100)*J$2:J$100),””) to add all the values in column J (Week 1) for the rows with resource ($C$2:$C$100) matching the selected resource Name ($C104). This process allows us to summarize the total allocation of a resource ($C104) in a given week (J$2:J$100).

Additional updates and changes can be made to add support for other features typically needed for project estimations like the following to name a few

1. identifying differences between planned allocation and calculated allocations
2. support for more detailed activity classifications like simple, medium, complex and associated time
3. Support for additional parameters like travel time, profit calculations, etc.
4. Copy the worksheet to develop multiple estimation models

Project estimation can be significant challenge during pre-planning/proposal phases but having a simple tool like the one shared here can significantly help develop and evaluate models without need for complex tools.