Monthly Inventory/Stock Valuation with Average Unit Cost: Help Wanted!

[Luke_F_Smith]

TLDR: How do you do your monthly Stock/Inventory Reconciliation using Average Unit Cost? 

We’ve been running Shopify & Stocky since January. The POS works great and we are getting some very useful sales reporting with Better Reports – our retail team are pleased.

However, in Finance things less positive. We need to reconcile the month’s opening stock valuation, purchases, sales & adjustments with month end stock valuation – this is the ‘Monthly Stock Reconciliation’. We need monthly stock valuations to be base

d on Average Unit Cost and we need to use the available reports to get a reliable, detailed monthly snapshot of our stock valuation. We are getting discrepancies between the various reports that give a stock valuation:

• Shopify: Month-end inventory value
• Stocky: Current stock on hand
• Stocky: Historical stock on hand
• Stocky: SKU/Variant Report

We have been guided by Shopify support to focus on Shopify report as that is the ‘source of truth’ and there appear to be timing and other differences with Stocky. However, Shopify does not have Average Unit Cost.

Average Unit Cost

Average Unit Cost is only available in Stocky. Average Unit Costs can be Synced to the Cost Price in Shopify (overwriting the existing Cost Price). The settings for this (in Stocky Preferences: COGS) confused us for a while. It now appears that only the Cost Price can be synced automatically. Average Unit Cost must be synced manually – to do this the automatic sync must be disabled, and the manual Sync Now button pressed. This manual ‘Sync Now’ step then needs to be done after every delivery or adjustment!!

Possible Solution: Help Wanted!

Based on the available functionality and reports, here is a possible way of getting a reliable, detailed average stock valuation based on Average Unit Cost. Does anyone else do it this way?

• Manually Sync the Average Unit Cost to Shopify after every delivery
• At month end, wait for ‘Shopify: Month-end inventory value’ report  and use that for our monthly stock reconciliation.

Whilst this could work, there are a couple of issues:

Issues:

1. Can only get Monthly snapshot once per month, at month end. This makes it hard to test, investigate, etc.
2. Month-end report takes a few days to arrive after month end. Very frustrating at month end.
3. The manual sync of Average Cost Price is time consuming and error prone. Automatic would be so much better.
4. Also, separate but related: Adjustments seem to only be available in Stocky. The Stocky Adjustments report shows latest Cost (or Average Unit Cost, if synced). It does not show Cost at time of adjustment. We need the Cost at time of adjustment.

Question:

1. Does anyone else do their monthly Inventory/Stock Reconciliation in a similar way? If so how do you deal with any or all of the issues above?
2. Do you have any alternative ways of doing a monthly Inventory/Stock Reconciliation (that uses Average Unit Cost)?

Thank you for reading

[Screenshelf]

Hi Luke

We are encountering the same issue with average unit price. Did you have any luck with finding a solution.

Regards 

[ItTeam]

Hi,

We looked at  using Thrive for stock management which seemed promising, but on closer investigation did not meet the needs of our finance team. Currently we are doing a horrific manual reconciliation process.  We are probably going to move off Shopify soon. Its a shame.

[Maxime_Obius]

Hi @Luke_F_Smith 
It is not a free solution, but you could look at Katana Cloud Inventory app. It calculates a weighted average unit cost based on purchasing costs and quantities from PO.

Hope this helps.