Designing Accounting Systems - General Ledger 1

[greenbird]

Does anyone know how to or have an overview on designing an accounting system, specifically a general ledger or an acccounts receivable system in MS Access?

 

[willir]

greenbird

Design - Paul Litwin and JeremyNYC web site...

http://www.abcdataworks.com/Developers/Index.html

Nmaes..

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=2190

Suggestion...
If this is a serious database (big numbers, taxes, payroll), and you lack experience, then please consider getting a contractor or such for assitance. This will be a learning expereince of which I am sure you will succeed, but playing with money is not fun.

On the other hand, if you have experience, or this will be a casual affair, then go for it.

Start kit

tblGL
GLID - autonumber, primary key (PK)
GLAccount - text, unique
GLDescription - text
+ other info for the account


tblJournalMaster
JournalID - PK
JouranlDesc
PostPeriod -- yyyymm or similar
PostDate -- date


tblJournalDetail
JournalDetailID - PK
JournalID - Foreign key to the master file, FK
GLID - FK to GL table
GLAmount


tblTransaction
TransID - PK
GLID - FK
JournalID - FK
PostPeriod
PostDate
TransAmount


tblGLBalance
GLBalanceID - PK
GLID - FK
PostPeriod
BalanceAmount

I broke a couple of rules for normalization, specifically, in the transaction file, but this is so you can quickly grab posting information.

Writing the code will be another thing.

Richard

 

[greenbird]

Richard,

Thank you for your prompt help. This is a good start for me. However, would it be possible for you to show me how these tables would be affected by a journal entry example, lets say using the example shown below:

Debit Oct. 22, 2003 Cash $500
Credit Oct. 22, 2003 Fixtures $500
Sale of fixtures to Joe


Regards,

Peter

 

[lespaul]

As a former accountant and a current programmer, I would seriously recommend you look into purchasing Peachtree (my personal choice) or quickbooks. They are ready out of the box for around $200.

But, if you are going to continue, here are some things to consider first: is this a cash or accrual based accounting? If accrual based then you will need to set up Accounts Payable and Accounts Receiveable journals. Is there inventory? I'm going to assume yes since you are showing a sale for your example. Then it's accrual based. Is it for an S-Corp, sole propreitor or partnership? This determines how the net income/loss is taxed. Is this to replace an existing accounting process? How are you going to store the beginning balance information? You'll need to be able to modify past year balances after tax preparation adjustments (like depreciation). What about employees? Are you going to want to calculate payroll? Then you'll need to program the tax deductions.

So, even if you just create a basic accounting program, you are going to have to do all the following:

Create/modify/delete General Ledger Accounts
Create/modify/delete Customers, Vendors, and possibly Employees

Purchase/Receive/Sell/Adjust Inventory
Write/Record/Edit Checks
Record/Edit Deposits
Record/Edit/Delete General Journal Adjustments

Create Account Reconciliation process

I would strongly suggest that you purchase a ready made accounting program.

Leslie

 

[willir]

Leslie

Another star for you...


GreenBird
To validate the use of Access as GL package, you ask yourself what is different from my accounting from what is offered in shrink wrap applications. I have not seen PeachTree, but from the "feel", QuickBooks is designed very similarily to how I would design an accounting application, and after spending hundreds of hours.

To answer your question...

tblGL
GLID GLAccount GLDescirption
1 ABC-100-0001 Cash
2 ABC-100-0002 Bank account
3 ABD-100-0001 Sundries
4 ABD-100-0002 Fixtures


tblJournalMaster
JournalID JournalDesc PostPeriod PostDate
1 Fix lights 10/2003 10/22/2003


tblJournalDetail
JournalDetailID JournalID GLID GLAmount
1 1 4 500
2 1 1 -500


tblTransaction
TransID GLID JournalID PostPeriod PostDate TransAmount
1 4 1 10/2003 10/22/2003 500
2 1 1 10/2003 10/22/2003 -500

 

[willir]

Leslie

Another star for you. I should have added this comment in my original post. Dahh.


GreenBird -- Sorry, hit the worng button and posted first by accident..

To validate the use of Access as GL package, you ask yourself what is different from my accounting from what is offered in shrink wrap applications. I have not seen PeachTree, but from the "feel", QuickBooks is designed very similarily to how I would design an accounting application, and after spending hundreds of hours. (And this was perhaps the first time I ever came to this conclusion after working with numerous of other applications including AccPac and ADP.)

To answer your question...

tblGL
GLID GLAccount GLDescirption
1 ABC-100-0001 Cash
2 ABC-100-0002 Bank account
3 ABD-100-0001 Sundries
4 ABD-100-0002 Fixtures


tblJournalMaster
JournalID JournalDesc PostPeriod PostDate
1 Fix lights 10/2003 10/22/2003


tblJournalDetail
JournalDetailID JournalID GLID GLAmount
1 1 4 500
2 1 1 -500


tblTransaction
TransID GLID JournalID PostPeriod PostDate TransAmount
1 4 1 10/2003 10/22/2003 500
2 1 1 10/2003 10/22/2003 -500


This schema will work, but you will still need to do a lot of coding - calculation of sales tax, create reports, etc. It would be a great excercise in learning Access, but will take a lot of work.

And lastly, if audited, the auditors may not have a strong belief in the integrity of your home-grown solution. They will have more confidence in the shrink applications since they are used by an incredible number of people in different countries using these products. Let us say a rounding error was made in the calculation of the sales tax. Having worked with a number of auditors, I suspect they would not be too happy.

Richard