Inventory and Invoicing Philosophy

[Galaxiom]

Are we designing inventory and invoicing databases based on the traditional paper methodology?

Traditionally, inventory and invoicing systems rely on double entry accounting where the "books" are expected to add up in two directions. This was done to ensure that entries are consistent.

With computer based systems many of these underlying book-keeping principals are obsolete. We don't need to concern ourselves with inconsistent entry. The whole process of writing cost and income ledgers is against the very principles of normalization.

The classic case is the cost of sales ledger written when the invoice is posted. There can be a problem if that cost is later found to be incorrect. This sometimes happens when the purchasing officer has made a mistake which only becomes evident after the supplier invoices are later reconciled. Because the ledgers are written immediately and not directly correctable, if the item has been sold the only way to fix the cost of goods sold is to redo the invoice or write a journal entry to compensate.

Of course we must be concerned with reporting and we would not want data changed after it was published. However writing immediate ledgers which can only be corrected by reversing and repeating the transactions doesn't make sense in an electronic database.

All this information should be properly normalized and dynamically calculated until the point where the reports are published.
At this pont all the data is locked. Only then we should be forced to use journals to make corrections but it would save a lot of hassle if the data was fully dynamic during the reconciliation.

Is the ledger table just another hang over from "books"? Like keeping a stock figure in the inventory table? How many systems maintain a cost figure in the inventory file when this should rightly be calculated directly from the suplier invoice information?

 

[dportas]

How would you produce a Balance Sheet and a P&L without a double entry ledger? You would still need some way of mapping each transaction item to Balance Sheet codes and P&L codes. It seems like the "obvious" way to do that in a database is with two or more row(s) in a table (or possibly tables).

The whole process of writing cost and income ledgers is against the very principles of normalization.

I don't see why that should be. Maybe you just mean some hypothetical database design may have redundancies. For a database to be normalized doesn't mean it must always be non-redundant because normalization is only concerned with certain types of redundancy.

 

[Banana]

This deals with a different area of accounting but I think it should help illustrate the legitimacy of having double entry. I wrote this as a sample for a poster I helped a while ago at UtterAccess.com

Link

I see nothing wrong with preserving double-entry bookkeeping even on a computerized database because from what I've had understood about double-entry bookkeeping is it's to protect against data entry errors, not calculation errors. I think we all can agree that data entry errors will always be present regardless of the system we actually use to enter the data.

Just my $.02.

 

[pbaldy]

It could also be stated that any "transaction" affects 2 things, hence the double entry. If I buy a widget to sell, I record the widget into inventory, but I also record the same amount of cash going out. So my double entry accounting entry is a debit to inventory and a credit to cash. When I sell that widget, I actually have 2 sets of entries. First I debit cash and credit revenue for the sale price of the widget. Second, I credit inventory and debit cost of goods sold for the cost of the widget.

It gets a lot more complicated obviously, but the double entry is not simply entering everything twice to ensure valid entries. Each side of the entry accomplishes a separate and distinct purpose. Neither side can be automatically inferred from the other, so each is necessary. In other words a decrease in cash doesn't mean an increase to inventory, nor does an increase to inventory mean a decrease in cash (might be on account).

 

[gemma-the-husky]

double entry is a tried and tested self-balancing accounting system.

computer accounting systems still use double-entry bookkeepping, although this may not be apparent to an untrained eye.

Personally, I cannot conceive of a system that doesnt use double entry bookkeeping.

Inventory entries should still form part of the oduble entry - although technically this is often done by means of something called a control account. Hence the discrete apparently single-entry inventory system is still actually controlled in the underlying double-entry system with an appropriate control account

 

[Atomic Shrimp]

I asked a question a bit like this a while ago, because it seemed to me that double-entry bookkeeping was a bit anti-normal, but as others have said, it's still a valid method, because although both sides of the transaction must always balance, they can be composed of different assorted components that do not individually marry up with any opposite counterpart.

