Software Evaluation Spreadsheet Instructions

An explanation and step-by-step instructions for using the software evaluation spreadsheets available for download. The evaluation spreadsheets allow side-by-side comparison of several software packages and generate objective scores. This allows a software selection team to:

  • Quickly create a short list of 3-4 options.
  • Objectively compare the options.
  • Focus discussions on key differentiators between packages.
  • Focus software demos on key differentiators.
  • Reach a group consensus based on objective analysis.
  • Support the final recommendation to management based on objective analysis.
  • Avoid surprise functionality gaps after the software has been purchased.

1) Download the Evaluation Spreadsheet(s)

Visit the store to download the evaluation spreadsheet. There are three options:

  • Blank Evaluation Spreadsheet – Does not contain any requirements. You’ll need to create your own requirements.
  • Process Specific Evaluation Spreadsheet – Pre-populated with software requirements for a particular process. Currently only have a spreadsheet for project management, but hope to add more.
  • Software Specific Evaluation Spreadsheet – Pre-populated with software requirements and whether a particular software package meets the requirement. All you need to do is add any additional requirements, and prioritize the requirements to generate a custom score.

2) Update the Constants Tab

The Constants Tab contains the values that are used throughout the spreadsheet. Set these values to specify the software and set your scoring weights. The scores are generated by multiplying the values of: Priority, Process and Functionality Available?

  1. Set Priority – Each software requirement is assigned a priority value by the evaluators. Set the weight you want to assign to each of the priorities. If you want to make sure only software packages are considered that meet all ‘required’ requirements then set the value high (for example: 10 or 100).
  2. Process – The example below is populated with project management processes. You can change the processes based on the type of software you are evaluating.
    • Name – The processes listed under the ‘Name’ field define the values that can be selected in the dropdown field on the ‘Requirements’ tab. These are the processes you are going to use to group your software requirements. It is useful to filter the spreadsheet by process to evaluate the software.
    • Definition – These values aren’t used anywhere in the spreadsheet. However, it is useful to have these definitions so there is common understanding of each process’s scope.
    • Weight – The processes can be weighted based on which processes are most important to your company. This will skew the total score towards software that performs well in your most important processes.
  3. Software – Simply list the names of the software being evaluated. These names are used as headers in the ‘Requirements’ and ‘Scoring’ tabs. Leave the last name as ‘Max Score’. This is used to generate a maximum possible score to be used as a denominator when scoring as a percentage.
  4. Functionality Available? – This is the value used to evaluate each requirement. Update the weights based on how much you want to avoid configuration or customization.
Constants Tab Screenshot
Constants Tab (Click to View Full-Size Image)

3) Edit Requirements

If you are using a process specific or software specific evaluation spreadsheet, you’ll want to review and edit the requirements to suit your needs. If you are using a blank evaluation spreadsheet you can skip this step.

  1. Irrelevant Requirements – If a requirement is irrelevant you can simply delete the entire row. However, it is recommended that you simply set the priority ‘Low’ and even possibly set the weight of the entire process low in the constants tab.
  2. Unclear Requirements – If the requirement is unclear or can be clarified, you can edit the requirement description without impacting anything. The requirements are written in the Agile user story format broken into three fields. The Agile user story format is: ‘As a X, I want to Y, so that Z’. You can update:
    • User, ‘As a’ … – You many need to update this value to match your company’s role names. Utilizing Excel’s search and replace feature may be useful.
    • Requirement, ‘I want to’ … – Reword this value as you see fit.
    • Purpose, ‘So that’ … – Reword this value as you see fit.
    • Functionality Available? (Y/N) – If you’ve significantly changed the requirement and there is an existing assessment, you should delete this value so that it is clear the software should be re-evaluated for your specific requirement.
  3. Change Requirement Format – If you don’t use the Agile user story format for requirements, then you can modify columns E, F & G as you see fit and it won’t impact anything. You can hide the irrelevant columns. You can change the column headings.
