All Collections
Lightspeed
Adjust product prices due to changed VAT rates
Adjust product prices due to changed VAT rates
Niels van Vlerken avatar
Written by Niels van Vlerken
Updated over a week ago

You can adjust the tax rate for your eCom store to match the new tax rates. You can also change product prices to maintain the same pricing strategy.

Examples of recent tax changes:

  • The German government will temporarily lower the normal and low VAT rates from 19% to 16% and from 7% to 5% respectively. This applies for the period from July 1, 2020 to December 31, 2020.

  • On January 1, 2019, the Netherlands changed the low tax rate from 6% to 9%.

Omnichannel

If your eCom store's inventory is linked to Lightspeed Retail, you'll need to change the item's online price in Lightspeed Retail instead of Lightspeed eCom. Retail Instructions:

  • Click here for instructions on how to edit your items manually.

  • Click herefor instructions on how to edit items with the Retail Import tool:

Content of article

This article describes the following:

  1. Adjust the tax rate

  2. Calculate new prices

  3. Change your product prices manually

  4. Change your product prices through import

Adjust the tax rate

Follow these instructions to adjust the tax rate:

  1. Click Settings in the eCom Back Office.

  2. Under Payment Settings , click Taxes.

  3. In the Manual tax rates section, select the title of the relevant tax rate.

  4. Change the percentage in the TAX section and select Save.

  5. Verify that the correct VAT appears for your products by testing your checkout. Your products may need to be refreshed by an import to force them to update their VAT rate. You can do this by continuing to adjust your product prices through import. Your products will even be refreshed when the same prices are imported. Import the same prices by skipping steps 5 and 6 of the import instructions.

Calculate new prices

You can calculate the new price with the following formula:

New price = [price ÷ (1 + old tax rate)] x (1 + new tax rate)

Explanation: The price of the product including VAT as currently entered on Lightspeed eCom.

Increased VAT rate, 6% to 9%:

New price = (89.99 1.06) x 1.09
New price = 84.90 x 1.09
New price = €92.54

Reduced VAT percentage, 7% to 5%:

New price = (89.99 1.07) x 1.05
New price = 84.10 x 1.05
New price = €88.39

You can enter the following formula in Google Sheets, assuming the sales tax has increased from 6% to 9% and the price is in cell C2:

=C2/1.06*1.09

Complete

Depending on your business style, you can also choose to round the price down to 9 or 99 cents. In the case of the example, the new price is then €92.99 if you want to round up or €91.99 if you want to round down.

In Google Sheets you can enter the following (the price you want to round is in this example in cell C2):

  • To the nearest whole value:
    ​

    • =round(C2,0)

  • To the nearest .99:
    ​

    • =round(C2,0)-.01

  • To the nearest .09:
    ​

    • =round(C2,1)-.01

Change your product prices manually

Follow these directions to manually adjust product prices:

  1. Click Products in the eCom Back Office.

  2. Select a product title to open its settings.

  3. Scroll to the section INVENTORY & VARIANTS and select a variant title to access its settings.

  4. In the PRICES section, you can use a calculator to determine the new price based on the above formula. For more information about how the formula works, see Calculating new prices.

  5. Enter the result in the Price field and click Save. If necessary, round the value up or down to an integer or 0.09.

  6. Repeat steps 3-5 for all variants of the product.

  7. Repeat steps 1-6 for all products with the low tax rate.

Change your product prices through import

