Lightspeed - the missing manual.....

NickSNickS Member Posts: 31

It has struck me many times that a detailed manual for Lightspeed would be really helpful. I appreciate that a live system changes all the time, but nevertheless some basic things remain the same.

My biggest bugbear is not being able to reconcile my Lightspeed inventory to my accounting (Xero) inventory.

Over the last couple of years since we switched to Lightspeed (and Xero) I have spent countless hours / days attempting to reconcile one to the other. You never quite know which one is more correct (in reality both end up being incorrect, but most of the difficulty stems from Lightspeed as Xero, being a double-entry bookkeeping system is pretty bullet proof).

I thought it might help others if I lay out the procedure I follow to attempt to reconcile the two and highlight some of the pitfalls awaiting.

One thing to say before we start into the detail. When setting up Lightspeed all of the literature (admittedly there's not much!) said that Lightspeed can be set to use an Average Cost of First In First Out (FIFO) convention for inventory, but that if you use Lightspeed with Xero, Quickbooks, etc that FIFO would not work and it was therefore recommended to use Average Cost. Despite my better judgement I went with that advice and immediately regretted it. I have subsequently been told (but can find no official confirmation) that you can set to FIFO and it will work with Xero. We switched last year and it has made my life a lot easier. My very strong recommendation is to use FIFO. Despite everything if you choose the Average Cost setting it will make Average Cost accounting entries (ie Cost of Goods Sold will be calculated at Average Cost), but the Lightspeed Asset report (the single most important report) is always calculated on a FIFO basis!

So at period end, let's say 31 December as that's what I've been working on recently, you will have to run some reports in real time (the History report is not going to help you - I have no idea what it is there for other than ornamentation as the numbers bear no resemblance to what the Asset report would say on the day). Ideally you would run the Asset report at midnight. You will also need to run the Lay-By report and the Negative Inventory report at the same time. Only by adding the totals of all three will you get to the true total of the inventory that corresponds to the accounting records. These reports should be downloaded as Excel files for further manipulation.

The Asset report needs to INCLUDE open orders (this takes into account any special orders that have been received - so, like Lay-Bys, although they are earmarked for a customer, until the sale is completed they are still part of your total inventory). To ensure you have a comprehensive list of inventory items (including items that may no longer be in stock, but that could have been bought / sold during the period you are looking at) I would untick the box 'In Stock Only' - I might even go so far as to include Archived as if anything has been archived during the period this won't have been picked up by your accounting.

To be able to reconcile your inventory you need to know with a high degree of conviction what you starting point should be - either a previous reconciliation or the detailed inventory you have imported from a previous POS or accounting system that you know to be correct (if you are importing for goodness sake use the actual cost , not the default cost as that will otherwise mess you up - I spent a very long time correcting those errors when we switched to Lightspeed and could have avoided that with better guidance during the onboarding process!). If you are starting Lightspeed with a blank canvas there's no need to worry about opening inventory as it will be zero.

More or less day 1 of bookkeeping school teaches you that Opening Inventory plus Purchases (net of Vendor Returns) less Cost of Goods Sold (net of Customer Returns equals Closing Inventory. So this is what you need to replicate in Excel to ensure that Lightspeed isn't playing tricks on you.

My Opening Inventory comes from the last time I did a reconciliation (typically quarterly in my case - so the balance at 30 September). So I have all of the individual items listed with their quantities and, crucially, the total value for each item. I use the System ID as the unique identifier in my Excel sheet (most Lightspeed reports have the system ID - a couple frustratingly don't so you have to do a little work around for Lay-Bys).

I then run a Received report for the period I'm looking at (being careful to check the 'From PO - Only' box) - say 1 October to 31 December. Followed by a Returned report for the same period. Finally I run a 'Grouped Sales Totals - Items' report that I can use to establish the Cost of Goods Sold by item.

I find it easiest to put each of the reports on a separate tab in the same Excel sheet. On one tab I do a roll forward from my previous closing inventory. On the left I have a complete list of all System IDs with the Opening Inventory (I use the VLOOKUP function in Excel to interrogate my previous version and it populates the first columns of the table - be careful to double check the total to make sure nothing has been left out). I then do the same thing for columns for Purchases, Returns and Cost of Goods Sold (this will automatically have netted out any Customer Returns). I create a final column i call Adjustments which for the time being will be blank. I then simply add up each row - Opening Inventory add Purchases less Vendor Returns less Cost of Goods Sold add Adjustments to get to a final column which is my theoretical Closing Inventory. I then do a look up of what each item has in the period end Asset report, Lay-By report and Negative Inventory report - adding these three together gives me what Lightspeed has as the total value at the period end. I then compare the two Closing Inventory totals to determine if / where there are any differences (there always are!!).

So why do differences occur and what to do about them?!

This could be due to manual adjustments being made in Lightspeed for quantities or unit costs. Those changes will be reflected in the Asset report, but as they won't have impacted Purchases / Cost of Goods Sold the roll forward will not have picked them up and critically they will not have been automatically communicated to the accounting software (Xero in my case). So you will need to make manual journal entries in your accounting software to get everything aligned. This can happen when there are stock losses due to theft or when you have done a physical inventory account. The Logs report can be filtered to identify where manual changes have been made, but frustratingly it does not quantify the impact (and therefore the corrections that need to be made in the bookkeeping).

Negative Inventory is another constant cause of differences for our business. This feature was introduced to help in the context of eCommerce. A lot of businesses drop ship from their suppliers so don't carry a complete range of stock. However, they want their customers to be able to purchase a wide range and may therefore list items that aren't in stock. This can then result in Negative Inventory - an item is sold that isn't actually there. In practice this alerts the operations team to order the missing item from the supplier so that the negative inventory is rectified and the item can be delivered to the customer. This is all great in being able to sell more, but it does result in some issue for the accounting side of things.

As you do not have the item in stock that results in Negative Inventory you can not possibly know its actual cost. When setting up Lightspeed you are asked to choose between using Default Cost or Last Recorded Cost in order to deal with Negative Inventory. Neither choice is ideal! If the actual cost turns out to be different when you back fill the sale your reconciliation will be out. That's because at the time of the sale Lightspeed needs to transmit a Cost of Goods Sold figure to the accounting to match the value of the sale and calculate a margin. This will be the Default or Last Recorded figure for that item. Unless your suppliers never change their prices you may be fine. However, that rarely happens with our business. We deliberately chose to use Last Recorded for our setting. It means that if we have never previously purchased an item (even though we advertise it for sale on our website) the COGS figure will be zero. Of course this means that it massively overstates our margin at 100%, but it also means it is easier to identify sales of items that have resulted in Negative Inventory and an incorrect COGS value.

Negative Inventory sorts itself out in Lightspeed, but it doesn't go back and correct the COGS / margins. To do that you need to go back to the individual transaction that resulted in Negative Inventory and manually overwrite the COGS number with the correct value based on the invoice subsequently received from your supplier. You will also need to do a manual journal entry in the bookkeeping to correct the COGS figure that was transferred across at the time of the Negative Inventory transaction.

One final thing to look out for is when colleagues go back into a purchase order that has already been closed and manually adjust things. Those adjustments will not flow through to the accounting records (as a closed PO will already have been transmitted to the accounting software). There is no handy Log showing when someone has gone in and made changes (let alone what those changes were). At present the only way to prevent this from happening is to have a very strict rule that however tempting it may be to go back in and make a change employees MUST NOT ON PAIN OF DEATH make changes once a PO is completed / finished. If something is wrong they should do an equal and opposite Vendor Return and start again!

Oh and watch out for 1 cent rounding errors - if you sell multiples of the same item, but the item has been purchased at different costs, in the Sale details you will see the total number of items multiplied by the average cost. Because Lightspeed (and the accounting) does not deal in fractions of a cent this can mean that the COGS figure is out by 1 cent so that the accounting will end up different to Lightspeed.

I think that covers everything and hope this will be useful. Maybe some at Lightspeed will fix some of the issues or at least get around to writing the missing manual?!

Have fun and feel free to reach out if any of my ramblings aren't clear or you need help!



  • thislittlegallerythislittlegallery Member Posts: 16

    What an amazing amount of info here. Thank you for sharing.

    I am trying to determine, and you may have answered my question, if we make a mistake in entering inventory/purchase order, can we not just go back and change that number to the correct number? For instance, someone on my team entered inventory as 6975 when it should have been 1. (don't ask)

    Being newish to LS, I simply manually adjusted that number. Now my costs are off by a mere 1 million. I can see why but how do I fix and avoid in the future.

    Thank you for any help you can provide.

    Have called LS support and after a long hold and then put back on hold by the support person, I was disconnected;( Now have been waiting on hold for an hour...

  • KayodeKayode Member, Lightspeed Staff Posts: 3 Lightspeed

    @thislittlegallery Hi, if someone makes a mistake on a purchase order and clicks the yellow "Add Received to Inventory" the inventory is recorded and can no longer be adjusted on the PO itself.

    To make the inventory adjustment, click on the item itself which will take you to the item card. On the left menu under "Details" you should see an "Inventory" tab. From there, you will see an "Inventory Details" section where you can adjust mistakes in Qty and cost entered.

  • NickSNickS Member Posts: 31

    Just be aware that if you do go back into a PO once it has been completed and start changing things this will NOT get pushed through to your accounting software (at least not with Xero and I would guess others as well). It will end up being one of the many items that needs reconciling. I can't recall if this is something you will be able to see in the logs once it is done. That may make it easier to track down. Best practice in my opinion would be to reverse the incorrect PO by doing a return and then reentering with the correct details. Both PO and VR will go through to accounting but can easily be netted off.

    If you simply change costs / quantities as Kayode suggests that will get everything in LS correct, but will also not get pushed through to the accounting. Changes to quantity and cost are most definitely visible in logs and that is how we deal with inventory adjustments if something gets broken or stolen for example. In this instance we use the logs as a check so that we know to make the same manual adjustment in the accounting. Having not used the stock taking functionality I'm not sure if that does a better job 'communicating' changes to the accounting.

    Just making the change in LS is the tempting thing to do (and one that my non-accounting colleagues regularly follow), but it will mess up the accounting. Along with many other things as noted in my original post!

    Good luck!!

  • thislittlegallerythislittlegallery Member Posts: 16

    Thank you both for the help. I appreciate it!

  • thislittlegallerythislittlegallery Member Posts: 16
Sign In or Register to comment.