This guide explains how to correct the average cost of products in LightSpeed. The average cost is an important value that LightSpeed uses to calculate the value of your inventory and the Cost of Goods Sold (COGS). If the average cost is incorrect, it can lead to inaccurate financial reports.

Why is Average Cost Important?

Every product in LightSpeed has an average cost. This cost is calculated by LightSpeed as a weighted average of the prices you pay when you receive new stock from suppliers. If you enter the wrong supply prices when recording stock orders or transfers, the average cost can be wrong, which then affects your COGS and inventory values.

How to Correct Average Cost

You can manually correct the average cost of products in LightSpeed by using a CSV file. A CSV file is a type of file that can be opened in spreadsheet programs like Excel or Google Sheets.

If you do not feel comfortable performing the update via spreadsheet, will reach out to Evo Support for assistance as they can perform this task on your behalf.

Here are the steps:

1. Export Inventory Report with Avg. Cost
   1. In LightSpeed, go to Reports > Inventory Reports.
   2. Set the report parameters to:
      • Report Type: SKU name
      • Measure: All inventory
      • Date range: All time
   3. Click More filters
   4. Toggle Show inactive inventory ON.
   5. Click Search
   6. Click Format results
   7. Select By outlet and click Apply.
   8. On the far right of the report, click the + symbol
   9. Ensure the below options are ticked only:
      • Margin %
      • Gross profit
      • Avg. cost
   10. Click Update report
   11. Click Export List... and choose XLSX as the file format.
   12. Open the Downloaded report in excel.
2. Review Avg. Cost
   1. Click into cell A1
   2. Click Sort & Filter > Custom Filter
   3. Ensure 'My data has headers' is ticked
   4. Sort by Margin (%) with the order set to Smallest to Largest
   5. Click OK
   6. Review the 
   7. Create an additional tab - clear this blank for now
3. Export Products
   1. In LightSpeed, go to Catalog > Products.
   2. Use the filter options to find the specific products you want to correct. This makes it easier to work with a smaller list.
   3. Click Search to apply filters
   4. Click Export List... and choose XLSX as the file format.
   5. Save a copy of the file as a backup (e.g., product-export-backup.xlsx). 
   6. Delete all the columns related to inventory (including their headers). This is Critical as this will override your on-stock hand values if imported.
   7. Copy the sheet information, and paste in the additional tab you created in the Inventory Report.
4. Add Cost Columns

   1. For each outlet where you want to update the average cost, add a new column.

   2. Name the columns using this format: average_cost_<outlet_name>

   3. Replace <outlet_name> with the exact name of the outlet in your system

      • For example, if your outlet is named "Service Outlet" the column name would be average_cost_Service_Outlet.

5. Enter the Correct Costs

   • In the new columns, enter the correct average cost for each product you want to change/correct.

   • Use 0 or a positive number. You can use up to 5 decimal places.

   • Leave the cell blank for any product you don't want to change. These products will be skipped.

6. Save and Import the CSV File

   1. Save the changes to the CSV file.

   2. Go back to Catalog > Products in LightSpeed.

   3. Click Import and upload the edited CSV file.

   4. Follow the prompts to map the file. 

   5. If the upload fails refer to the LightSpeed Common errors with product spreadsheet imports guide.

      
      

Next Steps

• Always double-check your supply prices when receiving stock to prevent average cost errors.
• If you have a large number of products to update, consider doing it in smaller batches.