variance with added ratio factor

[ammartino44]

Hello. I'm using excel to calculate sales and rate variance. I'm able to do this easily. However, when I add a ratio component I get confused. I want to determine the impact that the changing ratio had on the change in dollar amount. So for example 10 units multiplied by a ratio of x= volume for period 1 and 20 units multiplied by a ratio of x= volume for period 2. I want to split the volume variance into ratio impact and remainder impact. Is this possible? Spreadsheet attached. Thanks.

[MrShorty]

In looking at your question and your spreadsheet, my first reaction is if this is more of a math question than an Excel question. By that I mean, how do you want to measure the impact of ratio and/or volume? If you already know (mathematically) how you want to measure the impact of each quantity, I'm sure we can help you program those calculations into Excel.

If this is really more of a math question, a couple of thoughts:
1) The mathematician in me first wondered if you would want a "calculus" answer based on partial derivatives. The equations don't seem very complex, it should be fairly straightforward (for someone who has had at least one calculus class) to compute the desired partial derivatives.
2) In a similar vein (because it would essentially amount to numerically estimating these partial derivatives), you could estimate the impact of each variable by performing the calculation as you have it multiple times. I would suggest that your spreadsheet is not arranged in a way that makes this convenient. To approach it this way, I would arrange the spreadsheet so the entire calculation fits into a single row. Then it becomes easy to make copies of that row. In each copy of the calculation, you can change one variable. With enough copies of the calculation and some consideration to what change to make in each row, it should be possible to make some assessment of the impact each "independent variable" has on the overall calculation.