Evaluation Spreadsheet Requirements Tab
Requirements Tab (Click to View Full-Size Image)

4) Add Requirements

If you are using a blank evaluation spreadsheet you’ll need to populate all the requirements. If you are using one of the pre-populated evaluation spreadsheets, there is a good chance you’ll have some specific requirements that aren’t on the list. If you have additional requirements, please provide feedback so they can be added to the spreadsheet. Don’t miss the last step in the list below, you must add a ‘Yes-As Is’ value to the ‘Max Score’ column on the right side of the sheet.

  1. Add to Bottom – Add the new requirement to the bottom of the spreadsheet on the first blank row. The spreadsheet currently supports 950 rows. If you exceed 950 requirements, God bless you, you’ll need to modify the equations in the ‘Scoring’ tab.
  2. Req ID – Assign a unique ‘Req ID’. This will be useful if you ever need to add software for side-by-side comparisons.
  3. Process – Select the relevant process from the dropdown list. The dropdown list values are defined in the ‘Constants’ tab.
  4. Activity – Provides a dropdown list which is a duplicate of the ‘Process’ values, but can be overwritten by entering any value you wish. If you want to control the values, here is how you setup a fixed dropdown list on Excel.
  5. Requirement Name – Enter a short descriptive name for the requirement.
  6. User, ‘As a’ … – Enter the role of the person who has the requirement. It is sometimes useful to track who suggested the requirement so you can ask follow up questions or have them test. You can either add a comment to the cell with the person’s name, or if you are comfortable with Excel add an additional column.
  7. Requirement, ‘I want to’ … – Enter the functional requirement (aka What).
  8. Purpose, ‘So that’ … – Enter the business reason for the requirement (aka Why).
  9. Score Max Score – The last software listed on the far right of the spreadsheet should be the ‘Max Score’. Whenever you add a requirement you need to set the ‘Functionality Available?’ value to ‘Yes – As Is’ so that the max score will be calculated properly. The max score is used as the denominator on the ‘Scoring’ tab to calculate the score as a percentage.

5) Set Requirement Priorities

Typically, the requirement priorities are set by consensus by the software evaluation team. This can be a grueling process if you have hundreds of requirements. Here is a suggested process to make it less painful:

  1. Set Priorities Before Software Demos – Set the priorities prior to reviewing the various software packages. This will make the evaluation more objective and the team won’t rank some favorite bell and whistle as ‘Required’.
  2. Product Owner Sets Priorities – Pick the person most knowledgeable about the process and the system requirements take the first pass at setting priorities. This is usually a product owner, process owner or super user who will actually be using the software.
    • Required – A must-have piece of functionality. If the software doesn’t have it, you’ll have to build it.
    • Medium – A nice-to-have piece of functionality that will significant value.
    • Low – Nice-to-have but of very low value to the business. You can live without it.
  3. Review Priority Distribution – Row 1-3 in Column H totals the requirements by priority. Make sure there is a reasonable distribution of the priorities. Make sure all requirements weren’t set as ‘Required’. If the distribution isn’t reasonable, re-explain the process to the product owner and have them re-prioritize.
  4. Team Reviews Priorities Individually – Share the evaluation spreadsheet with the rest of the selection team. Have them review the priorities individually. Have them add comments if they disagree with a priority.
  5. Finalize Priorities – Meet as a team to only review the requirements where there was a disagreement about the priority. Focus the discussion on reaching consensus (i.e. everyone can live with it even if it isn’t perfect).

6) Evaluate Requirements

