1

We are currently putting together some sales reports for our company, and noticed some oddities when we have multiple tax rates, and it's bugging me as to why this is happening.

Tax Rate Retail Price (R) Tax Amount (retail) Cost Price (C) Tax Amount (cost)
0.2 1492.925 298.63 1036.68 207.31
0.05 1632.27619 81.63 1159.66 57.98

When we calculate the percentages and figures in isolation, the values are all correct.

However, when we add them all together, the percentage of tax between retail and cost is different:

Retail:

(298.63 + 81.63) / (1492.93 + 1632.28) * 100 == 12.17

Cost:

(207.31 + 57.98) / (1036.68 + 1159.66) * 100 == 12.08

I would have expected the two blended percentages to have the same value. Shouldn't both calculations give the same result?

ryang
  • 38,879
  • 14
  • 81
  • 179
BenM
  • 121

2 Answers2

3

However, the percentage of tax between retail and cost is different:

(a) Retail: (298.63 + 81.63) / (1492.93 + 1632.28) * 100 == 12.17

(b) Cost: (207.31 + 57.98) / (1036.68 + 1159.66) * 100 == 12.08

I would have expected the two blended percentages to have the same value.

  1. Since $\dfrac {C_2}{C_1}\ne\dfrac {R_2}{R_1}$ (that is, since items/rows 1 and 2 have different profit margins), there is no basis for assuming that quantities (a) and (b) should have the same value.

  2. Quantity (a) is the average tax rate of the two items, weighted by their retail prices.

    Quantity (b) is the average tax rate of the two items, weighted by their cost prices.

    Quantities (a) and (b) are not the average tax rate of the two items, which is just $12.5\%$ (the average of $20\%$ and $5\%$). You'd get this value with these alternative computations analogous to (a) and (b): $$\frac12\left(\frac{298.63}{1492.93} + \frac{81.63}{1632.28}\right)=12.5\%$$ and $$\frac12\left(\frac{207.31}{1036.68} + \frac{57.98}{1159.66}\right)=12.5\%.$$

    Fuller explanation at Average of ratios versus Ratio of averages.

Ambiguous terms like "blended percentage" and "retail percentage of tax" are not precise enough to distinguish among the various related quantities, and give rise to misunderstandings like this.

ryang
  • 38,879
  • 14
  • 81
  • 179
  • But if we look at the rows in isolation (i.e. the 20% and 5% tax rate), and re-run the calculations for each, they are equal. Why are they not equal when we have a mixture of 20% and 5%? – BenM Feb 28 '23 at 11:02
  • 2
    @BenM Please refer to my expanded answer. – ryang Feb 28 '23 at 12:31
2

Imagine you bought a car with a $20\%$ tax rate and a candy bar with a $5\%$ tax rate. Your overall tax rate would be just about $20\%$ (you'd hardly notice the effect of the tax on the candy bar in your overall tax rate). On the other hand if you bought the car with a $5\%$ tax rate and the candy bar with a $20\%$ tax rate, your overall tax rate would be just about $5\%$ (again barely noticing the effect of the candy bar on the overall tax rate).

paw88789
  • 40,402