Ideas

Payment Reconciliation Report

Build a report that better assists clients to reconcile their payments with ArbiterPay transaction included on the report inside ArbiterOne/ArbiterGame.

  • Guest
  • Apr 3 2019
  • Promoted to Feature
Please add more details

Currently, schools contact me and ask for a monthly reconciliation report based on which school paid how much in game fees, travel fees, per diem (if necessary) with the processing fee included.  That report is not available in ArbiterOne.  It is somewhat available in the ArbiterPay report that can be pulled, but the person that pulls it needs to know their way around excel to manipulate the report that is generated.

 

I have attached a sample report for Weslaco ISD.  I pull this out of ArbiterPay, break it down and build it back up to be able to create the pivot table.  The school asks for this so that they can reconcile once a month.  Please let me know if the attached does not work and I can resend where need be.

Category Payment
  • Attach files
  • Trey Castleberry commented
    1 May, 2019 02:43pm

    Ok!  That will work.  Anyone that knows copy and paste can reconcile the district.  Thanks for the clarification. 

  • Admin
    Chase Paxman commented
    30 Apr, 2019 11:15pm

    To answer your first question, it would have to be run out of the HS and MS accounts to reconcile for the district for now. We can revisit other options for running out of both accounts.

    For your second question. The same report will be available in ArbiterGame and ArbiterOne. So, schools who are not yet in ArbiterGame should be able to run this report as long as their assigner has set them up with the correct Bill To relationships.

  • Trey Castleberry commented
    26 Apr, 2019 04:55pm

    Yes I am.  This looks like a report that is set to be generated out of ArbiterGame, which is fine!  Will this report be available, if it is implemented, to the schools who are not utilizing ArbiterGame so that they can reconcile as well?

  • Admin
    Chase Paxman commented
    26 Apr, 2019 04:51pm

    I believe that is correct. When districts run this report, where are they running it from? Are you talking about running it in their High School account and their Middle School account?

  • Trey Castleberry commented
    26 Apr, 2019 03:40pm

    Morning Chase!

     

    I think this will work!  The split on the transaction fee is fine, as long as it totals the transaction of that official or game worker.  Also, I like the designation between official and game worker.

     

    I created a fake pivot on the last attachment you sent and it works perfectly.  The only step I see the school having to take is having to move the 2 reports into 1 IF they want to reconcile for the district and not by school since this report is being generated out of ArbiterGame/ArbiterOne...correct?  

  • Admin
    Chase Paxman commented
    24 Apr, 2019 10:38pm

    Trey,

    Thank you for the feedback. I made a few tweaks to update based on your recommendations and did a few other changes.

    Processing fees ($1.75 in this test case) are added here in their own column and are divided between the games if the transaction contained more than one payment. The only time we combine transactions in ArbiterGame is if the same payee worked multiple events on one day and the payor pays for all their events at once. In that case, I divided the processing fee between the events.

    If it's not obvious, the sort order on this is by Game Date, Last Name, First Name, Game Time

    Let me know what you think.

  • Trey Castleberry commented
    24 Apr, 2019 03:28pm

    Hey Chase!

     

    I was looking at the report and it looks great!  Gives me the name, sport, paid date, all amounts and transaction fee.  The 2 things that I see as improvements:

     

    1. I see how an official is identified, but I am also getting asked to separate them away from the game workers that were paid.  If a school runs this report, will this report pull in the game worker that was paid?  Since Game Workers are assigned to a 'Position' (Column I), could those workers be pulled in to the same report?  If so, that will be a great identifier to separate a Worker from an Official.

     

    2. The processing fee is displaying as a negative.  The 'Total' column (column V) is summing up the fees and the negative is reducing the cost instead of adding to it.  Example, line 14 has a $60 game fee, $0 travel, $0 Per Diem, -$25 for Add/Deduct and a -$1.80 processing fee and the total for that transaction is $33.20.  Viewing it as a school, they Add/Deduct doesn't concern them, but the rest does.  So, a school technically paid for that particular line $61.80.  My point, can the processing fee be a positive instead of a negative?

     

    Let me know if you have any questions!  Looking forward to hearing from you and excited about this report!  It will definitely help me out and make it easier for the schools to reconcile based on their business practices.

  • Admin
    Chase Paxman commented
    22 Apr, 2019 06:28pm

    Trey,

    We are planning on modifying the Payment Details report for assigners and schools. These modifications may make your process a little easier. If you check the attached file, this is the most recent iteration of what the new report would look like.

    If you ran this from a group as an assigner, you could run it per sport and per bill to to come up with a total for each school/sport. Let me know if you think this report would help at all, or if you would make any modifications.

  • Trey Castleberry commented
    8 Apr, 2019 06:18pm

    Sure!  Here is what I do:

     

    1. Pull the report out of ArbiterPay for the dates that the school is requesting.  For the attached report, it was 2/1/2019-2/28/2019

    2. Open the report and pull out all the unnecessary detail I need out of column D (i.e. payment corrections, processing fees, ACH transfers, wire transfers, etc.).  I will bring the processing fees into the report in a later step.

    3. Copy the data in column D and paste it to a new tab.

    4. Push this data to columns with the comma as my separator.

    5. The first column is always the school so that is my first column.  I then add a new column to the left of that which is labeled sport.  I take all the group numbers I can find and move those to a new tab to create my v-lookup table.  there are always duplicates to I make sure to remove those to just get the individual values.  I then make sure the appropriate sport is next to that group number and my v-lookup table is created.

    6.  I go back to my working spreadsheet and enter in my v-lookup formula into column B to get my sports.

    7.  I then add 4 new columns to the right of B.  These columns will be titled Game Fees, Travel Fees, Processing Fees and Total.  If I find Perdium in the report, I make sure to add that column if needed.

    8. I go through each column looking for all game fees and use the =RIGHT formula to get exactly what i am looking for.  I drag that down all rows to make sure they fill for the rows displayed.  I clear that sort on that column and move to the next looking for all game fees.  Repeat the process.

    9.  I repeat step 8 looking for Travel Fees in place of game fees column by column.

    10.  I repeat step 8 looking for Perdium IF I found any payments while I was doing step 8 or 9.

    11.  I go back to the raw data and filter in ONLY processing fees.  I look to make sure all are sitting at $2.60.  If there are any other then $2.60, I investigate which line that is and make sure to bring it over to the working tab.  I then insert all transaction fees for all rows.

    12.  Since all of my data is basically formulas, I have to copy everything and then make sure to paste all data as a value.  This will trigger excel to recognize that my data is not a number.  So, I then convert everything to a number to make sure my =SUM function will work.

    13.  In the total column, I insert my =SUM function to add up all game fees, travel fees, perdium (if paid) and processing fees.  This gives me the grand total for each row.

    14.  I take columns A-F (sometimes G if there is perdium) and create a pivot table on a new tab.

    15.  I build my pivot to show the school as my rows with sports underneath that and the game fee, travel fee, perdium (if paid), processing fee and grand total.  I make sure to rename my columns and convert my numbers to currency.

     

    This allows the schools to see what they paid for each official by sport broken out by game fees, travel fees, perdium, processing fees and a grand total.

  • Darla Barney commented
    8 Apr, 2019 05:56pm

    Trey,

    Can you outline the exact steps you go through to generate the example you attached?  I want to make sure we're not missing anything.  

    Thanks.