View Single Post
  #2   Report Post  
 
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.