Excel Templates

Analogous Estimating – Excel Tutorial

Posted on by Anthony James
30
Sep

In this article, I’ll show you how to setup Analogous Estimates formulas in Excel to support your Cost Management Plan.

Explanation:

Analogous estimating is a top-down approach that leverages data from similar past projects to estimate the cost of a new project. It is faster but less precise than other methods, as it assumes that historical projects are a good predictor of the current project.

Step-by-Step Guide for Excel:

  1. Define Historical Projects:

Collect data from similar projects completed in the past, including the project name, the total cost, and the key metrics that influence the cost (e.g., duration, team size, complexity).

  1. Define Current Project Characteristics:

List the characteristics of the current project that correspond to the metrics from historical data.

  1. Excel Calculation:

Based on the similarity between the current project and past projects, assign a cost estimate to the current project. This can be done by calculating an average cost from historical projects or adjusting based on known differences.

Sample Data to Import into Excel:

Historical Project

Team Size

Duration (Weeks)

Complexity (Low/Med/High)

Total Cost ($)

Adjustment Factor

Adjusted Estimate Formula

Adjusted Estimate ($)

Project A

5

10

Low

50,000

1.05

=E2 * F2

52,500

Project B

8

12

Medium

80,000

1.10

=E3 * F3

88,000

Project C

12

15

High

120,000

0.95

=E4 * F4

114,000

Current Project

10

14

Medium

N/A

1.00

=(AVERAGE(E2) * F5)

84,833.33

Explanation of Sample Data:

  • Historical Project: This column contains the names of past projects.
  • Team Size: The size of the project teams for each historical project.
  • Duration (Weeks): The length of each project in weeks.
  • Complexity: The overall complexity level for each historical project (e.g., Low, Medium, High).
  • Total Cost ($): The actual cost for each historical project.
  • Adjustment Factor: A multiplier to adjust for differences between the historical and current projects. For example, if the current project is slightly larger or more complex than a similar historical project, apply an adjustment factor (e.g., 1.05 for a 5% increase in costs).
  • Adjusted Estimate Formula: This formula applies the adjustment factor to the historical cost to estimate what the cost might be for the current project if it were similar to the historical projects.
  • Adjusted Estimate ($): The final estimated cost for the current project, based on historical data and adjusted for any differences.

Formula Breakdown in Excel:

  1. For Historical Projects:
    • In cell G2: Enter =E2 * F2 to calculate the adjusted estimate for Project A.
    • In cell G3: Enter =E3 * F3 for Project B.
    • In cell G4: Enter =E4 * F4 for Project C.
  2. For the Current Project:
    • In cell G5: Enter =(AVERAGE(E2:E4) * F5) to calculate the estimate for the current project based on the average cost of the historical projects and any adjustment factor.

How to Use the Table in Excel:

  1. Historical Data: Populate the rows with actual data from completed projects, including team size, duration, complexity, and total cost.
  2. Adjustment Factor: Adjust the factor based on known differences between the current project and the historical ones. For example, if the current project is larger or more complex, increase the factor slightly.
  3. Current Project Estimation: Use the average cost of the historical projects to generate an estimate for the current project, and apply any necessary adjustments to account for differences in scope or complexity.

Downloadable Excel Setup:

In your Excel sheet:

  1. Label the first row with Historical Project, Team Size, Duration (Weeks), Complexity, Total Cost, Adjustment Factor, Adjusted Estimate Formula, and Adjusted Estimate.
  2. Input historical project data and use the formulas in the Adjusted Estimate column to calculate the cost estimate for the current project.

Sample Interpretation:

  • Project A had a team size of 5, lasted 10 weeks, and cost $50,000. Adjusted for a 5% increase (Adjustment Factor: 1.05), the estimate for a similar project would be $52,500.
  • Current Project has a team size of 10, lasts 14 weeks, and has medium complexity. Based on the historical average costs and no adjustments (Adjustment Factor: 1.00), the estimate for the current project is $84,833.33.

This Excel template makes it easy for the user to leverage historical data for analogous estimating while allowing flexibility to adjust estimates based on known project differences.

Download Excel Templates

Use these Cost Management Plan templates to establish a framework for estimating, budgeting, and controlling the costs of your IT project. Includes Explainer Guide to configure the Excel formulas.

MS Word

  • Cost Management Plan: 41 pages
  • Explainer Guide: 15 pages

MS Excel spreadsheets

  • Analogous Estimating
  • Bottom-Up Estimating
  • Parametric Estimating
  • Three-Point Estimating

Download: Cost Management Plan templates (MS Word + Excel)

Anthony James