Manual on data exchange with Inventoro.com

This section contains information on how to exchange data with Inventoro.com:

1. Importing data
a) Sales file
b) Promotion file
2. Exporting data


I want to use Inventoro for:

Demand Forecasting only
or
Inventory Management
Prediction of sales or production using:
  • Sales Forecast
Complex Supply Chain Management using:
  • Sales Forecast
  • Reorder Levels
  • Optimal Ordering Quantities
  • Safety Stock
  • Products Segmentation

Download complete manual as PDF  


1. IMPORTING DATA

SALES FILE

Example

Please create sales file as CSV file. Here's an example how it should look like.

product_name; sale_date; sale_quantity; product_id; store_id; store_name; parent_store_lt; provider_lt; ...
D-035-14-1-1; 2010-05-01; -1; 1; 9; Central Warehouse; 3; 36;
D-035-14-1-1; 2010-05-01; -2; 1; 9; Central Warehouse; 3; 36;
...

Download Sales demo data here.
Column name Name Description Priority
1. product_name Product name If [product_id] is not filled in, then product name is used as an unique identifier. All characters are allowed. Maximum length is 256 characters. Required if [product_id] is empty.
2. sale_date The date of sales This date specifies the date of the customer’s requirement (date the customer wanted to buy the item), i.e. the date, when the item is supposed to be on stock, not the date when it is sent or delivered to the customer. Example: A customer came to buy certain item on 7th February. Item was not on stock, therefore the customer had to wait 14 days for replenishment and the item was issued on 21st February. The correct date for Inventoro is: 7th February. Required
3. sale_quantity The quantity of sales The quantity of sales in basic measurement unit. For a single product the quantity of sales remains always in the same basic measurement units. Inventoro uses the absolute value of quantity. Required
4. product_id Unique product identifier This indication helps to maintain information about products between conversions. This allows more efficient use of advanced prediction methods, which improve themselves through learning - like the neural network. Identifier must be unique. The most common form is a number or string without diacritics, whitespace or any other non-standard characters. If the column is not filled, than the name of product will be used as its unique identifier. Maximum length is 256 characters. Required if [product_name] is empty.
5. available_supply_quantity On-hand inventory Currently available stock in basic measuring units in the time of export. The unit used must be the same as for the sales quantity column. The value can be exported with suggestions of Inventoro in order to indicate the major problems. For a single item on one store the same value should be specified. The values are not summed. Very important
6. provider_lt Supplier delivery time Delivery time of the primary supplier of the item. Default value can be specified when uploading file. This value will be used for items, where delivery time is not filled. The system will suggest optimal stock level according to this delivery time with respect to the specified service level target. This value is expected in days. Very important.
7. provider_period How often you reorder. This value is used for the calculation of how much you need to order. It has meaning only for items supplied by the suppliers. This is used for calculation of the order quantities for replenishment. The default value can be specified when uploading file to be used for items where order period is not specified. If you want to send orders once a month, set this figure to 30, if once every 2 weeks, then set it to 14 etc. The system calculates the order quantity so the period between orders is close to the specified value of order period. The stock level is calculated with respect to the service level target. This value is expected in days. Important
8. provider_lt_std_dev Deviation of suppliers’ delivery time Standard deviation of suppliers’ delivery time. It is used to calculate the reoder level and safety stock to cover variability of the suppliers’ delivery times. This value is expected in days. Recommended
9. sale_price Selling price The Selling price is important for displaying in charts and for calculation of ABC segmentations. For ABC segmentations you can fill in just the columns Profit and Sales - then specifying the Selling price is not necessary, but the charts in Value will not be displayed. The Selling price specifies the price for one unit. In case you export the selling price in the time of sales, the system will use the weighted average for forecasts. We recommend using selling prices associated with particular sales. Important
10. stock_price Stock price Important for displaying in charts and for calculation of ABC segmentation. For ABC segmentations you can fill in just the columns Profit and Sales - then specifying the Stock price is not necessary, but the charts in Value will not be displayed. The Stock price specifies the price for one unit. In case you export the stock price in the time of sales, the system will use the weighted average for future stock levels. We recommend using stock prices associated with particular sales. Recommended
11. store_id Unique warehouse identifier This identifier helps keeping information about the product in stock conversions. This allows more efficient use of advanced forecasting methods, which improve through learning - like the neural network. Identifier must be unique. The most common form is a number or string without diacritics, whitespace or any other non-standard characters. If the column is not filled, than the name of product will be used as its unique identifier. Maximum length is 256 characters. This column, in combination with [product_id] serves as the primary identifier for the SKU and if it changes, the results of Inventoro may be deteriorated. Recommended
12. store_name Store name Main use of Store name is for displaying in reports. If [store_id] is not filled in, then Store name is used as a unique identifier. All characters are allowed. Maximum length is 256 characters. Recommended
13. parent_store_id Identifier of the parent store for redistribution The parent store will be used as a supplier for the product on the child store. Lead time for redistribution is defined in [parent_store_lt] and provider in [parent_store_period] or the default values can be set when uploading sales file. [parent_store_id] must exist as another record with same id in [store_id] column. Recommended if [store_id] or [store_name] is not empty and you use redistribution between stores.
14. parent_store_lt Delivery time from parent store in days It is used to calculate the Reorder stock level on child store. The default value can be specified when uploading file and will be used for items where delivery time is not filled. This value is expected in days. Recommended if [parent_store_id] is not empty.
15. parent_store_period How often you reorder. This value is used for the calculation of how much you need to order. It has meaning only for items supplied by the parent store. This is used for calculation of the Order quantity for replenishment from the parent store. The default value can be specified when uploading file to be used for items where period is not specified. If you want to replenish from parent store once a week, set this figure to 7, if three times a week, then set it to 2 etc. The system calculates the Order quantity so the period between orders to parent store is close to the specified period. The stock level is calculated with respect to the service level target. This value is expected in days. Recommended if [parent_store_id] is not empty.
16. category Category Categories are used for better orientation in Inventoro. All charts and tables for selected category can be displayed. Recommended
17. profit Profit over chosen period It is possible to export the data for ABC segmentation according to Profit (gross margin). If this value is not specified, then PW calculates profit from historical sales (provided the selling and stock prices are exported). The same value should be stated on all rows for one item on one store. The values are not summed over the rows. The sales value should be specified for the period suitable to your type of business – for FMCG 3 to 6 months is sufficient, for slower moving goods like spare parts the minimal sufficient period is 12 months. Recommended if [stock_price] or [sale_price] is empty.
18. revenue Revenue over chosen period It is possible to export the data for ABC segmentation according to Revenue (or turnover, sales). If this value is not specified, then PW calculates revenue from historical sales (provided the selling prices are exported). The same value should be stated on all rows for one item on one store. The values are not summed over the rows. The sales value should be specified for the period suitable to your type of business – for FMCG 3 to 6 months is sufficient, for slower moving goods like spare parts the minimal sufficient period is 12 months. Recommended if [sale_price] is empty.
19. sales_frequency Sales frequency Suitable for reducing the number of rows in CSV. For a single item, all the sales in one day (or other period) may be merged and therefore the number of rows can be reduced. Recommended if sales rows are grouped by date
20. provider_name Provider name Name or id of provider that will be shown in Inventoro.Each item product-store should have one defined provider only. Otherwise it will be replaced by the most frequent provider for the item given. Every item with no provider defined will get the default provider using default leadtime and period set when the computation was started. Important