Follow these directions to adjust product prices with the import tool:

  1. Export your products from Lightspeed eCom and create a backup file.
    ​

    1. Click Tools in the eCom Back Office.

      E-APP-ClickTools.png

    2. Click Export.

      E-APP-ClickExport.png

    3. Click New Export.

      E-APP-NewExport.png

    4. Make sure Products is selected and click Export.

      E-APP-EnsureProductsIsSelectedClickExport.png

    5. Wait for the export to complete and click Download.

      E-APP-WaitForStatusFinishedThenClickDownload.png

    6. Save the file in an easy-to-remember location, such as your desktop.

      E-APP-SaveFileOnDesktop.png

    7. Locate the file on the computer and copy it.

      E-APP-GoToFileAndCopy.png

    8. Paste the file in the same location.

      E-APP-PasteTheItemInTheSameFolder.png

    9. Give the copied file a descriptive title. For example: Backup product export - price change.

      E-APP-RenameAsBackupProductExport.png

    10. You can choose to move this file to an external drive or to a safe location on your computer.

      E-APP-SaveFileInBackups.png

  2. Open the original exported file in Google Sheets.
    ​

    1. Click here to sign in to your Google account and access Google Sheets.

    2. Create a new blank spreadsheet by clicking + .

      E-APP-ClickPlus.png

    3. Click File > Open.

      E-APP-ClickFileOpen.png

    4. Click Upload.

      E-APP-ClickUpload.png

    5. Click Select a file from your computer.

      E-App-ClickSelectAFileFromYourComputer.png

    6. Locate and select the original file you exported from eCom and click Open.

      E-APP-LocateSelectClickOpen.png

  3. Delete the rows for products whose price does not need to be changed.
    ​

    Pictures
    ​

    1. Select all cells by clicking the empty square above row 1, to the left of column A.

      E-APP-SelectAllCells.png

    2. Click Data > Create a filter.

      E-APP-ClickDataCreateFilter.png

    3. Scroll right and find the Tax column. Click the tax filter and sort the Tax column in alphabetical order.

      E-APP-SortTaxColumnAlphabetically.png

    4. Delete all rows that do not have a 6% tax rate by highlighting each row, right-clicking the row numbers and selecting Rows # - Delete # .

      E-APP-DeleteRows.png

  4. Remove the columns for which prices do not need to be adjusted.
    ​

    1. Delete column C, D and E, named Visible, Brand, Supplier, by highlighting the three columns, right-clicking the column letters and selecting Delete Columns C - E .

      E-APP-DeleteColumns.png

    2. Repeat step 4.a. for all columns except the following:
      ​

      1. Internal_ID

      2. Internal_Variant_ID

      3. Price

  5. Change the product prices based on the formula explained under the heading Calculate new prices in this article.
    ​

    1. In an empty column, click the cell in the second row and press the equals sign = on your keyboard.

      E-APP-SelectEmptyCellTypeEqualKey.png

    2. In the Price column, click the first price in the second row.

      E-APP-ClickFirstPriceinSecondRow.png

    3. Then enter (C2/1.06)*1.09 and press enter. Read the Calculate new prices section in this article to learn more about the formula.

    4. Apply this formula to all subsequent cells by double-clicking the square in the highlighted cell.

      E-APP-DoubleClickingSquare.png

    5. Select and copy the new prices.

      CopyResults.png

    6. Select the first cell under Price.

      E-APP-SelectFirstCellUnderPrice1.png

    7. Click Edit > Special>Paste Only Values.

      E-APP-ClickEditPasteSpecialPasteValuesOnly1.png

    8. Remove the column from which the increased prices were calculated.

      E-APP-DeleteTheColumnUsedToCalculate2.png

  6. Round the prices to the nearest decimal, or 0.09.
    ​

    1. In an empty column, click the cell in the second row, press the equals sign = on the keyboard and type round(.
      ​

      E-APP-TypeEqualRound.png

    2. Click the first cell under Price.

      E-APP-SelectFirstCellUnderPrice2.1.png

    3. To round to the next nearest value, do the following:
      ​

      1. Integer: Type ,0) and press Enter.

        E-APP-WholeValueTypePressEnter.png

      2. Value 0.09: type ,1)-.01 and press Enter.

        E-APP-NearestPointZeroNine.png

    4. Apply this formula to all subsequent cells by double-clicking the square in the highlighted cell.

      E-APP-DoubleClickingSquare.png

    5. Highlight and copy the new prices.

      E-APP-HighlightAndCopyNewPrices.png

    6. Select the first cell under Price.

      E-APP-ClickFirstCellUnderPrice.png

    7. Click Edit > Paste Special > Paste values only.

      E-APP-ClickEditPasteSpecialPasteValuesOnly2.png

    8. Remove the column from which the rounded prices were calculated.

      E-APP-DeleteTheColumnUsedToCalculate1.png

  7. Save the file as a CSV file in Google Sheets.
    ​

    1. Click File > Download As > Comma Separated Values.

      E-APP-ClickFileDownloadAsCSV.png

    2. Save the file in an easy-to-remember location, such as your desktop.

      E-APP-SelectDesktopClickSave.png

  8. Import the file into Lightspeed eCom.
    ​

    1. Open the eCom Back Office and click Tools.

      E-APP-ClickTools.png

    2. Click New Import.

      E-APP-ClickNewImport.png

    3. Click Browse computer.

      E-APP-ClickBrowseComputer.png

    4. Locate the file on the computer and click Open.

      E-APP-SelectFileClickOpen.png

    5. Under Behavior, select the option Only update existing products.

      E-APP-ForBehaviorSelectOnlyUpdateExisting.png

    6. At Find by, check whether the Product/Variant ID option is selected.

      E-APP-ForFindByEnsureProductVariantIDIsSelected.png

    7. In the SET FIELD TYPES section, check that each column is mapped to the correct field and click Continue.

      E-APP-InTheSetFieldTypesSectionEnsureAllAreSelectedAndClickContinue.png

    8. Click Start the import.

      E-APP-ClickStartTheImport.png

    9. Wait for the import to complete. If errors appear, you can make corrections to the data file and re-import the edits. Click here for more information on correcting these errors.

  9. Check your changes by comparing the import file with your products.
    ​

    1. Open Google Sheets and open the file you imported into Lightspeed eCom. Select any row and copy the product's internal ID.

      E-APP-SelectAnyRowAndCopyProductID.png

    2. Log in to your eCom store's back office and select Products.

      E-APP-ClickProducts.png

    3. Click on the title of a product.

      E-APP-ClickTitleOfProduct.png

    4. Replace the number after /admin/products/ in the address bar with the copied internal ID. Press Enter on the keyboard.

      E-APP-PastingTheCopiedInternalID.png

    5. Compare the product price in Lightspeed eCom with the price in Google Sheets and make sure your changes are reflected correctly.

      E-APP-ComparePricesAndInternalIDs.png

    6. Repeat steps 9.a.-9.e. for some other products until you are sure that all your changes have been imported correctly.

Did this answer your question?