Proposed Inventory plan (very unofficial long draft)

[Conrad Canterford]

All,
Here are my current plans for the implementation of an Inventory
Management System using Gnucash. For a background to my thoughts and the
discussion generated on the list about this matter, see the spate of
emails from September on this subject. 
Please note that I have, contrary to my earlier wishes, developed this
plan as an implementation based entirely on the Gnucash engine.

Required Features:
- Able to record the precise number of an item in stock.
- Able to deal with items to be distributed across an arbitrary number
of locations.
- Able to automatically calculate the "cost price" of an item when it is
removed from stock.
  - This tracking needs to be calculable on one of (at least) three
standards:
    - LIFO: Output price is most recent purchase price.
    - AVG: Output price is the average purchase price for that item over
an arbitrary period.
    - FIFO: Output price is the actual purchase price.
- Automatic allocation of amounts to the appropriate (user selected)
accounts on removal from stock. 
- Storage of recommended sale price, re-order levels and other
information on a per item per location basis (ie, can be different for
any item at any location).

Nice Feature:
- Able to have a hierachical list of locations.

Terminology:
"stock", and "in stock" refer to the entire holdings under inventory
management.
"mutual account" refers to the Gnucash account type more commonly
referred to as "stock".
"item" refers to a specific individual thing - a coke can, a rake,
whatever.
"item type" refers to a class comprised of a group items under inventory
management meeting that description. Eg: item type of "Coke cans" is a
class consisting of items meeting the description of "coke can".
"location" is a physical place where items are stored. Eg: "My garage";
"Storage Room"; "Warehouse 5".

Implementation thoughts:
I can't think of an easy way to explain this, so I'll start from the top
and work down. This is not particularly intuitive. Sorry.

Within the Gnucash account structure, all inventory management stuff
should be kept under just one account (with sub-accounts obviously). I
have tentatively called this the "Inventory Management" account. This
should be an account of type "asset". In a kvp frame attached to this
account would be a set of kvp frames describing various global features
of the system. This would include the default gnucash expense account
that deductions from the Inventory Management system should record to. 

Under "Inventory Management" would be a number of accounts - one for
each location. These would also be "asset" accounts. Similarly, there
would be a kvp frame of kvp frames containing various details relevant
to the location (maybe things like the street address, contact phone
numbers, that sort of stuff). In addition, there would be the ability to
record a location-specific expense account.

Under each location account would be a mutual account for each item type
ever represented at that location. In a kvp frame of frames would be
recorded the sale price, re-order level, etc. information.

A modified GUI would be required to deal with this. While for some
purposes it would be acceptable to use a slightly modified version of
the current register, for other stuff that would be a complete pain. In
particular, I think there needs to be a "stocktake" screen and a
"<insert time period here> sales update" screen at least.
Reports would also be needed to complete the system. My thinking hasn't
developed that far yet.

Benefits:
- No work is required on a new engine. All that infrastructure is there.
If/when the real database gets added to the engine, this stuff will work
with it automatically.
- Structuring it this way means that the value of the stock on hand is
automatically calculated and included in Gnucashs Balance Sheet.
- The entries in the mutual registers provide an exact record of all
stock movements for historical analysis and auditing purposes. 

Problems/Issues:
- This is potentially going to create an enormous number of additional
accounts. What effect this is going to have on file size, memory
footprint and execution times I would not care to estimate.
- To avoid cluttering up the accounts tree too much, it might be
worthwhile perhaps having a "hidden" option for accounts (with the
ability to toggle that behaviour in the GUI options). This would mean
that all the entries lower than the location could be (optionally)
hidden.
- I haven't quite worked out how the complete structure of this is going
to work. In particular, it may be necessary to have a table of all
possible item types somewhere. How this could be dealt with I have not
really thought about.
- I'm not sure how we can calculate the different cost basis for when
stock is removed from management. I need to think more about how this
can be done. 

Comments and suggestions are quite welcome. 

-- 
Conrad Canterford (conrad@mail.watersprite.com.au)
Water Sprite Pty Ltd   | info@mail.watersprite.com.au
 GPO Box 355,          | Incorporating:
 Canberra, ACT, 2601   |   Australian Tour and Event Management (ATEM)
 Australia.            |   Ticketing Services Division
Phone: 0419 122 553    |   Catering Services Division