Is your Shopify store growing?

Do you want to have better control over your inventory?

Do you want to forecast how much inventory you’ll need?

All of the above? Let us help.

We’ve created a free excel template you can use to see:

  • sales velocity
  • how long your current stock will last
  • how much you should order to have stock for the next 30 days

What to do:

  1. Export a CSV file of your products from Shopify. Read more from Shopify on how to export products.
  2. Export a CSV file of your orders from Shopify. You can filter to include only sales for a certain time period. Here is more information from Shopify.
  3. Open the Forecasting for Shopify by Inventory Planner spreadsheet in Excel.
  4. Click ‘Enable Macros’ when opening.
  1. If you don’t see this pop-up when opening the spreadsheet, learn how to enable macros here.
  2. Copy all information from your Shopify product export and paste it in the “shopify_products_export’ worksheet.
  1. Copy all information from your Shopify order export and paste it in the “shopify_orders_export” worksheet.
  1. Click on the ‘forecast’ worksheet and then click the Forecast button found in the upper left corner of the document.

Using the Forecast:

Your forecast and related product information should now show on the ‘Forecast’ worksheet.

Here is a look at what the columns mean and how formulas were calculated:

Column B: Product

The product name is pulled from the name used in Shopify.

Column C: SKU

The SKU name is the same SKU you use in Shopify. If you have variants for a product (such as various colors or styles), the SKU for the variants is used.

Column D: Sales

Sales are the number of units sold during the entire time period under consideration. The units sold for each order listed are added together to create the number shown.

Column E: First Sales Date

The date of the first sales of each product or variant is shown. Note that this will be the first sale date of the data copied from Shopify. If there were sales prior to the period exported, those sales are not considered when analyzing this what is copied into this excel document.

Column F: Selling for Days

The number of days from the first sales date until the current date.

Column G: Daily Sales Velocity

The sales velocity is the average number of units sold each day. The formula here is:

Daily Sales Velocity = Sales / Selling for Days

Column H: Stock

Stock is the current inventory level, or how many units of that product are in stock. Remember that if your product has variants (such as sizes or colors), each variant is listed individually in this spreadsheet and stock is listed for each variant.

Column I: Sell Out in Days

Taking into consideration the sales velocity and current stock level, ‘Sell Out in Days’ shows how many days until a product will be out of stock. Remember that this is based on an average rate of sales so the number of days can vary.

Column J: Replenish for the next 30 days

The replenishment recommendation is the  sales velocity multiplied by 30, then the current stock level is subtracted from that number. The formula is:

Replenishment recommendation = (Daily Sales Velocity * 30) - Stock

If the replenishment recommendation is a negative number, that means you have more stock on hand than you need for the next 30 days of sales.

Jill
September 12, 2018