Dear All,
Our Finance department has asked me to do the one thing that you never want to do and tie every payment to every ticket layer part of every ticket so that you can run payments against a performance at any specific pricing level or levels and know exactly how much revenue goes to each payment method for the ticket level or the facility fee or what have you. I tried to tell them that this is a bad idea because if you are, for example, halfway through the season and half of the performances have already passed and you have reported on them, the payment methods for the money going to those performances can still change if a patron calls in, their subscription order is loaded and that patron just adds one more ticket to that order because the money will shift around a bit. But they still want to be able to run this anyway, so I am at least trying to do it.
Anyway, I have gotten very close. I am able to link things such that all direct payment methods are accounted for 100% exactly. The problem is exchanges (because of course). And specifically the real issue is caused by those exchanges where there is a change between two performances but there is no payment because the prices are equal. To sum it up, the performance number does not get updated in T_TRANSACTION because there is no change in T_PAYMENT so you have a transaction originally assigned to and maintaining performance # 1 in the table while the transaction revenue itself has actually shifted to performance # 2.
If you have any ideas and/or would like to see the code I have thus far, let me know! I would be just fine to go back to the Finance people and tell them that it is impossible, but I said I would try, and thus here we are.
Thanks!
John
Ya... this is literally impossible. My Finance team was asking for similar information - 2 years and $10K in a custom report later, the Network confirmed this was not possible. As you discovered, this is relatively simple when processing a single transaction with tickets mapping to the same GL. Where this starts to get messy is when you - you've got it - process an exchange and/or you add another line item at a later date and process that transaction with a different payment method. Unfortunately, there's no way for Tessitura to recognize payment A is for ticket A and payment B is for ticket B, it just sees it has enough money to process the order and applies payments on a whim.
I went as far as to attend a transaction_no, sequence_no, and payment_no training session last year at TLCC 2018, and asked the question. Chuck Reif happened to be in the room and used the analogy of processing a transaction at a hardware store. If you're buying a hammer and nails, and pay $5 in cash, and $5 in CC, that POS isn't going to apply $5 to nails and $5 to the hammer, it's going to apply $10 to the transaction and call it a day. In terms of making this argument to your Finance team and having them not come back with "Tessitura sucks, this was so much better with [insert previous ticketing system]" just tell them they're asking the system questions it's not built to answer. Obviously not the best route, but it's helped our argument... or just go with the hardware store analogy.
If anyone can prove me/the Network wrong, PLEASE do so. :)
See, that is what I was expecting, and I am fine to give this answer to the Finance peoples for now because this IS kind of what I was expecting.
And I understand everything that Chuck said and do not really disagree with it. Payments do not care, you create an order, and there may be 8 things on it, and paid with 4 different payment methods. No one knows, and most people do not care. Certainly I cannot think of a single patron who would care about that so long as they got their hammer and nails and went about their way.
That said, I CAN also see the logic of wanting to know this sort of information. And, if an order is made and a payment is processed, even if the exact distinction is arbitrary, I mean... the disbursment of funds DID happen. At that moment in time, it seems like a non-idiotic idea to think that you could accurately report on what revenue from what payment method (assuming more than one were used) went to Ticket X, Layer Y, which went to Ticket X, Layer Z, and which ones went to the corresponding layers Y and Z on Ticket W within that particular order.
And again, knowing that the payments get shifted around if you re-load the order later and add another ticket to it and pay it, I can again see the logic of thinking "Well, obviously 100% of that new payment went to that new ticket" seeing that the order had been previously paid in full. Even with the shifting of payments, again, the order is in theory paid in full by the end, and the logic above would hold. Lastly, with exchanges, again it feels like you should be able to track back to the original order, given that there is a direct link to the original ticket number, unique to the order and payment, to again find the answer you want.
I am not saying I need to answer these questions. And I am not even sure that our Finance department really needs them. They just want them, and the best answer I have gotten is a variant of "because", which is 5 year old enough for me to not care.
But in the end, the driving factor really is this: We, like everyone else, pay credit card fees on all of our credit card transactions. We, also like most everyone else, have a facility fee embedded in all tickets we sell, including those of our 6 resident companies and all rental events, which do account for approximately 30 percent of the ticket sales and revenue of our campus every year, and 100% of all ticket sales go through our Box Office. Now, we are relatively small organization and our resident companies smaller still, so the fractions we are talking about here are small.
But essentially, come to the end of a production, we settle up with our resident companies and rental clients. We write them a cheque for the amount of revenue their tickets bring in minus everything owed to us, which consists of: the facility fee, staff hours, rental of equipment, and so forth with that sort of stuff. One of the things included there would be credit card fees. Since we process all the orders, we are paying all the credit card fees, so we charge our clients for that (specific breakdowns for each type of card and method of transaction, of course). Which works just fine; running the specific payment methods against the entire ticket price is actually doable enough that that is not a problem. Except for when you want to break out the facility fee since we keep that money.
Again, we are small enough that we are talking about around 3% of a fee that is approximately 2% of the ticket price. Someone just says "eh, $400 sounds about right" and we leave it at that. Especially as I routinely remind Finance that the numbers WILL shift around as we go through the season and if they tried to settle the first show of the season at the end of the season they would get different numbers despite the fact that no tickets have been purchased, refunded or exchanged since the moment the show was settled 9 months ago.
But at some point (as we continue to grow!! positive attitude!), that facility fee percentage number is going to keep getting bigger and someone is going to want to be able to get an exact number for that, and that really does not seem like something that would be too terribly unreasonable to give them.
Thanks for what you said, Emily, and it sounds like you want an answer to this probably more than I do. Also, sorry about the length of the response. I can get wordy.
Oh ya, I completely understand the reasoning behind wanting this information. We as non-profits certainly want to ensure we're not losing money on CC commissions and/or charging a rental client the right amount. And of course American Express has to charge more than everyone else so that's another layer of annoyance.
I'm happy to share the report our Finance team uses (as I continue to tell them it's wrong). They don't need things as granular to break things out by ticket layer, but we do have a custom Income by Payment method report that attempts to break out payment methods by GL in a singular batch.
If I did not already have my own similar report, I would accept and be grateful for it. And yes, AmEx gets annoying. For myself, I have started only using my own AmEx card when shopping at stores where I definitely do not care about their profit margins (e.g. Amazon), and rarely at local, small shops, even if they do accept it, because I know they are likely paying a premium in order to do so.
But yeah, I have batch/GL level, and I have another report that reports on the full ticket layers for any performance as it with "(exchange)" payments just lumped together and left as is. I just have to continually remind Finance that we cannot separate that one any further to get more exact on the "(exchange)" details and/or break out the ticket layers; it can ONLY be used to report all layers.
This time, they just pushed a little harder, and with a few more upgrades underneath my belt since I last looked at it, I thought I would give it a try. But I am not surprised that this is where I end up as much as I wish there were a solution. Honestly, if that transaction performance number would change with the ticket change even when there is a zero payment transaction, I think that might be everything that is needed for this.
But we leave that for another time it seems. Thanks again!
Just to muddy the waters, I have it in my head that it is also possible to use one payment for multiple orders. Is that true?
The only way we have found to do that is with cheques... basically just using a new payment for each order but using the same cheque number. Obviously the same thing can easily be accomplished with cash or a Gift Certificate.
For a card, I mean, you could accept extra payment from a credit card on one order and use an on account method to get it to another order, but I know of no way to basically directly accept a credit card payment to two (or more) distinct orders.