Clarity with Unique Finance System Table Design (1 Viewer)

[Kayleigh]

Hi there

I am designing a complicated accounting database to manage transactions of various types in a company.
The primary table is obviously tblTransaction. We have identified the fields involved in every transaction (payee, recipient, amount, date etc).

My question is for payee, recipient and other similar fields - should all this data be located in one table named contacts (with use of lookup) or several tables for each type of payee (individual or business)? Also what data do I use to refer to the company itself?

Examples of transactions can be a staff payout from company, or a company paying to another company, or a client payment - all these transactions are closely linked as the money from one contributes directly to another (e.g. a client payment is allocated to a staff payout).

I would appreciate if anyone can shed light on this complexity. (This is only a start...)

 

[Cotswold]

Kayleigh, I do presume that you understand accounting practice, which is maybe why you have been given this task? Bear in mind that to create a solid accounting system will take many, many thousands of hours.
I advise in the first instance you study (say) SAGE, Pegasus and QuickBooks as it will be perfectly clear looking through those just how they are working and how their tables interact. There used to be an accounting system called PAGE which was almost identical clone of SAGE but free. Maybe look around for something like that as well? One small but important thing, In your analysis and design, allow only for integer calculations from the start. By this I mean the results of calcs should only be stored in your tables as integers ( longs in Access), do not store doubles or any decimal numbers.

Further to the above. As you appear fairly new to software development and at the very start. I just wonder if you might be better with
a language that will operate on the internet and be able to be used from a variety of different operating systems and devices. Maybe
take a looking at b4x.com and in particular B4J for Windows, which could be an option?

 

[CJ_London]

You say you are developing an accounting database but you are only mentioning payments in or out. That sounds more like a cash book sytem or perhaps a sales/purchase ledger rather than an accounting system.

but then you say ‘ a client payment is allocated to a staff payout’ which implies commission or bonus system.

So is this homework or a learning project of some sort?

 

[MarkK]

I would expect a system like this to be a many-to-many relationship of posts between a transaction table and an account table, and all the post amounts in a single transaction should balance to zero.

tTransaction
TransactionID (PK)
Number
Date
Payee

tPost
PostID (PK)
TransactionID (FK)
AccountID (FK)
Amount

tAccount
AccountID (PK)
Name

Each post is dated via its transaction. Each post moves a single amount of money to or from a single account.

Each transaction shows money moving from one or more accounts, to one or more other accounts.

All the posts to a single account sum to show that account's balance.

If you create an invoice and you need to collect sales tax, then you create a transaction with three posts. One negative post to your revenue account for the amount of the sale, one negative post your tax account for the amount of the tax, and one positive balancing post to your receivables account.

When the customer pays the invoice, you create a transaction with two posts, one that reduces receivables and a balancing post that increases your bank account.

If you want payees to be in a table, you can do...
tPayee
PayeeID
Payee
Address
Phone

tTransaction
TransactionID {PK}
Number
Date
PayeeID (FK)

 

[gemma-the-husky]

Hi there

I am designing a complicated accounting database to manage transactions of various types in a company.
The primary table is obviously tblTransaction. We have identified the fields involved in every transaction (payee, recipient, amount, date etc).

My question is for payee, recipient and other similar fields - should all this data be located in one table named contacts (with use of lookup) or several tables for each type of payee (individual or business)? Also what data do I use to refer to the company itself?

Examples of transactions can be a staff payout from company, or a company paying to another company, or a client payment - all these transactions are closely linked as the money from one contributes directly to another (e.g. a client payment is allocated to a staff payout).

I would appreciate if anyone can shed light on this complexity. (This is only a start...)

Why would the primary table be any table in particular. All of the tables are important. The first thing to do is decide what tables you do need to precisely model the data fields you want to include in your database. .it would be good to see your table structure.

I don't agree with @Cotswold that it will take thousands of hours. Depending on the functions you need, possibly much less that that. The areas that would take more time are things like project costing, and inventory systems.

As an example, a decent general/nominal ledger to produce financial statements could be built from scratch in 20 hours or so. You need a chart of accounts, (accounts table), a transaction table, and some other tables, such as a general control table.

In your example you mention that payments and receipts are closely related, so for example "a client payment is related to a staff payout". That's just not correct. What do you mean by that?

 

[Cotswold]

