Weighted Averages

Hello Kelly,

Thank you for your question. You can calculate a weighted average for the margin erosion in Excel, considering the varying project sizes. Here’s how you can approach it:

Set Up Your Data:
Create a table with columns for:
Project Name (if applicable),
Revenue,
Original Gross Profit,
Current Gross Profit.

Add a column for Gross Profit Erosion (difference between Original and Current Gross Profit).

Calculate Gross Profit Erosion:

In a new column (say, Column E), calculate the erosion for each project:

= Original Gross Profit - Current Gross Profit
For example, if Original Gross Profit is in Column C and Current Gross Profit is in Column D, the formula in Row 2 would be =C2-D2.

Weight the Erosion by Revenue:

Create another column to calculate the weighted erosion for each project:
= Gross Profit Erosion * (Revenue / Total Revenue)

Here:
Revenue is the project’s revenue.
Total Revenue is the sum of all projects' revenue, which you can calculate using =SUM(Column_of_Revenue).

Sum Up the Weighted Erosion:

Use the SUM function to add up all the weighted erosion values. This will give you the weighted average margin erosion across all projects.

Example Formula:
If:
Gross Profit Erosion is in Column E,
Revenue is in Column B,
Total Revenue is in Cell B10 (calculated as =SUM(B2:B9)),

The formula for Weighted Erosion in Row 2 might look like this:

= E2 * (B2 / $B$10)
Drag this formula down for all rows and then sum up the weighted erosion column to get the weighted average.

I hope this makes sense. Please let us know if you need further assistance.

Kind regards
Marius Barnard