Product cost

[PatClay]

I am looking to have zen-cart store & calculate my product costs so I can calculate my inventory value & therefore profit each month & most importantly, at the end of the year for tax.

I have found these plugins:
https://www.zen-cart.com/downloads.php?do=file&id=1444
https://www.zen-cart.com/downloads.php?do=file&id=175
They will not work for me as they assume that each product only has one cost. If there is a price change part way through the year, then they cannot deal with it (if I am wrong, please correct me).

Example:
March I buy 10 units at $1.00 each
June I buy 10 units at $1.20 each.
December I still have 20 units, at an average cost of $1.10
Most of my products will have a different price each time I purchase them.

My accounting package does not have a great inventory system, so I think that zc will deal better with the few thousand inventory lines I have.

I believe that I can do it by adding a couple of tables:
table: purchases
fields:
purchases_id
date
supplier
invoice number
This would give me an audit trail back to the invoice

table: purchases_products
fields:
purchases_id
product_id
quantity
price
This would give the $ spent on each individual product.

Add a page similar to the current products admin page & when I get a new shipment, I can add the number of units & cost of each product, click 'update' & store the value at the same time I increase my stock numbers.

I am not sure what my SQL would look like to get the LIFO data for the current values (might have a look & see if Mr taxman says WAC is ok, it feels easier), but I know it will be possible if the data is there.

So, my questions:
1/ Has someone else already invented this & I am an idiot for not finding it?
2/ Has no-one invented it for a very good reason & I am an idiot for not doing the same way in X very simple way
3/ Is this actually a reasonable idea which could be implemented without a massive amount of anguish?
4/ How do other people value their inventory at year end (remember 2000 items & regular price changes)

Thanks for your help.

Pat

[kobra]

I believe that I can do it by adding a couple of tables:

But one then needs the admin code to access these tables and admin pages to enter the data along with math code to calculate what you want - - a lot of coding

Might be simpler to use a spreadsheet which already has those functions

[PatClay]

Thanks for your comments.

I am currently doing it in a spreadsheet, but am very aware that it is a manual process & therefore prone to errors. I am looking to expand my number of products, which just keeps getting harder/riskier with a spreadsheet, hence looking to other alternatives.

[PatClay]

OK, I haven't been overwhelmed with responses either way here, so decided to go ahead and make it.

I have completed a plugin which calculates the cost by product as described above & stores it, at the same time as increasing the inventory levels.

I haven't got the reporting sorted yet, but am working on it at the moment, how hard can it be

If anyone is interested in this functionality, let me know and I will send you something you can use. If I don't get back to you via the forum, feel free to contact me via my one of my stores.

Cheers

Pat

[sambenet]

OK, I haven't been overwhelmed with responses either way here, so decided to go ahead and make it.

I have completed a plugin which calculates the cost by product as described above & stores it, at the same time as increasing the inventory levels.

I haven't got the reporting sorted yet, but am working on it at the moment, how hard can it be

If anyone is interested in this functionality, let me know and I will send you something you can use. If I don't get back to you via the forum, feel free to contact me via my one of my stores.

Cheers

Pat

We're running into the same issue as well. I'm very interested in what you have so far. I'm not an expert programmer, but could help out some with the coding as well.

[PatClay]

Hi

Sorry for the delay in getting back to you - summer holidays mean a lot of distractions.

I have completed a basic but functional package.
When product arrives, I enter the number & price & can allocate shipping, currency exchange etc. This updates my stock level & saves the cost details. I have added a new table with a supplier for each product (saves dealing with 1000 products on an entry page).

Basic reporting on a fifo basis is completed, albeit with an issue that products in multiple categories are double counted (small issue for me at the moment so not fixed).

I can send you a copy of the files if you PM me, but I don't want to release them as a plugin as they are not at a level of polish needed for the general masses & I am not sure I have the time/skills to do so. Happy to be convinced otherwise though.

Cheers

Pat

[carlwhat]

pat,
as you are not doubt aware, inventory and product costing can be a very complex process. depending on the type of product carried, a business owner can make a decision to cost inventory at FIFO, LIFO or average cost. each has their own advantages/disadvantages... in addition, a larger business can use 1 method for tax accounting and 1 method for managerial accounting.

building an inventory accounting system plugin requires the need for building and maintaining a purchasing system. right off the bat, i can count 4 different new tables: vendor, PO header, PO detail, and a receipts table. from these tables, one could then start to build an inventory accounting system.

in taking a cursory look at the two plugins you linked to, i would agree that they seem lacking as far as getting an inventory costing system together.

i have implemented a rudimentary PO system due to the unique requirements of my clients. unfortunately it would still need a lot of work to make it into a true inventory accounting system.

i wish you luck in putting something together. it seems like a lot of work. i think most small business owners get away with writing off their inventory purchases, which is why just storing a single number for product cost makes it work for them. either that or they link it to an outside accounting system.