What I do think might be a mistake, is to imagine that because two opposing streams of data are being recorded, that they require two separate tables. As far as I can see, they don't - a transaction may entail any number of components - some of them credits and some debits, but I don't see any reason why they can't all be stored in the same table, with either a different sign explicit in the stored values for each component, or a different sign imposed by a definition in a table of transaction component types.

 

[Galaxiom]

What I do think might be a mistake, is to imagine that because two opposing streams of data are being recorded, that they require two separate tables.

A system I was intimately involve in as a user had a single table for the ledger. It recorded the ledger code and values along with the transaction ID that posted it.

The ledger information is derived from the transactions. It can be entirely regenerated from those transactions and hence is a case of storing derived data making it explicitly in contravention of normalization.

The ledgers to represent a summary of transaction data making for rapid reporting. I don't have a problem with this but they should be recognised as derived data.

What I object to is that such a derived record is often treated as something special. Systems frequently require the transaction to be reversed and redone to fix an error in the cost. This does not make sense.

 

[Atomic Shrimp]

What I object to is that such a derived record is often treated as something special. Systems frequently require the transaction to be reversed and redone to fix an error in the cost. This does not make sense.

From a system/db point of view, perhaps not, but from an accounting/audit perspective, reversing and re-doing the transaction is often absolutely essential, for several reasons, including:
- The period in which the original transaction occurred may have been balanced and closed - a correction may still be required, but it may have to be done at today's date (but still referring back to the original one).
- It generates an audit trail that represents the reality of the situation - i.e. we originally did it wrong, then on this other date, we undid it, then did it right.
- The original transaction remains unaltered, and thus still matches any hard copy document relating to it, or extracted/exported/reported version of the data.

 

[pbaldy]

What I do think might be a mistake, is to imagine that because two opposing streams of data are being recorded, that they require two separate tables.

I would agree with that. I wrote a rudimentary accounting application and I just used header and detail tables. The detail includes account number and the value as positive or negative. During data entry/edit, I don't let them save the transaction unless the detail items net to zero.

 

[gemma-the-husky]

HERES THE DETAIL

the normal procedure, eg wth an inventory system would be as follows

stock intake (value)

post an entry into the stock system.

now the value of that entry increases the stock by a value, say X

so say this represents a GRN (goods received note)

the NL entry is
DR: Stock control account with X
CR: Goods Received Note Suspense X
(in a high end system - representing the value of the invoice we are due to recieve)

then the invoice comes in
so post the invoice total to PL Account

NL Entries
DR: GRN suspense a/c to contra the above Y
DR: VAT VAT
CR: PL control a/c with total invoice Y+VAT
(Another control account)

now in a high end system, these transactions may give rise to variances - eg what if the invoice is charged at a different price to that expected. ie Y and X are different, all of which require additional entries. Simpler systems dont have this level of control.

The upshot of all this is
a) the balance on the stock control account, bold above, should agree with the inventory value as per thre inventory system

b) the balance on the PL (payables) control account should agree with the total outstandings per the detailed PL.

The double entry applies to the entries in the NL only.

And as I say, I cant imagine any system that doesnt maintain a NL with double entry bookkeeping in this way - thats why a trial balance balances! ie the total balances on all the NL accounts add to zero.


hope this makes sense.

 

[dportas]

The ledger information is derived from the transactions. It can be entirely regenerated from those transactions and hence is a case of storing derived data making it explicitly in contravention of normalization.

Normalization is never concerned with dependencies between different relations so there's no reason why having derived data in another table would violate any normal form. That still doesn't mean it's a good design of course!

This doesn't seem to have much to do with double entry accounting though. Even if you dropped the second table you would still need to represent two ledger codes per transaction item.

 

[Galaxiom]

Dave (Gemma) describes the problem exactly. Where the goods are invoiced at the wrong cost price the posting to the Cost of Sales ledger is incorrect. The cost price error is discovered when the supplier invoice is received but both the sales invoice and the ledger posting remain incorrect.

