Formula for budgeting

[Chris Giannoulakis]

I am a Bank Area Manager in GREECE.
I have a total of 17 branches in my authority and I need
a formula for the following project:
I have to assign the branches a budget for the second
semester. The increase on the original Area total balance
(S1 - first semester) should be 20%
(S2 = S1 + 20%S1).
The total balance for 17 branches on June 30th was 1000.
The total balance on December 31st must be 1200.
(Increase= 20%)
I dont want to give equal increase for all brances.
What should the respective increase be for each branch,
given that the increase should be inversely proportional
to the outstanding balance (the branches with higher
balances need to have a smaller increase, whereas the
ones with lower balances need to have a larger increase)?
The minimum increase must be 10% and maximum 30%
Here are the balances of the 17 branches in the first
semester.

Nos Jun December
1 100 100 + ?
2 95 95 + ?
3 94 94 + ?
4 91 91 + ?
5 85 85 + ?
6 80 80 + ?
7 70 70 + ?
8 65 65 + ?
9 61 61 + ?
10 48 48 + ?
11 44 44 + ?
12 39 39 + ?
13 36 36 + ?
14 31 31 + ?
15 26 26 + ?
16 25 25 + ?
17 10 10 + ?
TOTAL 1000 1200
INCREASE : 200 (20%)

 

[Landxlii]

Nos Jun December

1 100 100 + (100/1000*200)
2 95 95 + (95/1000*200)
3 94 94 + (94/1000*200) etc
4 91 91 + ?
5 85 85 + ?
6 80 80 + ?
7 70 70 + ?
8 65 65 + ?
9 61 61 + ?
10 48 48 + ?
11 44 44 + ?
12 39 39 + ?
13 36 36 + ?
14 31 31 + ?
15 26 26 + ?
16 25 25 + ?
17 10 10 + ?
TOTAL 1000 1200
INCREASE : 200 (20%)

 

[Michael Malinsky]

Well it took me a while, and maybe there's an easier way, but here's what I
did:

Beginning in A2, I have the following info:

Low% 10%
High % 30%
Balance High 100
Balance Low 10
Desired Increase 200

Beginning in E1, I have the following:

June Balance % Increase Amount Increase December Target

In D2 through D18, I have the numbers 1 through 17, representing branch
numbers.

In E2 through E18, I have the corresponding June Balance for that branch.

In F2, I have the formula: =B2.

In F3, I have the formula: =F2+((E2-E3)*(($B$3-$B$2)/($B$4-$B$5)))
and copied down to F18

In G2, I have the formula: =F2/SUM($F$2:$F$18)*$B$6
and copied down to G18

In H2, I have the formula: =E2+G2
and copied down to H18

I have totals in row 19 for the respective columns.

This seemed to work and is (hopefully) adjustable for future periods for
changes in the desired increase and the actual balance.

I hope this is helpful!

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh