View Single Post
  #4   Report Post  
 
Posts: n/a
Default How do I calculate pro-rata formulas?

"dukes" wrote:
The payraise totals are $129,625. The improvements totals are $425,866.
How do I pro-rate these amounts to the various providers and what would
the formulas look like?


Thanks for the example. I totally misunderstood your question. Looking
back, I see I simply misread, not giving enough weight the very last
sentence of your original posting.

CONTRACTORS Beginning Budget PayRaise Improvements Ending Budget
Creative 166,635
GHL 1,029,902
Haralson 95,971
Lookout Mounta 1,731,389
Three Rivers 1,432,702
TOTALS 4,456,599


First, I am surprised that you want to allocate the pay raises and
improvements proportionately. But it is just a class exercise. It
does not have to make real-world sense.

Assume the columns above are A (contractors), B, C, D and E
(ending budget). Assume that Creative is row 4. TOTALS is
row 9. The total beginning budget is B9.

Suppose you put the total pay raise into B1, and the total
(other) improvements into B2, leaving room for appropriate
titles in A1 and A2.

The ending budget (E4) is simply =B4+C4+D4. Copy down.

The pro-rated pay raise (C4) is =ROUND($B$1*B4/$B$9,0).
Copy down.

Similarly, the pro-rated improvements (D4) is
=ROUND($B$2*B4/$B$9,0). Copy down.

Explanation: Each pro-rated amount is the total amount
(pay raise or improvement) times the fraction of the total
beginning budget for each contractor.

I assume that B9 (total beginning budget) is =SUM(B4:B8).
Copy that into C9 and D9 creating =SUM(C4:C8) and
=SUM(D4:D8) respectively, rather than use the pro-ration
formula. There is nothing wrong with using the pro-ration
formula. But the SUM() formulas will provide a useful check.

Note that I rounded each pro-rated value. That will probably
cause a small error in the sums for C4:C8 and D4:D8.

To avoid the error, it would be ideal if C8 and D8 were
=$B$1-SUM(C4:C7) and =$B$2-SUM(D4:D7) respectively.

Explanation: The last contractor gets the remainder of the
total amount (pay raise or improvement) minus the sum of
the other other contractors' pro-rated amounts. It should
be a small error for the last contractor.

Hope that helps. Be sure that you understand the concepts.
I did not test these formulas. I might have made some
typing errors.

I might also note that there is plenty of room for improvements;
naming B1 and B2, for example.