Bookkeeping
From FreemedDeveloperWiki, the FreeMED developers' Wiki.
The FreeMED bookkeeping system needs to provide a way in which to answer certain queries. A design shall be proposed, and a selection of queries shall be demonstrated. NOTE: the specification is currently incomplete. It is still in serious flux.
| TODO: answer all of the questions at the bottom. add more questions. should the cash record refer to the A/R record that the payment was applied to, or does referring to the procedure accomplish that? |
Contents |
The Design
Having absorbed the basics of double-entry accounting, the system shall reflect the basic principle that every transaction is a transfer in some sense. Each transaction will entail two records, attached to two different ledgers. The information needed for each record will be as follows:
- transaction date (and time?)
- amount
- ledger type (see below)
- link to the payor and the procedure involved. (if applicable---payor may be blank, see below.)
- description (check number, etc)
- type of transaction: payment, write-off, refund, transfer (others?)
An effort will be made to keep the scope of the bookkeeping system under control. For instance, Irv has asked what would happen if a family of four all had procedures---would four bills be sent out? That problem is not within the scope of this design; making one patient responsible for another's bills is the responsibility of another subsystem.
A Sample Ledger
The Accounting Equation is: ASSETS = LIABILITIES + EQUITY. We don't track liabilities, so that becomes ASSETS = EQUITY. Assets include cash and accounts receivable; equity includes revenue. An example ledger containing "Day 1: Did a procedure and billed $50 for it. Day 5: Got paid in full." would appear as follows:
| Assets | Equity | ||
|---|---|---|---|
| Cash | A/R | Revenue | |
| Day 1 | 50 | 50 | |
| Day 5 | 50 | (50) | |
| TOTAL | 50 | 50 | |
Thus, at every point, the accounts balance.
The Ledger Types
In accounting, there are five major types of account: Assets, Liabilities, Equity, Income and Expenses. We don't use all of them, as FreeMED is not designed to track expenses.
- ACCOUNTS RECEIVABLE accounts represent accounts receivable from a particular payor. (Assets)
- REVENUE accounts represent the value of the services provided, at the time they are provided. (Equity)
- CASH accounts represents actual receipts as they are paid. (Assets)
Note that all ledgers can be represented by entries in one big ledger table. Each entry is simply marked by its type and a link to a procedure. Thus, ledgers are virtual in the sense that they are only "created" when records are added referring to them.
A Note on Signedness
Our goal is that SUM(amount) for the entire ledger table will come out to zero. How will we accomplish this? With judicious use of signedness. The semantics are as follows:
- ACCOUNTS RECEIVABLE accounts are negative when the practice is owed money. Payments to an A/R ledger cause the value to increase.
- REVENUE accounts are positive when the practice performs a procedure.
- CASH accounts are negative when money is received into them.
The Flow
A procedure is performed. Revenue and accounts receivable are recorded, attached to that procedure and the responsible payor(s). Payment is received; the accounts receivable are debited, and cash increased.
The Queries
The structure of this section is designed to be of use to the programmer, not the end user. The answers will thus be slanted toward SQL-speaking readers.
Note that every transaction adds up to zero. Because every transaction adds up to zero, the accounts will always balance.
The Basics
- How do I enter a procedure?
The cost of the procedure is x. Add an entry to the accounts receivable referring to both the responsible party and the procedure record, for amount -x. Add an entry for x in revenue referring to the procedure record.
| Account | Amount | References | Type |
|---|---|---|---|
| Revenue | x | Procedure | PROCEDURE |
| A/R | (x) | Payor, Procedure | PROCEDURE |
- How do I enter a payment?
The payment is for x. Add an entry to the accounts receivable for amount x, referring to the payor and the procedure record. Add an entry for -x in cash referring to the payor and the procedure record.
| Account | Amount | References | Type |
|---|---|---|---|
| Cash | (x) | Payor, Procedure | PAYMENT |
| A/R | x | Payor, Procedure | PAYMENT |
- How do I bill to more than one payor?
In this instance, the cost of the procedure is x, but there's a copay c. Add multiple entries to the accounts receivable, one for x-c for the insurer, and c for the patient. Add an entry for x in revenue referring to the procedure record. (This is why a revenue record does not refer to a payor---it's not well-defined.) If the patient has paid the copay on the spot, enter a payment for it as specified above.
| Account | Amount | References | Type |
|---|---|---|---|
| Revenue | x | Procedure | PROCEDURE |
| A/R | (x-c) | Payor (Insurer), Procedure | PROCEDURE |
| A/R | (c) | Payor (Patient), Procedure | PROCEDURE |
- How do I transfer responsibility for a bill?
Issue a debit to one A/R account and a credit to another, as follows.
| Account | Amount | References | Type |
|---|---|---|---|
| A/R | x | Payor (Original), Procedure | TRANSFER |
| A/R | (x) | Payor (New), Procedure | TRANSFER |
- How do I perform a write-off?
Issue a pseudo-payment to the A/R record. And decrease the revenue pool, because it turns out you didn't really make that money after all.
| Account | Amount | References | Type |
|---|---|---|---|
| A/R | x | Payor, Procedure | WRITEOFF |
| Revenue | (x) | Procedure | WRITEOFF |
- How do I perform a void?
More Complex
- How do I generate a patient statement?
Select from accounts receivable where the patient is the responsible party. To get a full statement, select all A/R records for that patient and show the full history. To get a compact statement, group by procedure and show only those with a total balance not equal to zero. For instance, say that part of the ledger looked like this (the references would be database IDs, of course, but writing it this way is clearer):
| Date | Account | Amount | References | Type | Note |
|---|---|---|---|---|---|
| Day 1 | A/R | (50) | Martin Heidegger, Toe amputation | PROCEDURE | Initial procedure entry |
| Day 1 | A/R | 25 | Martin Heidegger, Toe amputation | PAYMENT | He pays some of it on the spot |
| Day 5 | A/R | (20) | Martin Heidegger, Office visit | PROCEDURE | Initial procedure entry |
| Day 5 | A/R | 20 | Martin Heidegger, Toe amputation | PAYMENT | He pays off most of the rest of the toe amputation |
The proper answer is that Martin owes $5 for a toe amputation on Day 1, and $20 for an office visit on Day 5. The generated statement would look like:
Martin Heidegger Toe Amputation (Day 1) 50 Payment (Day 1) 25 Payment (Day 5) 20 Remaining 5 Office Visit (Day 5) 20 Remaining 20 Total charges 70 Total payments 45 Total remaining 25
The format is unimportant; all of the relevant information can sliced out of the database.
For a compact statement, grouped by procedure and displaying SUM(amount):
| Account | Amount | References |
|---|---|---|
| A/R | (5) | Martin Heidegger, Toe amputation |
| A/R | (20) | Martin Heidegger, Office Visit |
- How do I generate reports on a per-provider basis?
Use a join with the procedure table and the provider table, with SQL code like "WHERE ledger.procedure_id = proc.id AND proc.provider_id = provider.id AND provider.id = 'foo'".
- How do I make sure that the books balance?
SELECT SUM(amount) from ledger. It should come out to zero.
- How does closing work? Daily, monthly, yearly closes?
- How much did we bill to a particular payor?
- How much did we bill out from each department?
- What was the total revenue for all instances of a particular procedure?
- How much did I bill out to a particular insurance company on a particular day?
- How much am I owed, and by whom?
