Aller directement à la fin des métadonnées
Aller au début des métadonnées

Most of the action is over at: Accounting integration improvements

Prior discussion and development of proposals for improving support for financial tracking and accounting integration in CiviCRM is below.

General discussion of questions and issue around CiviAccounts is happening on the forum over at: http://forum.civicrm.org/index.php/topic,12264.0.html

Remembering that this is a wiki page with multiple contributors the text may not always seem to flow (sourire)

This page has a lot of the use cases that were originally anticipated would be put here: CiviAccounts Functional Requirements (Should we move them all there, noting that authorship of different bits will then be incorrectly attributed, and then separately create a CiviAccounts Civi-Make-it-Happen Campaign page once the different use cases to be targeted are clearer on the CiviAccounts Functional Requirements page?)

State of Play

CiviCRM 3.2 includes a range of Data Schema changes as a result of Phase 1, discussed in the Completed Phases section below.

We are now working on a range of additional use cases to discuss how these can best be accommodated in the new Data Schema, or what additions/changes may be desirable, to facilitate integration with an accounting package for the majority of users of CiviCRM (whether they use a Cash or Accruals method of accounting).

Completed Phases

Phase 1 - Separating payments from obligations/contributions

Phase 1 of the "CiviAccounts Project" was to separate the recording of payments from obligations/contributions, so that there can be one payment for multiple obligations/contributions and multiple payments for one obligation/contribution (ie partial payments for events, memberships, pledges and donations).

This phase involved extending the existing civicrm_financial_trxn table.  Changes made that were included in CiviCRM 3.2 are in the following page.

Phase 1 Implementation - Data Structure Changes

Phase 2 & Phase 3 Implementation - Data Structure Changes

Future Phases

Phase 2 - User interface/experience

Phase 2 of the "CiviAccounts Project" is to develop the user experience for separating payments from contributions/obligations to pay, using the work done on the data schema in Phase 1, with amendments to the data structure as required.

The following page was created for much of this detail (Proposed Phase 2 Implementation - User experience), but for now, the more high level thoughts and use cases are set out below.

Phase 3 - CiviAccounts API

Once CiviCRM is storing data consistently, it is then possible to build the API.  It is important, however, to ensure that the requirements for the API are understood at the outset, so as to ensure the data entered through the UI is stored appropriately.  It is proposed that the requirements for the API be developed here: Proposed Phase 3 Implementation - API

Priorities for the planned CiviAccounts Civi-Make-it-Happen campaign

Overview goal: All features that are expected parts of a standard Accounts/Receivable ( A/R ) system.

These items are ordered by order of importance: 

