I'm hoping some experienced people can help me with some design issue I have on a financial application. I'm stubling on these for a couple days and I first need a good foundation to build on before I'm continuing development.

I need to be doing some financial accounting and I have some trouble designing it and finding some good data on how to design it. Any documents I've got usually provide me with information, but I cannot seem to understand these. I'm studying for a software engineer and not a accountant and unfortunally I don't have an experienced accountant to consult on these problems.
For the record, I'm Dutch, so any translations might be incorrect (please correct me if so).

Basically I'm accounting financial transaction and Tax/VAT for:

• Customer payments
  These can be based on Net_Amount, however prices will probably be set in Gross_Amount. Again this is trouble...
• Internal transaction between different (bank) accounts.
  They are only to balance the accounts and are not interested for tax/VAT.
• Other transactions
  Both expenditures and income (non related to above categories). The bills should specify Net_Amount, VAT_Amount, Gross_Amount. I don't know if invoices are split into parts, so it can store Net_Amount, VAT_Percent.

Theoretically (but not practically) I can store:
Net_Amount, VAT_Percent
And compute:
VAT_Amount = ROUND(Net_Amount * VAT_Percent, 2)
Gross_Amount = Net_Amount + VAT_Amount
This has trouble that I cannot represent all Gross_Amount values possible.

A (theoretic) solution would be to store:
Gross_Amount, VAT_Percent
VAT_Amount = ROUND(Net_Amount * VAT_Percent / (100% + VAT_Percent), 2)
Net_Amount = Gross_Amount - VAT_Amount

Next problem would be how to compute the VAT over the selected period (month, quarter) and how to do the yearly balancing for this.

The application I need to convert has all the rows added into the table, but I consider them to be redundent.

Does anyone have some good practices for me?

Thank you,

- Joris Dobbelsteen

Dear Sir,
I am not an accountant. I am a data architect. Here are some important ideas

1) Derived data. It is considered generally a bad pracitce to store derived data. It is done sometimes because of the amount of processing power to repeatedly calcuate the derived data, and possibly legal or regulatory requirements to capture a reported amount.

(If you derive it every time, and you can somehow change the rules, then you're not being accurate)

So you see...... there are some reasons to store redundant data as I explained above.

I like your approach. 'Conceptually' you want to store the busines rule or calculation parameters, that way you can recalculate it.

You never provided a definition for VAT, so I don't know what that means but you said it changes over time.

So create a time period table and then relate or store the VAT factor for each time period. I might still store the redundant data so it wouldn't require re-calculation

Vmusic

Following the theory that a picture is worth 1,000 words, here is a picture of
a Data Model showing Customers, Invoices, Orders and Products that might provide some insight for you ...
http://www.databaseanswers.org/data_...ices/index.htm

It shows derived fields beginning with 'derived_'.

An Invoice is produced for each Order, with one Invoice Item Line for each Product in the Order.

Study the Model and give it some thought and hopefully the design will be clear.

HTH

B.Dimple
DBA

Value Added Tax: a device for goverments (and the European Union) to extort money from its citizens (and any unfortunate visitors to that country), broadly equivalent to Sales Tax found in most US states. However in the Europe it can be anywhere between 17.5% and 25% of the value of the item, I think approxiamtely 1 to 2% of the sales value goes straight to the EU to fund the Common Agricultural Policy and other worthy schems like the EU rival to GPS). VAT on inputs is reclaimable (ie a VAT registered company can claim back the cost of VAT on purchases to be offeset against VAT levied by companys on the governments behalf. It is also a method of encouraging people into the black (or cash only) economy as customers seek to reduce the cost of the work by avoiding paying VAT.

Calculating VAT is one of the exceptions to storing derived data. Yes it is arguable that by matching the date that the VAT rate changes (in the UK there are 3 VAT rates in force (exempt (0%), fuel (8.5% [I think]) & standard rate (17.5%)) and then applying the correct rate.

Your calculations of VAT have to be precise, including how you do your rounding. The UK Customs & Excise are the main UK body not to mess around with, their powers make any Police state look on with envy.

In this case I would always store the Net and the VAT amounts. That way round you can always tie back the amounts to what was claimed or levied. If you allow the vlaue to be caluated there is a risk that if the basis on which the calculation is based changes then you may loose your link back to what you are responsible for.

You have another problem where an invoicing period may span a period when the VAT rate changes. So you system must be able to puill up the correct rate, which (I'm pretty certain) is the rate in force at the time of supply.