Difference between price/volume variance at summary/total level

Hello. I've attached a sheet with price/volume level variances for subcategory as well as total levels. I would like to know why adding the detail level variances don't match up to the total variances. Someone said that $286.12 should be used for last year's average price but I didn't understand this. Additionally, I calculated a mix variance but I don't think that I did it right. All of the mix variances are negative, which doesn't make sense because in some categories you are moving towards a higher mix of higher priced products (product c for example). Please help

The total "Last Year Price" is hard coded in Cell I7 at 291.45.
If you replace the number with the formula =SUMPRODUCT(I4:I6,F4:F6)/F7, then the result should be 291.447453.....
Then there will be no variance.

No, that is not what I am asking.

Your first Question was, why the variances don't match:
- The total variance needs to be negative, the current sales are lower than the Prior year's sales.
Variance was $8.75, due to hard coding of the Last year total price.
2nd part of your question:
- Product C mixed result are negative due to your formula:
=$D$7*(I5-$P$4)*(E5-G5)
=2773*66.9719617742517*-0.021516379351524
The third part of the formula is negative, so it will end up negative. If you would have two negative numbers out of three, then it would end up positive.
There is an issue with your formula. What do you what to say with the result of mixed variance?
-for P4, why would you divide last year's sales by current year's volume??

1. I'm suggesting, do not hard code Totals.
2. suggested result see file.

Oh yeah, I didn't catch that. The total variance should be negative but that doesn't change my question.

If you look at the price variance and volume variance taken by summing up the same variances at the product level, it doesn't match up to the calculation at the top level. L8 does not equal L11 and M8 does not equal M11, but the total variance is equal and matches up to the -43,220. The hard coding doesn't matter, it doesn't what I'm asking ($8.00 is irrelevant, a rounding error).

Yes, I'm not sure my equation for the mix piece of the variance is correct, but I also don't think you understand what it is supposed to reflect. Part of the variance is due to a mix impact; that is, moving towards or away from different products in your mix. The equations that you put in for mix aren't correct.

I have the exact same question. Why does the price/volume variance equation produce different (offsetting) results if it is done at the summary level vs individual product level. Has anyone found an answer to this?