I have to agree with CJL and do wonder if this isn't a project being part of an educational course. Hence my gentle sarcasm in the first sentence.
In #5 GemmaTH suggests a solid, reliable and practical fraud resistant accounting system can be created in less than three days. Which does seem somewhat ambitious and unachievable to me. Additionally, the indication of a staff pay-out requirement indicates it should include a payroll system.
It used to be generally accepted that a comprehensive commercial payroll system was a ten-man years development. And a payroll lacks some of the complexity of accounts, but still nothing like a trivial application. No need for Cost Centres, VAT control etc. Plus, it will probably
require HMRC links and approval in this day and age, apart from the company auditors' full approval of course. Then the matter of ongoing support.

My advice to anyone contemplating the creation of an accounting and payroll would be to use a standard commercial system. If one doesn't have some necessary features, then adding them using Access is more than feasible and often a very and usually a very successful option. All accounting systems will provide import and export features which means that all data in and out will be verified by the accounting system. Thereby avoiding any possible claims of invalid, or incorrect adjustments to the accounting system. If anything goes wrong companies like SAGE will always out-gun you, so don't write directly into their files, even though you can. If you do decide to write directly, then increase your insurance premiums!

Personally, I'd be surprised if this project proceeded as a commercial application. If it is simply to impress an examiner, then I'll give it a possible maybe.

 

[gemma-the-husky]

Here's a bit more detail. First, I am a qualified accountant.

For clarification. I wrote a General/Ledger Nominal ledger application for my own use many years ago in about 2 days. I just didn't want to keep my actual data on paper or spreadsheets. Among other things It has full reversing journals, although I don't bother to use them. I would probably do some things differently if I did it again, but I have used this to manage and prepare my own accounts for at least 10 years. I am happy to provide this for research or actual use to anyone who is interested. I don't use Sage. I know how Sage works generally, and my system is much less complicated. I know what my system does, it's easy to use and it suits me. I don't like the idea of being able to write or amend data in previous periods, so you can only process transactions to the current period. Thinking aloud I imagine you could set the current period indicator to an old period. That might work, but it might well cause problems, especially if you changed to an old year, and I don't do it.

All you really need for a general ledger is a table of accounts with a distinction between P&L and balance sheet accounts, a journal entry form, and a journal entry process. Then some period control management, a year end process, and double entry management. It would be easy to add regular or repeating journals, automatic cost allocation journals and so on. To prepare accounts, I actually just export a TB, and adjust the TB in a spreadsheet. The table of accounts you use is the really important area to get right, in my opinion. One tip is to use an alpha numeric account code, and not a numeric code, so that some cost centres can be analysed in more detail than others.

This doesn't include Accounts Receivable or Payable. I added an accounts receivable module about 18 months ago, which took about 3 days. The Sales Day Book (if you will) was surprisingly complicated and wasn't built as I would have done for a larger business. The accounts receivable process was actually a good deal harder to do than the general ledger, although I have since found it very useful not to have to control a Sales Ledger manually. There's only rudimentary cash allocation, and a system for a bigger company would need rather much more complex cash handling/matching processes. I do not want to give that code away. I haven't needed accounts payable.

A plant register to calculate depreciation would be useful, and that's an easy project as well.

I just can't believe a payroll system would take 10 man-years. I would venture 10 days at the outside for a working system. I would not want to provide or sell a payroll system though because of the downside of making any mistakes. eg coding PAYE or NI incorrectly. (I only know the UK). I suppose it depends how many clients you have as to whether it's worth it. I use the UK government's free basic PAYE tools, and I can't believe that contains work amounting to 10 man years. Having said that, I imagine there's a lot of care taken with all the auto-updating and verification processes it must contain, but generally payroll shouldn't be that complicated in principle. The government need to include checks to force users to use the correct release. I have no doubt I could produce a payroll system which would match my genuine payslips in a few days, but it doesn't make it easy when the government makes changes at the drop of a hat. It would probably be an interesting project, though.

I just opened the government's app, and it took over a minute to start up on a quick laptop, so there's a lot of background stuff going on. By comparison Excel started in about 3 seconds, my accounts system in less than that.

 

[Cotswold]

Here's a bit more detail. First, I am a qualified accountant.

