Financial Reporting: Break out extra charges
Anytime Memberships are reported out, maybe not at the high summary level, but at the indivdual membership level I would like to see a breakdown of membership fee vs. extra item fee (e.g. In July I had $500 of membership income, how much was from elite level, how much from regular level, and for each, what percentage is membership fee and what percentage is extra items).
Release 5.1.2 implemented the wishlist item 'Custom Reports/Full Transaction Data Extract' for financial reports. However, not all financial fields were made downloadable. We still need to export invoice line items (so, line item description and the amount and whether it's taxable), rather than just the total amounts for an invoice.
What information would you miss if we export invoice line-items (Option 2)?
Robert Routledge commented
Option 1 is the only one that meets our needs, and I would assume the need of any non-profit that has to reconcile their books? We have to be able to see the "extras" a member purchases as part of their membership in detail in order to do this. Option 2 doesn't show enough detail, option 3 appears to be "change nothing".
Jon Carlson commented
Yes, I really need this too - break out the items people signed up for. I'm similar to Pamela Graham in that I need to import the "Extra Items" amounts separately from the base membership fee to break out different budget items. I am currently trying to import the Quickbooks export into Quickbooks Online (with mixed success), and I've had to write my own script software to massage the exported .IIF data.
For budgeting and reporting purposes we need to break out Extra Costs. As a swimming pool, we charge for Guest Passes, Voluteer Fee, Initiation, Bond, etc. Right now it's all lumped into "Membership".
PAmela Graham commented
As a sailing club Members pay fees and boat charges according to type of boat and variables as to whether cruisers are on a mooring or not. We have set up boat charges as 'extra charges' for invoicing, but we will need to be able to export the detail of charges by boat type etc for financial accounting (boat fee income by various category). We are currently piloting WA to see if the financial reporting and link with Quickbooks makes it a long term option for us. We really like some aspects of WA, but hopefully your current work on financials will be addressing some of our concerns (these are similar to those expressed on this and other threads). Thank you.
During analysis of related thread on improving financial exports (https://forums.wildapricot.com/forums/308932/suggestions/8826166), we have also investigated some of the options to report on extra charges. We explored several solution options and want to verify them with you before making any further moves.
We see at least 3 options to break out extra charges:
Option 1: further improve invoice export and add dedicated column for each extra charge. E.g. one column for shipping, another - for certificate option, etc. See example: https://docs.google.com/spreadsheets/d/1ekz1xSkvhTs8pE_ZS7ctmVY9QOreLcs3BmSsXh3TFdM/pubhtml?gid=1372848087&single=true
- might be too many columns to export, especially when invoices for events and membership mixed
- hard to implement at the moment, requires deep architectural changes
Option 2: make an invoice line-item export, where each row represents single invoice item. Common invoice fields, like number, date, etc are duplicated in each row. See example:
- no architectural problems for implementing
- format is digestable by QuickBooks (probably with some extra plug-ins/adaptors)
Option 3 is not to bother with individual extra charge export and report consolidated numbers. I've added some more details in this thread: https://forums.wildapricot.com/forums/308932/suggestions/10198449
Any comments on which option is better are appreciated. Or suggest your own one :)
Oleg, Product designer @ Payments team
Shelbi Hoffman commented
I vote for the feature! I have a client that charges a membership fee, but gives the options for membership materials to be shipped for $20. We would like to easily break out this fee instead of having to track it manually. I would also vote for the ability to add donation fields within the membership application/renewal process.
When we hold an event, national dog show, our registration form covers a lot of territory with some item choices being free while others, such as meals/seminars, charge various dollar amounts. The export to Excel feature includes the item label but not a dollar amount column. To see a complete record broken down by attendee and each registered item and cost, you either have to go through all the invoices or manually add a dollar column after each item. From a “reporting to treasurer” standpoint, this is a fairly big problem.
I have “wished” for better reporting since we joined Apricot back in the Stone Age and hope this will be considered in their next update. PBGVone, PBGV Club of America
Any news yet on whether this function will be added?
Our "Member Late Event Registration plus Membership Renewal" is the same price as our "Non Member Event Registration" for example (there are other combinations that are likewise indistinguishable in the total, especially if one person is paying for multiple guests with different items for each!).
We also have some items we charge for (Continuing Education Units) that require separate paperwork processes and follow up to ensure people get their items.
Simply having those item component fields appear in the search and in an export would be very helpful to see who paid for what and make sure everyone gets what they paid for.
Thanks for your comments - we will be working on that but I cannot promise the dates, unfortunately.
We certainly agree that the accounting for extra fees is woefully inadequate. The lump payment is stored and retrievable but non of the details are available either in excel, quickbooks or via reporting. We need to be able to distinguish these extra fees from dues.
Jennifer Walker commented
I agree. Being able to code costs on the back-end, or see a more detailed breakdown, will be extremely helpful when it comes time to record revenues in an accounting program. Otherwise you are required to manually allocate rec'd revenues to multiple revenue accounts.
I would go a step further and request the ability to code a single fee to multiple categories. For instance, I work with a client who receives annual member fees that are allocated to three separate accounts: $150 - $75 to member dues, $70 to regional fees (this is paid to the regional offices bi-annually) and $5 for printing fees.
The greater the ability to pull detailed financial reports, the greater the ability to streamline and increase efficiencies, the greater my ability to make my clients happy :-)
I would also like further breakdown as to the source of the revenue --- broken down by the source of the revenue. It makes it easier for a trip leader if all the money order/checks that they have collected are totaled clearly in one column, keep credits applied separately, and on line revenue (credit card payments) a separate total.
I think the old payment report separated on line revenue from manual payment revenue, and I don't see that in the new one. That will be missed. Now that we can also issue credits to invoices, that should be a separate entry too.
Dmitry Buterin commented
Sorry, we did not have this on the wishlist so we simply did not think about it. Now that we have this here, I would appreciate comments from others to help us prioritize this.
I think a possible solution is to separate the base registration fee/membership level fee from all other charges in the Income report.
I was disappointed to see that the financial upgrades in version 4.0 continues to lump income for an individual member into a single bucket with no breakout as to source.
Specifically, membership fees and extra charge items purchased during membership signup/renewal are reported as a single lump sum. In my case I have membership levels at $20 and at $25 but I also have an option for members to have their newsletter delivered by surface mail for and extra $5. I may have $500 in membership fees at the end of the month but no idea how to divide up the revenue between membership and newsletters.
Granted, we can export the membership files and do the whole thing manually but we could do that before version 4.0.
Also, as I understand it, the same is true for Events. Extra items purchased during event registration are lumped in with registration income and we have to go in at a detail record level and break out these charges manually.
The reality is that Excel is still the best reporting tool.
As of right now, when you run a Payments/Refunds report it only shows what invoice type, like Membership Renewal or Conference, was paid for. I would love for it to include the membership level, and for conference payments a list of the add-ons purchased. Or maybe we could integrate General Ledger account numbers into the system, and the reports could just do a breakdown, like $300 - 500 (Conference Registration)/ $50 - 525 (Conference Dinner).
It would also be awesome if I could get a Income Report based on the payments received instead of invoice date like it currently is set.