1) Get many-to-many transactions to obligations working. ( Both in the database as well as the UI. Many-to-many includes the following use cases:  

 - One check/credit card transaction that needs to be allocated to many obligations ( such as event fees, member dues, donations, etc ) (eg Staff/bookkeeper gets a single check, that can be applied to multiple pledge payments, event fees or membership obligations.  The bookkeeper should be able to change the allocation if needed. ( For example, 50% of the check is originally applied to an event fee. Later on, it isrefunded for the event and instead applied to a membership fee. )

- One obligation that will be fufilled by many transactions. Also known as partial payments. So someone could make a deposit for an expensive event or member dues, and pay it off over time. 

- Ideally, an administrator would generate an "invoice" for a website member.  This invoice would be a comprised of one or more pending contributions.  The administrator would generate the invoice in PDF or HTML format so that it could be emailed to the website member (there are already custom mail merge tokens that can be used in CiviCRM 3.1 or better to produce statements. A sample statement ( as a PDF ) is attached to this wiki document)

 

Detailed Use cases that need to be supported:  

- Within the staff area of CiviCRM, a staff person ( such as the bookkeeper ) who is sitting with a stack of checks, has a screen to record checks, and allocate various amounts to different obligations. For example: one check for $200 could be allocated as $50 for an event deposit ( the event total price may be $300) , another $50 could be a voluntary donation, and the last $100 is a partial payment for their annual membership dues. The checks would be from a variety of contacts. The bookkeeper must be able to handle this task without being forced to jump around to various contact screens. ( The headache in the CiviCRM version 3.2 is that the bookkeeper must first bring up the contact summery screen for the right contact, then click the "pledge" tab and find the right link for "Record payment." Then they must bring up the contact record for the next contact, and repeat these steps. If recording 50 checks from 50 different contacts, this is very time-consuming. ) 

- Re-allocation of previously applied amounts. Continuing on the previous use-case, the person cancels their participation on the event, and now wants the $50 ( originally for the event deposit ) reapplied towards their member dues. 

- A staff member should be able to record a returned payment, such as when someone withdraws their child from school or cancels an event registration. ( The staff would need to issue a check or credit card refund outside of CiviCRM, since CiviCRM is not an Accounts/Payable system.  ) . This payment would be a separate record in the CiviCRM database (as the original payment is unchanged). Another example here is a check that bounced. In order to cater for invoice based accounting the original contribution record should also remain but a credit contribution should be created & linked with the event (this provides full tracking of payments too). The credit may be less than the contribution (partial refund). Organisations with different accounting requirements may still prefer just to cancel contributions and it may be that a separate drupal permission for 'cancel contributions with attached payments' is required to allow organisations to enforce accounting rules if they wish.

- The staff person should be able to batch-register people for a paid event or membership, or donations or pledge, which results in a unpaid financial obligation for all those contacts. ( Since CiviCRM already supports batch actions for event registrations and membership creation, those existing screens can be enhanced. ) 

- The contact should be able to see all financial activity, unpaid obligations, and scheduled payments on their contact dashboard.  So if a person ( such as a member ) logins into the website,they can see all their financial activity and current unpaid balance.

- The contact should be able to make a payment online that covers multiple obligations ( either in part or in full ). Such as they make a deposit for school tuition, plus a fundraising dinner, and part of their member dues in a single credit card transaction.  A common situation with parents paying for schools is they pay an initial deposit before the school year starts, then make several large payments at the end of each semester.   

- The staff can email or print a statement that shows a summery of all obligations, money received, open balance, and amount due right now. ( i.e. the statement that is attached to this wiki article that uses my custom mail merge tokens. )

- When the staff creates any type of  financial obligation for a contact, they can set up the planned schedule ( such as a pledge of $2400, with payment expected every month for 12 months.    ( This already exists in the pledging area of CiviCRM)  

- Plus all the use-cases detailed in the partial payment child document attached to this wiki page. 

2) Better tax-handling. Currently each contribution type is either tax-deductable or not. In the world of nonprofits, this is not sufficient.  Some events of $100 may have a fair market value of $30, so only $70 is tax-deductable. An annual membership of $2000 may have a fair market value of $500.   

Use cases that need to be supported:

- An event is setup with the fees being $100 for an adult, $50 for a child.  The fair market value of the adult meal is $30, so only $70 is deductible. The fair market value of the child's meal is $20, so only $30 is deductible.  

- An event is set up with a complex price set. Each item in the price set has a different fair market value. 

- Different membership types and dues are set up, with each type having a different fair-market value.  ( For example, some membership type may include use of the swimming pool and therefore has a higher market value than a lower membership level. ) 

- The staff should be able to email or print a year-end statement that shows all their financial activity for that calendar year, with tax-deductible amounts split out into its own section. ( This is needed by the donor in order to claim those amounts on their U.S. tax returns ) 

Plus all the use-cases described in the fair-market value child document attached to this wiki page. 

3) Make it easier to reconcile income against deposits. ( one bank deposit may include many smaller checks. ) 

- NEED DETAILS

4) Simplify and automate exporting relevant data to QuickBooks and other widely used general ledger programs. 

5) Create additional financial reports that are part of typical Accounts/Receivable (A/R) systems.

- an aging report

- a report that shows money that is expected, but not yet received, grouped by contribution type. 

6) Third-party payments, shared obligations

use-case 1 :  A parent registers their 2 children for school ( a CiviEvent ) and elects to pay the tuition obligation with an initial deposit, followed by 12 monthly payments. The parent pays the initial deposit and first 3 monthly payments as expected.  Then for month 4 and 5, the staff gets a surprise check from the grandparents to cover the tuition payment for month 4 and 5.   The grandparent expects to get a tax-letter and receipt for their payments. However, the obligation for the parent would be reduced. 

A Caution

I know that we serve organizations in all kinds of tax-jurisdictions. However, in the United States the grand-parent is not eligible for a "tax-letter" as donations that benefit a single individual are not in fact donations:  

In this case the staff recording the check from the grandparents should have the option to indicate if this particular payment is tax-deductible or not. This requirement overlaps a bit with the above part 2 titled "Better tax handling"

use-case 2:  A person signs up for membership that is an annual fee of $3600.  They elect to pay this obligation in monthly payments of $300. They pay the first 6 months of payments as expected. Then their parents mail a check to cover 1 or more months. The parents expect a tax-receipt for the check they sent. However, the membership obligation for their adult child would be reduced.  

