[AccessD] Building A Product Costing Database

On 9 Jun 2006 at 9:55, paul.hartland at fsmail.net wrote:

> To all,
> I am sure that I have seen something similar discussed on this list before
> but for some reason I can't access the archives !!!!....Anyway I am looking
> at building a production database (in the first instance for a paint
> company).....It will have to start from the very raw materials right through
> to the end product and produces costs, has anyone done something similar
> before that I could possibly have a look at (don't want code etc, just want
> to get the basic understanding of how something like this should work).
> 
I've got exactly this application running  for a major international paint 
company manufacturing in PNG. I started this for them about ten years ago 
as a simple raw materials tracking system.   It has grow over time :-)
It now does all the purchasing, costing, production scheduling,  printing 
of detailed instructions for the production and costing.  

Much of it is still the stuff I wrote years ago and I frequently cringe 
when I look at some of the design and code I wrote then. As one example, at 
a very early stage, I used  raw material codes and product codes as natural 
primary keys.  These codes have been changed several times over the last 
ten years as a result of amalgamations/buy outs within the various major  
international paint companies!  I'd certainly do a number of things 
differently if I was to start again from scratch or if the client was 
prepare to foot the bill for a major refactoring effort :-)  

The crux of the application  is to store: cost per kg of each component, 
the formula (list of raw materials) for an end product in Kgs of each raw 
material per Kg of end product and Specific Gravity (Mass per Litre) of 
each raw material.  

Then you can print out instructions to produce x litres of end product, 
adjust stocks and do the costings. There are obviously a number of other 
things to deal withincluding details of the process steps, the 
specifications of the end product so that it can be tested and what sort of 
containers the product is to be sold in (you need to have various other 
costings tables to cover labour/overheads and containers costs)

> The database would have to produce costs at every stage as they will in some
> instances use a completed product which they have in stock to mix in with
> other raw materials to produce another type/colour of paint etc.
> 

That's generally called a "factory formula". A combination of raw materials 
which are made up in bulk and then used in the production of a range of end 
products.
It is stored as an item in both the raw materials and products tables. 
Unlike real "raws" where you increase the stock on hand and adjust the 
price based on shipping receipts, when you produce a batch you need to 
deduct the component raws from stock,  increase the stock of this "raw" and 
calculate the new cost of this item based on the cost of the raws.

Contact me off-list for further discussion.

-- 
Stuart
-- 
Stuart