For clarification. I wrote a General/Ledger Nominal ledger application for my own use many years ago in about 2 days. I just didn't want to keep my actual data on paper or spreadsheets. Among other things It has full reversing journals, although I don't bother to use them. I would probably do some things differently if I did it again, but I have used this to manage and prepare my own accounts for at least 10 years. I am happy to provide this for research or actual use to anyone who is interested. I don't use Sage. I know how Sage works generally, and my system is much less complicated. I know what my system does, it's easy to use and it suits me. I don't like the idea of being able to write or amend data in previous periods, so you can only process transactions to the current period. Thinking aloud I imagine you could set the current period indicator to an old period. That might work, but it might well cause problems, especially if you changed to an old year, and I don't do it.

All you really need for a general ledger is a table of accounts with a distinction between P&L and balance sheet accounts, a journal entry form, and a journal entry process. Then some period control management, a year end process, and double entry management. It would be easy to add regular or repeating journals, automatic cost allocation journals and so on. To prepare accounts, I actually just export a TB, and adjust the TB in a spreadsheet. The table of accounts you use is the really important area to get right, in my opinion. One tip is to use an alpha numeric account code, and not a numeric code, so that some cost centres can be analysed in more detail than others.

This doesn't include Accounts Receivable or Payable. I added an accounts receivable module about 18 months ago, which took about 3 days. The Sales Day Book (if you will) was surprisingly complicated and wasn't built as I would have done for a larger business. The accounts receivable process was actually a good deal harder to do than the general ledger, although I have since found it very useful not to have to control a Sales Ledger manually. There's only rudimentary cash allocation, and a system for a bigger company would need rather much more complex cash handling/matching processes. I do not want to give that code away. I haven't needed accounts payable.

A plant register to calculate depreciation would be useful, and that's an easy project as well.

I just can't believe a payroll system would take 10 man-years. I would venture 10 days at the outside for a working system. I would not want to provide or sell a payroll system though because of the downside of making any mistakes. eg coding PAYE or NI incorrectly. (I only know the UK). I suppose it depends how many clients you have as to whether it's worth it. I use the UK government's free basic PAYE tools, and I can't believe that contains work amounting to 10 man years. Having said that, I imagine there's a lot of care taken with all the auto-updating and verification processes it must contain, but generally payroll shouldn't be that complicated in principle. The government need to include checks to force users to use the correct release. I have no doubt I could produce a payroll system which would match my genuine payslips in a few days, but it doesn't make it easy when the government makes changes at the drop of a hat. It would probably be an interesting project, though.

I just opened the government's app, and it took over a minute to start up on a quick laptop, so there's a lot of background stuff going on. By comparison Excel started in about 3 seconds, my accounts system in less than that.

Well Gemma-the-husky, I take my hat off to you. There is no doubt that you can and do work a site fast than I do, or even imagine I could.

In all fairness, the ten-man years for payroll was from the days of coding in DOS and CPM/MPM. It was a reasonable guide for someone thinking of entering the market with a new package in competition to the likes of SAGE and Pegasus at the time. But I wonder today would that time drop by half for a commercial system? From just the aspect of tax, that is way more complex today than it was in the '80s. No deductions for student loans etc, back then. Mind you we did get an allowance against tax for the mortgage interest, so not all bad. We also got a full refund of all the years tax when we married. Which was why virtually everyone got married in March/April. I suppose it sounds like it was a lot easier then, but it didn't feel like it at the time. Happy daze.

 

[gemma-the-husky]

Yes, payroll is tricky. There's lots of rules about how tax code changes are applied. You need to distinguish between Week 1 and cumulative codes, you have special rules for NI, for directors and so on. You have salary deductions and increments being pre and post tax. Special holiday stamp schemes in some industries. All sorts of things. Then the government can change rules at the drop of a hat, and the payroll section/programme has to change things with little advance warning. I am pretty sure there is even a maximum tax rate you can deduct (or maybe even refund) due to changes in tax codes, and one-off bonus payments.

When my son started his first job, mid month. Instead of paying him for a two week period, and then a full month period, they paid him a single 6-weel period in month 2. The effect of that is that he pays the same tax, but more NI, but they didn't care, which I though was pretty poor. I imagine it's legitimate to do it either way, but with a bit of thought they could have helped out some relatively lowly paid staff.

 

[CJ_London]

I’m an accountant also - for a very basic, fairly secure accounting system perhaps a few days. Like Gemma I have built my own, for my purposes. But would need a lot of work to be a commercial product