use-case 3: A divorced mother registers her 2 children for school.  She will be responsible for 50% of the tuition, and the children's father will be responsible for the other 50% of the tuition. 

Case for an API approach

The next section is written on the assumption that rather than focussing on writing the integration the focus will be on developing an API interface to allow multiple integrations which may or may not be built into the core. The assumption is that CiviCRM itself may change the way it stores it's data (e.g. to accomodate multiple payments against one event) but the changes will not be visible to the integration.

The API should return results both as a multi-dimensional array and as XML.

Accounting data concepts

If CiviCRM is able to make data available that maps to the key Accounting data concepts then this allows interested parties to develop for their favoured accounting package. I am not envisaging that CiviCRM would provide 'double entry accounting of any sort' - that is the role of an accounting system. It should provide relevant data instead. The data concepts that are important in an accounting system are
* Debtors & creditors
* Invoices & credit notes
* Payments
* Credit matching

Accounting Vocabulary

The word "invoice" can mean different things in different contexts or audiences. 

To the person who owes money to a nonprofit, they may call the piece of paper they get in their mailbox ( or via email inbox  ) once a month an "invoice". If it is for a single new amount owing, it is indeed an invoice. However, documents that summarize all financial activities for the previous month that are generally sent out are technically a "statement" or "statement of account". They  list all their financial obligations, transactions, and a calculated current total balance. 

To the bookeeper at the nonprofit, an "invoice" means one unique financial obligation toward the nonprofit (more details in 2) Invoices / Credit Notes section below).  One person may have multiple invoices: $50 for an event, $1200 for a pledge, $2400 for a different pledge, and $3000 for annual member dues. This means that individual has a total of $6650 of obligations to the non-profit. 

Accrual basis accounting = Recording income as accounts receivable when earned and recording debts as accounts payable when they are incurred.  In this system, if someone registers for an event and chooses "pay later", this obligation would need to be shown in the general ledger. When the check/cash/credit card is received, this "adjustment" transaction would need be sent to the general ledger. If the person cancels their event participation in the event, then another adjustment transaction would need to be sent to the general ledger.  

Cash basis accounting  Method of accounting in which expenses are recorded when cash is paid and revenue is recorded when cash is received.  In this case, if a person registers for an event and chooses "pay later", nothing would be shown on the general ledger. When the check/cash/credit card is received, then the information would show on the general ledger.   If the person cancels their event participation before they have paid, then nothing needs to be sent to the general ledger. As this example shows, cash basis is generally simpler for nonprofits.  

For CiviCRM to know which transactions need to be sent to the accounting package/general ledger program, CiviCRM will need to know if the organization is using accrual basis or cash basis accounting.

1) Debtors and Creditors.

These are essentially just contact records filtered according to whether or not they have engaged in a contribution or outgoing. The exisitng getContact API may be sufficient but the following are likely to be required (potentially as search fields)

  • created date
  • last modified date
  • last financial transaction date

2) Invoices / Credit notes -

An invoice is issued to indicate a payment is or will be due from the perspective of the issuer. Payment of invoices may occur at the time the invoice is generated (like an on-line donation) or it might take place in installments over years (for a pledge that has been invoiced). Accounting systems can be set up so that payments of certain types can only be entered if they are applied to invoices. Generally this is the case when the accounting system is also being used to track client accounts for those kinds of payments. In some of these approaches it can be appropriate to create invoices when a payment is received in order to allow the financial tracking to work for that class of funds. Pending invoices will not have payments matched against them yet, even if a batch of post-dated cheques may be on hand since they won't be entered until the appropriate period. A recurring contribution can be set up a series of invoices (appropriate when there is no ongoing commitment (ie no pledge) and it could be stopped at any time) or as a series of payments against a single invoice (eg monthly payments to pay for an annual membership or a multi-period pledge). Depending on the accounting approach adopted, pledges can be accounted for either as assets in the form of future receivables or booked as income in the form of current receivables in the period in which they are received.

A credit note is a decision to cancel some or all of a commitment. At the moment CiviCRM doesn't deal with these very well as there is no good way of recording a partial credit/ refund. From an accrual method of accounting point of view the original invoice should stand but a credit note (of the same or a lessor amount) should be raised. Currently CiviCRM just shows the contributions as cancelled. This should be 'opaque' to the API and if CiviCRM changes it should be invisible to the accounting system.

