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.