Re: Proposal: Outline of financial table schema for 1.4

["Chris Travers" <..hidden..>]

On Nov 11, 2007 1:05 PM, Herb Richter <..hidden..> wrote:

> Having now committed three of five of our companies' books to LSMB I do
> have some concerns and suggestions.  Particularly: 1) a journal vs GL
> driven accounting system and  2) expanding the accounting system into
> include other business functions (as opposed to co-operating with those
> functions).

See below as to journal vs GL.  In reality these are different views
of the data, and data entry/storage in all cases looks more like a GJ
than a GL.

I suspect the issue is rather one of point of view and communication
rather than disagreements on substantive issues :-)

>
>
> 1) Journal vs GL:
>
> I'm not sure that I know what is meant by "journal" in the proposed
> schema.  I think of journals as being part of a businesses' "Books of
> Original Entry" i.e. General Journal, Sales Journal, Receipts Journal,
> Purchase Journal, Disbursements Journal and perhaps other special
> journals such as a Wage Book.  In a data system, I think of these as
> views of the more basic atomic data.  I wonder if "journal" means
> General Journal (though it actually looks a lot like a General Ledger).

Yes.  Basically a General Journal which is expanded to include
financial data from other journals (i.e Sales Journal, etc).

>
> The big advantage to centering the accounting system around the GL is
> that all businesses have relatively similar GLs while the Sales,
> Receipts, Purchase and Receipts Journals can be very different from one
> business and another or from one industry and another.  The GJ is (as is
> the GL) quite consistent across businesses but it would quickly become
> cluttered if used to record all transactions.

I would call this a "Master Journal" rather than a General Journal
because the other journals are essentially subsets of the data
presented (no separate sales journal).  The data is entered and stored
in a way similar to the General Journal, and other tables store
additional data.  This essentially centralizes the accounting data in
one place (currently the system claims to be GL-centric, but
transaciton data can be stored in ar, ap, *or* gl, and it makes a GL
simply a logical view of the master journal.

It might be helpful to say what is changing rather than just the
conceptual framework behind it.
1)  *all* financial transactiosn will be stored in journal and
journal_line, rather than the master record being in either ar, ap, or
gl.
2)  payments will be full-blown financial transactions rather than
lines on other transactions.  This means that there is *one* date on a
transaction, not dates on line items, and we map payments back.

Note that the terms "General Journal" and "General Ledger" don't
really match what is going on internally of any computer accounting
system I am aware of.  Obviously the GJ and other journals are books
of original entry, and then the data is taken from these multiple
books and written into a general ledger, one account per page or
section.  The GL does not contain all necessary information relating
to the transactions, and entries are referentially linked to the
source journals for more information.

The ideal database design for these things is to consolidate the
financial aspects of all journals together and then add referential
solutions for additional information specific to the journals.  The
multiple journals and GL become logical views of the entered data.

>
> I think any auditor would have a big issue if a business had no GL!
> When assessing a company, I think, most people would look first at the
> financial statements, then the trial balance and GL and then at
> individual transactions via any journals.

Correct.  Not that we don't have a GL.  This is largely a labelling
issue more than fundamental issue.  Do we call the main transaction
metadata table a "journal" or a "ledger?"  It doesn't quite match the
storage formats people use on paper, but then no computerized
accounting systems do (do you really want us duplicating data between
journal and ledger tables? ;-) ).  I think that "Journal" is a better
label because the other joining tables provide enough metadata to
create the journals (and ledgers) from it.

>
>
> 2) Accounting system and other business functions:
>
> It is very tempting to add stuff to an accounting system but can end up
> mixing apples and oranges.

Yes.  One example is the fact that accounting practices are very well
defined and deviation from them is bad, while customer service
processes are not so prescriptive.  However, most of these things end
up hitting the financial books, so one runs into issues of where to
stop.  Do we want to just do accounting?  Or do all of the ERP
functions as well?

I think the sense right now is that we need to do at least financial
and supply chain management stuff well (primarily because supply chain
management is very tightly integrated with financial logic).  A lot of
other things can be available later as add-ons.  For example, I don't
see us ever offering a full CRM solution out of the box but I can
imagine an assortment of addons available in this area.

Noice that I said "financial" rather than "accounting."  I don't see
us handling all areas of "accounting" out of the box either (for
example, tax accounting varies greatly from country to country, so
while we track sales tax as part of SCM, corporate income tax is
beyond what we want to include out of the box).

Otherwise I agree with your points below.


> Tolerance for errors:
> The Accounting division of course needs to balance to the penny (which
> is easily enforced) but the accuracy of the entries needs to be achieved
>   in other ways.  But lets say that the books need to be 99.9% correct.
>   It would be a mistake to try to impose this accuracy onto say
> inventory or onto the front line sales staff or order pickers.  In my
> previous retail business (of 25+ years) I considered that if inventory
> was more than 97% correct (that is in line items - not total value) that
> my staff was waisting time and money being too careful.

Ok, these are not new problems.  You have the possibility of theft of
merchandise, cashier error, and a whole number of other possible
issues.

The typical way that operational error is handled is to simply accept
the numbers in general and then periodically check and correct.  We
need to be working on interfaces for some of these adjustments
(particularly as relate to inventory.

For factors you can reasonably predict, the accountants should have
reasonable ways of setting allowances for them.

>
> Operations vary drastically:
> Among the millions of points here would be that one business might need
> to handle only a few invoices and sales per period while another might
> need to be very efficient at handling many sales of small dollar amounts
> and margins.

Agreed, which is why we are working on separating mechanism from
interface.  The mechanism needs to be solid and handle accounting
issues correctly.  The interface may need to be customized for each
deployment.

>
> Notice that "invoicing" is in Operations not in Accounting.

It isn't just a question of Operations vs Accounting.  GL Journal
Entry has the dame issue.  Not every business needs the exact same
thing.  I think it is better to look at this from an interface vs
mechanism rather than an operations vs accounting angle.  In fact, can
you think of any screen in the application would would be so stable as
to need no customization for anyone?

>
> Lets take as an example a retail operation where a customer walks in and
> orders a $30. item that is not in stock and needs to be ordered.  It is
> the store's policy to require a deposit on any to-be-ordered item.

Ok, that brings up one case I need to add to the proposal-- tracking
pre-payments on orders.

>
> My interest in a specialized LSMB would be for multi-tenant commercial
> property rentals.  This involves:
> - mostly re-occurring entries but also
> - many disbursements that need to be allocated to some or all tenants in
>    a particular property based on a formula
> - budgeting

Depending on how you want to amortize some costs, you might also need
to track unit availability.  For example, suppose you put in new
counter-tops.  Do you amortize the cost of those counter-tops over the
usable life in renter-months?  Or straight-lined?  (I don't know what
acceptable practices are in that area; you should check with your
CPA).

Best Wishes,
Chris Travers