A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do I calculate pro-rata formulas?



 
 
Thread Tools Display Modes
  #1  
Old November 10th 05, 12:36 AM
dukes
external usenet poster
 
Posts: n/a
Default How do I calculate pro-rata formulas?

I have a spreadsheet that I need to complete for a class assignment. I am
new to Excel and would appreciate any help. It consists of 4 columns.
Columns look like this: beginning budgeted amount, pay raise,
improvements, ending budgets.

I have the beginning budgets for each program and the pay raise and
improvements dollar amount. How do I pro-rate the pay raise and improvements
to each program.
Ads
  #2  
Old November 10th 05, 12:48 AM
[email protected]
external usenet poster
 
Posts: n/a
Default How do I calculate pro-rata formulas?

"dukes" wrote:
> I have a spreadsheet that I need to complete for a class assignment. I am
> new to Excel and would appreciate any help. It consists of 4 columns.
> Columns look like this: beginning budgeted amount, pay raise,
> improvements, ending budgets.
> I have the beginning budgets for each program and the pay raise and
> improvements dollar amount. How do I pro-rate the pay raise and
> improvements to each program.


I think some key pieces of information is missing: what period of time
the budget covers, how far into that period the improvements are
effective, and what period of time does the pay raises and other
improvements cover (e.g, monthly, annual, or for the remaining
duration of the budget).

For example, if this is a 12-month budget and the improvements are
effective starting in the 8th month, then the ending budget is:

(beginning budget) + (pay raises + other improvements)*(12 - 8 + 1)

That assumes that the improvements are recorded as monthly changes.
If they are annual changes, then alter the formula above to:

(...) + (... + ...)*(...)/12

In Excel-speak, assume that the budget period (in months) is in A1,
the effective month of improvements is in A2, and for one program,
the beginning budget is in A3, the annual pay raise is in B3, and the
other annual improvements is in C3. Then the second formula for
the ending budget is:

=A3 + (B3 + C3)*($A$1 - $A$2 + 1)/12

If the pay raises and other improvements are recorded for just the
remaining period of the budget, then the formula for the ending
budget becomes quite trivial, namely:

=A3 + B3 + C3

No proration is required.

Hope that helps. And I hope that's right(!). If you have any doubts,
a concrete example with expected results is always best.
  #3  
Old November 10th 05, 01:59 AM
dukes
external usenet poster
 
Posts: n/a
Default How do I calculate pro-rata formulas?

This is what the spreadsheet looks like. 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?

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

Thank you so much for your help!
  #4  
Old November 10th 05, 03:57 AM
[email protected]
external usenet poster
 
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(D48) 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 D48.

To avoid the error, it would be ideal if C8 and D8 were
=$B$1-SUM(C4:C7) and =$B$2-SUM(D47) 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.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
new sheets by this pc cannot calculate formulas Jeffry61 Excel Discussion (Misc queries) 12 September 23rd 05 07:21 PM
How do you calculate persentages and formulas EXCEL Excel Discussion (Misc queries) 1 August 8th 05 07:11 PM
formulas won't calculate Indigo Boy Excel Worksheet Functions 1 May 16th 05 09:53 PM
Excel won't calculate my formulas correctly. Shelfish Excel Worksheet Functions 2 March 18th 05 05:29 PM
How Excel & ACCPAC 6.1 calculate formulas???? Bass Mama1 Excel Worksheet Functions 1 February 9th 05 03:25 PM


All times are GMT +1. The time now is 08:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.