PROMOTION FILE

Download Promotion demo data here.
Column name Name Description Priority
1. product_id Unique product identifier This column has the same properties as the same column in Sales File. The value must agree with the data from Sales File. Required
2. date_from Date From Date the promotion action is launched. This is the date when customers are allowed to use the promotion benefits (buy for discounted price or validity of a discount catalogue). Required
3. date_to Date to Date when the promotion action ends. This is the last day when customers are allowed to use the promotion benefits (last day to buy for discounted price or last day of validity of a discount catalogue). Required
4. store_id Unique store identifier This column has the same properties as the same column in Sales File. The value must agree with the data from Sales File. This column is not mandatory. If it not specified, than the operation is applied to all the stores. Required if using store_id or store_name columns in sales data
5. power Power of promotion Power of action is an appropriate metric used for calculating the effect of promotion action. For example, for discounts it may be the percentage of discount (e.g. 15% or 30%) , for media campaign it may be campaign cost, for printed catalogues or leaflets the number of issues/copies can be used etc. The system is able to work with these metrics and and forecast the effects of future promotions of the same type. For each promotion type the same metric and measuring units should be used. Recommended
6. promo_type Promotion type Serves for dividing promotion actions by type. For one item, each type of action may have different effect measured by different metric and units. For instance, for a single item, a “Discount” and “Leaflet” promotions can be defined with different effects measured by different metrics. The system then calculates the effects of different promotions separately. Important

