Hi. How can we help?

Search

Adjusting product prices due to changes in VAT

You can adjust your eCom store's tax rate to match a government's change to the VAT rates. You can also adjust your product prices to keep the same pricing strategy.

Examples of recent tax changes:

  • The German government is temporarily reducing the regular and reduced VAT rates from 19% to 16% and from 7% to 5% respectively, on July 1st 2020 until December 31, 2020.
  • The Netherlands changed the low tax rate from 6% to 9% on January 1, 2019.

Omnichannel

If your eCom store's inventory is connected to Lightspeed Retail, you will need to change your item's Online price in Lightspeed Retail instead of Lightspeed eCom. For Retail instructions on:

  • Manually editing your items, click here.
  • Using the Retail Import tool to edit items, click here:

Article contents

This article explains how to:

  1. Adjust your tax rate
  2. Calculating new prices
  3. Manually adjust your product's prices
  4. Adjust your product's prices via import

Adjust your tax rate

To adjust the tax rate, follow these instructions:

  1. In your eCom Back Office, click Settings.
    E-APP-ClickSettings.png
  2. Under Payment Settings, click Taxes.
    E-APP-ClickTaxes.png
  3. In the Manual taxes section, select the title of the appropriate tax rate.
    E-APP-SelectTaxTitle.png
  4. In the TAX section, change the percentage and select Save.
    E-APP-ChangeTaxAndNameIfNeededAndClickSave.png
  5. Verify the correct tax appears for your products by testing your checkout. Your products may need to be refreshed by an import to force them to update their tax rate. You can do this by proceeding with adjusting your product's prices via import. Your products will be refreshed even by importing the same prices. Import the same prices by skipping steps 5 and 6 of the import instructions.

Calculating new prices

Calculate the new price using this formula: 

New price = [price ÷ (1 + former-VAT-rate)] x (1 + new-VAT-rate)
Where price is the original product's price including VAT found in Lightspeed eCom.

Tax increase, 6% to 9%:

New price = (106 ÷ 1.06) x 1.09
New price = 100 x 1.09
New price = €109

Tax decrease, 7% to 5%:

New price = (107 ÷ 1.07) x 1.05
New price = 100 x 1.05
New price = €105

In Google Sheets, assuming an increase from 6% to 9% and the price in cell C2, type the formula:

=(C2/1.06)*1.09

Rounding

You may also choose to round the price to the nearest .09 or .99 depending on your businesses style. So the new price may either be: €92.99 or €91.99 depending on whether you want to round up or down.

In Google sheets, assuming the price to round is 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

Manually adjust your product's prices

To adjust the price of products manually, follow these directions:

  1. In your eCom Back Office, click Products.
    E-APP-ClickProducts.png
  2. Select the title of a product to open its settings.
    E-APP-ClickTitleOfProduct.png
  3. Scroll to the INVENTORY & VARIANTS section and select the title of a variant to open its settings.
    E-APP-ScrollAndSelectTheVariantTitle.png
  4. In the PRICES section, using a calculator, calculate the new price using this formula. See Calculating new prices for more information on how this formula works.
  5. Enter the result into the Price field and click Save. Optionally, round the value up or down to a whole number or 0.09. 
    RoundingUp1.1.png
  6. Repeat steps 3-5 for all the product's variants.
  7. Repeat steps 1-6 for all the products with the low tax rate.

Adjust your product's prices via import

