Clarity with Unique Finance System Table Design

[CJ_London]

You would use one table with following fields

Transaction PK
name of dr column
Name of cr column
Event type
Event description
Date of event
Amount

‘name of’ could be an account number or fk to another table, event type speaks for itself, event description would perhaps be name or account number of client A

Presentation wise - you can use a crosstab to present per my example

When summing a column - if dr then multiply by 1, if cr then multiply by -1

could get more complicated if the payment from client A is split for some reason between say company a and company b but without knowing more…..

 

[gemma-the-husky]

@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.

As @CJ_London just noted. He gave an example of normal transaction fields. If you want to have some unusual transactions, you may want to be able to link your transaction to a specific pdf/document scan stored on your servers to explain the transaction. Alternatively, keep notes in an external reference document. It's your purpose built system, so you can have things that other systems don't. However I would think that at heart, it's still just a standard double entry bookkeeping system.

 

[Kayleigh]

You would use one table with following fields

So I've drafted a basic table design following the above comments:

Please advise if the above is correctly normalised. I am not 100% sure if the accounts table is correct - how to link any company/unit/client to an account?

Would system details like who paid in a transaction be entered in the audit log?

NB Fund allocations are not actual funds deposited but a weekly/monthly amount noted to be allocated to this unit (ie similar to debt).

could get more complicated if the payment from client A is split for some reason between say company a and company b but without knowing more…..

Yes - this could well be the case. How would it be implemented?

In terms of presentation, we would like:

• record of all client payments (even from other sources)
• record of all funds paid to units and deductions due to debts to other companies
• record of debts to/from company and which clients it is originally from
• totals of all accounts
• Statements for each client and unit

Would it make sense for transaction functionality to simply show the payer, payee, amount, date and any client or unit involved and the intermediate transactions will be created behind the scenes?

Nope I don't expect anyone to do the work for me - but I'm looking for advice from the experts.

 

[CJ_London]

Without having a clear idea of what the app is required to do, difficult to advise on the relationships

Would help to see some example data - what are account types? what's the difference between client, company and units - I appreciate the simple difference, but why treat separately? In my view they are all the same entity. If a unit is 'owned' by a company then include a parentfk field same with clients

I don't see the purpose of maintaining debt and invoice tables? They would simply be an 'opening balance' type transaction

An audit log is whatever you want it to be, if you want to include who entered the transaction, just include their userFK in the transaction table. You might also want to include a timestamp field so you know exactly when a transaction was entered - can help when you want to know what the system 'knew' at any point in time.

split payments - you would include an extra transaction field to link different records together - might contain an invoice number for example or perhaps the PK of the invoice transaction. You might need a suspense account to hold 'unallocated' funds. Again would need to see some example data to cover all potential scenarios.

You only need to provide for the user to enter data, anything else is for information only. If the information is not useful to the user when entering data, don't clutter up the screen.

You appear to now be moving to requiring a sales ledger as well - but your companies already have that with their existing accounting systems. If that is the case, link to those accounting systems for client information and use their account numbers to identify them. Don't confuse the issue with your own account numbers and potentially different spellings of the client name

 

[gemma-the-husky]

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 )

@The_Doc_Man will tell you that a DEC format (Dibol/DBL/Cobol I think are all similar) has a text number storage format, so 1234.56 is stored as characters 0000123456, (as many characters as you need) and the user asserts the decimal point, as you are doing. It works without any issues at all, and never seems slow. I don't know if any PC language represents numbers as literal strings like that.

 

[Kayleigh]

Without having a clear idea of what the app is required to do, difficult to advise on the relationships

So the main purpose of the app is for logging the funds owed to each entity and aid the finance team in tracking down clients' debts (generate statements etc).

Would help to see some example data - what are account types? what's the difference between client, company and units - I appreciate the simple difference, but why treat separately? In my view they are all the same entity. If a unit is 'owned' by a company then include a parentfk field same with clients

I don't see the purpose of maintaining debt and invoice tables? They would simply be an 'opening balance' type transaction

To answer your query what the difference between client, company and units - yes all units are allocated to a company but since I am only working from the perspective of Company A, all units will automatically be owned by Company A.

I took account types from post #19 ie P&L, sales ledger - unless there is another way to convey this information?

Whenever an invoice is generated, where would you store this data - it is not yet a transaction as no money has been paid?

I can imagine a considerable amount of data would be imported from existing systems - however I am attempting to understand the connection between the entities to ensure the link is correct.

 

[gemma-the-husky]

@Kayleigh

In your relationship diagram, I would absolutely not have both a DR Acc and CR Acc for a single transaction.
You would not do this. You might have several DR values, and a single CR value. You don't specifically have both an equal and opposite value for each transaction.

I would only have columns for Account and Value. Unlike others have suggested, I would include the sign in the value.

I would enforce the double entry by making sure that any posting batch needs to have DR and CR amounts that add to zero.

So for instance - to allocate say, fixed asset depreciation

