Change Order Revenue % Explained: How to Track Scope Creep and Project Risk in Excel

How to Calculate Change Order Revenue as % of Contract in Excel

1. Create a Unique List of Project Managers

• Start by building a unique list of project managers from the PM Name field in your table.
• Use the UNIQUE function to pull each project manager only once.
• Sort the list alphabetically so it is easier to read and work with.
• This list will become the row labels for your analysis.

2. Create a Unique List of System Types

• Next, build a unique list of system types from the System Type field in the same table.
• Use the UNIQUE function again to remove duplicates.
• Sort this list alphabetically as well.
• This list will be used as the second dimension of the analysis.

3. Move the System Types Across the Top of the Table

• Since the system types need to run across the columns instead of down a column, convert that vertical list into a horizontal one.
• Use the TRANSPOSE function to flip the list across the top row.
• After that, copy and paste the results as values so the layout stays fixed.
• This creates the structure for a matrix with project managers on the side and system types across the top.

4. Build the Main Percentage Table with SUMIFS

• Use SUMIFS to total the change order value for each combination of project manager and system type.
• Then use SUMIFS again to total the base contract value for that same combination.
• Divide the change order totals by the base contract totals to return the percentage for each intersection in the table.
• This produces the core matrix showing change order revenue as a percent of contract for each project manager and system type combination.

5. Lock the References So the Formula Can Be Copied Correctly

• Before copying the formula across the full matrix, adjust the cell references so they behave correctly when pasted.
• Lock the row for the system type headers so they do not shift downward.
• Lock the column for the project manager labels so they do not shift sideways.
• This lets the formula update correctly across the table without breaking the lookup structure.

6. Copy the Formula Across the Full Heat Map

• Once the references are locked correctly, copy the formula across all system types and down all project managers.
• This fills in the full table with the percentage values.
• At this stage, you have a more detailed view than a single overall KPI because the data is sliced by both who manages the project and what type of system is being installed.

7. Add an Overall Row by System Type

• To create an overall view by system type, reuse the same logic but remove the project manager condition from the calculation.
• Keep only the system type filter in the SUMIFS setup.
• Copy that result across the row so each system type has an overall percentage.
• This gives you a higher-level comparison across system categories.

8. Add an Overall Column by Project Manager

• Next, create an overall value for each project manager by removing the system type condition.
• Keep only the project manager filter in the SUMIFS logic.
• Copy that formula down the column.
• This gives you a summary percentage for each project manager across all system types.

9. Add a Grand Total Percentage

• Create one overall percentage for the full dataset.
• Use the SUM function to total all change order values.
• Then total all base contract values.
• Divide the total change order value by the total base contract value to get the overall KPI for the entire dataset.
• This gives you the broadest possible view, which can then be compared to the more detailed breakdowns in the table.

10. Add a Target Cell for Benchmarking

• Create a separate cell labeled Target.
• Enter a benchmark percentage, such as 1.5%, that can be adjusted later.
• This gives users a simple threshold they can update based on their own expectations or operating standards.
• Because the target is placed in a single cell, it can drive dynamic formatting throughout the table.

11. Apply Conditional Formatting to Create the Heat Map

• Highlight the matrix and the summary values you want to monitor.
• Use Conditional Formatting with a rule that highlights cells greater than the target value.
• Link the rule to the target cell so the formatting updates automatically whenever the benchmark changes.
• This turns the table into a dynamic heat map that instantly shows which combinations are above target.

12. Test the Heat Map by Changing the Target

• Change the target percentage to a different value, such as 2% or 3%.
• Watch the formatting update automatically to reflect the new threshold.
• This makes the report more interactive and helps teams quickly identify where performance is outside the acceptable range.

13. Expand the Analysis Further

• Once the structure is built, the same approach can be reused for other cuts of the data.
• You can apply the same logic by crew, month, year, job type, or residential versus commercial.
• You can also group projects into ranges and run the same analysis by value bands using a bin structure.
• The key is that once the table layout and formula pattern are in place, the KPI becomes easy to analyze from multiple angles.