Accounting Reporting- Inventory Reconciliation

alysonlanealysonlane Member Posts: 3

We are trying to reconcile the changes in inventory for each month from the Beginning balance of inventory to the ending balance of inventory. We are using all reports from Lightspeed Reporting and the numbers are not reconciling. We are starting with the Asset History report (for example at 1/1/20). A

Beginning Inventory as of 1/1/20 (based on the Asset History report

Plus Inventory Received from vendors (from Inventory Received report during the month of January)

Less Cost of Inventory sold (in multiple reports, such as Sales Lines)

Transfers In/Out from other store location (using reports in the Transfers section)

When we calculate using the above, the ending value does not add up to the ending value in the Asset History report as of 1/31/20.

Can anyone give any insight into where the difference would be coming from? More importantly, are there better accounting reports that are available so I don't have to spend so much time each month figuring things out? I find this a MAJOR flaw in Lightspeed Retail. This type of reporting is very subpar. I would love any advice on how to get the information I need.

Thank you.

20 comments

  • VanessaDVanessaD Moderator, Lightspeed Staff Posts: 204 moderator

    Hey @alysonlane ,


    I understand your point of view on this. I know the Asset history report seems to be rather unstable, but in reality it's calculated differently than what you'd normally expect. 


    Here is how the Asset History report currently gets that insight for the end values; 

    It takes the assets for today, and then calculates backwards. It takes every single item in stock and queries each sale, transfer, inventory adjustment through a PO, manual adjustment, return to vendor, and return. For every single change in inventory, it recalculates the item's cost using the cost value of that adjustment at that time. It runs through these adjustments for each item for each day going back to the one date you want to see. Then, it takes these newly calculated numbers and sums them all together into a total cost at that time.


    Except that it's more of an estimate. Each adjustment (whether it's a sale or an inventory change) uses cost in a different way. There have also been case studies regarding this particular report potentially changing the cost of only 1 or 2 items, which seems insignificant but can cause tens of thousands of dollars worth of product discrepancies in the long run. Thus why we're looking and are currently working on revamping this report altogether.


    As a workaround, we suggest to export your Assets report at the end of the month and keeping a hard copy so that when this happens NEXT year, you'll have a sheet of paper to consult instead of our system.


    Hope this helps!

    ———


    If you have any further questions Please do not hesitate to contact LightSpeed support or check out our Support Page.


    Cheers!


    Vanessa D.

    Lightspeed Retail Support

    866-932-1801 ext. 2 (Toll-Free)

    514-907-1801 ext. 2 (Montreal)

  • NickSNickS Member Posts: 22

    Hi Alyson,

    I would take anything your are told by the support team with a huge shovel full of salt. The vast majority of the staff at Lightspeed don't have the first clue about accounting. I've been using Lightspeed for 1.5 years and have constantly battled to get straight answers on this subject. The 'history' report is pretty flakey. Even if you run a report at the end of each accounting period (my recommendation) and save it to Excel you will still have your work cut out in reconciling anything.

    I reached out to the CFO of this quoted company to check if he actually had an accounting qualification (still waiting for an answer on that one). I didn't get a direct response but I was put in touch with one of the technical team who finally acknowledged a few of my grave concerns.

    I have Lightspeed linked to Xero as my accounting package. All of the guidance tells you that Lightspeed can only link to Xero using an Average Cost method of accounting for inventory. It would not have been my first choice, FIFO being far more logical / intuitive. However, I went with it and then wondered why at the end of each accounting period the Assets report did not agree to my accounting. Aside from the weird way things like lay-bys and special orders are dealt with (I haven't even attempted anything since negative inventory has been allowed!), the major flaw is that the Asset report uses a FIFO system (irrespective of me having to use Average Cost for linking to Xero). So there is never going to be an easy and instant reconciliation between POS and accounting!

    I've tried to get someone to fix this, but so far to no avail. I have just learned that apparently Lightspeed can now handle FIFO, but there has been no announcement / guidance on this officially (just my friendly tech support letting me know when I asked about progress).

    Strangely the support and help pages are full of really trivial feature requests that get lots of attention it would seem while something fundamental is quietly forgotten. I guess flashy marketing stuff is more important?!

    I hope these posts get read and that one day my concerns will be properly addressed at the appropriate level within the company. I wonder what Lightspeed's auditors make of the fact that the product doesn't do one of the most important things an accountant could wish for?!

    Good luck,

    Nick

  • VanessaDVanessaD Moderator, Lightspeed Staff Posts: 204 moderator

    Hi @NickS ,

    I'm sorry to hear about your strife between Xero and lightspeed accounting while using the FIFO method of inventory. As this is something in beta, we confirm that we've done all the necessary testing before deploying this into production but it's not possible to test all real life scenarios so we advise as such in case something arise.


    This is part of standard beta testing but still feel your frustration on this matter. Rest easy, the more testing we get in the field, the easier it is for us to help an fix anything that comes up.


    Thank you for your feedback!

    ———


    If you have any further questions Please do not hesitate to contact LightSpeed support or check out our Support Page.


    Cheers!


    Vanessa D.

    Lightspeed Retail Support

    866-932-1801 ext. 2 (Toll-Free)

    514-907-1801 ext. 2 (Montreal)

  • gregaricangregarican Member Posts: 535 

    I hear you. Our CFO is still scratching his head when it comes to monthly financial reporting. There are always discrepancies, although since we usually deal with unique serialized items that are 95% of the time(i.e. - a quantity of one with no cost changes) the scenario above doesn't come back to bite us much.

    The one thing we ran into was when we needed to report each site location's on-hand inventory for monthly financials. Rather than run a canned Retail report for each site one at a time, we had a Lightspeed staffer assist us in creating an Analytics report that would group the results for each site across all sites. One report to get what we need.

    Little did we know of a glitch where inventory that was transferred between sites during the month was duplicate counting. So if Item #12345 (quantity of one) was transferred between three sites during the reporting month, the Analytics report would show there were three on-hands for Item #12345. This little glitch resulted in our overstating our owned inventory for literally six figures.

  • NickSNickS Member Posts: 22

    Hi @VanessaD

    Just to clarify - I am NOT using FIFO. When I started using LS FIFO was not (and officially is STILL NOT) compatible with Xero (or Quickbooks to the best of my knowledge). So I had to put the accounting setting to AVERAGE (and that is still the case). However, the Inventory reporting in LS is done on a FIFO basis (no that you would necessarily know this or expect it given you have selected AVERAGE as the setting). Hence inventory balances in LS and Xero are different and extremely time consuming to reconcile. It would be great if someone could clearly specify that the Inventory report is valued at FIFO. It would be even better if that report was available as both AVERAGE and FIFO (I could pick AVERAGE and hopefully the total would miraculously agree to the balance in Xero - I have no idea if that would be possible given all the historic transactions involved?!).

    At some point I would very much like to switch the setting to FIFO, but at present I have no clue what would happen and how I would ever reconcile everything. My 'plan' without any knowledge or guidance would probably be to do another very painful roll forward to determine what my current inventory balance should be had it always been calculated using FIFO (that should agree to LS), make adjustments in Xero to get the two to agree and then switch on FIFO as my accounting setting in LS and hope for the best.

    Any help or more in depth conversations would be greatly appreciated!!

    Thanks,

    Nick

  • VanessaDVanessaD Moderator, Lightspeed Staff Posts: 204 moderator

    Hey @NickS ,

    Since you're requesting for insight that's specific to your business, I do suggest you call-in to our support team and one of our agents will be able to gather information about your operations and answer your questions.


    We're open 24/7! Feel free to contact us anytime!

    ———


    If you have any further questions Please do not hesitate to contact LightSpeed support or check out our Support Page.


    Cheers!


    Vanessa D.

    Lightspeed Retail Support

    866-932-1801 ext. 2 (Toll-Free)

    514-907-1801 ext. 2 (Montreal)

  • JeanBJeanB Member Posts: 5

    I find all Inventory reports available in LS to be absolute crap. It doesn't make sense that I can run the "Assets" Inventory report first thing on June 1st and get one number, and then a month later run the "History" Inventory report for May 31st and come up with a different number. Which report is the accurate one? It looks like I can't trust any reporting that comes out of LS.

  • VanessaDVanessaD Moderator, Lightspeed Staff Posts: 204 moderator

    Hey @JeanB,


    I understand your point of view on this. I know the Asset history report seems to be rather unstable, but in reality it's calculated differently than what you'd normally expect. 


    Here is how the Asset History report currently gets that insight for the end values; 

    It takes the assets for today, and then calculates backwards. It takes every single item in stock and queries each sale, transfer, inventory adjustment through a PO, manual adjustment, return to vendor, and return. For every single change in inventory, it recalculates the item's cost using the cost value of that adjustment at that time. It runs through these adjustments for each item for each day going back to the one date you want to see. Then, it takes these newly calculated numbers and sums them all together into a total cost at that time.


    Except that it's more of an estimate. Each adjustment (whether it's a sale or an inventory change) uses cost in a different way. There have also been case studies regarding this particular report potentially changing the cost of only 1 or 2 items, which seems insignificant but can cause tens of thousands of dollars worth of product discrepancies in the long run. Thus why we're looking and are currently working on revamping this report altogether.


    For sure, the information that will be the most accurate is if you were able to export your Asset report on the day you wish to conserve because your information will be fresh from this day. So we do suggest to export your Assets report at the end of the month and keeping a hard copy so that when this happens NEXT year, you'll have a sheet of paper to consult instead of our system.


    Hope this gives some insight!

    ———


    If you have any further questions Please do not hesitate to contact LightSpeed support or check out our Support Page.


    Cheers!


    Vanessa D.

    Lightspeed Retail Support

    866-932-1801 ext. 2 (Toll-Free)

    514-907-1801 ext. 2 (Montreal)

  • NickSNickS Member Posts: 22

    Hi @VanessaD

    Again just to clarify this is NOT specific to just my business the confusion caused by the difference between FIFO reporting and Average Cost reporting of inventory affects all businesses!

    I have tried engaging with support directly on multiple occasions and I have even tried contacting your CFO. Unfortunately there seems to be little or no acceptance that the inventory reporting / accounting is an issue. If there has been a change to improve this it has not been officially announced and there is no guidance. I'd love to be wrong - please point me in the right direction!

    Thanks,

    Nick

  • TrungTrung Member, Lightspeed Staff Posts: 2 Lightspeed

    Hi @NickS,

    Trung here! Sorry for the late reply, I was out of the office. I am still testing out a few things on my end to come up with a solution to address the issue. You can expect to hear from me on our current ticket by the end of the week.

    Thank you,

    Trung L.

    Integrations Support

  • VintageWineGuyVintageWineGuy Member Posts: 105 ✭

    Piling on, I gave up and now run extracts out of the API into my own data warehouse and build my own reporting off that. We had too many cases of metrics not matching across what we were expecting, from Sales tax to costing. We pay some of our people and vendors based on revenue KPIs, so it is hard to explain to them why things don't match/keep changing even if it is only a couple dollars, so we had to go outside the system for a consistent answer we could explain.

  • JeanBJeanB Member Posts: 5

    Hi @Trung ...wondering if you've come up with any sort of workable solution for this dreadful inventory reporting issue yet? Perhaps reprogramming the reports????

  • gregaricangregarican Member Posts: 535 

    My $0.02...

    The only solution at least we here found was to extract all of the sales, refunds, transfers, products, etc. on a nightly run. Once I have all the data locally housed in a SQL Server DB then I can slice and dice things however I want. No gotchas, no disconnects, no inordinate Lightspeed Analytics subscription expense, etc. Not real-time reporting, but previous business day is something we can live with.

    When we ran into a lack of reporting in the standard web client, and found Lightspeed Analytics to have some wrinkles making it priced out of its viability I put in the time and energy to get a handle on things. Once I could get at the data locally and could whip up a standard Crystal Reports set we are fine now moving forward...

  • NickSNickS Member Posts: 22

    Hi @JeanB and @gregarican - I've just finished doing a monster 18 month reconciliation between LS and Xero. I've fed back in some detail to one of @Trung 's colleagues. For my $0.02 (and I have a lot of those floating around looking for a home!) there are two fundamental issues:

    1. Reporting - the LS reports are fundamentally lacking - if you have your accounting setting as Average your Asset report is FIFO (like it or not - I never liked it and also never wanted to use Average but had no choice - which is kind of crazy). We have just switched our setting to FIFO as I understand this is now possible (not sure why it wasn't historically - I just took the advice at face value - although I have yet to see an announcement - as the LS Community Forum is still flagged as 'New!' that may take some time!) so that should eliminate some of the pain. It is and has never been clear to me if I should add lay-bys and special orders to my Asset report to get a number that should be the TOTAL that corresponds to our accounting - the jury is still out after my reconciliation exercise as something should and others shouldn't (fun fact - despite having Asset report at FIFO irrespective of the Average setting, the Lay-By report will be at Average, unless the setting is FIFO in which case it is FIFO - mix and match accounting conventions - gotta love it). Negative inventory is also a bit hit and miss. Suffice to say the reporting could be better. That's before I even get going on logs (no record of items being merged, easy to miss stuff being archived - random manual changes made to historic COGS flying under the radar - although that has helped me fixing many differences - , manual adjustment for cost / quantity showing up, but not in an especially useful way - like here's the accounting impact, no audit trail for when lay-bys turn into sales - at least not one that's easy to follow).
    2. Internal LS peculiarities - having worked through my reconciliation I realised that more often than not LS is not being consistent with itself - eg when refunding customer returns - the return doesn't always seem to match the COGS from the original sale (similar issues with vendor returns). The biggest bear trap being negative inventory where the COGS will be communicated at default or last purchase cost at the time the sale comes through in eCom - once you physically receive the goods and check them in the fact that the PO is at a different unit cost doesn't get reflected in the accounting. There are a few others - like PO date being out by a day - if you are doing a month end reconciliation if this happens mid-month no big deal, but if it happens on the last day of the month the PO in LS will be the last day but in Xero it is first day of next month.

    I was holding my piece on these thoughts as I wanted the team at LS to have a chance to respond and take the initiative. I've been waiting 18 months to get some acknowledgment that there are bunch of really fundamental things that should work in a POS software - adhering to accurate and generally accepted accounting practices being utmost. I don't know how the CFO of a quoted company can sleep at night knowing that his company's solution doesn't provide really basic comfort.

    Looking at LS Ideas wish list most stuff seems to be front of house - I get that - you want to be able to sell more and have great additional functionality that keeps up with new innovations in e-commerce etc. But the accounting stuff really only needs fixing once - double entry bookkeeping is pretty robust - it has been around for half a millennium at least - there really should be some simple checks or even just exception reports that highlight when LS is going adrift from the accounting records - there are only two things being communicated most of the time purchases and COGS - there are a few manual adjustments possible, but these could relatively easily be captured and highlighted (assuming it is not possible to have a feedback loop from Xero or other accounting package that immediately identifies differences as they happen with the daily accounting push).

    Looking forward to a message from Dax or Brandon.....

  • JeanBJeanB Member Posts: 5

    We moved from a pretty archaic POS system to LS in 2015 and have had nothing but issues ever since. I'm regretting the switch ... we had MUCH better reporting in our old system. If things don't get sorted I'll be recommending we drop LS and find something that actually works.

  • gregaricangregarican Member Posts: 535 

    The one lesson learned for us is that a brief 14-day trial isn't sufficient to vet all of the nitty-gritty operations of an entire platform. Even thought we got a trial extension we didn't have an opportunity to identify all of the functional gaps, develop workarounds, etc. In retrospect, that should've been a huge red flag.

    The saving grace that kept us afloat was the API. Which is rather stagnant, as it doesn't appear to have been updated since 2018. At least based on the release notes. But without it we'd have dropped out awhile back.

  • beckykbeckyk Member Posts: 4

    Question. We now use LS Retail (converted to them last summer), Big Commerce (Modern Retail integrates Big Commerce with LS) and QuickBooks desktop. Historically, we've looked at cost of goods, but are trying to become more focused on cash flow. We've always kept our accounting and POS software separate, but are considering linking the two. What are pros/cons of linking any accounting systems with POS and more specifically, linking LS Retail with QuickBooks. Thanks for help!

  • NickSNickS Member Posts: 22
    edited August 24

    @beckyk I would be curious to know how you enter data into your accounting software at the moment if you don't have it integrated to POS? Presumably a manual entry based on a reported figure from POS? Do you do this by individual transaction, daily, weekly, monthly?!

    In theory the big 'pro' is that you don't need to do any manual data entry. You can use LS to track the detail of sales activity and your inventory position without cluttering up the bookkeeping. In this day and age I would expect that to work seamlessly. I use Xero, but I suspect the interface will be very similar for Quickbooks (we looked at both when moving to LS at the beginning of 2019). I'm not familiar with Big Commerce so not sure what role, if any, that plays in your thinking. I don't see any major 'cons' with linking LS to QB.

    However, I would caution that the reporting from LS is flawed. I have been trying to get a number of things fixed / improved and continue to fight that battle. A decent accounting package will give you the tools you need to figure out cash flow. The issue you will have is more around cost of goods sold which obviously impacts the reporting of margins and because of how bookkeeping works the value reported for your inventory.

    As I have commented elsewhere beware that even if you have selected 'Average Cost' as the accounting convention for reporting COGS / inventory, the Asset Report in LS is on a FIFO basis. Moreover, to get to the accounting balance you see in QB you will need to add together the Asset total with Lay-Bys and Negative Inventory (watch out - despite Asset total always being FIFO, the Lay-By report will follow the setting you have chosen so could be Average of FIFO). Negative inventory creates its own issues - naturally if you don't have an item in stock you don't know what it will cost when it comes in - consequently when Negative Inventory occurs it reports the COGS at default or last used cost (depending on the setting chosen). When you finally take delivery of the item that gave rise to the negative inventory balance it could very well be at a different unit cost to default / last used (if you have never had that item before and have the setting 'last used' it will assign a zero value to COGS giving you a very healthy, but totally misleading 100% margin!!). This will create a mismatch between your LS reporting and QB. There is no easy way to spot this other than doing regular reconciliations (which are painful in themselves).

    Even if you continue to transfer data manually from LS to QB you will get the same reporting errors. Over time these may add up to be quite material and you will always be questioning your sanity when you see that each system reports a different inventory total and potentially different margins (the latter less likely as both systems may be equally incorrect on that front).

    In any event probably better to automate than not - if you don't have a lot of accounting experience it is best to get a professional involved.

    Good luck.

    Nick

  • gregaricangregarican Member Posts: 535 

    While we use Dynamics GP for accounting, we traditionally keep the systems separate. Figure it's a handful of manual G/L entries to account for sales, refunds, new inventory, RTV's, etc. typically performed on a month-end basis.

    The question I don't know the answer to involves how LS Retail conveys sales of owned, non-serialized inventory. In terms of integrating the sales into an accounting system.

    For example, say we have SKU #123456789012. There are three on-hand, and each came in on invoice over the course of three different orders placed months apart. Each invoice item came in at a slightly different cost. Let's say Item #1 came in at $100, Item #2 came in at $105, and Item #3 came in at $99.

    Now let's say SKU #123456789012 sold yesterday. I suppose depending if the shop is configured for FIFO or average cost, the sale is either reported as either $100 or $101.33 cost. So one of those values would be pushed over into the accounting system I'm assuming. And the accounting system would need to agree on the cost method so it's able to accept the sales line as being equivalent. But what if the accounting system is configured as LIFO? I suppose it wouldn't jibe, right?

  • NickSNickS Member Posts: 22

    @gregarican I can't speak for Dynamics, but the system we use, Xero, doesn't track inventory in any kind of detail. It works on the simple principle that opening inventory add purchases (net of vendor returns) less cost of goods sold equals closing inventory (give or take any manual adjustments that might be necessary for losses or write downs in value of certain inventory items or when you archive or when negative inventory messes things up). So the figure in Xero is entirely dependent on what gets pushed over from LS. I initially took LS and Shogo (LSA) at their word in that LS / Xero integration would only work if you selected Average Cost. I have recently been told that LS can now be used with the FIFO setting. With hindsight I question why it wouldn't always have worked. Xero only records what LS sends across - garbage in, garbage out. There is no feedback loop as far as I am aware (other than perhaps some checking behind the scenes to make sure there are no duplicates or omissions). As LIFO isn't an option in LS I don't think it would be possible to report in that way.

    I've done a lot of work tracking things through LS and I believe the FIFO reporting is robust (sadly just not always entirely joined up to the COGS reporting). So if FIFO is what you want it is more or less fit for purpose (indeed before switching to FIFO last month we used Average, because I thought we had to, and that seemed to be OK, but is a lot less intuitive - I would say FIFO is much closer to a serialized system in that individual items will have their original purchase value attached to them - I expect if you set up LS to attach a new System ID to each new serial number you would effectively end up with a serialized approach - not sure how manageable that would be - depends how may items of inventory you have and how quickly they turnover - we have a few thousand unique System IDs and turnover around 1000 individual lines of transactions each month (not all of which involve inventory, but a significant proportion that does).

Sign In or Register to comment.