Attributing Difference in Unit Margins to Regions

[JeremyLac]

There are three regions: East, West, and Central.

Each region has dollars, gallons, and dollars per gallon.

There are forecasted numbers as well as actual numbers.

East - Forecast - USD50,000; 1,200 gal; USD41.67/gal
West - Forecast - USD75,000; 1,700 gal; USD44.12/gal
Central - Forecast - USD110,000; 2,600 gal; USD42.31/gal
Total - Forecast - USD235,000; 5,500 gal; USD42.73/gal

East - Actual - USD49,625; 1,305 gal; USD38.03/gal
West - Actual - USD74,842; 1,658 gal; USD45.14/gal
Central - Actual - USD105,845; 2,507 gal; USD42.22/gal
Total - Actual - USD230,312; 5,470 gal; USD42.10/gal

I can attribute the change in total volume to the regions as follows:

Total actual: 5,470 gal
East variance: (105) = 1,200 - 1,305
West variance: 42 = 1,700 - 1,658
Central variance: 93 = 2,600 - 2,507
Total forecast: 5,500 gal

How can I attribute the change in total unit margins to the regions?

Total Actual: USD42.10/gal
East variance: ?
West variance: ?
Central variance: ?
Total forecast: USD42.73/gal

 

[jvicens]

To attribute the change in total unit margins to the regions, we need to calculate the unit margins for each region. The unit margin is the difference between the actual dollars per gallon and the forecasted dollars per gallon.

East unit margin = USD41.67/gal - USD38.03/gal = USD3.64/gal
West unit margin = USD44.12/gal - USD45.14/gal = -USD1.02/gal
Central unit margin = USD42.31/gal - USD42.22/gal = USD0.09/gal

To determine the contribution of each region to the change in total unit margins, we can multiply the unit margin by the change in volume for each region.

East contribution to change in total unit margins = USD3.64/gal * (1,305 gal - 1,200 gal) = USD152.40
West contribution to change in total unit margins = -USD1.02/gal * (1,658 gal - 1,700 gal) = USD41.16
Central contribution to change in total unit margins = USD0.09/gal * (2,507 gal - 2,600 gal) = -USD9.36

Therefore, the total unit margin change can be attributed to East: USD152.40, West: USD41.16, and Central: -USD9.36. This means that the East region had the highest contribution to the increase in total unit margins, while the Central region had a slightly negative contribution.