To adjust the price of products using the import tool follow these directions:

  1. Export your products from Lightspeed eCom and create a backup file.
    1. In your eCom Back Office, click Tools.
      E-APP-ClickTools.png
    2. Click Export.
      E-APP-ClickExport.png
    3. Click New Export.
      E-APP-NewExport.png
    4. Ensure Products is selected and click Export.
      E-APP-EnsureProductsIsSelectedClickExport.png
    5. Wait for the status of the export to be finished, then click Download.
      E-APP-WaitForStatusFinishedThenClickDownload.png
    6. Save the file in an easy to locate place, for example on your Desktop.
      E-APP-SaveFileOnDesktop.png
    7. Go to the file on your computer and copy it.
      E-APP-GoToFileAndCopy.png
    8. Paste the file in the same location.
      E-APP-PasteTheItemInTheSameFolder.png
    9. Rename the copied file with a descriptive title. For example: Backup Product export - Price change.
      E-APP-RenameAsBackupProductExport.png
    10. Optionally, move this file on an external drive or in a secure location on your computer. 
      E-APP-SaveFileInBackups.png
  2. Open the original exported file in Google Sheets.
    1. Click here to login to your Google account and open Google Sheets.
    2. Create a new blank sheet 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 rows for products that don't require a price change. 
    1. Select all cells by clicking the blank 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 to the right and locate the Tax column. Click on the tax filter and sort the Tax column alphabetically.
      E-APP-SortTaxColumnAlphabetically.png
    4. Delete all rows don't have the tax rate by highlighting each row, right clicking on the row numbers and selecting Delete rows (# - #).
      E-APP-DeleteRows.png
  4. Delete columns that are not required to adjust the prices.
    1. Delete Column C, D and E, titled Visible, Brand, Supplier highlighting the three columns, right clicking on the column letters and selecting Delete Columns C - E.
      E-APP-DeleteColumns2.png
    2. Repeat step 4.a.  all columns except the ones titled:
      1. Internal_ID
      2. Internal_Variant_ID
      3. Price
  5. Adjust the product prices according to the formula discussed in the Calculating new prices part of this article.
    1. In an empty column, click on the cell in the second row and type the equal symbol = 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 type(C2/1.06)*1.09 and press enter on your keyboard. For more information about this formula, see the Calculating new prices part of this article. 
    4. Apply this formula to all the following cells by double clicking the square on 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 > Paste Special > Paste values only.
      E-APP-ClickEditPasteSpecialPasteValuesOnly1.png
    8. Delete the column used to calculate the adjusted prices.
      E-APP-DeleteTheColumnUsedToCalculate2.png
  6. Optionally, round the prices to the nearest decimal, or .09. 
    1. In an empty column, click on the cell in the second row and type the equal symbol = on your 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 nearest:
      1. Whole value, Type ,0) and press Enter.
        E-APP-WholeValueTypePressEnter.png
      2. .09 value, type ,1)-.01 and press Enter.
        E-APP-NearestPointZeroNine.png
    4. Apply this formula to all the following cells by double clicking the square on 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. Delete the column used to calculate the rounded prices.
      E-APP-DeleteTheColumnUsedToCalculate1.png
  7. Save the file as a .CSV in Google Sheets.
    1. Click File > Download as > Comma Separated values.
      E-APP-ClickFileDownloadAsCSV.png
    2. Save the file in an easy to find place, like on your desktop.
      E-APP-SelectDesktopClickSave.png
  8. Import the file to Lightspeed eCom.
    1. Open your 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 your computer and click Open.
      E-APP-SelectFileClickOpen.png
    5. For Behavior, select Only update existing products.
      E-APP-ForBehaviorSelectOnlyUpdateExisting.png
    6. For Find by, ensure Product/Variant ID is selected.
      E-APP-ForFindByEnsureProductVariantIDIsSelected.png
    7. In the SET FIELD TYPES section, make 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 any errors are visible, make corrections to your working data file and import your edits again. For more information on how to correct these errors, click here.
  9. Verify your changes by comparing the import file with your products.
    1. Open Google sheets and access the file that you imported to Lightspeed eCom. Select any row at random and copy the product's Internal ID.
      E-APP-SelectAnyRowAndCopyProductID.png
    2. Login to your eCom store's Back Office and select Products.
      E-APP-ClickProducts.png
    3. Click the title of any product.
      E-APP-ClickTitleOfProduct.png
    4. In the web address bar, replace the number after /admin/products/ by pasting the copied Internal ID. On your keyboard press Enter.
      E-APP-PastingTheCopiedInternalID.png
    5. Compare the product's price in Lightspeed eCom with the price in Google Sheets to ensure the changes you made are reflected correctly.
      E-APP-ComparePricesAndInternalIDs.png
    6. Repeat steps 9.a. - 9.e. for a couple products until you're certain all your changes have been correctly imported.

Was this article helpful?

0 out of 2 found this helpful