These have to be corrected by journal entries. In some systems even correcting the cost price in the inventory involves reversing the bookin and redoing it. But correcting this cost price in the inventory does not correct the cost on the customer invoice of goods already sold nor the associated postings to the stock value and cost of sales ledgers.

I believe we should not store the cost price in the customer invoice or the inventory record. These values should be taken from the applicable Supplier Invoice determined by counting back the sales and purchases in the same way that the inventory quantity should be determined rather than having a Current Quantity field in the inventory table.

This way when the cost price is corrected, the cost of the sale and the value of the inventory will both be automatically corrected. The ledgers should remain dynamic until reports are published. They can be written immediately so up to date reporting is possible but they should be recomputed prior to reporting.

Where the error does cross the reporting period the correction to the supplier invoice entry should be balanced by posting to Stock Value Corrections and Cost of Sales Corrections ledger accounts.

Variance is caused only because values are recorded rather than being derived from the authorative record, the Supplier Invoice. It is a fudge calculation necessitated by recording values that aught to be determined by looking up the real figure.

Of cousre there would be many details to investigate but I am quite sure recording of cost in the inventory record and customer invoice and immediately and iretreivably posting to their associated ledgers is as wrong as recording the inventory quantity.

It is just another mind set to be overcome. It will be even harder than the struggle developers have coming to terms with best practice for determining Inventory Quantity.

 

[gemma-the-husky]

if you look at a standard costing system, there are hundreds of variances

cost variances, production variances, quantity variances, usage variances - i oversimplified the position - the point is however that in every decent accounting system there should be a double entry method - with certain sets of records managed by control accounts, and traceable postings and adjustments.

personally i dislike systems that enable you to edit previously committed transactions. although you can design stuff like this, it is counter-intuitive and dangerous. if you produce figures for one accounting period, but cant trust them because they may change subsequently where are you ... its better you a) are encouraged to get it right the first time and b) have a proper audit trail of eberything you do.

note that in the UK at least, there is a legal requirement to maintain proper books of account. so in particular if you issue an invoice (VAT invoice) you cant automatically just withdraw it. if its wrong you have to issue a credit note.

single entry bookkeeping - generally here we are talking about small businesses, with an analysed cah book type of record, just dont cut it.

 

[Galaxiom]

personally i dislike systems that enable you to edit previously committed transactions. you produce figures for one accounting period, but cant trust them because they may change subsequently

Provided the reporting process includes a mechanism to lock the values and any other reports are marked as draft then there isn't a problem.

... its better you a) are encouraged to get it right the first time

Absolutely agree. But I managed an integrated system where my colleagues were not the least bit interested in understanding what they were doing. Quick and easy was the universal goal even if it stuffed everything. They all started out in an primitive system where the bookkeeper corrected everything before reporting in a completely separate system. It led to a philosophy where they insisted all that mattered was the stock quantity because this was the only thing the bookkeer could not sort. The purchasing officer was quite happy to adjust that up and down without investigating why it was wrong.

and b) have a proper audit trail of everything you do

Also agreed. However the shortcoming of the prevalent philosophy of storing cost in the invoice and cost of sales ledger is that although the audit trail records the mistakes and corrections it does not carry through the corrections to other affected records. The cost price of an item remains wrong in the invoice and the the ledger posting because it is recorded in tables.

note that in the UK at least, there is a legal requirement to maintain proper books of account. so in particular if you issue an invoice (VAT invoice) you cant automatically just withdraw it. if its wrong you have to issue a credit note.

That is pretty universal. One cannot change an invoice that has been issued. The only valid reason to edit an invoice record is to change the cost price and this has no effect on the customer side so is quite legitimate. It is this recording that is wrong philosophy and should be eliminated.

What I am proposing is all about rationalisation of the costing by not keeping a value anywhere other than the supplier invoice record and only locking it into the cost of sales ledger at reporting time.

