+ Reply to Thread
Results 1 to 16 of 16

Equitable solution for sharing cost in club when usage varies amongst members

  1. #1
    Registered User
    Join Date
    01-12-2019
    Location
    USA
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    7

    Equitable solution for sharing cost in club when usage varies amongst members

    Assumptions:
    There are 9 members in a club that share a pool of season tickets to football games at a local university. Membership in the club affords members the option to buy tickets from the pool and attend games during the current year. In addition, it also renews that privilege the following year. The university charges the club $27,000 (total) every year for the tickets, with that cost shared by members according to their level of participation. If any member fails to pay their portion of their financial obligation in any year, they are barred from the club forever.

    Before the season starts and before ticket selections are made, each member is obligated to pay the identical amount (in this case $3,000 each). As members declare for and are awarded their favorite tickets, that obligation shifts accordingly. Some members are active and purchase tickets. Other members are passive and do not purchase tickets. All members wish to keep their membership in order to have the option to buy tickets for the next year.

    In summary, the university charges the club a yearly fee of $27,000 for:
    1. use of the tickets for the current year and
    2. a guaranteed option to buy them again the following year

    This year, only 4 of the 9 members exercise their option to buy tickets from the pool in the following amounts:

    Member 1: $12,000
    Member 2: $9,000
    Member 3: $4,000
    Member 4: $2,000
    Total: $27,000

    As active members make payments to the pool for their respective tickets, the obligation of the other passive members to the university is reduced accordingly. Both active and passive members acknowledge that the contribution of one member reduces the financial obligation of others, equitably and to the extent of the respective contributions.

    Question: What is the monetary obligation of each of the 9 members?

    A worksheet with formulas included would be greatly appreciated. Thanks in advance.

    OldScout
    Last edited by OldScout; 01-12-2019 at 12:53 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Equitable solution for sharing cost in club when usage varies amongst members

    A
    B
    C
    D
    1
    Member
    Tkts
    Position
    2
    1
    $12,000
    ($9,000)
    C2: =$B$11 / 9 - B2
    3
    2
    $9,000
    ($6,000)
    4
    3
    $4,000
    ($1,000)
    5
    4
    $2,000
    $1,000
    6
    5
    $0
    $3,000
    7
    6
    $0
    $3,000
    8
    7
    $0
    $3,000
    9
    8
    $0
    $3,000
    10
    9
    $0
    $3,000
    11
    $27,000
    $0
    B11: =SUM(B1:B10)
    Last edited by shg; 01-12-2019 at 01:23 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    01-12-2019
    Location
    USA
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    7

    Re: Equitable solution for sharing cost in club when usage varies amongst members

    It's obvious how much active members are paying in excess of their pre-season base amount of $3,000. But does Member 4 get to go to the games, keep his options for the following year - all for free - AND get a $1,000 refund to boot?? Nobody - including passive members - gets a free ride. Member 4 actually enjoys all benefits for free AND gets a $1,000 bonus?? Do you see where my quandry is?? It's a dynamic situation where in the contributions of one affects the obligations of the others - in amutual sort of way. Cuts both ways.

    There's a flaw in this approach because someone could actually net a profit by just showing up and declaring for tickets, as is the case with Member 4. How do I fix the inequitable flaw??

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Equitable solution for sharing cost in club when usage varies amongst members

    Member 4 doesn't make a profit -- he pays $2000 for $2000 worth of tickets.

    It's your club -- you tell me how costs should be divided.

  5. #5
    Registered User
    Join Date
    01-12-2019
    Location
    USA
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    7

    Re: Equitable solution for sharing cost in club when usage varies amongst members

    I see what you're saying. But Member 4 gets $2,000 worth of tickets and gets the option for the next year for his $2,000 contribution, yet passive members pay $3,000 and get no game tickets. I think the contributions of the active members should reduce the obligation of all other member to some degree. My question is this: To what degree sould that happen? The "Position" column should also total $27,000. Right now, it totals "$0.00". It's a re-assignment of financial obligation depending on usage. It's dynamic. As some members assume larger pieces of the obligation pie, the other members' obligation should shrink simultaneuosly.
    Last edited by OldScout; 01-12-2019 at 01:56 PM.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Equitable solution for sharing cost in club when usage varies amongst members

    My brother in law and three dozen of his friends, from South Bend to South America, are in the lottery for Notre Dame tickets every year. Winners have first first dibs to pay for the tickets and go to the games. If they don't want them, there's always someone else in the group that does.

    Also, members 5 through 9's participation gave member 1 the opportunity to go to many more games than had they not participated, which probably made him deliriously happy.

    That said, you can have any rules you like.
    Last edited by shg; 01-12-2019 at 02:05 PM.

  7. #7
    Registered User
    Join Date
    01-12-2019
    Location
    USA
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    7

    Re: Equitable solution for sharing cost in club when usage varies amongst members

    The assumption that there's always someone else that will buy the tickets if they're not spoken for is not valid in this case. Also, if members pass on tickets in any given year, are they allowed to come back the following year, since the active members' contribution this make next year's option possible in the first place?? Active members are pulling the wagon while passive members are "lounging" on the wagon, contributing nothing. Active members are buying tickets for this year and also buying the option for next year. If $$ isn't sent to the university, the option to buy tickets again the next year goes "poof" and is sold by the university to some other lucky fan or group of fans.

    My situation is a bit different from your ND scenario since we always have dibs on the same tickets every year - as long as they're paid for every year.
    Last edited by OldScout; 01-12-2019 at 02:08 PM.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Equitable solution for sharing cost in club when usage varies amongst members

    Again -- your club, your rules.

    Pick some rules.

  9. #9
    Registered User
    Join Date
    01-12-2019
    Location
    USA
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    7

    Re: Equitable solution for sharing cost in club when usage varies amongst members

    Those are the rules (my OP). I'm simply trying to apply them mathematically and equitably to account for level of particiation this year and entitlements the next year. As one member's obligation rises, the others fall. Establishing those specific mathematical variances is where I'm having trouble.

  10. #10
    Registered User
    Join Date
    01-12-2019
    Location
    USA
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    7

    Re: Equitable solution for sharing cost in club when usage varies amongst members

    I think part of my problem is that I'm trying to reconcile everything in the same year. For the passive members, they should reimburse the active members the next year when it's time to re-up, or else they're out the club.

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Equitable solution for sharing cost in club when usage varies amongst members

    A
    B
    C
    D
    E
    F
    G
    1
    Adder
    2
    $1,000
    3
    Member
    Tkts
    w/ Adder
    Factored
    Is Owed / (Owes)
    Formula
    4
    1
    $12,000
    $13,000
    $9,750
    ($6,750)
    C3: =C$1 + B3
    5
    2
    $9,000
    $10,000
    $7,500
    ($4,500)
    D4: =$B$13 / $C$13 * C4
    6
    3
    $4,000
    $5,000
    $3,750
    ($750)
    E4: =$B$13 / 9 - D4
    7
    4
    $2,000
    $3,000
    $2,250
    $750
    8
    5
    $0
    $1,000
    $750
    $2,250
    9
    6
    $0
    $1,000
    $750
    $2,250
    10
    7
    $0
    $1,000
    $750
    $2,250
    11
    8
    $0
    $1,000
    $750
    $2,250
    12
    9
    $0
    $1,000
    $750
    $2,250
    13
    $27,000
    $36,000
    $27,000
    $0
    B11: =SUM(B1:B10)


    Change C2 as desired.

  12. #12
    Registered User
    Join Date
    01-12-2019
    Location
    USA
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    7

    Re: Equitable solution for sharing cost in club when usage varies amongst members

    shg - thanks for this. I'll take a closer look.

    I think a great analogy is that of a group of 9 members that owns a beach house - inherited from their late father. When he was alive, the father paid all the expenses, including property tax, and simply let the kids use it at their leisure. When the father died, the kids inherited the beach house and the all recurring expenses, including property taxes. Now all 9 kids get to use it free for 2 weeks out the year. If the taxes aren't paid, it's sold at public auction. Certain kids who never use the beach house can't understand why they should also pay 1/9 of the property tax when they never use it and pitch a hissy fit when their 1/9 priviledge is revoked. That's the same scenario that I'm addressing with the tickets.

    I realize this isn't a family counseling forum, but you get an idea of the issue in front of me.

  13. #13
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Equitable solution for sharing cost in club when usage varies amongst members

    i realize this isn't a family counseling forum,
    rofl ...

  14. #14
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Equitable solution for sharing cost in club when usage varies amongst members

    If the tickets aren't fully subscribed,

    A
    B
    C
    D
    E
    F
    G
    1
    Must Raise
    Adder
    2
    $27,000.00
    $1,000
    3
    Member
    Tkts Used
    w/ Adder
    Factored
    Is Owed / (Owes)
    Formula
    4
    Alan
    $10,000.00
    $11,000.00
    $8,735.29
    ($5,735.29)
    C4: =C$2 + B4
    5
    Barb
    $9,000.00
    $10,000.00
    $7,941.18
    ($4,941.18)
    D4: =$B$2 / $C$13 * C4
    6
    Cain
    $4,000.00
    $5,000.00
    $3,970.59
    ($970.59)
    E4: =$B$2 / 9 - D4
    7
    Dana
    $2,000.00
    $3,000.00
    $2,382.35
    $617.65
    8
    Eric
    $0.00
    $1,000.00
    $794.12
    $2,205.88
    9
    Fran
    $0.00
    $1,000.00
    $794.12
    $2,205.88
    10
    Gary
    $0.00
    $1,000.00
    $794.12
    $2,205.88
    11
    Hana
    $0.00
    $1,000.00
    $794.12
    $2,205.88
    12
    Ivan
    $0.00
    $1,000.00
    $794.12
    $2,205.88
    13
    $25,000.00
    $34,000.00
    $27,000.00
    $0.00
    B13: =SUM(B3:B12)

  15. #15
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Equitable solution for sharing cost in club when usage varies amongst members

    Another way is to set a minimum payment amount:

    A
    B
    C
    D
    E
    F
    1
    numMemb
    Must Raise
    Min
    2
    9
    $27,000.00
    $1,000
    3
    Member
    Tkts Used
    Pays
    Is Owed / (Owes)
    Formula
    4
    Alan
    $10,000.00
    $8,200.00
    ($5,200.00)
    C4: =$C$2 + (B4 / $B$13) * ($B$2 - $A$2 * $C$2)
    5
    Barb
    $8,000.00
    $6,760.00
    ($3,760.00)
    D4: =$B$2 / $A$2 - C4
    6
    Cain
    $4,000.00
    $3,880.00
    ($880.00)
    7
    Dana
    $2,000.00
    $2,440.00
    $560.00
    8
    Eric
    $1,000.00
    $1,720.00
    $1,280.00
    9
    Fran
    $0.00
    $1,000.00
    $2,000.00
    10
    Gary
    $0.00
    $1,000.00
    $2,000.00
    11
    Hana
    $0.00
    $1,000.00
    $2,000.00
    12
    Ivan
    $0.00
    $1,000.00
    $2,000.00
    13
    $25,000.00
    $27,000.00
    $0.00
    B13: =SUM(B3:B12)

  16. #16
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Equitable solution for sharing cost in club when usage varies amongst members

    I think both of those have flaws.

    Maybe a rising-tide allocation of unused tickets ...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 01-17-2016, 11:58 AM
  2. [SOLVED] I am in need of a formula, that tells me the attendance of Club members.
    By C21thirlmere in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-22-2015, 11:27 AM
  3. Replies: 3
    Last Post: 06-28-2015, 06:59 PM
  4. Replies: 3
    Last Post: 04-06-2014, 01:26 PM
  5. [SOLVED] Calculate cost of gas usage with different rates
    By kryptic in forum Excel General
    Replies: 9
    Last Post: 11-30-2011, 05:07 AM
  6. Sorting club members into teams
    By simon_p_w in forum Excel General
    Replies: 5
    Last Post: 08-15-2010, 03:37 PM
  7. [SOLVED] night club operating cost spreadsheet needed
    By Mosh Pit in forum Excel General
    Replies: 0
    Last Post: 10-03-2005, 03:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1