Recursive calculation (to get the fairest solution)?

[deLockloire]

Hi!

We're doing a project. Suppose we need $20,200 to complete the project. There're X participants to throw in money. Each is committed to give more if necessary. We're raising funds in two rounds. In the first round, everyone throws in as much as they think. If we successfully raised the necessary funding or more in the first round, we do the project, case closed. But if after the first round, there's not enough money, we hold a 2nd fund raising round. This time, however, each participant is told how much more s/he has to throw in. Now imagine that X=5, and the first round was not good enough:

#1 gave $922
#2 gave $2,302
#3 gave $5,643
#4 gave $3,836
#5 gave $1,666

So in total, we raised $14,369 and we still need $5,831. Optimally, everyone should've given $4,040. But that's not the case so we have to make do with what we have. Now here's the question: what formula should I use to determine how much more money we should ask from the individual participants IF

1) we want the smallest contribution to differ as little as possible from the largest contribution; (so obviously, we won't ask anyone who contributed more than $4,040 to contribute more, and the less one contributed in the first round, the more s/he should be asked to contribute in the second round to fulfill the "smallest possible difference between contributions" criteria); in general, we would like everyone to participate equally while also accounting for the fact that some people might have contributed more than s/he should've).
2) Less important than the previous criteria, but the more people pay an equal amount, the better. Still, the 1) criterion should enjoy preference in any solution.
3) In the second round, we only want to reach the project cost and no more (in this case, $20,200).

What is the fairest solution? I suspect that the solution will be some recursive, iterative formula, I just don't know what. Do you have any idea? This is how the excel sheet looks like in this example:

A1 = project cost (here: 20,200)
A2 = amount still needed to complete the project (here: 5,831)
B2:B6 = contributions #1-5, respectively (but if X>5, B2:B[X+1])
C2:C6 = THIS IS WHERE THE REQUIRED 2ND ROUND CONTRIBUTIONS SHOULD BE CALCULATED

Thx,
deL

 

[shg]

That's like a rising-tide distribution. Results below, workbook at https://www.box.com/s/a03b2051a19e8006280e

       ----B---- ------C------ ----D---- ---E---- ---F---- G --H--- --I--- --J--- --K-- --L--
   2      Needed     $ 20,200                                                                
   3   Collected     $ 14,369                                                                
   4     Balance      $ 5,831      Check $ 5,831                                             
   5                                                                                         
   6     Name    Prior Payment Breakpts  Collect   Total     Rates                           
   7   Alan             $ 922       $ 0  $ 2,652  $ 3,574    100.0% -50.0% -16.7% -8.3% -5.0%
   8   Eric           $ 1,666     $ 744  $ 1,908  $ 3,574      0.0%  50.0% -16.7% -8.3% -5.0%
   9   Barb           $ 2,302   $ 2,016  $ 1,272  $ 3,574      0.0%   0.0%  33.3% -8.3% -5.0%
  10   Dana           $ 3,836   $ 6,618      $ 0  $ 3,836      0.0%   0.0%   0.0% 25.0% -5.0%
  11   Cain           $ 5,643  $ 13,846      $ 0  $ 5,643      0.0%   0.0%   0.0%  0.0% 20.0%

 

[deLockloire]

Hi, shg!

I've looked through your solution and it is exactly what I had in mind. I figured out the main idea behind your solution, so I can say, I understand it. Still, I don't say I could reproduce it without your table--chiefly because I don't understand how exactly the formulas behind the named ranges "rates" and "breakpts" work. Could you explain them?

And thanks for the solution and the work you've put into it. I love it.

Best,
deL

That's like a rising-tide distribution. Results below, workbook at https://www.box.com/s/a03b2051a19e8006280e

       ----B---- ------C------ ----D---- ---E---- ---F---- G --H--- --I--- --J--- --K-- --L--
   2      Needed     $ 20,200                                                                
   3   Collected     $ 14,369                                                                
   4     Balance      $ 5,831      Check $ 5,831                                             
   5                                                                                         
   6     Name    Prior Payment Breakpts  Collect   Total     Rates                           
   7   Alan             $ 922       $ 0  $ 2,652  $ 3,574    100.0% -50.0% -16.7% -8.3% -5.0%
   8   Eric           $ 1,666     $ 744  $ 1,908  $ 3,574      0.0%  50.0% -16.7% -8.3% -5.0%
   9   Barb           $ 2,302   $ 2,016  $ 1,272  $ 3,574      0.0%   0.0%  33.3% -8.3% -5.0%
  10   Dana           $ 3,836   $ 6,618      $ 0  $ 3,836      0.0%   0.0%   0.0% 25.0% -5.0%
  11   Cain           $ 5,643  $ 13,846      $ 0  $ 5,643      0.0%   0.0%   0.0%  0.0% 20.0%

 

[shg]

The breakpoints are the thresholds for changes in collection. At $0, all the money is disgorged from the lowest participant; at $744, equally from the first two; at $2016, equally from the first three, ...

Look at the first row of rates. Up to the first breakpoint, 100% of the money is taken from the lowest participant. After the first breakpoint, the rate of contribution for the lowest participant is half that (from 100% to 50%, a change of -50%). After the next breakpoint, his contribution changes from 1/2 to 1/3, a change of -16.7%. And so forth.

Breakpts is just the dynamic named range below D6 that contains the breakpoints.

Rates is a dynamic named range in the row of the referring formula that starts in col H and is as wide as Breakpts is tall.

The clear strategy in these economically-challenged times is to only put in a dollar in the first round.