The accounting system may wish to engage with individual invoices or aggregate amounts. Contributions already have a field for accounting code but I think this is insufficient as I have always used this plus two additional fields in the accounting systems I have used - e.g. I can categorise transaction in my accounts system by account code, project code & job code giving me a range of reporting options. Xero refers to these extra custom fields as 'tracking fields'. I would suggest that at least two should be part of the information returned from and API call. In addition, line items in transactions, such as elements in price sets, need to have full accounting information available since not all line elements in a transaction are necessarily the same account (see 5) Enabling Contribution Type to be specified at line item level section below).

Invoices should have at least 3 available reference fields - one for the processor, one for any banking reference and one for the accounting system.

The Xero API page is useful for considering what sort of data might be in an invoices API. Note that they differentiate between incoming and outgoing using a single field 'ACCPAY' vs 'ACCREC' which is a good option for allowing future extensibility.

http://blog.xero.com/developer/api/Invoices/

Note that a multidimensional array would include line items.

3) Incoming & Outgoing payments

One or more payment might be matched against any invoice. The payment/s could be negative or positive. (e.g. someone overpays and gets a refund, aka a credit note). A payment could cover more than one invoice.

I actually believe the most logical way to represent payments and invoices in CiviCRM is to move all actual payment data to the civicrm_financial_txn table - not just credit cards. There is already a table which could be one-to-many linking this with the contributions table. Conceptually the contributions table would then be 'invoices' and the financial_txn table would be payments. But I recognise the change may be unrealistic. On the other hand, this change would lay the groundwork for multiple payments against one event & partial refunds.

4) Credit matching information (ie. a table that connects the two)

Credit matching is the process of linking commitments to pay with actual payments. It is a fundamental concept in accounting systems and a separate table would hold the linkages. This may not be a separate API. Xero has one payments API:http://blog.xero.com/developer/api/Payments/

5) Enabling Contribution Type to be specified at line item level

For accounting purposes it is often important to be able to indicate what monies are received for. CiviCRM supports an arbitrary number of Contribution Types and the definition of an Accounting Code for each one. It would be beneficial to be able to indicate the Contribution Type for each line item, rather than at Contribution Page level. This will involve changing the data schema, a number of Administration pages for CiviContribute, CiviEvent, and CiviMembership, and several reports.

The RNAO is intending to work on implementing this feature. See Enabling Contribution Type to be specified at line item level to assist in developing the specification.

6) Changing and Cancelling Financial Transactions

This is a more focused version of items 2 and 3 above.

CiviCRM should have payment processors that support refunds or reversals of charges in order to automate changes and cancellations of charges and ensure the financial information is accurate in CiviCRM. This would allow higher level functionality to be developed that will be very useful for administrators.

CiviCRM should be changed so that changes and 'deletions' of payments do not change the original records but post auditable 'difference' transactions that adjust the original transaction appropriately. Reversing a charge, as when a prepaid event registrant can no longer attend, should result in a second refund transaction being posted. In some cases, for example, registering for events with pricing for optional sub-events like luncheons, banquets, or per-session fees, it is very time-consuming to administer changes and cancellations of registrations. After a change, 'difference' transactions would be posted into CiviCRM that included line items that reversed previous charges no longer appropriate (eg refunding cost of luncheon no longer desired) and that added charges for new items (eg for a dinner banquet now desired). Where the changes resulted in a net difference in the total amount owing, an email would be issued in cases where payments were to be made offline, and a refund or new charge would be posted to a payment processor otherwise.

The RNAO is intending to work on implementing this feature, perhaps in a phased manner concentrating on meeting their priority needs first. They intend to implement a Chase PaymenTech plugin upon which the higher-level functionality could sit. See Changing and Cancelling Financial Transactions to assist in developing the specification.

Batches for Data Entry and Financial Auditing

A standard accounting practice for dealing with large volumes of payment transactions is to create batches that summarize anywhere from a few to a few hundred individual transactions. Several of Joe Murray's clients batch together cheques that have been received. Some payment processors also only report online transactions in terms of batches, e.g. all of the transactions for a day are reported together as a single batch and are included in a single transfer to your account.

A batch tends to correspond to a bank deposit. Sometimes there is a need to produce a list of cheque amounts that are included in the deposit to save them writing out a long deposit slip (especially one that might have different data than the system gets), but other times it is sufficient to have just the number of items like cheques and the total value of all items recorded on the deposit slip. Each batch needs a unique batch number that the system should generate. This is the idea of tagging a bunch of transactions as matching a particular banking entry. A financial batch API would return a multidimensional array with each batch (corresponding to a transaction at the bank) having several transactions as part of it. Depending on the integration this might be used or the payments API might be used.