If the change to the booked in value is managed correctly in conjunction with ledger locks at reporting there would not be a problem. It would save work for the bookkeeper by allowing them to easily correct the mistakes of accouting-ignorant staff instead of making disconnected journal entries.

Current practices fly in the face of database principles. I promise that this situation is no different from the resistance to not maintaining a current stock figure in the inventory record. Hopefully, time will bear out my assertions.

 

[gemma-the-husky]

galaxiom - not trying to be contentious, but from an accountants point of view, here's a slightly extended consideration of costs

The only valid reason to edit an invoice record is to change the cost price and this has no effect on the customer side so is quite legitimate. It is this recording that is wrong philosophy and should be eliminated.

but this is part of a problem - this is confusing two sets of data. An invoice is a record of what you have charged somebody - this is absolutely nothing to do with what the cost of those goods was (although at first sight it may appear to be). Although it may seemingly be convenient to store the costs with the invoice, this is certainly not required, and indeed may be more complicated in the long run. At the very least, I think it is denormalizing the data.

Income is what you sell something for. costs are what it costs you to make things/have them available for sale - in many ways more closely related to inventory - two different things, which need to be considered separately.

eg, costs are immediately distorted by the value stored in any unsold stock and work in progress - this clearly represents money already spent and not represented by sales made. So any attribution of costs to sales already needs to make an (arbitrary) distinction between the cost related to the sale, and the cost carried forward in inventory.

eg - even something apparently simple like a grocery store - you sell a tim of beans for 50p - but you bought some of these for 35p , and some for 40p - so what is the cost of the beans you sold, and how much profit did you make? - there's no correct answer.

This is why inventory systems are so complex, there are many alternative ways to treat your costs, all of which will most likely attribute different notional costs to your sales, and therefore report a different profit. And that is also why high end (standard costing) inventory systems are most unlikely to let you change anything retrospectively, as changing anything would produce literally hundreds of variances.


Maybe in some industries (eg motor sales) where products are uniquely matched between acquisition and sale, then the model that directly relates sales and costs may be apposite - but in most normal environments it is not true/correct to directly relate costs and income - or certainly not the most effective way of dealing with it. You need the quantity sold, but you dont need to attribute a cost to those sales.

eg, in most real world industries the cost of something changes depending on the volume/mix you make, (and in any event is the result of an accounting exercise - eg splitting indirect costs over different production batches) so any cost you come up with can only ever be a guess/estimate at best. Companies set prices etc based on overall production budgets, and very rarely based on unit costs.

 

[Banana]

I'm following this thread with interest, but I can't help but feel like we're talking about slightly different things here. Galaxiom seems to be arguing that costs doesn't have to be stored, while nobody has asserted otherwise, if I've understood the flow correctly. There's also the issue of how the mistakes should be handled.

From what I've understood (and my sincere apologies if I've not comprehended), storing costs create problems where we still have wrong costs quoted even with the subsequent correction. However, I'm under the impression that one doesn't actually store the cost, but rather the transactions:

On first invoice:

Invoice #: 1

Record 1: X widget sold for Y
Total (calculated; not stored): X * Y

Later find out that Y was wrong, and should be Z, we go back to the original transaction and add new entry:

Invoice #: 1

