[Eg-oversight-board] 2016 Conference reconciliation

[Grace Dunbar]

Hi all,
I have spent quite a bit of time attempting to track down all the anomalies
in the Host Budget and the SFC ledger.  I also commandeered quite a bit of
assistance from Galen. :)

Here are my findings thus far:
*RECEIVABLES*

The income amount for Sponsorships was recorded correctly as $21,200.00.
As far as I know, all those payments were received.  Awaiting confirmation
from the SFC. But this all looks to be correct.

The income amount for Exhibitors was recorded as $6,740.00.  This is $285
less than should have been recorded.  The total seems to have been missing
an additional registration for Ebsco ($220) and a pre-conference
registration ($65) for MassLNC.  With those factored in, the amounts now
balance for exhibitors.
* Note that exhibitors were given the option of adding additional
registrations on their exhibitor agreement form so those were not tracked
along with the people who registered through EventBrite.

This brings us to a corrected total of $28,225 for exhibitor payments.

 Now we get into some odd issues with the attendee registrations.
The Host Budget spreadsheet matches the EventBrite numbers for
registrations for the conference and pre-conferences.
The total registrations sans bad debt/refunds looks to be correct at
$48,985.00.

However, there a few issues here that are making this difficult to
reconcile with the actual ledger.
1.) If the library requested to be invoiced  EventBrite knows nothing about
actual payment (or lack of payment) or refunds.
2.) When refunds occur, depending on when the refund is requested, there
are certain fees or portions of fees that might be assessed by
EventBrite/PayPal/SFC. This makes is difficult to reconcile to the penny
particularly in light of the more challenging aspects of the SFC's ledger
system.
3.) Registrants names are not always listed in the ledger if a library pays
by check (for example) for 4 registrants.
4.) Bad debt.  There was a registration from a library system for 4
attendees who requested an invoice.  The web site stated that invoices were
non-refundable.  The library ended up wanting to send 3 attendees instead
of 4 and refused to recognize the invoice.  They ended up just ignoring the
invoice (telling us they were going to "wait for it to expire", which isn't
actually a thing that happens in reality) and they re-registered the 3
people who were going to attend and paid for them with a CC.  This was
annoying but shouldn't have been a crippling problem in the books...
however, it ended up being difficult to account for because of the way the
SFC was taking its cut.  I will address that down at the end of the email.

*PAYBLES*

The amounts paid for the Band, the Cake, the Keynote gift, EventBrite
registration fees, and the Food/AV bill from the Sheraton were all
completely correct and easily reconciled. The total was $66,832.56.

On the Host spreadsheet, the amount for the SFC donation was incorrectly
listed as $7,009.50.  At 10% of their original total it ought to have been
$7,692.50.  With the corrections I have mentioned above, the correct amount
should be $7,721.00.

Additionally, there are the afore mentioned refunds and bad debt issues
which could be as high as $1,557.13 but I would need a magic wand and some
fairy dust to be certain.  Or maybe another 2 days and a CPA.

Lastly for payables, I can find no record in the SFC ledger of a payment
for the NCDCR attendees (The folks from the NC Cardinal org.)  Their
registrations in EventBrite are all marked as "Paid by Check", however the
SFC has no payment listed in our ledger. The total of all these referenced
registrations that seem to not have payment recorded is $3,930.00.  I have
emailed my post-conference contact at NC and asked if they could confirm
payment - perhaps the check got lost in the mail.

So,we're closer to answers for closing out the conference.  Once I have
some idea of what happened with the NC payment we should be able to
reconcile with some certainty.

~~~~~~~~~~~
To be honest, this process is being made approximately 400 times more
difficult than it ought to be due to the in-house ledger system the SFC is
using.  I know we've all had out moments of confusion regarding the reports
we get... for example, why income is reported and recorded as a negative
number.  But the problems with the system seem to be much deeper.  As I've
investigated the conference reconciliation a few things stuck out.
1.) Payment date is not recorded.
2.) Ledger cannot be reconciled easily with registrations.  Double entry
(or triple) is required when payment is received to ensure ledger,
EventBrite, and budget tracking spreadsheet match.
3.) This brings me to what I mentioned above with the SFC cut.  In the case
of investigating the specific "bad debt" write off I detailed above, I
noticed there was a peculiar entry for $130 related to this case.  $130
didn't match what I initially thought it did (two pre-conference
registrations).  Instead, to the best of my ability to discern, it appears
to be a write off for lazy and incorrect accounting purposes.  According to
the Fiscal Sponsorship agreement, the SFC gets a 10% "donation" of *gross
income*.  From looking at the ledger, it appears that when
registrations/sponsorships are invoiced, the SFC is transferring its 10%
cut immediately.
In the case of this mysterious $130 as far as I can tell, it breaks down
like this.
The library was invoiced $880 for 4 registrations at the early bird rate of
$220.
The SFC took it's 10% cut of $88.
The library refused to pay the invoice.
The library re-registered 3 attendees at the regular rate of $250.
The library paid $750 by CC.
The SFC did NOT take a 10% cut of that payment.
The SFC created a bad debt entry of $130.
10% of $130 is equal to the $13 difference between $88 and $75.  This seems
to have been done to avoid returning the $88 and taking the correct amount
of $75.

Not only is this bad accounting practice, I don't believe it is reflective
of the agreement we have with them.  It seems reasonable that the SFC
should only be transferring their donation after payment is received.

Apologies for the long email but it's been quite an eye opening
investigation.  I understand that this is coming a little late for
substantive discussion at our meeting today but I wanted to at least get
the ball rolling while we wait for other answers.

Kudos if you made it to the end of the email!
Grace

-- 
Grace Dunbar, Vice President
Equinox - Open Your Library
gdunbar at esilibrary.com
1-877-OPEN-ILS  |  www.esilibrary.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/eg-oversight-board/attachments/20160721/a50ac747/attachment.html>