Track WIP Over/Under Billing in Excel
to Protect Cash Flow

How to Build a WIP Over/Under Billing Heat Map in Excel

1. Add the Supporting Columns to Your Data

Before building the summary table, create the two supporting fields used in the analysis:

• Add a column for the end of the month based on each project’s start date
• Use the EOMONTH function to return the final day of each project month
• Format the result as a date so the month-end values display correctly

Then add the second supporting column:

• Create a column for over/under billing
• Compare billed to date against earned revenue at completion
• This gives you the project-level variance used throughout the rest of the analysis

2. Create a Unique List of Months

Set up the first part of the matrix by listing the reporting months:

• Add a header for Month
• Use the UNIQUE function on the month-end column you just created
• Format the month values correctly
• Center the results if needed for readability
• Copy and paste the results as values using Ctrl + Shift + V

This creates the month list that will run down the left side of the matrix.

3. Create a Unique List of Job Types

Next, build the column headers for the different job categories:

• Use the UNIQUE function on the Job Type field from the table
• Sort the results alphabetically
• Transpose the list so the job types run across the top row instead of down a column
• Paste the transposed list as values
• Adjust the column widths so the headers display clearly

This creates the structure needed to analyze over/under billing by both month and job type.

4. Calculate the Monthly Average by Job Type

Now fill in the main body of the matrix:

• Use AVERAGEIFS to return the average over/under billing amount
• Average the over/under billing column
• Filter it by the selected month
• Filter it again by the selected job type

This gives you the average value for each month and each job type intersection.

5. Lock the References So the Formula Copies Correctly

Before filling the matrix across and down, adjust the cell references:

• Use relative and absolute references so the formula behaves correctly when copied
• Lock the month column reference so it stays fixed as you copy left to right
• Lock the job type row reference so it stays fixed as you copy top to bottom
• Copy and paste the formula across the matrix

This ensures each cell points to the correct month and job type.

6. Add the Overall Average by Job Type

Once the matrix is complete, calculate the overall average for each job type:

• Use AVERAGEIFS again
• Average the over/under billing column
• Filter only by job type
• Copy the formula across the full row of job types

This gives you an overall average for each job category, regardless of month.

7. Add the Overall Average by Month

Then calculate the overall average for each month:

• Use AVERAGEIFS
• Average the over/under billing column
• Filter only by month
• Copy the formula down the month list
• Format the results as currency using Ctrl + Shift + 4

This gives you an overall monthly average across all job types.

8. Handle Missing Combinations

Some month and job type combinations may have no jobs, which can create errors:

• Wrap the averaging logic inside IFERROR
• Return a dash or another placeholder when no records exist
• Copy and paste that updated formula across the full matrix

This keeps the table readable and prevents empty combinations from showing calculation errors.

9. Build the Heat Map with Conditional Formatting

Once the matrix is complete, turn it into a visual heat map:

• Select the full analysis range
• Go to Conditional Formatting
• Use Highlight Cells Rules
• Choose Less Than
• Reference a target cell that contains your threshold, such as zero

This highlights any values below your selected target.

10. Make the Threshold Adjustable

To make the heat map more flexible:

• Use a separate input cell for the target value
• Set the default target to zero if you want to flag anything below break-even
• Change the input to a negative number if you want to allow a wider tolerance range

This makes the heat map interactive and easier to use for different review scenarios.

11. Review the Results by Month and Job Type

With the matrix and heat map in place, you can quickly scan the results:

• Look for months with repeated negative values
• Identify which job types appear most often below target
• Compare the detailed grid against the overall monthly and job-type averages

This gives you a faster way to spot where billing performance is putting pressure on cash flow.

12. Optional: Filter the Source Data for Detail

After identifying problem areas in the heat map, you can inspect the raw job list:

• Apply a number filter to the over/under billing column
• Filter for values less than zero
• Review the specific jobs that were underbilled

This helps you move from summary-level insight to project-level follow-up.