In the UK now there is the requirement for MTD (making tax digital), the implications of which I’m just starting to investigate. I believe there is an API available for excel, so seems to me, an access app should be able to either use it directly or at worst dump the data into excel and go from there

so far the OP has not been clear about the actual requirement - As stated before descriptions do not sound like an accounting system

 

[gemma-the-husky]

I’m an accountant also - for a very basic, fairly secure accounting system perhaps a few days. Like Gemma I have built my own, for my purposes. But would need a lot of work to be a commercial product

In the UK now there is the requirement for MTD (making tax digital), the implications of which I’m just starting to investigate. I believe there is an API available for excel, so seems to me, an access app should be able to either use it directly or at worst dump the data into excel and go from there

so far the OP has not been clear about the actual requirement - As stated before descriptions do not sound like an accounting system

Yes. I have just had to sign up for VAT on MTD for my last VAT return. The easy way is to register with HMRC via an app you use. Then rather then enter the VAT figures manually you specify the spreadsheet and cells that contain the figures you upload, and they get uploaded by the app. And you pay a fee for the privilege to the app vendor. Crazy, eh?

 

[Pat Hartman]

By this I mean the results of calcs should only be stored in your tables as integers ( longs in Access), do not store doubles or any decimal numbers.

Did you mean to say store them as Currency which is a scaled integer or did you mean to scale them yourself? I store anything that can be limited to 4 decimal places as Currency to avoid floating point errors which I think might be your point.

@Kayleigh I strongly recommend purchasing an accounting system, Quickbooks is a great choice for a small business, and building the Access app to do the custom work and interface with s standard accounting system. You can even buy an ODBC driver for Quickbooks that makes the integration with Access easier.

 

[gemma-the-husky]

Storing as integers can be just too limiting. A long of 2 Billion in pence or cents is £20m which might not be quite big enough for some SMEs.

