In this page you can find a description of the data import file format with all the information we need to provide our demand forecasting and inventory optimization service. Formats available for your files are the following:
- 1. CSV: comma-separated values file.
- 2. Excel (XLSX): Microsoft Excel file with XLSX extension.
- 3. Excel (XLS): Microsoft Excel file with XLS extension.
The Catalog file contains some information about your products. Here’s the content of a catalog with three products: pants_0 and pants_1 belong both to category Clothes, while belts_0 belongs to category Accessories.
|Category ||SKU ||Name ||Publishing Date ||End of Life Date ||Location ||Stock on Hand ||Lead Time ||Service Level ||Purchase Price ||Selling Price ||Coverage ||MOQ ||Packing Unit ||Supplier |
|Clothes ||pants_0 ||Winter pants ||2018/12/18 ||2020/12/18 ||A ||50 ||15 ||0.95 ||20 ||27 ||30 ||50 ||50 ||A&B |
|Clothes ||pants_1 ||Summer pants ||2018/12/20 ||2020/12/18 ||B ||40 ||10 ||0.90 ||25 ||30 ||20 ||10 ||5 ||C&D |
|Accessories ||belts_0 ||Leather belts ||2018/12/01 || ||C ||300 ||10 ||0.97 ||35 ||42 ||45 ||1 ||1 ||X&Co |
Two different categories of informations can be provided.
- 1. Category: the category a product belongs to. If not provided, the category Default will be created.
- 2. SKU: a unique identifier for the product, it can be a Stock Keeping Unit or a user-defined value.
- 3. Name: a human-readable name for the product.
- 4. Publishing Date: when the product was published (or is going to be published).
- 5. End of Life Date: when the product was considered as discontinued (or is going to be considered as discontinued).
- 1. Location: the location/warehouse where the product is stored. If not provided, a default location will be created.
- 2. Stock on Hand: the current product stock level for a location.
- 3. Lead Time: required time (in days) for the replenishment of the product.
- 4. Service Level: the probability to meet demand during the reorder cycle, in which a stockout may possibly occur. If not provided, the default value is set to 0.95.
- 5. Purchase Price: the cost at which the product is bought by the user.
- 6. Selling Price: the cost at which the product is put up for sale.
- 7. Coverage: the number of days the suggested reorder quantity should cover once the stock arrives.
- 8. MOQ: minimum order quantity, the smallest amount of the product required by the supplier for accepting a purchase order (default 1).
- 9. Packing Unit: the number of products bundled for shipment (default 1).
- 10. Supplier: SKU supplier’s name.
Note that the inventory informations are all optionals. For the computation of the purchase order suggestions we strongly suggest to provide all the information about your inventory. Some of them, for instance the lead time, dramatically affects the timing of your replenishment events.
Order lines file
Order lines file contains all your recorded order lines: every line represents a sold product quantity referred to an order. More than one product can belong to the same order. In this example 10 pants_0 and 27 pants_1 are sold in region ASIA and belong to the same order 00001, while 18 pants_0 are sold in region US with another order.
|Order ID ||SKU ||Date ||Quantity ||Amount ||Region ||Location |
|ORDER_174312 ||pants_0 ||2018/12/22 ||10 ||270.00 ||ASIA ||A |
|ORDER_463462 ||pants_1 ||2018/12/22 ||27 ||810.00 ||ASIA ||B |
|ORDER_844213 ||pants_0 ||2018/12/24 ||18 ||486.00 ||US ||C |
- 1. Order ID: the identifier of the order line. Useful to track each order line.
- 2. SKU: the identifier for the product.
- 3. Date: the date in which the order was emitted.
- 4. Quantity: the sold product quantity for the order.
- 5. Amount: the amount of the order line.
- 6. Region: the region/channel/customer which issues the order.
- 7. Location: the location which serves the region/channel/customer.
Advanced Usage: Promotions file
Promotions file contains all your promotions: every line represents a promotion identified by its start date and end date. You can define promotions at each aggregation level (whole catalog, category, sku, region). Here are some examples:
|Name ||Category ||SKU ||Region ||Start Date ||End Date ||Discount ||Units |
|Black Friday || ||pants_0 ||US ||2017/11/01 ||2017/11/15 ||30 ||1000 |
|Christmas 2017 || || || ||2017/12/15 ||2017/12/31 ||40 ||5000 |
|Chinese New Year || || ||ASIA ||2018/02/01 ||2018/02/18 ||20 ||3000 |
the first row contains a Black Friday promotion which applies a 30% discount to 1000 units of the SKU pants_0 over the region US. It starts on 2017/11/01 and terminates on 2017/11/15. If you want to define a promotion for the whole catalog you have to leave blank the category, SKU and region fields (as you can see in the second row). The third row represents a promotion applied only to the region ASIA.
- 1. Name (optional): the promotion name.
- 2. Category (optional): the category involved in the promotion.
- 3. SKU (optional): the SKU involved in the promotion.
- 4. Region (optional): the region/customer involved in the promotion.
- 5. Start Date: the promotion start date.
- 6. End Date: the promotion end date.
- 7. Discount: the percentage of discount related to the promotion.
- 8. Units (optional): the number of discounted units.
Advanced Usage: Purchase Orders file
Purchase Orders file contains all of your emitted purchase orders: every line represents a purchase order identified by an unique ID, a SKU, a location, the issue date, estimated time arrival (eta) and an ordered quantity. Here are some examples:
|Item ID ||SKU ||Location ||Issue Date ||ETA ||Quantity |
|ITEM_0001 ||pants_0 ||A ||2018/02/01 ||2018/03/01 ||50 |
|ITEM_0002 ||pants_0 ||C ||2018/02/01 ||2018/03/15 ||150 |
|ITEM_0003 ||pants_1 ||B ||2018/01/10 ||2018/03/25 ||200 |
the first row contains a purchase order with 50 units of the SKU pants_0 for the location A. It was emitted on 2018/02/01 and it has an eta 2018/03/01.
- 1. Item ID (optional): a unique identifier of the purchase order item. If not given, it will be set as a combination of sku, location and issue date.
- 2. SKU: the sku involved in the purchase order.
- 3. Location (optional): If you have two or more location, it represents the location filled by the purchase order. Otherwise you can skip this field.
- 4. Issue Date (optional): the purchase order issue date.
- 5. ETA: the estimated arrival time of the order.
- 6. Quantity: the order quantity.
Advanced Usage: Anomalies file
Anomalies file contains all stockouts and exceptions that occured in the history and that may affect the forecasting analysis and inventory optimization.
|SKU ||Region ||Start Date ||End Date ||Type ||Quantity |
|pants_0 ||US ||2018/10/01 ||2018/10/09 ||Stockout || |
|pants_1 ||ASIA ||2018/05/14 ||2018/05/17 ||Stockout ||10 |
|pants_2 ||US ||2018/09/01 ||2018/09/30 ||Stockout || |
|pants_2 ||US ||2018/09/27 ||2018/09/30 ||Exception || |
|pants_3 ||US ||2018/12/05 ||2018/12/11 ||Exception ||+20 |
|pants_3 ||ASIA ||2018/06/11 ||2018/06/17 ||Exception ||-15 |
The first row contains a stockout for SKU pants_0 registered on region US from 2018/10/01 to 2018/10/09 and there is no info about the lost sales in terms of units.
- 1. SKU: the sku involved in the anomaly.
- 2. Region: the region involved in the anomaly.
- 3. Start Date: the anomaly start date.
- 4. End Date: the anomaly end date.
- 4. Type: the anomaly type. Could be either Stockout and Exception.
- 5. Quantity (optional): the quantification of the anomaly. If Stockout, is the number of lost sales. If Exception, the quantity could be positive (an unexpected sales) or negative (an unexpected return).
Download our sample data. You can use it as a template and to discover every feature of the intuendi.com’s forecasting and inventory optimization app. CSV XLSX