2. EXPORTING DATA

The outputs will be provided in a file in the xls format (Microsoft Excel) and csv format (file separated by semicolons). This file will contain the following outputs:

Data Description
1. Sale forecast For each SKU, the forecast of future sales.
2. Reorder level For each SKU, the forecast of proposed level when reorder.
3. Order quantity For each SKU, the forecast of proposed quantity to order.
4. Safety stocks For each SKU, the forecast of proposed safety stocks.
5. Current stocks Instant state of available stocks in store.

Data can also be exported in the value if prices are imported.

The sale forecast, order levels, order quantities and safety stocks can be exported up to 24 months in the future and can be broken up into days, weeks or months.

Download demo data export here.
Column name Name Description Exportation Defined for
1. product_id Unique product identifier from import data Unique product identifier from the import data. Always exported item
2. inventoro_product_id Inventoro Product Id Unique product identifier Inventoro uses internally. It has always a numeric format. Exported optionally item
3. product_name Product name from import data Product name as obtained in import data. Always exported item
4. store_id Store id from import data Store id as obtained in import data. Always exported item
5. inventoro_store_id Inventoro Store Id Unique store identifier Inventoro uses internally. It has always a numeric format. Exported optionally item
6. store_name Store name from import data Store name as obtained in import data. Always exported item
7. date_from From which date the row is valid First day of data row validity. In case of monthly forecast break-up this date is first day of month, in case of weekly forecast break-up this date is a Monday and in case of daily forecast break-up this date can be any day. Always exported row
8. date_to To which date the row is valid Last day of data row validity. In case of monthly forecast break-up this date is last day of month, in case of weekly forecast break-up this date is a Sunday and in case of daily forecast break-up this date can be any day. Always exported row
9. category_id Category id from import data Category id as given in import data. Exported optionally item
10. inventoro_category_id Inventoro Category Id Unique category identifier Inventoro uses internally. It has always a numeric format. Exported optionally item
11. abc_margin ABC Margin ABC value by margin computed for the item. Exported optionally item
12. abc_margin_revenue_sales ABC Margin Revenue Sales Frequency Aggregated ABC value by margin, revenue, sales frequency computed for the item. This ABC value is used for the final ABC value determination for each item. Exported optionally item
13. abc_margin_sales_frequency ABC Margin Sales Frequency Aggregated ABC value by margin and sales frequency computed for the item. Exported optionally item
14. abc_revenue ABC Revenue ABC value by revenue for the item. Exported optionally item
15. abc_sales_frequency ABC Sales Frequency ABC value by sales frequency for the item. Exported optionally item
16. abc_sales_quantity ABC Sales quantity ABC value by sales quantity for the item. Exported optionally item
17. category_id Category name from import data Category name as given in import data. Always exported item
18. forecast_accuracy Forecast accuracy Forecast accuracy for current month for the item. Exported optionally item
19. forecast_quantity Forecast quantity Forecast quantity Inventoro assumes to be sold between date from and date to. Exported optionally row
20. forecast_value Forecast value Value of forecast quantity Inventoro assumes to be sold between date from and date to. Last known sale price is used. Exported optionally row
21. order_quantity Order quantity Quantity to order once the reorder level is reached. This level is always defined at monthly basis Exported optionally item and month
22. order_quantity_value Order quantity in value Quantity to order expressed in value. Last known stock price is used. Exported optionally item and month
23. reorder_level_quantity Reorder level quantity Reorder level expressed in quantity. At this stock level a new order must be made. Exported optionally item and month
24. reorder_level_value Reorder level value Reorder level expressed in value. Last known stock price is used. Exported optionally item and month
25. on_hand_quantity On hand quantity from import data Available supply as provided in import data. Exported optionally item
26. on_hand_value On hand quantity from import data in value Available supply as provided in import data. Last known stock price is used. Exported optionally item
27. safetystock_quantity Safety stock quantity Computed safety stock level expressed in quantity. Exported optionally item and month
28. safetystock_value Safety stock value Computed safety stock level expressed in value. Last known stock price is used. Exported optionally item and month
Note: The columns order may change depending on the columns selected to export.