Logical Help Needed

[MGCC]

All brains of this forum unite!

I have been trying to resolve this for few hour now but without any help. Not a classic Excel question but more a logical teaser.

This is the issue:
I have to calculate the variance between two scenarios but variance is driven by three different segments. Three calculations need to be done and they need to be done in this order:
-Volume Variance (Isolated impact of changing the prediction for number of store visitors)
-Penetration Variance (Isolated impact of changing the expected penetration of visitors who will actually buy something)
-Sell Price Variance (Isolated impact of changing the item sale price)
-TOTAL SUM BETWEEN ABOVE MUST MATCH THE TOTAL VARIANCE BETWEEN THE SCENARIOS and I CANT GET IT TO MATCH

#	Formula	Item	Scenario 1	Scenario 2
A		Visitors	20.00	15.00
B		Sale Price	100.00	150.00
C		Penetration	60%​	45%​
D	B*C	Sale Per Visitor	60.00	67.50
E	D*A	Total Sales	1,200.00	1,012.50	(187.50)
F		Visior Volume Variance		(300.00)
G		Penetration Variance		(225.00)
H		Sell Price Variance		75.00
			TOTAL	(450.00)
			Validation	(262.50)

Who ever solves this has a beer on me!

Thanks

 

[Cubist]

How do you define "Variance"?

 

[MGCC]

Difference between two scenarios which is driven by one of three segments.

 

[Cubist]

Mathematically not possible to attribute the total variance to each factor when more than 1 factor has been changed.
It is only possible when exactly 1 factor has been changed.

Consider this. The total cost of scenario 1 T1 = AxBxC, and scenario 2 T2 = A'xB'xC'
Taking the difference T2-T1 = (A'xB'xC')-(AxBxC)
Suppose you're looking at the impact of changing A. You cannot factor in the form of (....)*(BxC). Where (....) would tell you the variance of changing A.

However, when only 1 factor has changed.
The total cost of scenario 1 T1 = AxBxC, and scenario 2 T2 = A'xBxC
Taking the difference T2-T1 = (A'xBxC)-(AxBxC) = (A'-A)(BxC). This tells you the impact of changing from A to A' can be attributed to their difference (which is trivial).

In short, if you want to attribute variance to their factors, you can only change 1 at a time.