Software Cost Estimation With Use Case Points – Free Excel Spreadsheet

apple technology by diego medrano

We just completed a series of articles detailing how to use Use Case Points for software cost estimation. In this article we have a free MS Excel Spreadsheet for calculating use case points. Download it today to make it easier to do your project cost estimations.

Free Excel Spreadsheet Download

Download the use case point spreadsheet (zipped) for free today from Tyner Blain.

Background

This is the seventh article in a series on applying use case points to create reliable software cost estimates. What makes use case points different is that they allow the project cost estimation to happen much earlier in the process. This cost estimation technique was developed by Gustav Karner for Rational Software Corporation in the mid 1990’s.

The introduction to software cost estimation is the right place to start if you came to this article first. In the previous articles we discussed:

  1. Technical Factors of the Implementation. Characterizing (non-functional) requirements of the software.
  2. Environmental Factors. Describing the team and the process.
  3. Use Case Quantity and Complexity. Representing what the software is asked to accomplish.
  4. Actor Quantity and Complexity. Enumerating the users of the software.
  5. Final Calculations. Doing the math.

The free excel spreadsheet in this article will do the math for you. It provides an easy way to organize (and edit) your estimates, and presents the final calculation results for you.

Using the Use Case Points Spreadsheet

The spreadsheet has five tabs, one for each area of data collection and processing. The tabs map directly to the individual articles in the series (linked above). Each tab also includes links back to the articles for future review.

spreadsheet tabs

To calculate the use case points, you only have to fill in the highlighted (yellow) cells in each tab of the spreadsheet. All of the math is done for you.

UCP Technical Complexity Factors

Enter the relative magnitude of each technical factor. Brief descriptions of each factor are included for quick reference.

UCP Environment Factors

Enter the relative weightings of the environmental factors for the project. Brief descriptions are included for reference.

UCP Environment Factors Spreadsheet

UCP Use Case Analysis

Enter the names and complexity of each use case.

Note that the complexity values are selected from a drop-down instead of being typed in. This allows for automatic calculation, just from listing the use cases.

UCP Spreadsheet Dropdown

UCP Actor Analysis

Enter the names and types of all actors that will use the system.

UCP Actor Analysis Spreadsheet

The actor analysis section also uses a drop-down to select the type of actor.

UCP Final Calculation

Return to the final calculation tab at the front of the spreadsheet.

UCP Final Calculation Spreadsheet

All of the work has been done for you. If you want to use a different ratio for converting from use case points to hours of effort, just change the highlighted value.

Conclusion

This concludes our series on software cost estimation with use case points. With this free excel spreadsheet, you don’t have an excuse for not calculating the use case points on your project. The time you will invest is minimal. The value may be substantial.

  • Scott Sehlhorst

    Scott Sehlhorst is a product management and strategy consultant with over 30 years of experience in engineering, software development, and business. Scott founded Tyner Blain in 2005 to focus on helping companies, teams, and product managers build better products. Follow him on LinkedIn, and connect to see how Scott can help your organization.

13 thoughts on “Software Cost Estimation With Use Case Points – Free Excel Spreadsheet

  1. How can the ratio number be defined ?
    What kind of information should I base it on ? I don’t understand it. How did you come up with 28 ?

  2. Rodrigo, great question, and welcome to Tyner Blain.

    The ratio – 28 hours per use case point, is a realistic, but otherwise arbitrary number. It can absolutely be changed.

    Here’s the approach I suggest.
    1) Estimate use case points for your project.
    2) Use a ratio, say 28, as a “best guess” to project estimated hours
    3) Track the hours spent on the project.
    4) Use the actual value (from 3) and the estimate (from 1) to compute an updated ratio.
    5) Use the updated ratio, for your team, for your next project, and repeat steps 1-4.

    You should also review to determine if your estimates (per 1) were off, and include that feedback loop so that you get better estimates of the number of use case points. Over time, your ability to estimate / predict will improve.

  3. Pingback: miguek
  4. Hi, shouldn’t the multiplier at Environmental tab be negative for point 6?
    I don’t get why “Stable requirements” reduce the total Environmental Factor when using higher numbers, as “Higher numbers represent more change (or a less effective system for managing change)”
    Thanks!

  5. just one question: The effort estimated in this method (784 hours in example) apply for the complete development cycle (analysis + design + codification + testing…. ) or just applies to codification stage.

    Thanks to all .

    1. Whichever way you want to estimate it. When you establish your baseline for converting from points to hours, use whatever you want to measure for your baseline, and that will yield the same scope when estimating effort

  6. Pingback: Juan Pablo Moya
    1. Well, you cannot truly validate the estimation without doing the task, measuring how long it took, and reconciling with the estimate. This activity, however, will help you in increasing your confidence in using this estimation approach (or any other) for future projects. Estimates are ultimately just predictions of the future. I wrote more about the philosophy of prediction in this article on PERT estimation and this article on agile release planning.

Leave a Reply to Scott Sehlhorst Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.