Advantages of this kind of financial workflow include:

  • Data entry errors are minimized because the batch total can be checked easily and must match the deposit total recorded by the bank.
  • It is possible to trace a cheque that has been received to the corresponding bank deposit, and go from a bank deposit to all of the items in the deposit.
  • Financial auditors require the use of batches in many cases.
  • Banking transactions can be reconciled with the batches in a straightforward manner.

Normally a batch is opened, data entry proceeds, there may be a need to make additions and corrections of typos over the course of a few days, then once the batch is finalized it is closed and posted, and no further changes are allowed to it.

A user interface for this functionality would allow a financial admin person like a clerk to create a batch, have the system generate a unique batch id, and allow the user to enter two pieces of information at a minimum: the total number of items, and the total amount of all the items. Line item data entry needs to allow existing contacts to be referenced easily as the contributor, and also allow new contacts to be added easily. Usually there would be other information recorded, namely what the payment is for. This might require just selecting a value from a custom select field for a mailing, or possibly include a bunch of functionality discussed elsewhere on this page that would assign the incoming payment to outstanding items on a statement for a client.

How many batches do orgs have open at a time?

  • Sometimes, an org will have one batch per payment type per staff person dealing with that payment type per transaction type (ie different batches for cheques for each current event and also for memberships).
  • Question: how do we deal with errors in batches? option 1: post change/adjustment transactions against batch. Option 2: Edit batch without leaving history ??? Option 3: only close batch after reconciliation is work ???

Classes and Subclasses

 In many double-entry accounting packages, notably QuickBooks, support the concept of classes and subclasses.  Classes and subclasses have many potential uses and is a useful tool that many accountants/bookeepers make use of to help organize their financial information.  For example, a nonprofit may have a "Clergy Fund" and within that fund there is a restricted class and an unrestricted class. 

In any transfer of data from CiviCRM to QuickBooks ( and other accounting packages), the class/subclass information needs to be part of the data transfered from CiviCRM. 

Scoping considerations

Integration with an accounting package will necessarily require specified transaction data to be stored by CiviCRM, so that it can be exported to or otherwise accessed from within the third-party accounting package (whether Quickbooks or otherwise).

In deciding what transaction data to store and how to store it, a review of Open Source accounting packages is useful.  In this process it is worthwhile considering whether a CiviAccounts "Component" should be built to use the API or data structure/store of an existing accounting package, so that people who want more full blown accounts systems can install that accounts package alongside CiviCRM.

If an API is used, this creates a dependency that may cause maintenance issues in future and make it more difficult for basic users to implement the CiviAccounts Component.

If a shared data structure/store is used instead of an API, there may not be a dependency (as installing the full accounts package would be optional), but it would still create maintenance issues as the accounting package develops independent of CiviCRM.

A nice feature in either case would be for the design to provide for interchangeable "Accounting Connectors", like interchangeable Payment Processors contributed by the CiviCRM Community, that will bridge CiviAccounts with accounting packages that are in demand (or a basic "inbuilt" CiviAccounts data store - which may be the place to start).  Having "Accounting Connectors" will make the job more complicated at the outset but provide greater flexibility.  Either approach would also create a need for cross-domain knowledge to be gained and maintained within the CiviCRM community in order to keep up with developments on the Open Source accounting side (which has its pros and cons).

CiviCRM is currently focused on tracking Contributions, rather than expenses.  It is therefore proposed that the first phase of this project should focus on developing the appropriate data structures to store Contribution/Pledge information and Payments/Accounts Receivable information.

Accounts packages currently being focused on

A comparison of accounting packages can be found on Wikipedia.

Packages for which interest has been shown so far are

Quickbooks Integration - using .iif (Intuit Interchange Format) files exported from CiviCRM and imported into QuickBooks (and some other packages too) - see technical spec for them at http://support.quickbooks.intuit.com/support/Articles/HOW12778

Xero

MYOB

If you have a view on any existing Open Source accounting packages, please make them known!

GnuCash

Data Model

Here is an image of the current draft data model:

