Algebra in real life, part II

This post is dedicated to the memory of Mr. Earl Wog, who taught us about Joe Pythagoras and also taught me how to drive. :)

I've needed to know this two different times in the last six months or so, and had to figure it out from scratch both times, so it's time to document it for posterity.

When you're trying to get payment from somebody via, say, an online credit card processor such as PayPal, and the processor takes a percentage of the total transaction as its transaction fee, how much do you need to charge in order to receive the amount of money you actually want? Here is the correct formula:

[amount you want to receive] ÷ ( 1 - [fee, as a decimal] )

The following, seemingly plausible formula does not work, and will result in you receiving less money than you wanted:

[amount you want to receive] + ( [amount you want to receive] × [fee, as a decimal] )

Pro tip! If you accidentally used the second formula instead of the first one, don't make an indignant post in the credit card processor's user support forums demanding to know why they're shorting you.

Thanks, Mr. Wog, wherever you are!

Update: I received a suggestion that the following formula may also yield the desired result:

[amount you want to receive] × ( 1 + [fee, as a decimal] )

However,

x(1+y) == (x × 1) + (x × y) == x + xy

which is the same as the second formula above and isn't correct. Thanks to GAM for helping me with the proof on this. Make no mistake, my algebra skillz are tenuous and that's why I have to struggle through formulas and post them on my blog. :)

Algebra I FTW!

Never let it be said that Algebra I isn't useful in real life. Mr. Wog would be so proud.

So here's the problem:

I need to construct a complete, detailed payroll history for an employee over a particular 6-month period.

During the period, the employee is paid from 4 different budgets, which we'll call A, B, C and D.

Budget A is active all 6 months, and I have complete payroll detail for it during that time.

Budget B is active all 6 months, but I only have its payroll detail for the first 3 months.

Budget C is active all 6 months, but I have no payroll detail for it at all. I know that it was not paid during the first 3 months, and that a lump sum was transferred from Budget A to Budget C to cover the first 3 months of my arbitrary period and the 3 months prior to the period I'm studying. Unfortunately, it's applied to earn dates by academic quarter rather than calendar month, which means it's offset by 2 weeks in addition to being partial. I don't know for sure, but I assume, that Budget C was paid normally for the last 3 months.

Budget D is active only the last month, and I have its complete payroll detail.

I have monthly averages for each of the 4 budgets as calculated before the transfer and after the transfer. I also have the total monthly average paid to the employee.

I have the total monthly amount paid to the employee (A+B+C+D) for the first 3 months only, during which time it is constant, but I can tell from the monthly average that the employee received a pay increase sometime during the last 3 months. I don't know exactly when or how much.

I need to reconstruct the detail in such a way that the monthly averages for each budget, both before and after the transfer, and the total monthly average, come out the same. I'm allowed to make brute-force assumptions about anything that doesn't affect that outcome, e.g., I actually have to construct it by 2-week pay period, but since the averages are monthly, it's OK for me to arbitrarily divide the monthly total by 2 to come up with a pay period amount.

My tools for this exercise are Excel and a whiteboard. In Excel, I create some PivotTables to group and average the data so I can track the effect of each change to each budget.

The first thing I see is that the monthly total of Budget B for each of the first 3 months is exactly the same as its 6-month average. I decide to fill in that it was paid at the same rate for all 6 months. Budget B is now solved.

I have Budget A data for the last 3 months, and now that Budgets B and D are solved as well, I can get Budget C with simple subtraction... except now I need to figure out the pay increase.

It makes sense to me to assume that the pay increase occurred in month 6 only, because that's when Budget D became active and the pattern of Budget A seems to support this idea.

On the whiteboard, I figure out the amount of the pay increase with this equation:

(5(18208)+x)/6 == 18261.84

x == 18531.04

I decide to assume this, which gives me a grand total for months 4 (18208), 5 (18208) and 6 (18531.04). Now that I know Budgets A, B and D during those months, I can fill in Budget C by subtracting. If this isn't precisely right, I figure I can make up the difference in the first 3 months and make the averages come out as desired.

Now I try to guess how the lump-sum transfer for the first 3 months should be pro-rated. Its effective date is supposed to be only partially overlapping with the period I'm working on, so I try to calculate the per-pay-period amount on that basis, but the adjusted monthly average I come up with doesn't match the adjusted monthly average I've been given. On a hunch that this math was too tortured for the poor soul who made the adjustment in the first place, I decide to see what happens if I pro-rate the lump sum over only the partial period that I'm investigating and not the total transfer effective period. Bingo! Everything falls into line.

To a degree of accuracy that I care about (and no more), I've successfully filled in all the missing payroll detail for my employee, and I have a complete picture of all 4 budgets for the entire 6 month period.

Math is cool.