With 64 bit long longs you can do everything in pence if you want, avoiding all currency issues (unless someone STILL wants to sell in fractions of a penny, which they can do as it happens. That's how they price fuel at the filling station.)

 

[Cotswold]

In the past I had just one issue with a huge report being a penny out (*) when storing the results of calcs as decimals in a table. Due no doubt to rounding with 16 places of decimals in the background. Maybe I'm wrong but Currency from my experience is only a mask and it still has 16 places sloshing about in the background. (is it not 20 places for 64Bit?).

I converted, at my cost that particular software to integer calcs and I decided then that the situation would never arise again. From that date all calc results I have stored as Longs in tables. I then, by using functions string handle the decimal to 2 for money and three for tonnes and kilos on-screen and in reports. If I add a column of integers the result can only be the same. Individual invoices for the applications I've been involved in will never go beyond £20,000. I export invoices to accounting systems but that sends invoice totals over as text fields anyway.

In my manufacturing systems, individual contract weights will not exceed 100,000 tonnes (100,000,000 Kgs). In fact most of them would struggle to
have100,000 tonnes as a Work-in-Progress total. I would never consider, for a moment, dealing with anyone in the motor trade or a petrol station. But if I did, I doubt if any individual reports would bust a Long in England. Maybe with BP themselves but not with the local Esso. However, there is zero chance of BP ordering an Access application of me anyway. So not an issue.

I'm happy sticking with Longs. Once they are set, results never need checking........ever. I like them, they are reliable, and I will not change that opinion or the analysis in my systems. If I'm in a minority, I'm not bothered, they work just fine for me.

( * I know, who is the sad guy who will check a huge report by first adding it up with a calculator and then believe it or not , typing all the numbers into a spreadsheet? But he did )

 

[Kayleigh]

Hi guys! Sorry I've been off work a couple of days and just catching up with the messages. Didn't realise this would be such a hot topic!

So I believe this company does have an accounting system like quickbooks, as well as a standard payroll system in place.
I've been commissioned to design a system for a niche department which works in a very unique way (hence I have changed thread name to avoid confusion). Unfortunately I am not at liberty to release all information so I have to be careful to keep the details very generic.

I will attempt to map out the process below:
Let's call the main company: 'Company A'
It has several named clients: 'Client A', 'Client B' etc who are invoiced for goods from Company A
It also has several departments who are allocated funds: 'Unit A', 'Unit B' etc
And there are debts to or from: 'Company B', 'Company C' (all are under the same enterprise)

There are several scenarios which will need to be recorded in my system:

• Client A pays Company B for goods from Company A. Company B has a debt to Company A. So money is deducted from debt and deducted from Client A's balance. Sometimes this can also be allocated to a specific Unit if the debt was recorded to have originated from there.
• Or Company C pays Company A on behalf of Client B. So Client B has paid an amount to Company C with a request that this is given to Company A. This also may be allocated to a specific Unit by Company A.
• Company A may also pay Company B directly on behalf of Unit A who has a debt to Company B. This will be deducted from funds allocated to Unit A.
• If Client C is in Unit C, it would like to deduct its invoice balance from the funds allocated to its unit.
• There may be others...

So far I have been advised to have a table of client invoices, fund allocation to units, transactions which include all above scenarios. A transaction can be linked to a previous transaction using a connector ID. There is also a list of contacts which include clients and units (a client can potentially be in a unit as well) and each record is boolean flagged which category it is in.

I have also been told that every transaction will have all or some of the following items of data so the above can be fully tracked. But I am lacking clarity how best to record these (ie. which tables would be required for each):
Payee (unit paid to?), Recipient (company paid to), Beneficiary (final company receiving funds), Allocated To (company debt), Client (to deduct invoice balance), Payed By (Unit processing it). I have been through the above scenarios with these items of data, but I am unsure if it is sufficient or if there is repetition.

Another complication is when the transaction would be classed as a debit or credit - as it would be adding to one balance and deducting from another - although it all must be centred around Company A. I have managed to create a client module with a running balance of invoices and transactions but it gets much trickier when involving various units and companies!

Any clarity on this is really appreciated. I very much hope this system is do-able in 20 hours or so!

 

[gemma-the-husky]

Personally, I would break all those special cases into their individual elements, and process each separately. Maybe try to build in some sort of document link to tie them all together.

A owes B, B owes C, so A pays directly to C to discharge B's debt. As long as everyone agrees debts have been discharged, that's fine.

if you are B, then you can show A paying you, and you paying C, either with cash, or credits or journal adjustments. You just need a clear audit trail of the transactions, and maybe a file/diary record of the process. Can you attach pdf's to some of your entries. That might work.

I don't think you want to be doing this sort of thing without documenting the steps in advance. If your units and departments are all distinct legal entities then you need to take this into account. Don't treat these special cases as a single transaction type. Just treat them as a series of linked transactions, and then all you need is basic bookkeeping with a few embellishments.

 

[Pat Hartman]

Maybe I'm wrong but Currency from my experience is only a mask and it still has 16 places sloshing about in the background.

The documentation says four decimal digits only. So, that means it rounds to that and that is what is stored.

 

[Pat Hartman]

I'm with Dave. If A pays C on behalf of B, then you need Two transactions. A to B and B to C. Add comments to explain the chain of events but always document EVERY step in the chain with a separate transaction. Otherwise, you will never get past an audit.

 

[CJ_London]

this sounds like intercompany transactions. Just taking your first scenario

Client A pays Company B for goods from Company A. Company B has a debt to Company A. So money is deducted from debt and deducted from Client A's balance.

What is missing from this scenario is which company raises the invoice - I'm assuming company A
each company should have an intercompany account for each of the other companies - treated a bit like a bank account

So simplistically (excluding any sales tax implications)

Event	Company A Sales (P&L)	Company A Sales Ledger	Company A InterCo a/c Company B	Company B InterCo a/c Company A	Company B Bank Account
O/Bal			Cr 2000	Dr 2000
Sale to Client A	Cr 1000	Dr 1000
Receipt from Client A				Cr 1000	Dr 1000
Inter Co recon		Cr 1000	Dr 1000
C/Bal	Cr 1000	nil	Cr 1000	Dr 1000	Cr 1000

Note that for each line total Dr=total Cr

 

[Kayleigh]

@CJ_London You have mapped out the process really clearly - thank you! Now can you help me with the next step - translating into the table design. Would it be necessary to have a table for each column or can it be in the same table? Also would both the credit and debit amount be required as data points or can it be calculated?

@gemma-the-husky I would like to have a clear audit trail of transactions. Would this be more than user entering transaction, date, form, record, control name, old and new value (which is what I've used in the past)? Also I am not sure what pdf's you are after?

If you can provide a very simple sample it would be really useful - I am a very visual learner and understand best from something tangible.