How to determine value of inventory (stock) at a certain date?

[SirBob]

Hi

 

In order to comply with proper accounting policies it is necessary to determine the value of stock on hand from time to time.

 

This is straight forward if I use the Stats module if I want to know the value of stock on hand today.

 

However, I would like to know the value of stock that was on hand at the end of last month - how would I do this?

 

(I have managed to create a SQL query in SQL Manager that shows me all inventory items with a qty on hand of 1 or more - but it's simply a snapshot of 'now' rather than of some date in the past)

 

Any ideas / suggestions welcomed.

Edited May 22, 2015 by SirBob (see edit history)

[Dh42]

You cannot really do it effectively or easily. You would need to hook PrestaShop into your accounting software to make reports like this or have a module made to make them. The main reason is that there is not a column in the database that tracks when stock was added to a product. 

[SirBob]

Thanks for your reply Dh42 

 

I was really hoping that it would be possible (instinctively I would have thought it possible) - but if there is no date anywhere in the DB from which one can calculate when a product was added, as well as when stock was increased/decreased to an existing product then I'm kinda stuck.

 

Just to clarify I am not using advanced stock management (would that have made any difference had I done so?)

 

...any other suggestions anyone?

[Dh42]

It still really does not matter if you are using it or not. PrestaShop is an ecommerce software with simple stats for running your business. It was never meant to replace accounting software. There are some platforms that do both like Netsuite, but the general deployment is usually upwards of 200k. 

[irrelevant]

ASM does include stock movement data - see the screen called thus in BO - so it ought to be possible to back-track to a given date with some custom coding, possibly even in pure SQL.  

 

I'm actually looking forward (not!) to having to do this myself, as I find myself needing a stock valuation as it was at the beginning of the year!  I'll try to remember to post back here with more info as and when I work it all out !

[SirBob]

Hi irrelevant

 

As noted above I am not using the ASM... not sure if that means I'm totally stuck or whether a glimmer of hope still exists - please do post back here with any findings / progress you make. It would be highly appreciated.

 

Let's be honest - I made a rookie mistake - I was so busy working on the sales that I didn't do a stock-take at financial year end - and when my Accountant asked about the stock value on hand I 'instinctively' (although perhaps incorrectly) thought - have database, can query!

 

The problem, as noted by Dh42, is that there is no way to query the stock at a specific date because no field exists in the DB for this. Otherwise it would have been a breeze.

 

In my naivety I assumed I could so a SQL query with a 'system date' - ie. return the stock holding when system date = my financial year end (or any other date for that matter).

 

Anyhow, if you have a Eureka moment - please share it.

 

Thanks

[irrelevant]

Sorry I forgot to note that that might not help you...

 

Have you got a backup, perchance, from near the relevant date?

 

It should be straightforward to trawl through ps_order_detail to get details of all items sold since a given date.  All you then need is the incoming stock.  I'm not sure, off the top of my head, how to track that when ASM is not in use - how do you normally add to the stock?  Do you do it often enough that you can't manually track through your supplier invoices and construct a column in a spreadsheet?

 

Having just had a look at ps_order_detail, there is a column  product_quantity_in_stock which seems to hold the remaining quantity at the time the order was raised.

 

Something like

SELECT product_id, product_quantity, min(id_order_detail), product_quantity_in_stock
FROM `ps_order_detail` od
left join ps_orders o on o.id_order = od.id_order
where o.invoice_date between '2015-01-01 ' and '2099-12-31 23:59:59'
group by id_order_detail, product_id

should work to select this from the first sale of each products after 1st Jan - just add the two quantity columns to get the stock before that sale...  Any products not in the list won't have been sold this year, so you can take current stock values.

 

This seems to check out on my live system, baring some odities on very old items,. Again, I'm using ASM, so not sure if this field is updated the same way when it's not ins use.  But it is worth a try!  

 

 

Edit (again) to add:

This would also show wrong results when stock was added between Jan 1st and the first sale thereafter... you may have to  adjust for that manually..

Edited May 24, 2015 by irrelevant (see edit history)

[Christiaan_01]

This was the main reason I started using ASM. It allows to enter the purchase price of every item, and you can choose the accounting method, like Fifo or Lifo. Then you can take a look at your warehouse and it gives the current value of your entire stock.

 

I don't understand how others do it. Purchase prices change, especially if you buy in different currencies. Without ASM or other inventory software it's virtually impossible to keep track of the stock value, which is crucial to understand your business!

[SirBob]

Hi Irrelevant, Christiaan_01

 

Thanks for your input and comment. @irrelevant I will give your sql query a try and see if the info it returns looks meaningful / plausible. Can you just explain your argument "min(id_order_detail" contained in your select statement? And if I wanted a different date (say not the 1st of Jan 2015) could I simply adjust the first date in the SQL 'where' statement to the date I want? 01 March 2015.

 

@Christiaan_01 - I agree its critical to know your stock value (and I'm not sure how others do it) but I may have failed to see the merit of using advanced stock management at the time that I implemented Prestashop. A rookie mistake perhaps - it was my first turn around the ecommerce block. :-)

 

Nevertheless 'instant' stock holding is possible in either mechanism - and had I run the report at financial year end there wouldn't have been a problem. But I didn't - and that's why I was trying to find out what the stock holding (and valuation was) at a date in the past. Are you saying that ASM can do this easily?

 

Kind regards