Inventory reconciliations

NickSNickS Member Posts: 33 ✭
edited September 10 in Feedback

I've just been prompted by one of the support team to make a submission for suggested improvements.

I've been using Lightspeed for several years now and have repeatedly asked to that a fundamental requirement for anyone taking a business seriously and trying to maintain and prepare accurate accounting records is the ability to be able to reconcile the inventory value in Lightspeed with the inventory value in any linked accounting package (in my case Xero).

Even determining the total value of inventory at a period end is painful. The asset report does not include lay-aways (even though they are still strictly speaking the property of the business - they have not been sold) so they need to be manually added. Even though negative inventory is a 'theoretical' thing it also needs to be taken into account (as the accounting balance will have been reduced by the theoretical cost of sale). Special orders are still a bit of a mystery and sometimes appear to have to be added.

I religiously run an asset report, a negative inventory report and a lay-away report at each month end so that I have a reliable snap-shot. (The history report is a complete waste of space clearly conceived / developed by someone that has never been anywhere near a basic accounting education). Sadly sometimes the asset reports aren't entirely reliable (eg at 31 December i have discovered to my great consternation!).

The end of period asset report combination I have noted above SHOULD give you a closing stock figure. This SHOULD agree to any closing stock figure in the accounting package that Lightspeed is integrated with. Sadly in reality the totals never agree.

So how does one go about figuring out the differences?! I've been wrestling with this for years and have made numerous suggestions (that have largely fallen on deaf ears).

My methodology is to start with a known opening stock balance - one that i have previously agreed to both Lightspeed (asset report + lay-aways - negative inventory) and the accounting package. I then use 'Accounting 101' rule that opening stock add purchases less sales equals closing stock. Purchases comes from the 'Received' report (net of any vendor returns from the 'Returns' report) and sales come from the 'Margin Per Line' report. Each report is run for the period of interest and provides an item by item breakdown of what has been purchased / sold for the period. I check that the total purchases / sales agree to the total for the same period in the accounting package. Invariably they are (sometimes there are slight timing differences at period end - an issue reported but not sure if ever fixed where there can be a one day delay between an item being recorded in LS and the item date in the accounting package - only an issue of movements take place on the last day of a particular period).

So if opening stock plus purchases less sales equals closing stock the balance in LS should be the same as the balance in Xero. However, that is never the case (at least not for our business which I don't think is especially big or complicated).

Over time I have discovered a number of reasons why that might not be the case. The key ones are down to manual adjustments (they don't get communicated to the accounting package) and, much more prevalent and problematic, issues caused by allowing negative inventory. When a sale is made that results in negative inventory the value communicated to the accounting package is a theoretical value or placeholder based on a combination of that item's history and settings chosen for how to deal with negative inventory (it could be default cost, last used actual cost or zero if the item has not previously been purchased). We could switch off negative inventory, but that limits our ability to sell more items (if something isn't in stock we can usually get it from our suppliers very quickly and would rather do that than only allow e-commerce customers to purchase what we physically have available). Having communicated the theoretical value of the cost of sale to the accounting package, once the item is replenished and physically shipped out, potentially / likely at a different actual cost, the updated value is not communicated to the accounting package and the cost of sale value in Lightspeed is not updated. This means at period end when carrying out the 'roll forward' of opening stock plus purchases less cost of sales to get to closing stock the 'cost of sale' figure will not be the right one for any items where negative inventory occurred (and the eventual purchase price is different from the theoretical price). So you might have an item who's opening stock is, say £50 and, keeping things simple, you sell two of these (even though there is only one in stock). This will result in a negative inventory situation. The cost of sale will be reported as £100 and the negative inventory balance will be -£50. If / when the 'back order' is fulfilled and the new purchase cost turns out to be, say £60, we have an 'issue'. Our real cost of sale was £110, not £100. Doing the same roll forward for the next period I would start with -£50 and add £60 as my purchase. This would result in a balance of £10 even though my back order has now been fulfilled and I have a real balance of £0 for this item. The asset report would show the correct balance of £0, but my accounting software balance will be £10 and my accounting cost of sales will be under reported by £10. This needs to be corrected manually in both Lightspeed (if I want any subsequent margin by line reports for the period to be accurate), by overwriting the cost of sale in the sale associated with the negative inventory, and in my accounting software by doing a manual journal.

A similar process is needed for manual adjustments to quantity and/or cost. In theory these should be easier to identify using the 'logs' report for a given period. Although I discovered recently that if an item is merged (possibly also for archived items?!) the history disappears making this task more challenging.

In any event if someone at Lightspeed would like to engage with my I would be very happy to work on improving the product for everyone. Just an acknowledgement that this is a major issue would be a nice start. I have to assume that the CFO/ CEO and other senior executives in the organisation do not have any kind of formal accounting training despite running a business that is inextricably linked to the keeping of accurate business records?!

Thanks for your attention and feel free to reach out.....


1 comment

  • NickSNickS Member Posts: 33 ✭

    A bit of an update after completing my marathon annual reconciliation....

    Lay-bys - if when running the Asset Report you select 'include open orders' the lay-bys do get added in (one less report to run and worry about). I'm a little more confident that he 'Special Orders' report doesn't need to be included to capture an inventory total that theoretically matches the accounting number, but negative inventory does have to be added.

    I also discovered an additional annoying rounding error issue. The cost in the lay-by report (or when looking at the lay-by section of inventory for a particular item) rounds things up based on the number of units multiplied by the average cost of those units. For example if you have one lay-by that cost 50 and another that cost 49 the total value SHOULD be 99. However, the figure shown in the inventory analysis for the item will be 100. I believe LS is adding up the correct underlying individual values, dividing by the quantity to get to the nearest cent/penny value and then multiplying back by the quantity. The same error means that the cost of goods sold (where there are multiple items with different values on the same invoice) also is often incorrect (one of the reasons why the accounting stock value will not equal the LS stock value - ditto COGS in the two systems. Now that i know this when I am reviewing all my differences at an individual item level I can quickly spot the ones that are likely to be due to this. It is then a manual review of all sales for that item in a period to see any where multiple units have been sold as they are more likely to have been rounded in this way resulting in an error, but it really is needle in a haystack stuff.

    I guess the way things have been coded creates this issue - another small bug that I would have thought could be ironed out.

Sign In or Register to comment.