Accounting Patterns in CATS
Terminology
| Account (aka Ledger) | A grouping of transactions which, when summed together, has a specific, reportable meaning |
| Account Types | Whether the account records something we have (Asset), something we owe (Liability), or is transforming the item from one account/identifier to another (Turn). See ??? |
| Event Category | The broad intent behind a particular set of related (and mutually exclusive) Accounts. See ??? |
| Transaction Set | Two or more transactions that together form a single zero-summed entity. This is the basis of double-entry accounting, in that the sum of positive and negative transactions in a single set will always sum to zero. Where a single transaction set spans more than one Event Category, the sum of each Event Category within the Transaction Set must also equal zero |
Why use double-entry accounting?
Use of double-entry accounting simplifies the development of complex stock movement reports and allows the reproduction of a stock-status at any particular point in time.
The document Accounting Patterns by Martin Fowler (see attachment: Accounting (Martin Fowler)) provides a detailed discussion of the benefits of using the accounting patterns.
Transaction Table Design Principles
The following guiding principles have been considered in designing the transaction table for CATS.
- The table will only record data elements that have:
- An identifiable Commodity Type (cereal, pulse, etc.); and
- A weight (in Metric Tonne)
- Data elements contributing to the CATS KPIs must be included in the transaction table
- Data elements that require historical-point-in-time reporting should be included
- Conceptually similar events that result in different legal responsibilities or reporting requirementsshould be denominated into separate accounts
- Adjustments to stock due to losses in the DRMFSS stores and Adjustments to stock due to transporter negligence are conceptually similar: both are stock adjustments.
- However, the legal responsibilities are different, and therefore should be registered in separate accounts
- Retroactive adjustments to entries in the transaction table will use the difference adjustment accounting method
- The original transaction record will remain unchanged, and a new set of records will be inserted into the transaction table.
- The sum of the original records and all related adjustment records will add to the final, revised amount.
- Adjustments are registered in the same accounts the original, and are flagged by the letter ‘A’ in the sub-account field.
Account Management
Accounting Events
The Transaction Table us based around the different commodity-movement events, whether actual or planned, that occur within DRMFSS.
These events are are categorised according to their type and relationship with each other, and follow the logical flow of planning and actual commodity movement activities.
The following diagrams describe the different types of events and associated adjustments.
Normal workflow:
Adjustment workflow:
Account Types
Asset Accounts
As with a bank account, a positive balance in an Asset account indicates a desirable state of affairs, whereas a negative balance, indicating an overdraft, shows that some error has occurred. For example, the account “Stock on Hand” is an asset account. Both the overall balance for this account, and the balance based on each key identifier for this account (DRMFSS Hubs, Commodity Type, /wiki/spaces/CTS/pages/2162693, etc.) should always be zero or greater than zero. A balance less than zero implies that more stock has been taken out of the warehouse than actually exists!
Sum-of-Account States
- > 0 (Normal) More detail needed
- = 0 (Closed) More detail needed
- < 0 (Error) More detail needed
Liability Accounts
As with a bank loan, a negative balance in an Liability account indicates a normal state of affairs, whereas a positive balance, indicating an overpayment, shows that some error has occurred. For example, the account “Stock under Care” is an liability account – as long as the donor has entrusted us with their goods, DRMFSS has an obligation to take care of those goods.Both the overall balance for this account, and the balance based on each key identifier for this account (hub, commodity type, SI, etc.) should always be zero or less than zero (thereby showing the obligation). A balance greater than zero implies that more stock has been repaid to the owner than was originally donated.
Sum-of-Account States
> 0 (Error) More detail needed
= 0 (Closed) More detail needed
< 0 (Normal) More detail needed
Turn Accounts
More detail about Turn Accounts (if there are any)
Sum-of-Account States
- > 0 (Error) More detail needed
- = 0 (Normal) More detail needed
- < 0 (Error) More detail needed
Account Groups
- Actual: those events resulting in change to a physical commodity; and
- Planned: the registration of an intention to do something to the commodities
- A number of separate Plans can exist within CATS, each with a specific meaning
- Each individual Plan has a specific meaning for reporting (A Dispatch Plan, a Receipt Plan, etc.)
- A Plan can be compared against either the Actual status or against another Plan to obtain a comparative status. For example, compare the Dispatch Plan against the Receipts Plan to determine future warehouse usage
While the separation between ‘Actual’ and ‘Planned’ is logical and obvious, the separation between the different types of Plan is less clear. One way to determine whether two events belong together is: does one event have a direct, automatic impact in the other.
For example:
- The Humanitarian Requirements Document (HRD) sets out the total agreed requirements for the ‘Relief Programme’ for the coming year.
- At the same time, the PSNP Annual Plan also sets out the agreed-to requirements for the the Productive Safety Net Programme for the coming year.
- These two plans will impact
From this, it initially looks as though all three items belong in a single plan.
However:
- When a region makes a Relief request for the allocation of their assigned commodities, an RRD is issued, and the amount remaining to that region in the HRD is automatically reduced. In this case, the RRD and the HRD belong together in a single plan
- When a region makes a PSNP request for the allocation of their assigned commodities, an RRD is issued, and the amount remaining to that region in their PSNP Annual Plan is automatically reduced. In this case, the RRD and the Annual Plan belong in a single plan
- As a result of the annual requirements of both Relief and PSNP, DRMFSS, along with donors and NGOs formulates the total requirements for the year, and issues a call for donations. Resulting from this, donors pledgeto supply a certain quantity of food.
- When a pledge is made, this does not automatically mean that the goods will materialise – only that a promise has been made. Thus, the pledge cannot be taken as a guarantee of supply until the Gift Certificate has been processed, and WFP has indicated what portion of that Gift Certificate will become the property of DRMFSS.
- An increase or decrease in amount pledged does not automatically impact the the HRD or the PSNP Annual Plan, and therefore must be considered as a separate plan.
The following rules apply:
- While a single Account Set may impact multiple Event Categories (usually when a ‘Plan’ event triggers a physical commodity movement), the individual transactions within the account set must always sum to zero within their own Event Category. For example, within a single Account Set containing both ‘Plan – Commodity Utilization’ and ‘Actual Movement’ Event Categories:
- The negative ‘Plan – Commodity Utilization’ Event Category transaction must be matched by one or more positive ‘Plan – Commodity Utilization’ Event Categories, so that the sum of all ‘Plan – Commodity Utilization’ Event Categories totals zero
- The negative ‘Actual Movement’ Event Category within the same Transaction Set must be matched by one or more positive ‘Actual Movement’ Event Categories, so that the sum of all ‘Actual Movement’ Event Categories within the transaction set totals zero.
Actual - Physical Commodity Movement
This Event Category records movements that change the status of a physical commodity in some way. This can either be a geographical relocation of a commodity, granting or removal of access to a physical commodity (as in a loan or swap), or the removal or addition of a physical commodity from a location.
Sum-of-Event-Category
| < 0 (error) | More commodities have been distributed than DRMFSS has access to |
| = 0 (closed) | All commodities have been distributed to beneficiaries |
| > 0 (normal) | Physical commodities remain to be distributed to beneficiaries |
Planning - Commodity Utilisation
This Event Category records the current status of the planned distribution of commodities. All transactions in this Event Category must be backed by an official document of some kind (published HRD, signed RRD, etc)
Sum-of-Event-Category States
| < 0 (??) | ??? |
| = 0 (??) | ??? |
| > 0 (??) | ??? |
Planning - Pledges
This Event Category records ???. All transactions in this Event Category must be backed by an official document of some kind (??????).
Sum-of-Event-Category States
| < 0 (??) | ??? |
| = 0 (??) | ??? |
| > 0 (??) | ??? |
Events that Affect Transaction Table
Requirement Stream
- Requirement document [-]: for each FDP and for each round we need to register the commodities
- Requirement document plan [+]: The transaction will specify the weight of commodity
Receipt Stream
Pledge: Commodity promised by donor or other organizations for DRMFSS
- Pledge receipt [+]: the amount received by DRMFSS from donor organizations per commodity
- Requirement document plan [-]: this resets what has been registered in the requirements document event.
Gift certificate: occurs when DRMFSS is informed about the amount of incoming commodity.
- Gift [+]: donated amount by donor specifying the donation type (Donation, Loan from other donors, in-advance delivery, Local Purchase)
- Pledge receipt [-]: represents what has been registered on the positive side of the pledge event
Receive allocation: a receiving plan for each hub allocated by logistics at the federal level
- Receivable [+]: the total amount of commodity to be received by hubs
- Gift [-]: resets what have been registered during gift certificate event.
Hub receipts: occurs when donor commodities arrive at the hub’s Stores
- Goods on Hand [+]: the total amount arrived at the hubs
- Goods under care [-]: the total amount arrived at the hubs broken down by donor.
- Statistics [+]: the total amount arrived at the hub’s
- Receivable [-]: Resets what have been registered during the receive allocation event
Dispatch Stream
RRD document: final allocation of commodities destined for FDPs
- Requirement document [+]: resets the requirement document registered during planning
- Pledged to FDP [-]: broken down allocation of commodities for each FDP
Hub assignments/committing: assignment of SI and project code for each FDP allocation
- Pledged to FDP [+]: resets what has been registered during RRD document
- Committed to FDP [-]: registers the committed for each FDP together with SI and project code and designated pickup Hub?
Dispatch: happens when cargo is leaving the hubs to FDPs or Other hubs
- Goods in transit [+]: dispatched quantities broken down by [FDP or Other Hub], SI and project code.
- Goods on hand [-]: dispatched quantity that balances the hub receipts together with SI and project code
- Committed to FDP [+]: dispatched quantity that draws down the amount during registration of hub committing
- Statistics [-]: the total amount of commodities that is leaving the hubs
FDP receipt: when commodities have arrived at FDPs and a corresponding delivery receipt is issued (when the delivery receipt is presented to DRMFSS by the transportation company).
- Delivery receipt [+]: the total amount of commodities reaching the FDPs broken down by SI/project
- Delivery receipt (sub account – Lost) [+]: the total amount commodities lost during transit/transport
- Goods in transit [-]: balances the total amount dispatched from hubs
Distribution: when the FDP have actually distributed the commodities to beneficiaries and sent to DRMFSS payroll/distribution documents.
- Goods under care [+]: balances the amount during hub receipt
- Delivery receipt [-]: balances the total amount during receipt.
Adjustment Stream
Adjustment for receive planning discrepancies (Differences between plan and actual receipt). Variation between receive allocation and actual receiving: when there is a variation in any of the registered attributes recorded at the moment of the receive allocation; and the actual cargo arrived at the hub. The attributes expected to change are commodity, SI and/or hub.Example: Received plan of 5000mt of wheat are planed to arrive at Adama hub but actually it is redirected to Dire Dawa.
- Receivable (Adjustment) [-]: this will balance the receive allocation with the planned attributes (commodity, SI, hub)
- Receivable (Adjustment) [+]: the attributes (commodity/SI/hub) reflecting the actual received
Cross program borrowing. When PSNP commodities are distributed for relief program and vice-versa variation in program between hub assignment and the actual dispatch. Generally speaking this event covers all the other cases when any of the other attributes will change between planning and dispatch. The following attributes are foreseen to be changed: commodity, program, FDP, SI. Example: 5000mt of wheat is assigned for PSNP but the actual quantity for that program is only 3000mt so we take the balance of 2000mt from Relief.
- Committed to FDP (Adjustment) [+]: this will balance the hub assignment with the assigned attributes (program, commodity, SI, FDP)
- Committed to FDP (Adjustment) [+]: the attributes (program, commodity, SI, FDP) reflecting the actual dispatch.
Adjustments on annual stock take. When there is a difference in store number, SI between the storekeeper book and goods on hands account.
- Goods on hand (Adjustment) [-]: with the quantity difference between the inventory and the storekeeper’s books quantity.
- Goods under care (Adjustment) [+]: with the quantity difference between the inventory and the storekeeper’s books quantity.
Requirements planning adjustment. This happens when there is a variation in Requirement Planning
- Requirement document (Adjustment) [-]: with the quantity difference between the previous and the actual plan.
- Requirement document plan (Adjustment) [+]: with the quantity difference between the previous and the actual plan.
Accounts
Planning - Commodity Utilization
Requirement Document
Describes the amount of commodities planned by the HRD document and not assigned to any RRD.
Account Code | RDU (Requirements Document Unassigned) |
Account Type | Liability |
Sum-of-Account States | < 0 (Normal): Part of the plan remains unassigned = 0 (Closed): The plan has been fully assigned > 0 (Error): More has been assigned than was originally anticipated |
Mandatory attributes |
|
Reporting |
|
Events/Use cases |
|
Counter accounts |
|
| xxxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
Requirement Document Plan
Describes the amount of commodities planned by the HRD document and not pledged by donors
Account Code | RDP (Requirements Document Unassigned) |
Account Type | Asset |
Expected Account Value | < 0 (Error): ?? = 0 (Closed): ?? > 0 (Normal): ?? |
Mandatory attributes |
|
Reporting | A negative sum indicates the total quantity of commodities pledged by donors. A positive sum indicates the total quantity commodities requested by the regions. The total sum indicates the total quantity of commodities that are requested but not pledged by donors. If the total sum is negative, it indicates that Donors have pledged more than the request. |
Events/Use cases |
|
Counter accounts | Regional document Pledged receipt |
| xxxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
Pledge Receipt
Promises by donors against HRD document
Mandatory attributes | ProgramId, Donor, |
Reporting | A negative sum indicates the total amount of gifts effected by the donor. A positive sum indicates the total quantity commodities pledged. The total sum indicates the total quantity of commodities that are pledged but not yet gifted. |
Events/Use cases |
|
Counter accounts | Request document plan Gift |
| xxxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
Gift
Donations expected to arrive at hubs from donors
Mandatory attributes | ProgramId, Donor, Commodity Source, Gift Type (Loan, Local Purchase, Donation and In advance delivery) |
Reporting | A negative sum indicates the total amount of gifts allocated to be received by hubs. A positive sum indicates the total quantity of commodities donated by the donors. The total sum indicates the total quantity of commodities that are gifted but not allocated to be received. |
Events/Use cases |
|
Counter accounts | Pledge receipt Receivable |
| xxxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
Receivable
Commodities due to arrive at hubs
Mandatory attributes | ProgramId, Donor, Hub, SI/Project Code |
Reporting | A negative sum indicates the total amount of received commodities. A positive sum indicates the total quantity of commodities allocated to be received. The total sum indicates the total quantity of commodities that are allocated but not received. A negative total sum indicates an overdrawn amount of commodities |
Events/Use cases |
|
Counter accounts | Goods on hand Gift |
| xxxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
Goods on Hand
Inventory
Mandatory attributes | ProgramId, Donor, Hub, SI/Project Code, Store, Stack |
Reporting | A negative sum indicates the total amount of dispatched A positive sum indicates the total quantity of received commodities The total sum indicates the balance in the stores |
Events/Use cases |
|
Counter accounts | Goods in transit Receivable |
| xxxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
Goods Under Care
Liability account of donor balances
Mandatory attributes | ProgramId, Donor |
Reporting | A negative sum indicates the total quantity of commodities arrived at hubs A positive sum indicates the total quantity of commodities distributed to beneficiaries The total sum indicates the commodities that have arrived at DRMFSS hubs but not yet arrived to beneficiaries |
Events/Use cases |
|
Counter accounts | Delivery receipt Goods on hand |
| xxxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
Statistics
Available inventory by store
Mandatory attributes | ProgramId, Hub, Store |
Reporting | A negative sum indicates the total quantity of commodities dispatched from stores A positive sum indicates the total quantity of commodities received into stores The total sum indicates commodities available in the store |
Events/Use cases |
|
Counter accounts | Gifts Committed to FDP |
| xxxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
Committed to FDP
Commodities that are due to dispatched
Mandatory attributes | ProgramId, FDP, Hub, Donor, SI/Project |
Reporting | A negative sum indicates the total quantity of commodities assigned FDP for dispatching A positive sum indicates the total quantity of commodities that are actually dispatched to FDPs The total sum indicates commodities which are allocated but not yet dispatched |
Events/Use cases |
|
Counter accounts | Pledged to FDP Statistics |
| xxxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
Goods in Trasit
Commodities on the way to FDP / Other Hubs
Mandatory attributes | ProgramId, Source Hub, Destination (FDP/Other Hub), Donor, SI/Project, |
Reporting | A negative sum indicates the total quantity of commodities unloaded at the FDP A positive sum indicates the total quantity of commodities that were dispatched from the corresponding hub The total sum indicates commodities which are on the road between hubs and FDPs |
Events/Use cases |
|
Counter accounts | Goods on hand Delivery receipt Lost in transit/damage |
| xxxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
Delivery Receipt
Stock at FDPs
Mandatory attributes | ProgramId, FDP, Donor, SI/Project |
Reporting | A negative sum indicates the total quantity of commodities distributed to beneficiaries A positive sum indicates the total quantity of commodities that are received at the FDP The total sum indicates inventory at the FDP |
Events/Use cases |
|
Counter accounts | Dispatch Goods under care |
| xxxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
Sub Accounts
- ‘A’ represent adjustment sub-accounts that will be used whenever there is an adjustment for the respective account.
- ‘L’ represents loss. In any phase of the actual delivery a commodity is lost this sub account will be moved with the lost quantity.
- ‘D’ represents damages. Whenever a commodity is damaged we move this account with the damaged quantity.
CATS KPI and Reporting
CATS KPIs will be calculated from the transactions table as follows.
- Requirements are the total quantity of commodities to be provided to the beneficiaries. It can be calculated from the positive sum of Requirement document plan account plus the total balance of the corresponding adjustment accounts.
- Allocation is the total quantity of commodities that have been requested by the region. It can be calculated from the positive sum of Requirement document plan account plus the total balance of the corresponding adjustment accounts grouped by region.
- Receipt is the total quantity of commodities that have been received by DRMFSS hubs. This value is calculated from the positive balance of Goods on hand account plus the total balance of the corresponding adjustment accounts.
- Dispatch is the total quantity of commodities leaving DRMFSS warehouses. This value is calculated from the balance of Goods in transit account.
- Delivery is the total quantity of commodities delivered to the FDP or Woreda. This value is calculated from the positive sum of Delivery receipt account plus the total balance of the corresponding adjustment accounts grouped by FDP or Woreda.
- Distribution is the total quantity of commodities signed for by the beneficiaries. This value is calculated from the negative balance of Delivery receipt account plus the total balance of the corresponding adjustment accounts grouped by FDP or Woreda.