Golf Prize Distribution

At first glance this may seem to be out of my normal range of spreadsheets, most of which are financial based. Anyone that has worked at a credit union or bank, however, knows that there are at least three or more times a year that an association or client will hold a golf tournament. Anyway, this is an Excel spreadsheet that computes the winners of a tournament and their prize money. The default data shows 61 golfers playing for a total prize amount of $3,000. There are 10 potential winners ranging from $1,000 for the first-place score to $40 for the last winning score.

The problem, of course is that most likely there will be ties at one or more of the winning scores. For example, the default scores show three players tied for first place with scores of 70. Each of the three players cannot receive $1,000, or the rest of the winners will receive nothing. So, the first three winners will take the total of the first three prizes of $1,000, $700, and $400 and divide the $2,100 by three for a split of $700 each. Likewise, the second-best score of 71 had two winners. They would get the 4th and 5th prizes of $300, and $175, for a total of $237.50 each. Due to the splitting, there was 11 winners and only 10 prizes.

Your can see where in a tournament of say 25 prizes, this could be more challenging. The trickiest part was calculating the winner’s names separated with commas. I used the FILTER function which I think is still only available on Office 365 Excel. I also used the SEQUENCE function, only available on Office 365.

This formula is for private use only and is not endorsed by the PGA or any other golf association.

 

Download  Excel File: Golf

 

 

 

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

6 Comments

  1. hello Don, I would like to design a calculator to calculate fees for the financial product. Do you do that kind of work for a fee or recommend someone?

      1. Vladimir,
        The way this model works is that if the borrower was just given only a 12 month interest only period, for example with 311 payments remaining, at the end of that 12 months a new payment is automatically calculated that will amortize the loan over the remaining 299 payments. Therefore, there is no impairment, because the borrower is making up the 12 months of no principal payments, with higher remaining payments.

        If you are not going to adjust the payment to amortize the loan until payment 311, paste the original payment into the payment column starting at payment 13 and copy it down to payment 311. The remaining balance is $3,643.26, which when discounted equals an impairment of $3,924.47.

        I hope that helps

        Don

  2. Hi there,
    I can’t find an email contact so I thought I would try to ask you a question this way…do you have any excel templates useful for interest rate ramp calculations?

    Thanks,
    AnnMarie

Leave a Reply

Your email address will not be published. Required fields are marked *