Dr Dep'n Charge Account for Dept 1 £500
Dr Dep'n Charge Account for Dept 2 £300
Dr Dep'n Charge Account for Dept 3 £400
Dr Dep'n Charge Account for Dept 3 £200
Cr Fixed Assets Dep'n Account -£1400

You only accept this batch of postings when the total sums to zero.

Even for a straight account to account transfer you have two transactions.

Dr Account A £1000
Cr Account B -£1000


I think some of your tables seem specific to your own company but may not be strictly necessary with a different data structure (eg, the fundallocations and the debts table). It's not clear to me precisely what they do, and why they are necessary.

What does the ToClaim and Claimed fields in the Transaction table actually indicate? I would have thought whatever those values represent would be derived from other information in the database.

 

[gemma-the-husky]

@Kayleigh. Re this

Whenever an invoice is generated, where would you store this data - it is not yet a transaction as no money has been paid?

Sorry, but of course it's a transaction. In the UK when you raise an invoice you have a tax point, and the invoice will generally create a VAT liability, and maybe other tax liabilities depending on your business. I'm sure it does something similar wherever you are based. You have to declare the sales tax (VAT) according to the date represent on the invoice, so it's absolutely critical that sales invoices get recorded correctly according to the legal date of the invoice (and therefore the tax liability)

If you raise an invoice, you credit a revenue account, (so in general that's your trading income, on which eventually the business pays tax) and debit a debtor account. (which shows as a [current] asset on your balance sheet). When the debt is discharged, you credit the debtor account, and debit the bank account. Financial Accounts are not just statements of a business's position in respect of cash and bank holdings.

 

[gemma-the-husky]

this sounds like intercompany transactions. Just taking your first scenario



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

But the above table by @CJ_London is a presentation, as he noted.
The first two items are balances representing any number of previous items.

The next 3 lines represent six distinct transaction records

The first 2 lines represent 4 separate postings which sum to zero, to maintain the double entry.
Sale from Company A Income for Invoice 123456 for Client A -1000 (the event that you thought did not need recording)
In passing, note that if the £1000 includes both revenue AND a tax liability, the ACTUAL sale posting will require at least two postings (transactions), one for the income, and one for the company's tax liability. The income feeds into your company's accounts. The accumulated tax liabilities are discharged in due course.
*Debtor for the above invoice +1000
Receipt to bank records from Client B +1000
*Creditor for the above payment -1000

Note that generally speaking the * starred items will also raise a transaction in the Accounts Receivable ledger AND a summary transaction in the Accounts Receivable Control Account in the General Ledger.

You then need to create another pair of transactions to transfer the money from Client B's account to Client A's account (together with supporting transactions in the Accounts Receivable ledger) - which might be what you meant by "To Claim" and "Claimed" in the transaction table.

The final line represents 2 further separate postings which again sum to zero, to maintain the double entry. Again these two postings may cause additional General Ledger postings to be recorded.
Transfer Money from Client B +1000
Transfer Money to Client A -1000

Now each accounts receivable account (for client A and client B) now includes two transactions both for +1000 and for -1000. So now you need further transaction matching procedures to mark these transactions as "cleared" within the accounts receivable ledger. Depending on your traceability requirements you may or may not need to record a general ledger transaction of zero value to represent this procedure. If you are working in an investment institution, you may well need to do this.

This might all sound complicated. It's not really, but it does reflect the way these events need to be recorded in your books. Generally they don't happen at the same time. You raise the invoice first, and it's only later perhaps that you agree to process a payment from a different client in this way.

 

[Kayleigh]

Hi @gemma-the-husky Thank you for the very detailed response
Over the weekend I hope to review my design in light of your comments.

 

[Kayleigh]

@gemma-the-husky See attached my basic DB reviewed to reflect comments above. (not yet incorporated an audit yet) The transactions so far are taken from @CJ_London 's example (it does not include an account for Client A - although I would imagine this is also necessary). Please do indicate where normalisation is incorrect or if I have misunderstood a finance concept.

I think some of your tables seem specific to your own company but may not be strictly necessary with a different data structure (eg, the fundallocations and the debts table). It's not clear to me precisely what they do, and why they are necessary.

Tables fund allocation and debts are holding temporary data - when it is decided that funds are to be allocated to a specific unit within a company it must be logged and then as monies are transferred from various internal/external sources to the company and if flagged for a specific unit it would be knocked off the amount in the temp table. The same idea applies to debts which are owed from one company to another.

What does the ToClaim and Claimed fields in the Transaction table actually indicate? I would have thought whatever those values represent would be derived from other information in the database.

I use the ToClaim/claimed flags to indicate if a money logged has actually been received by the company or if it must be claimed at a later date. I am not sure how else to show this info.

If you raise an invoice, you credit a revenue account,

Definitely true but this is not the purpose of this system and I am loath to over-complicate things. All sales are being recorded in a quickbooks system. The purpose here is only to keep track of the funds swiftly moving between various accounts.

This might all sound complicated. It's not really, but it does reflect the way these events need to be recorded in your books. Generally they don't happen at the same time. You raise the invoice first, and it's only later perhaps that you agree to process a payment from a different client in this way.

Actually it sounds very reasonable! But I am pretty new to the above-mentioned concepts so would appreciate a little guidance with setting up my DB to correctly follow this advice. Thank you very much!

 

[Cotswold]

@Kayleigh #31 : The purpose here is only to keep track of the funds swiftly moving between various accounts.

I still don't know how this will work, or what the analysis is. But I have to say I haven't downloaded your data. I have only referred to the posts. You have mentioned Quickbooks and I see that you have what looks like three name and address tables in your analysis and that you are checking on invoices/charges for some reason. All of these and the rest of your tables will need to be kept up to date from Quickbooks. I also note that your tables analysis does not include any Nominal Codes or Cost Centres.

All of this begs the question how does the data get into your system? Will it be manually entered or will it be imported from Quickbooks. Whichever is the case, are you preventing duplicate entries. Additionally, names and address details have a habit of being frequently amended. Which probably means you will need to be importing at least once a day. Depending of course upon how up to date your data needs to be. Furthermore, after completing your operations, will any of your data be exported and then imported into Quickbooks to keep it up to date with any changes your system makes?

If Quickbooks imports your data, does your analysis prevent duplicates being imported. Most account systems will prevent duplicates in the supplier and client tables,. However, as you will know, they will happily import duplicate sales, purchase data and just about anything else, over and over again, without a care. Providing they are happy with the Client, Supplier, Nominal and Cost Center Codes of course

I also wonder why you aren't using Cost Centres in Quickbooks to achieve the results you need. But I presume you have looked into that.

 

[Kayleigh]

You are 100% right that the import process should prevent duplicates being imported. Usually I would use an ID to check what has been imported previously and compare data. Since I am only at the stage of understanding the data design I did not involve these data points. I will have to look in more detail at the overlapping data in Quickbooks to work out the best way to do the import function.

As much as possible I would like to avoid the need to enter data at multiple points. So I will actually only implement the accounts which are absolutely necessary for the operation. It is still to be clarified if this data will be further exported to other sources.

I have actually mapped out the other examples I gave above similar to @CJ_London 's visual presentation above (although I'm not certain it has been done correctly). The purpose of this is to zone in on the data which is essential and the correct way to outline it:

