Accounting Reports and GL Codes
complete
Dave Rapin
Add a dedicated Accounting area (under the Club dropdown) that lets clubs attach GL-ready codes to every sellable item before export or integration.
Features:
- Configure up to three accounting categories (Account, Class, Project) and enter the GL codes your finance team already uses.
- Build labeling rules in that Accounting area. Filters can target wide segments (e.g. all events, all fees) or narrow ones (such as event type = league and name contains “mens”). Multiple filters are additive; create additional rules for OR behaviour.
- When an order is submitted or paid, the system resolves the rules and stores the chosen codes on each order item so refunds, exports, and future integrations stay accurate even if assignments change later.
- Reporting and CSV exports now include these categories, so bookkeepers can generate GL-ready files without manual tagging.
There is no catch-all default: items stay unassigned (and show up in coverage) until the club defines explicit rules.
Steve Copeland
Also, we have a separate account to track this credit card processing fee. The actual amount deposited into cash is less the credit card processing fee.
Dave Rapin
Steve Copeland So with SportsPay we don't actually get a per item processing fee, we're just estimating that in the payout. We should be getting that info in the settlement though, so I'll check. Definitely a good catch, we need to track this.
Dave Rapin
Steve Copeland OK processing fees have been added with their own accounting code (if you choose to set it). The fees are expensed when the payments are deposited and not before (that's how we get accurate amounts).
Steve Copeland
Dave Rapin That's perfect. Its how we track them now.
Steve Copeland
One other comment. We currently have a separate account for club credit that is issued as part of a refund. How would this show up in the accounting report?
Dave Rapin
Steve Copeland yes, we need to add this to the transactions. Currently club credits / debits are tracked separately. We'll tackle this next.
Dave Rapin
Club credit tracking is live.
Steve Copeland
This looks great! Will have to run it in parallel with our current process to make sure it works the same way. One item that is missing is discounts/adjustments. How are these handled? Is the revenue reported in the accounting transaction less any discounts/adjustment that may have been applied? Currently we have a separate account code for discounts so that we can track them. Discounts are a per line item, but adjustments are applied to the entire order.
Steve Copeland
I see that the discount is applied to the line item. That should be fine.
Dave Rapin
Steve Copeland Yes discounts are just applied, we're not tracking them to a separate account. Open to consideration though.
Dave Rapin
Steve Copeland adjustments are tracked and they have their own account code.
Steve Copeland
Dave Rapin We do keep track of discounts. I wonder if other clubs do also.
Dave Rapin
marked this post as
complete
You can now turn on accounting code fields for your accounts and items from the Club (dropdown) -> Settings section.
Steve Copeland
Dave Rapin How will refunds show up? For example, if an order for September has been uploaded into the accounting system, and a refund has been issued in October, will there be a new line item with an October date in the order items report to reflect t his, or will the original order now show refunded. If the latter, the refund won't be captured in the accounting system.
Dave Rapin
Steve Copeland Refunds are not guaranteed to be associated with an order item, but when they are, it's just a status update on the order item.
It's really just the payments and refunds that are your credits and debits.
- Orders can have one or more items.
- Payments are associated against the order, not the order items, so unless / until the order is paid in full, it's not a clear path between items and payments.
- Refunds are associated against a payment and optionally against one or more items. You could have an adhoc refund without selecting any items.
- Both payments and refunds can be partial.
So I think those are what you are looking for. Maybe we need to produce one transaction log report that includes order item, order, curler, payment / refund, GL codes, etc.? The problem is still that a payment and refund may be associated with multiple order items and therefore multiple GL codes.
Steve Copeland
Dave Rapin Yes, a single transaction log report would be helpful. This would capture events when they happen, and not simply a status update to an entry that could have happened weeks ago.
Dave Rapin
Steve Copeland agreed.
How are you allocating partial payments across multiple items currently? Say I partially paid for an order that contains a league, a fee, and a locker. The total was $500 but I only paid $250.
Do you split the payment between the items, or do you allocate the payment sequentially (first item paid first, second item next, etc.)? What happens if you want to refund the league in entirety, but only part of the payment was allocated towards it?
Steve Copeland
Dave Rapin Currently we use the order items report, and a lot of SUMIF functions to capture each individual item that would be associated with an account. Assigning account numbers will make it much simpler going forward. Here's an example of the SUMIF - =SUMIF($B:$B,"Product",$W:$W)+SUMIF($B:$B,"League",$W:$W)-SUMIF($C:$C,"Youth Donation",$W:$W)-SUMIF($C:$C,"Locker
",$W:$W)-SUMIF($C:$C,"
Engraving*",$W:$W)We also make it more complicated for ourselves as almost everything tracked as a PRODUCT, so we need to break out various items.
Refunds are tricky. We refund the line item, issue a club credit, purchase a new item apply the credit and get paid the difference. Can't always guarantee that the journal entries are correct, which creates month end issues when reconciling.
Dave Rapin
Steve Copeland Do you ever have these scenarios?
- You want to apply a partial refund to just one item?
- You want to apply a partial refund allocated across all order items as a percentage while not fully refunding any of them?
- You have a partial payment for an order with a league and a fee, but later realized the curler already paid the fee and you want to refund just the fee, keeping the club. If a partial payment had split allocations, then you would need to rewrite history (not good!) or issue an adjustments in order to refund the entirety of the fee (complex!).
If we can just say NO to all of the above, I think we can keep things relatively simple while producing exactly what you need for your accounting system (immutable record of transactions).
Steve Copeland
Forgot to mention. The offsetting entry from the order items report is accounts receivable. We assume every order becomes a receivable, so we aren't concerned with a partial payment at this stage. We then use the payments report to credit receivables and debit cash. A remainder of a partial payment would still be a receivable. So, in your example, we would show revenue of $500 (broken down by category), cash $250 and a receivable of $250.
Steve Copeland
I think the answer to the second two items is no, but here is a typical scenario for the first one. We have a curler sign-up for one-night. They then decide that they want to upgrade to a full membership. We don't have an upgrade product, instead, we issue a refund for the full amount of the one night membership, issue a club credit, enter a second order for the full membership, apply the credit, and then collect the outstanding balance. I don't see us issuing a partial refund for a single line item, but we do issue a full refund for a line item within a larger order.
Dave Rapin
Steve Copeland OK so what I'm working on should address all of this.
For the refund / club credit dance I think we want to leverage order adjustments to end up with correct journal entries. Which should save you some time there w/ proper account codes.
Dave Rapin
Steve Copeland check it out: https://curling.io/docs/accounting/accounting-transactions
Ross Bairstow
Nice Dave, Guelph Curling (ON) (8 sheets) uses Quick Books Online and we welcome any integration with Curling/io that will improve our data collection and entering
Ross Bairstow CPA, Finance Committee
Dave Rapin
Ross Bairstow Good news then, we've already put in a preliminary QBO integration, so once we're done with this ledger work can I reach out and get you in the pilot test? (after this season wraps up most likely). Take a look at the updated description if you don't mind.
Dave Rapin
We're going to have a solution for tighter accounting integration / reporting by next season or earlier. This is one of our top three priorities for this year. This will include account labelling / categorization and better accounting specific report(s). It will also tie in with our Quickbooks Online integration and potentially other account packages down the road.
I have updated the description to match what we're planning. Open to feedback.
Dave Rapin
Merged in a post:
GL Category for Leagues, Products, Fees, Competitions
d
darrylhorne@shaw.ca
We'd like to put a GL category against Leagues, Products and Fees. Single field in each with the same title. Text only. No processing, just reported out with Order Items.
Amy Duncan
I have up to 3 levels for some of our items: 1) Chart of Account Number, 2) Class and 3) Project being able to label each item at set up would make bookkeeping so much easier later.
Dave Rapin
Amy Duncan In your case, are codes different for different events, or is it just one code to represent all competitions, one code to represent all fees, etc.?
Amy Duncan
Dave Rapin the first codes is the same but the second (and 3rd) are different.
Dave Rapin
Amy Duncan And these represent different accounts / buckets / ledger in QuickBooks? Can you provide me with a list (or partial list) if it's not sensitive data? (you can send it to me direct).
Dave Rapin
marked this post as
planned
Dave Rapin
Merged in a post:
Produce financial summary report
Steve Copeland
Add a report to be used to support accounting entries, summarized by item type/line item. Sample provided, along with how the journal entry would look
Load More
→