Simple Math / Complicated Question!

Enigmachrysalis

Active Member
Joined
Apr 13, 2009
Messages
352
Office Version
  1. 365
Platform
  1. Windows
So here are the players in a cost per unit (CPU) calculation:

After period (a)
This year's cost
Last year's cost
This year's units
Last year's units

Before period
This year's cost
Last year's cost
This year's units
Last year's units

Here is the calculation by business segment for the difference of the Before/After periods year over year:

(TY_Cost_After/TY_Units_After)-(TY_Cost_Before/TY_Units_Before)
minus

(LY_Cost_After/LY_Units_After)-(LY_Cost_Before/LY_Units_Before)

To get the total CPU for all business segments, I simply sum all the before/after periods and perform the same math.

Now this works at the business unit level, but if I take each business segment variance and multiply it times TY_Units_After, i.e. the number of units in this year's after period, sum these products, and then divide the total sum TY_Units_After for all business units, I get a different cost per unit!

I have other calculations that similarly break down when they are compared in these 2 manners. I think it is a matter of mix, but until I can explain it properly, I can't be confident in which is correct!

Do you guys see the problem?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Forum statistics

Threads
1,225,656
Messages
6,186,245
Members
453,343
Latest member
hacigultekin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top