• 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.

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

• 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.

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

• 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.

Event	Unit A interCo a/c Company A	Unit A interCo a/c Company B	Company B Bank Account	Company A Bank Account
O/Bal	Cr 2000	Dr 500
Inter Co recon	Dr 500	Cr 500	Cr 500	Dr 500
C/Bal	Cr 1500	nil	Cr 500	Dr 500

• If Client C is in Unit C, it would like to deduct its invoice balance from the funds allocated to its unit.

Event	Company A Sales (P&L)	Company A Sales Ledger	Client C Accounts Receivables	Unit C interCo a/c Company A
O/Bal				Cr 3000
Sale to Client C	Cr 1000	Dr 1000	Cr 1000
Allocated to Unit C		Cr 750	Dr 750	Dr 750
C/Bal	Cr 1000	Cr 250	Cr 250	Cr 2250

Any comments welcome!

 

[Cotswold]

If I am exporting data to accounts, then I'll send it in batches. The import filename has the batch number as part of the filename. Very briefly, the batch number is written to each record in the batch. When the next batch is created it forms up to a date, usually a month, or week ending. This means that any later records added after the previous batch, will be automatically included in the next, as the record's batch number is zero. Each export simply gathers every charge without a batch number. Once a batch has been created the batch number is advanced so it cannot be repeated. In practice there are additional functions to allow a batch to be reformed, to prevent it from being imported more than once, to create a credit import......etc.

As you are importing exported accounts data, you are in a very different situation with no other method of forming an export except by date range.
You are importing data from accounts and therefore need a unique identifier against each and all imported transactions. Otherwise you have no control over the imported data and will have duplicates. For names and addresses it will be the Account or Client code.

However, for transactions it is not as easy as it is doubtful that accounts will give each line a unique identifier. You will need to check the export options and study the export files as they may include a unique identifier. If you haven't already done so. Your problem is that charges and invoices can be added into accounts at any time. I doubt very much that Quickbooks will store details of any export of its data. If you export charges up to (say) a date, it is possible that after that export, additional charges are added that are earlier or of that date. Even if you knew about it, then how would you ensure you can include them in the next export? Exports and imports must be reliable. You cannot be in the position where they each need checking in detail. The only check needs to be the batch totals of course, where the export totals must match the import totals..

I do suspect that unless you can locate an identifier for each charge for your export from accounts, it is looking pretty close to herding cats, or punching clouds.

 

[Kayleigh]

I understand your point re exporting from QuickBooks. Hopefully there will only be minimum overlap between my solution and their systems in place already as I am not trying to replicate what they have in place. So only relevant data will be necessary to be be exported.

 

[Cotswold]

Good luck with that Kayleigh