Now the fun begins. Each requirement is evaluated and assigned one of three values. Comments are added to expand on the assigned value. This is sometimes referred to as a fit/gap analysis.

  1. Understand the Evaluation Values – You must set clear definitions of the evaluation values. Especially, what extent of low-code changes are considered configuration versus customization. In the end, you are dealing with the issue that with enough time and money any software can be made to do anything. Therefore, you’re trying to assess how much time and effort will be required to fulfill the requirement.
    • Yes – As Is – Meaning, the requirement is met with out-of-the-box functionality.
    • Yes – With Configuration – Meaning the requirement can be met with minimal configuration or no-code updates. Typically, this rating is used for simple things like changing picklist values, adding a field to an existing data object, creating a report, etc. Often, these requirements can be met by a business analyst or super user and don’t require a developer.
    • No – Not Available or Requires Customization – Meaning the requirement cannot be met without significant investment of time or money. Examples, would be writing custom code or extensive low-code efforts. Often, to meet these requirements would require work by a developer or software engineer.
  2. Confirm Accuracy of Software Specific Evaluation – If you’ve downloaded a software specific evaluation spreadsheet, the evaluation has already been performed. However, it is your responsibility to confirm the evaluation is correct. Every attempt is made to keep the evaluations up-to-date and correct but there are going to be some errors. It is especially important you confirm the accuracy of the evaluation for your critical requirements.
  3. Have Software Vendor Perform Self-Evaluation – Software sales reps are usually happy to complete these evaluations if the software is a significant expenditure. You have to take their evaluations with a grain of salt but it is very useful to develop a short list of options.
  4. Confirm Accuracy of Vendor’s Self-Evaluation – You need to validate the accuracy of the vendor’s self-evaluation for the most critical requirements or key differentiators. The key is you can focus your evaluations on the most important requirements. Software demos and free trials are an ideal way to perform these evaluations.

7) Combine Sheets for Side-By-Side Comparison

Once you’ve evaluated several software packages, you’ll want to place them into one spreadsheet so you can perform a side-by-side comparison. This will be an important tool for identifying key differentiators and to focus group discussions.

  1. Save New Spreadsheet to be Consolidated VersionSave one of the spreadsheets to be the consolidated version. You don’t want to use one of the original sheets in case you make a mistake at some point.
  2. Update the Software Values – Update the software names in column F of the ‘Constants’ tab.
  3. Sort by Req IDSort the ‘Requirements’ tab by ‘Req ID’ in both the source and target spreadsheets. If requirements are not consistent between the sheets, you’ll need to make them consistent (add extra requirements to all sheets).
  4. Copy Functionality Available Values – Copy the values to the appropriate column on the ‘Requirements’ tab. If the requirements or requirement IDs were not kept consistent, a simple copy and paste will result in the evaluations being assigned to the wrong requirements between the software packages. You then need to use a VLOOKUP command based on ‘Req ID’ to populate the spreadsheet for each software package.
  5. Skip Over Weighted Score – The ‘Weighted Score’ is a formula field, you don’t need to touch it.
  6. Copy Comments Values – Copy the values to the appropriate column on the ‘Requirements’ tab.

8) Review Software Scores

Now it is time to review the various scores for the software packages and perform a side-by-side comparison. If everything has gone smoothly, the scores should be automatically calculated on the ‘Scoring’ tab.

  1. Review Scores – Open the ‘Scoring’ tab where you’ll see weighted scores and percentage-based scores. If you are comfortable with Excel you can obviously build your own formulas.
    • Percentage of Required Functionality Met (Unweighted) – Gives a total of ‘Required’ requirements met either ‘As-Is’ or with configuration. This is a simple count, and doesn’t weight the requirements.
    • Total Score (Weighted) – The total weighted score and sub-totals by priority.
    • Total Score (Weighted) – The total weighted score and sub-totals by process.
    • Total Score Pct (Weighted) – The total weighted score as a percentage of Max Score and sub-totals by process.
  2. Identify Key Differentiators – Use the filters in the ‘Requirements’ tab to identify the requirements that differentiate one software from another. For example, the software packages may have similar scores for ‘Schedule Management’. Filter the ‘Process’ column for ‘Schedule Management’ to see where the functionality varies (i.e. ‘Functionality Available?’ values differ). You might also want to filter by ‘Priority’ to only review the ‘Required’ requirements.

That is how you use the software evaluation spreadsheets. Of course, the functionality score should only be one of several factors used in your final purchasing decision. You need to also consider price, usability and other factors.