Étiquette
  • Aucun
  1. Nov 24, 2009

    Removing as I edited the main part instead

  2. Feb 15, 2010

    I have a few simple ideas that I would like to flick around for comments. Please let me know what you think about it.

    PART 1 - MODULE FOR CALCULATED FIELD "Account Balance"

    The purpose of this module is to provide a simple solution for handling currency accounts balances and statements

    1. FIELDS

    In this customization we will need a Custom Data Group called "Currency Account". with the following fields:

    Field: CONTACT - "Currency Account Balance" (Money)

    Description: this field records the assets and liabilities associated with a given contact. Assets are the contrbutions made. Liabilities are the fees payed for events. The field value for a contact is calculated according to the sum of its assets minus the sum of its liabilities.

    Behaviors:

    1) Whenever a contact is Registered to an event of a certain type (i.e. his event workflow status is changed to "Registered"), the total event fee is SUBTRACTED from this field.

    2) Whenever a contact makes a contribution of certain type(s), the contribution amount is ADDED to this field.

    Field: CONTACT - "Currency Account Top-up Threshold" (Money).

    Description: This field is used to evaluate wether it is already time to send request for a contribution. This is a standard field defined arbitrarily - it is not calculated. This field will be used to trigger reports and/or workflows, as described below.
    Field: CONTACT - "Currency Account Top-up Deposit" (Money)

    Description: This field is used to define the amount of the top-up contribution that will be requested to the contact. This is a standard field defined arbitrarily - it is not calculated. This field will be used to trigger reports and/or workflows, as described below.

    2. WORKFLOW

    In my implementation, I will need the following workflows:
    Custom Workflow 1: Request Contribution Top Up

    How the Workflow Would be Done if Done Manually:

    1) Once a week, users of a certain group (let's say, Managers) must review all the account balances, and find out wether they need to request additional contribution top-ups to certain contacts, based on the value of their "Currency Account Balance". They need a "Currency Account Balance Report", showing, for each contact, the following columns:

    Field: "Currency Account Balance" (Money)
    -----------------------------------
    Field: "Currency Account Top-up Threshold" (Money).
    ------------------------------------
    Field: "Currency Account Top-up Deposit" (Money)
    -------------------------------------

    2) Then, they evaluate each record as follows:
    If the Field: "Currency Account Balance" is lower than the Field: "Currency Account Top-up Threshold" (Money), then the Manager writes a Standard Email Message to the Contact requesting a Contribution of a certain Type and Description, corresponding to the monetary value of the Field: ""Currency Account Top-up Deposit".

    How The Workflow Could be Done Automatically with civiCRM:

    1) Once a week, users of a certain group (let's say, Managers) generate a Listing called "Low Balance Report", showing all contacts whose Field: "Currency Account Balance" is lower than the Field: "Currency Account Top-up Threshold" (Money).

    2) The Manager sends a Mailing to all contacts in the group, containing:
    a) A descriptive summary of the "Currency Account" history (a statement).

    This statement shows all the credits and debits in the Contact's Currency Account.

    The statement period is variable according to the following criteria: the statement covers the period between the first previous date the Contact payed a Contribution of the type "Deposit", and the date the statement was generated.

    The statement includes the last deposit, and all subsequent credits or debits to his "Currency Account", including Event Fess of a certain type, and other credits set arbitrarily by the Manager (e.g.: Contributions of the type "Discount", "Credit", etc.)

    Looks more or less like this
    >>>>

    Dear John,

    As of today, your Currency Account Statement is the following:

    Statement for Contact John Doe

    Date Description Amount Currency Account Balance
    January 10, 2010 Deposit - Thank you! $ 200 $ 210
    January 15, 2010 Event Fee $ 80 $ 130
    January 22, 2010 Event Fee $ 100 $ 30

    >>>
    b) A Reminder of the commitments made by the Contact in the past.

    The reminder reminds the Contact of the committments he has made.

    The committment is registered in the Field: "Currency Account Top-up Threshold"

    The message looks more or less like this
    >>>

    When you joined our organization, you requested that we notify you whenever your Currency Account Balance falls below $ 50.

    As you can see by the statement above, that has happened on January 22, 2010.

    >>>
    c) A request for an additional deposit.

    The amount of the request is registered in the implied in the Field: "Currency Account Top-up Deposit"

    The request looks more or less like this:

    >>>

    Please make sure to top-up your account now. Please make a deposit of $ 200.

    All you have to do is click on the link below to make the deposit:

    Click here to make a deposit of $ 200 to your account

    >>>
    3. CONFIGURATION INTERFACE FOR THE MODULE

    The module needs a configuration interface.

    The following items should be configurable:

    Select Event Types for Currency Account Balance

    The user selects in a dropdown menu all the Event Types he wants to include in the Currency Account Balance.

    Selecting the Event Type causes the module to "pay attention" to event fees generated by events of this type, therefore charging the Currency Account with the corresponding fee.

    Select Contribution Types for Currency Account Balance

    The user selects in a dropdown menu all the Contribution Types he wants to include in the Currency Account Balance.

    Selecting the Contribution Type causes the module to "pay attention" to contributions that type, therefore crediting the Currency Account with the corresponding amount.

    Configure the Currency Account Statement Message

    The user writes in a text area the desired messages to be included in the account statement.

    This is particularly useful for supporting multiple languages.

    This could be implemented either in the module conifguration interface as a series of text areas, or as a the standard custom mailing configuration, in where the Manager includes the custom field markers for the variables to be included in the Account Statement message, including:

    Unknown macro: {The Contact Name}
    Unknown macro: {The Account Statement Table}
    Unknown macro: {The Currency Account Top Up Threshold}
    Unknown macro: {The Currency Account Top-up Deposit}

    PART 2 - MODULE FOR VARIABLE CREDIT TRANSFERENCE BETWEEN ACCOUNTS

    Description: This module is useful for when a Contact gets a comission for Event Fees and/or Contributions payed by another Contact.

    We can think of a special relationship type called "Receives Commissions From".

    The Contact may receive commissions due to Event Fees and/or Contributions payed by all Contacts that belong to a certain group, or (optionally) with whom they have a special type of relationship (e.g.; "Receives Commission From").

    1. FIELDS

    In this implementation we will need a Custom Data Group for contacts called "Comissions". This Group comprises the following fields:

    Field: CONTACT - "Comission Level"

    Description: this field records the percentage of comission to be payed to this contact.

    Field: CONTACT - "Comission Value"

    Description: this is a calculated field. It is a record of the value of the comissions due to this contact.

    2. WORKFLOWS
    Custom Workflow 2: Transfer Comission to Currency Account

    How the Workflow Would be Done if Done Manually:

    1) Once a week, users of a certain group (let's say, Managers) must review all the Event Registrations with a view to calculate the Comissions to be transfered to selected Contacts Currency Accounts.

    The Manager would generate a report similar to the Custom Report "Event Participant" (see above). This Manager keeps in his desk a reference table called "Comissions" in where he has a column listing all the groups, the names of the Contacts receiving comissions in this group, and a column displaying the Comission Level percentage.

    Then, he would calculate, for each Group, the total comission to be payed, based on the "Total for Group - Sum of All Fees", multiplied by the percentage of the Comission as per the reference table.

    Then, he would open the Contact that receives comission for that group, and add a Contribution equivalent to the comission amount (Contribution type "Comission Paid"), to the Contact that receives comission.

    Then, he would repeat this tedious operation for all groups and comissioned contacts.

    How The Workflow Could be Done Automatically with civiCRM:

    1) Once a week, users of a certain group (let's say, Managers) generate a Listing called "Comissions Due", showing a list of all Contacts to whom comissions are due. This list contains the following colums:

    Group
    Contact Receiving Comission for that Group
    Comission Level (from Custom Data Group "Commissions")
    Event Fees (sum of all Event Fees for the Group)
    Comission Value (calculated)

    2) Then, he could either "Select all" or select just a few, and then click on the button "Transfer Comission to Contacts as Credit".

    3) As soon as the Manager clicks the button, the system generates the "Confirm Comission Transfer" page (more or less like the confirmation page in the Event Registration), showing all the credit transfers about to be made, informing that, as soon as the Manager clicks the buton "Comfirm Conission Transfer", these credits will be added to the corresponding Contact Currency Account as "Contribution: Type: Comission Credit". The manager may also hit the "Go Back" button to review his previous selection.

    4) When the Manager clicks the "Confirm Comission Transfer", the Field "Comission Due" for each contact is reset to zero.

    5) Concomitantly, the system triggers the worflow that sends the email message "Currency Account Balance Statement" (see description module above) to each Contact that is receiving comission.

    3. CONFIGURATION INTERFACE FOR THE MODULE

    The following items should be configurable (and possibly others, as needed)

    Select Event Types for Comissions

    The user selects in a dropdown menu all the Event Types he wants to check for comissions due. This will cause the module to query only for those event types when calculating comissions.

    1. Feb 17, 2010

      One thing that strikes me is that you are looking at using a field for the currency account balance rather than having it calculated. Is this for speed reasons (i.e when generating reports off that field)

      Will CiviCRM's ability (or lack of) to accept part-payments for events matter to you?

      1. Feb 17, 2010

        I feel more comfortable having a field rather than relying on calculations. I know how to handle fields in mailings, but I don't know how to implement calculations and reports, so the field approach seemed easier for me from the user point of view. Developers are welcome to devise more efficient and usable solutions with these requirements/ideas in mind.

        In regard to civiCRM's limitation to accept part-payments for events: that doesn't matter much to me, provided that the contact has a urrency account in where we can check at any time wether the contact is in debt or in credit.

        André

  3. Nov 08, 2010

    Shawn Duncan dit :

    Building our own accounting package doesn't leverage our strength.  CiviCRM tracks people and their interactions with the organization.  Some of those interactions involve money.  We should improve our flexibility with money, and our reporting, such as Sarah's statement.  We also should build an interface that we can extend and adapt to communicate what CiviCRM knows about money to software that specializes in tracking money.  This architecture is what I have in mind.

  4. Jan 21, 2011

    small wish-list item --

    It would be useful to have a tool to move contributions from one record to another, similar to how we can move case activities. it's not uncommon in a charitable org, for someone to make a contribution online and later want it attributed to their family (household record) or a trust, or other entity. currently there's no way to move it except via the db. i understand there are auditing implications, but there are legitimate reasons why a contrib should move.

  5. Mar 01, 2011

    It doesn't make sense to have Civi know or care about cash vs accrual accounting. I think it should be cash only in-civi. Major accounting packages that I have looked at for integration work only want a list of customers, invoices, and payments for those invoices (more or less) and don't care what method the other end of the tie-in uses. Once it has that data, it is then up to the accounting package to determine how to format it for it's reports. 

    1. Mar 09, 2011

      I agree that Civi doesn't need to be involved in which accounting method is used - beyond possibly some reports that people using one or the other may be used. The focus of discussion around these two to my mind is whether the work being discussed meets the needs of those using both methods.

      Key aspects that we do need for syncing with accounts are

      - many to one payments

      - 'fixed invoices' - i.e once an invoice is created it should continue to exist for auditability

      - 'fixed payments' ie. once a payment is actually received the record of it should always continue to exist (even if it is refunded)

      - auditable invoice sequencing (having a record of all invoices created - this is a key audit requirement to demonstrate a staff member hasn't been doing some personal fund-raising on the side and deleting the paperwork)

      Most of the complexities that have been discussed come from these 4 requirements.

    2. Mar 09, 2011

      Andrew Perry dit :

      Further to Stephen and Eileen's comments, whether you account for your transactions on a cash vs accrual basis is "just" a reporting issue, the key is ensuring that Civi captures the transactions in a streamlined way that enables those reports to be generated (eg by an Accounting Package) - so Civi needs to capture Purchases/Orders (ideally one or more items purchased/ordered at the same time eg membership + event rego), Payments Received against one or more Purchases (eg bank deposits), Expenses incurred (eg Payment Processor fees that are deducted from the amount receipted to the donor) and Payments made (eg Grant payments).  The new financial data schema has been designed with this in mind.

      Whether you only report on the money paid into the bank and out of the bank for the purposes of accounting (Cash basis) is then a matter for the organisation, but they'll still want to know if someone has RSVPd for an event/pledged funds, etc, but have not yet paid, or that you've agreed to give a Grant but haven't paid it.

  6. Apr 18, 2014

    Just asking - is the CiviAccount project still ongoing?

    thanks,

     

  7. Apr 18, 2014

    JoeMurray dit :

    Most of it was released in 4.3. Some of the elements that did not make it into the final scope come back as separate projects. There is active work on Sales Tax support, and a minor bit of work on Partial Payments.

  8. Aug 06, 2014

    So is CiviAccount part of core, or is it an Extension? sorry, I don't see the UI and wanted to use Partial Payments..thank you!

  9. Aug 08, 2014

    JoeMurray dit :

    CiviAccounts was an initiative to improve core support for bookkeeping, eg to make it auditable.

    Not all of the original scope of CiviAccounts was completed given limitations on funding. Partial payments got pushed out of scope towards the end, though the schema changes support it. CiviCRM 4.5 which is coming out soon includes:

    I'm also currently working with a few other consultants and organizations to round up funding to hopefully extend this to contributions and memberships for 4.6. Please contact me at joe dot murray at jmaconsulting dot biz if you might be able to contribute to that.


Creative Commons License
Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-Share Alike 3.0 United States Licence.