Record 1: X widgets sold for Y
Record 2: Retroactive correction X*(Z-Y)
Total (still calculated) : (X*Y) + (X*(Z-Y) (can be simplified to X*Z, which is same)

This way, when someone hands in the original invoice for correction, it's easy up to look up the invoice and quickly spot the changes because it's recorded in Record #2 for same invoice, and it's labelled as retroactive correction, with a timestamp that's later than the original invoice date, offering evidence that what the clerk see on the screen is in fact correct over the paper version handed in and there's no ambiguity if she only saw one record of X widgets sold for Z (and she may not know what is the correct cost price for the widgets X, Y or Z). Thus, the program is always consistent with the paper trails it may produce in past.

That's how I've understood and as pointed out, this really doesn't require storing the total cost, though we do need to store the price that widget X sold for within a certain date range.

One more thing. From what I've seen, it's possible for a business to not a flying hoot about the retroactive changes and are just as content to merely update the price table and print out the new invoice and insist that it supersedes the old invoice unconditionally, while other business insist it is absolutely essential that all 'mistakes' are not "covered up" else they'd be facing audit issues. For that reasons, it's possible to settle on a simpler design that doesn't make use of price table with effective date ranges; it really depends on what is the business's requirement. The more complex the accounting requirement, so must be the database design to support the data they must know to keep their books in order.

If I've not understood or misconstrued anything from anyone, my apologies and I welcome any corrections.

 

[gemma-the-husky]

i went back and looked at the original post. so here's some revised thought, hopefully final thoughts.


Galaxiom wrote

With computer based systems many of these underlying book-keeping principals are obsolete. We don't need to concern ourselves with inconsistent entry. The whole process of writing cost and income ledgers is against the very principles of normalization.

Ok, so profits are struck and balance sheets are generally constructed on the basis of balances represented by a nominal ledger. Transactions in the nominal ledger come from many books of prime entry, such as cash books, sales day books, purchase day books, journals.

So the reason to not construct a nominal ledger as such is that every item that would otherwise be in the NL could be derived from the underlying database transactions. This may be true, but in practice this would mean at least two things

a) a very slow performing ledger - because in large systems each NL item is the result of adding many hundreds/thousands of items together. AND
b) a storage problem, as you would never be able to delete any historic data

I can see where Galaxiom is coming form in principle, but in practice I feel it is still better to denormalise and actually generate these additional tables of transactions for the appropriate prime transaction postings. In practice it does add some complications, as often the accountant or auditor will need to trace the summary posting back to the individual constituent items - and this is quite often a complex procedure - precisely because the NL postings ARE accumulations of many line items.

Now, with regard to double entry - is this still double entry if we don't have a NL transaction table. Most definitely yes. In order to produce accounts we still need to understand the basic principle of double entry

each posting generates two postings (or to be more precise any number of postings where the debit totals [increasing an asset] and credit totals [increasing a liability] are equal and opposite) - so a sale generates a credit posting for the sales value (recorded as income/profit), and a equal and opposite debit posting for the amount due (showing as an asset on the blanace sheet) - and both these accounts would need to be populated whatever source the financial statements were generated from - whether it be sales invoice records, goods despatch notes, or inventory records.

And again, as stated previously, you do need an appreciation of the complexity of real world costing systems. A full production standard costing system in a manufacturing environment is enormously complex to design, set up, manage, and understand. in such a thousands, maybe millions of individual transactions take place that produce thousands of summarised nominal ledger entries affecting many different accounts. I dont believe it is practical to expect to rebuild these every time a NL account is queried, and because it is not practical to do this, it is therefore also impractical to keep completed records open for editing, generally, in any system other than a very small one

i also think that in the end you tie yourself in knots trying to avoid technically denormalisiing. eg, take a sales invoice. having an invoice record is not really necessary -since you could generate the invoice again from entries such as the underlying delivery notes, and vat records. but then you have the issue of allocating payments to selected invoices - so you would have to have a mechanism to do this - it is just easier to accumulate the deliveries into a separate invoice record, and store the total invoice value on that record.


