Hi Anthony,

I've built an accounting system from scratch and here are some of the things
I did (I'm sure they can be improved upon but it currently works):



1) I have a general ledger table and a sub-ledger table. The
sub-ledger holds all accounting at the order number level. The general
ledger holds summarized entries by company, division, accounting period,
batch id and g/l number. My users can enter in a "batch" of invoices of say
20 or 30 invoices to separate customers. Upon posting the batch, I print out
journals for user entry verification (as well as screen edits). Also upon
posting, a batch id is assigned and the records get posted to the sub-ledger
and ledger tables (and others as needed like an invoice history table). My
general ledger table has one amount field in it but when I run my G/L
report, I create calculated fields: 1 for a debit amount, and another for a
credit amount. I also have a "source" field in the both ledger tables.
This tells me how the entries got there: INV (invoicing), JRN (journal
entry), REC (cash receipt), CHK (check/disbursement), ADJ (adjustment). And
of course I log the user id and entry timestamp. Having only one field in
the general ledger table makes it extremely easy to post records to the
table and query.



2) Manual journal entries are always required. There are always
beginning balances even if starting a company from scratch: capital
investments, cash, fixed assets, etc. And J/E are needed on a monthly basis
as well.





3) I too have a year-end close process that simply roles up all the
income and expense accounts into retained earnings (or a similar account).
This process also creates a balance record for each company/division/year/GL
account number. This way, I don't have to query many years of data, just go
to the g/l balance table, get last year's balances and total up the current
year.



4) I don't have sub-account numbers, just a 10 character G/L number
but I wish I did have sub-accounts. Several of my customers have requested
them but changing to a format like that now would be a huge deal.





5) Reporting you can go crazy on. I've built in some drill-down
features which have turned out be invaluable. When my customers run the G/L
report and select "drill down capability", the user can click on say a batch
id of invoices. The report expands to show all the order numbers/source
entries that make up the summarized G/L report entry. (hope this makes
sense). I also put the drill down on my balance sheet and income statement
to show source G/L entries and a drill down within a drill down to show
order numbers as well. Users really like this and it helps finding entry
errors.



Also, it can never hurt to have a company field or in my case, company and
division fields. This way you can have separate financials for each company
and/or division. My system can handle multiple companies and multiple
divisions within a company. If you create something like this then you will
probably have to have a "consolidation" feature for your financial reports.
This allows the user to create different consolidation combinations with
company/divisions to typically produce a company-wide balance sheet/income
statement.



Hope this helps,

Kevin













_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Anthony Tanas
Sent: Friday, December 08, 2006 11:21 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] My ACCOUNTING table structure



Some obversations:

a) Is this for a single entity, or are there divisions, branches,
departments involved? May need additional fields & reporting for this.

Single entity. There may be multiple offices but I think I have that
covered.

b) Printed output - reports of patients accounts, statements,
reminders & mailing, aged listings, trial balances, reportwriter access

There will be a "walk out statement" that will correspond to one SUPERBILL
and a "mail out statement" that will show activity that has an open balance.
Also I will need to provide AR reports and various productivity reports.

c) Cash accounting records not clearly detailed here - cash books,
monthly bank reconciliations

Not sure what you mean about cash accounting records? There will be a CASH
account, or more specifically two seperated by the source of the money
(insurance or patient). I'm not sure if bank reconciliation is necessary as
I'm not trying to replace the role of an accounting package (at this time).
In my current released version of my beta software I have set up a simple
single entry accounting system (basically just charges and credits) and have
run into some issues that are not too cleanly resolved (like refunds,
patient credits, reversal of write offs, et. al.). I want to do things in
more of an accounting way to better handle these issues and also I would
like the option to expand it into a full featured accounting system in the
future.

d) Budget figures required?

Not at this time.

e) Ledger account entries typically have an opening balance, a debit
column, a credit column and a (current) balance column, with
a date for each posting to each account.

Well my LEDGERACCOUNT table just defines the accounts and then the LEDGER
table stores each transaction. As far as the current balance goes, won't
that be a calculation? I mean I shouldn't necessarily store that I don't
think. The opening balance would be assumed to be 0 on all accounts, aside
from any data I convert into the system.

f) Are subaccounts required to provide a categorised breakdown
per account?

No, I'm trying to keep it as simple as I can at the moment.

g) Manual Journal entry functionality is commonly required

Can you please elaborate on this? In my application I plan to wrap up all
of these tables into various classes that will then move data back and forth
to the user inteface. My billing screen will have simple to use functions
for the user for any activities they would need. Basically this would
include the following functions: adding CHARGES, PAYMENTS, DISCOUNTS, WRITE
OFFS, INSURANCE ADJUSTMENTS, REFUNDS and then reverseing any of those. I
will not allow anything to be deleted once it is "posted".

h) Multiuser with access/role controls for security reasons

Well my system is definately multi-user and I have security. I would take
care of security at the application level. Basically my application has a
login, and my application controls what can be done based on the application
log in. (There is a single Firebird login embedded in the application.)

i) Ability to handle other accounting functionality - creditors
& suppliers accounts and payments, rent, repairs & maintenance,
vehicles, assets, equity & capital accounts etc

Yep, defiantely don't want to do that now. This is a pretty big task at the
moment. However I definately want what I do have to be done correctly so
that I can grow into that sort of thing in the future.

j) Year end accounting close off possibly months after the
actual end but still able to process & report on the new
year.

OK, now here is something I was wondering about. Why is this necessary? I
can tailor my reports to report on any period of time the user wants. Why
is it necessary to "close off" the account and functionally how would this
be done?

k) Reporting will be extensive - current output functions
will be a good guide to the level of complexity & transaction
volume, which will then indicate the type & extent of
storage & summarisation required.

HTH,
John

I guess to summarize what I am trying to do is that I want to have sort of
minimal accounting functionality done in a correct way so as to be flexible
and avoid problems I have encountered in my beta test so far and leave me
the option to grow accounting functionality in the future. However it is
not required to take the role of the accounting pacakge at this time. Thank
you for your insights! :)

-Anthony

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]