Excel Templates

Three-Point Estimating – Excel Tutorial

Posted on by Anthony James
30
Sep

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

  1. Previous: Bottom-Up Estimating
  2. Next: Parametric Estimating
  3. Download: Cost Management MS Word and Excel Templates

Context

To help users perform calculations for Three-Point Estimating using Excel, we’ll provide a detailed explanation and include sample data. The three-point estimating method is based on gathering Optimistic, Most Likely, and Pessimistic estimates to calculate a weighted average, improving the accuracy of cost estimation.

Three-Point Estimating Formula:

The formula for Three-Point Estimating is: Three-Point Estimate=(Optimistic+4×Most Likely+Pessimistic)6text{Three-Point Estimate} = frac{(text{Optimistic} + 4 times text{Most Likely} + text{Pessimistic})}{6}Three-Point Estimate=6(Optimistic+4×Most Likely+Pessimistic)​

This gives more weight to the most likely estimate while still factoring in the optimistic and pessimistic estimates.

Step-by-Step Guide for Excel:

Define Your Estimates:

  • Optimistic (O): The best-case scenario for the cost.
  • Most Likely (ML): The most probable cost.
  • Pessimistic (P): The worst-case scenario for the cost. 

Excel Calculation:

Create a simple table with the three estimates and use Excel to calculate the final estimated cost using the Three-Point Estimating formula.

Here’s the data structure you can use in Excel:

Sample Data to Import into Excel:

Task

Optimistic (O)

Most Likely (ML)

Pessimistic (P)

Three-Point Estimate Formula

Three-Point Estimate

Software Design

10,000

15,000

22,000

=(B2 + 4*C2 + D2)/6

15,333.33

Development

25,000

35,000

50,000

=(B3 + 4*C3 + D3)/6

36,666.67

Testing

5,000

7,500

12,000

=(B4 + 4*C4 + D4)/6

7,833.33

Deployment

8,000

11,000

15,000

=(B5 + 4*C5 + D5)/6

11,166.67

Formula Breakdown in Excel:

For each task, use the formula in Excel:

  • In cell E2: Enter =(B2 + 4*C2 + D2)/6 to calculate the Three-Point Estimate for Software Design.
  • In cell E3: Enter =(B3 + 4*C3 + D3)/6 for Development.
  • In cell E4: Enter =(B4 + 4*C4 + D4)/6 for Testing.
  • In cell E5: Enter =(B5 + 4*C5 + D5)/6 for Deployment.

This structure provides a clear and accurate calculation for each task’s cost estimate, factoring in uncertainty.

Downloadable Excel File Setup:

In your Excel sheet, you should:

  1. Label the first row with Task, Optimistic (O), Most Likely (ML), Pessimistic (P), Formula, and Three-Point Estimate.
  2. Enter the sample data in the columns.
  3. Use the formulas in the Three-Point Estimate column to automatically calculate the final estimates.

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