[edited
and thinking again - the nominal posting is often NOT stored with the underlying transaction, but is a function of the database. So if you decide to change your NL structure, then it follows that old stuff will now be allocated to a different place - ie following the new rule. So if you want to retain the knowledge of what you did with stuff at the time, then this means you need to store a whole other load of data with each base record.

 

[Galaxiom]

I'm following this thread with interest, but I can't help but feel like we're talking about slightly different things here. Galaxiom seems to be arguing that costs doesn't have to be stored, while nobody has asserted otherwise, if I've understood the flow correctly. There's also the issue of how the mistakes should be handled.

Storage and editing of cost figures is exactly what I am interested in. Someone mentioned that by nature of variance the cost of a sale is an inexact science and perhaps implied that it wasn't worth persuing because the accounting inaccuracties were the same magnitude as the natural variability.

However we do need to have a figure to attribute to the tranasaction in order to prepare Profit & Loss reports. Although the cost of buying someing might vary, the total value of purchases and sales must balance over time. Costing is an important aspect of accounting.

For simplicity I am considering the simple retail model. We can entirely neglect the income side of the invoice which should never be edited. Inconsistent invoice copies are unacceptable. For the purpose of this discussion we can also neglect the technique used to record the quantity information and focus entirely on determining and recording the value of an item in a tranasction. I use a model where costs are attributable to individual invoice lines rather than a summary of the combined lines.

When an inventory transaction is incurred, some value must be attributed to that transaction. When it is booked into inventory it must have a value to add to the inventory ledger. When it is sold it must increment a cost of sales ledger and decrement the inventory ledger. If the amounts for the purchase and the sale of the same item are not identical then the inventory ledger becomes incorrect.

My understanding is that there are two models of attributing the value of an item through to sale. Average costing and First-In-First-Out.

In average costing the a supplier purchase order value is added to the total value in the existing inventory and their average stored as single value per item. When sales are transacted, this figure is used as the cost of that item on a customer invoice. So in effect you are selling your both old and new stock at the same time.

In the FIFO the supplier invoice records are used as the value information of the sold item. Each time a customer invoice is written, the sale is attributed to the oldest supplier invoice that has not been entirely sold out and that cost is used for the item. Although items are not individually tracked the stock transactions are.

Traditionally a ledger is written when the supplier is paid and when the sale is made. A supplier invoice and a customer invoice complete the double entry system.

When determining the reported value for cost of sales and inventory value ledger we should recalculate the costs all the way from the current value stored in the supplier invoice records. The cost can be calculated as Average or FIFO depending on your preferences.

This is the cost equivalent of determining current stock quantity by a similar countback logic. The cost of the sale should not be stored on the invoice at all and the ledger considered a dynamic record that represents any edits perfomed on the supplier invoice.

A ledger just represents the same data in a different form and is really a query. However they are needed as tables as a permanent record of the current infomation when it is reported.

Of course subsequent alterations to costs after the ledger lock must be still accounted by other means but a properly designed system would be aware of the lock and post these to a costing error ledger when the supplier invoice is changed after the lock os applied.

The data would be free of any costing anomalies which would otherwise cause the inventory value ledger and calculated stock value to be different. This is assured because it would be calculated directly and precisely from the same data.

This is what I was originally getting at rather awarkedly, and still am I think. The idea of the ledger and the inventory transactions as two independent recordings with tracking of every mistake to be balanced at the end of the month should not be necessary. The primary records for costs are the supplier incoices, nowhere else. When these are edited a properly constructed system should cope. However many systems don't because somewhere they record a derived figure as for cost for a sales transaction.

 

[Banana]

For simplicity I am considering the simple retail model. We can entirely neglect the income side of the invoice which should never be edited. Inconsistent invoice copies are unacceptable. For the purpose of this discussion we can also neglect the technique used to record the quantity information and focus entirely on determining and recording the value of an item in a tranasction. I use a model where costs are attributable to individual invoice lines rather than a summary of the combined lines.

FWIW, I'd have done it the same way as I am inclined to view cost as a property of a stock. But I think we need to define what is a 'stock'? As you noted earlier, some business may just average out the costs, other use FIFO accounting, then it also depends on what data they must have to run their business and keep their books in order.

In case of average costing, we merely record the quantity of widgets as shipment come in, and set the price in some fashion. But with FIFO accounting, we must separate out each batch of shipment (and perhaps even batches within same shipment) and record each.

In former case, the cost is a property belonging to the product, as whole, while in latter case, the cost is now a property belonging to the batch. Thus we must define clearly what we are really tracking here. Nonetheless, in both cases, invoicing costs is done line by line based on what product we actually chose to include in the invoice, the difference being only that in former we use a generic ID for the product, while latter, we must track which batch of the product we're using and thus quote the cost accordingly (and in that case, it's possible to have more than one line for the same product, if we didn't have enough of the product from only one batch to fill the order).

This is the cost equivalent of determining current stock quantity by a similar countback logic. The cost of the sale should not be stored on the invoice at all and the ledger considered a dynamic record that represents any edits perfomed on the supplier invoice.

But that's the problem. As earlier noted, inconsistent invoices are inacceptable. But mistakes outside the database does happen (a wrong price was quoted, incomplete or damaged batch arrived, a batch was incorrectly marked as new but in fact refurbished, retroactive changes had to be made, etc. etc.), and a good database should be able to accommodate those unfortunate mistakes.

If the invoice then is a query, then it's possible for us to have a invoice that quotes a $X today and quotes a $Y tomorrow. Now, I've already noted that for some business, they can just say the database is always right and paper is always wrong and unconditionally follow the invoice they view at right over that paper and that's fine by them. But not all business can operate in that manner. For auditing reasons, for accounting reasons, for whatever reasons, some business must track all mistakes made and in that case, this would be unacceptable. That's why I suggested earlier that if a mistake was wrong, a record of retroactive change has to be made against the same invoice so it's still consistent with the first & original invoice while clearly recording the changes made to the invoice.

In latter case, we do need to store the cost, though not for normalization reasons but rather for business rules requiring that a complete and uneditable records be made against the invoice where only adding new record can edit the old records. If we do not have this requirement, then yes, we don't even need to store cost in this case.

If I may digress for a bit, we also need to consider the fact that costs of product itself may not directly determine the price of the same product. Sometime they sell it at loss, or at a price they determine with different factors beyond the simple cost + profit margin. Normally, in a complex inventory system, we probably have a Prices table that quotes a price for a product from date X to date Y, which provides us with complete history and exempt us from storing the price in the invoices, even though price could have had changed since an invoice was presented. This is a system that's been tried and proved true.

Here's my idea: Could the costing be structured similarly? Use a Costing table to store the cost for a product or the batch of a product, then add any retroactive corrections as necessary so the old invoices always pick up the costs as quoted on that day they were printed as well any subsequent corrections that may have been made.

Is that something we're looking for?

 

[neileg]

Late to this thread since I'm no long doing any Access at all and not visiting the forums much.

Accounting conventions are just that, they are not correct or incorrect, just different fixes for problems.

Banana's assumption that FIFO costing requires identification of the physical stock that was first in is incorrect. If your stock item is sand, then you're not going to identify the grains, but you could still use FIFO since you assume it is the oldest you are taking first. It's a convention, you see, not a fact.

There are lots of other stock conventions, there's LIFO (last in first out), latest invoiced price, standard costing, weighted average, etc, etc. They all give different answers, and there are arguments in favour of all of them, but that doesn't make them wrong or right.

If you are dealing with high value, easilly identified stock, like cars for instance, it's pretty easy for a dealer to know exactly which car is being sold and therefore what it cost. But does Ford care which of 5,000 identical spec red four doors it has just sold? They're almost like grains of sand to them.

Turning to the construction of the ledger, Dave has covered this pretty well since he and I come from the same background that's not a surprise. But perhaps I can make an observation. Accountants are not, by nature, lean process thinkers. Duplication of data is fine, auditing used to involve finding two identical figures and ticking them. They don't like correcting things by erasing the wrong entry, they like to be able to follow the error and its correction with a historic perspective.

There have been accounting systems that allow for retrospective error correction, but they've never really sold any better because of that. Don't expect the average accountant to be an innovator. Do you want your tax returns done on a new and interesting basis, or do you want them to be acceptable to the authorities?