Accounting Reporting- Inventory Reconciliation

alysonlanealysonlane Member Posts: 2

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.

11 comments

  • VanessaDVanessaD Moderator, Lightspeed Staff Posts: 133 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: 14

    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: 133 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: 449 

    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: 14

    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: 133 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: 3

    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: 133 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: 14

    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: 87 ✭

    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.

Sign In or Register to comment.