Table of contents

  • Introduction
  • Setting up Dropbox
  • Prepare current inventory
  • Import data
  • Counting
  • Export Data
  • Update Magento
  • Stock valuation

Inventory count with Magento and cobby

Every year merchants face a task that’s fun for a few, but a lot of work for everyone. Ware talking about the inventory count at the end of the year.

In Germany, every merchant is obliged to count his inventory if he is also required to draw up a balance sheet, his total turnover is more than 500,000 € (incl. tax free sales) or his profit is more than 25,000 € during the fiscal year.

In addition to this statutory duty there are other good reasons to carry out an inventory count. Especially after turbulent periods such as Black Friday or Christmas an inventory count can help increase data accuracy again.

The most time consuming part is probably counting the items itself. To facilitate this work, there are many little helpers that can simplify this process. We would like to explain based on a real world example how cobby and a smartphone can help.

In our example we use the apps Inventory Scanner 2015, Dropbox and an iPhone. Once the apps are installed on the iPhone, we connect those two.

#1: Setting up Dropbox

First open Inventory Scanner 2015, open Settings (1) in the group Dropbox choose Link Dropbox. Allow access from Inventory Scanner to your Dropbox (2).

#2: Prepare current inventory

It’s time to prepare our current inventory. First we load all Products into a new workbook with cobby. To be able to scan the products we need the sku, a barcode and the name of the product. A good idea is to order these columns directly behind each other in the AllProducts sheet. Save this layout as a preset for later use.

Now we set a filter on quantity so that only products with a stock greater than 0 are displayed. Mark the three attributes copy and paste them into a blank sheet. Finally we add the correct column headers before we save the file as CSV into our Dropbox.

Path Dropbox/Apps/Inventory Scanner 2015/import

#3: Import Data

To load the current inventory we start the Inventory Scanner App on our iPhone. Navigate to Import (3) > Data Import (4) > Import from Dropbox (5). Now we select the previously saved CSV file (6). A data import dialog shows up, change the option Master Data only to inactive (7) and start the import.

#4: Count

All preparations have been made and we are ready to count our inventory. In the group Inventories we choose Open. You’ll see an entry named Import followed by the current date tap on it. A list showing the current inventory is displayed, now tap on the camera icon to begin the scan.

 

#5: Export Data

Once all stocks are taken we choose Export > Upload to Dropbox (8) and the count is directly  transferred to Excel (9).

#6: Update Magento

To update our stocks in Magento we first open the just exported list.

Path Dropbox/Apps/Inventory Scanner 2015/export

Now we start the Import mode of cobby. This is located in the extended Load products menu. To updates stocks we move the Quantity coulmn within the first attributset sheet beside the SKU column. All other attribute columns should be hidden. (By hiding these columns they are not transferred to Magento.) Now we copy the SKU and the counted stock from our inventory sheet and paste them into the first attributset sheet. As Website – Storeview we use the All Store Views value. Click Save products to update Magentos stocks.

#7: Stock valuation

In the final step the current inventory should be valuated for your accountant. A common valuation approach is to use the purchase price. To determine the current value of our goods we first Load products with cobby and add a new column valuation in the AllProducts sheet. In this column we add a formula that multiplies the Quantity with the purchase price. With the SUM formula we calculate the valuation of all our products. Now we hide the columns we don’t want to see and print the evaluation.

Please note! At least in Germany you have to keep a signed copy of your original counting list. This document must be kept for the next 10 years.