User Tools

Site Tools


Sidebar

Back to Main Website

Video Tutorials

Documentation for Streamline 4.x

User Guide

1. General Information

2. Starting Up

3. Connecting data

4. Demand and Sales Forecasting

5. Inventory Planning

6. Reference

order-list-connection

3.3. Transactional spreadsheets

Unlike the Aggregated spreadsheet connection, Transaction spreadsheet connection is designed to import sales and other data in a form of a set of transactions. Aggregation of the transactions is performed automatically while the data is being imported. There are several data aggregation periods which you can select from before the import has been started. These are:

  • Days
  • Weeks
  • Months

Thus, if you keep sales history and inventory information in a database, ERP, accounting or inventory management system, and have an ability to make an extract of sales transactions as Excel or CSV file, you can use the Transaction spreadsheet connection to create a new Streamline project.

Watch a video tutorial (3:20)

 

Data that can be imported

Transaction spreadheet connection imports the following pieces of data:

We strongly recommend that you provide Streamline with the In-transition information and Information on pending sales orders, despite that it is optional. It significantly improves inventory replenishment/purchase plans generated by Streamline.

Transactional data

Each sale or inventory movement is typically represented by a transaction in the ERP database. A transaction indicates what, where, when and how much was sold (or changed the inventory level). In this section, we describe the required and optional transactional data types which you should provide for:

  • demand planning;
  • revenue forecasting;
  • inventory planning.

Demand planning

  • Date – the transaction date.
  • Quantity sold – the item amount that was sold in the transaction.
  • Item code – the item identifier is also known as SKU.
  • Location (optional), such as a store, warehouse, or region, is used to forecast consumption of each Item code in each location.
  • Channel (optional) represents a channel via which an item is distributed or sold. For example, e-commerce, direct sales, distributors, or a single customer. This data type should be used if you need to plan your demand by channels or customers.

Revenue forecasting

To forecast revenue, you should additionally (to the demand planning data types) provide one of:

  • Sales price/unit – the price of one item in the sale transaction. It is also used to perform the ABC analysis.
  • Transaction revenue – the amount of the transaction (Quantity sold * Sales price/unit).

Inventory planning

To generate purchase/replenishment plans, identify potential stockouts and overstocks, and create purchase recommendations, Streamline needs you to provide item on-hand information.

There are three ways to import on-hand information (see table below).

Variant Data type Description Piece of data
1 Last on hand The item on-hand at the time of import. Item information
2 On hand The on-hand remaining after the transaction. Transactional data
3 On hand change How much on-hand quantity has changed due to the transaction. This data type must return not only sales transactions but all the transactions impacting on-hand.
Last on hand The item on-hand at the time of import. Item information

The second and third variants import on-hand history into Streamline that allows:

The On hand and On hand change should extend the demand planning transactional data.

If you import on-hand using the 3rd way, you should set the Quantity sold to zero for the transactions that are not sales.

Inventory KPIs

  • Transaction profit – the profit obtained from the transaction. This allows Streamline to calculate total gross profit for each aggregation period and also over the last 12 months.

Item information

The primary goal of the Item information piece is to provide Streamline with the on-hand quantity for every planning item. Thus, it must include a planning item identifier. If you do not use locations, the identifier is the Item code, otherwise, it is a pair Item code, Location.

In addition, Item information enables you to import data required to optimize inventory in situations such as:

Demand planning

Streamline allows you to plan your demand at a category level. It means that you are able to:

for a particular level of the category tree. To be able to do this, you should provide Streamline with the following data types:

  • Item category (optional) – an item category such as a product group. The number of categories is unlimited.
  • Location category (optional) is used to create a hierarchy of locations, for example, Region > City > Store. The number of categories is unlimited. The location category hierarchy is imported in Streamline the same way as for the item category hierarchy.

Below, we indicate descriptive information that can be imported additionally.

  • Item description (optional) – an item description, used to better explain what a particular item code means.
  • Info field (optional) – any additional characteristic of the item (e.g. purchasing preferences, comments, color, size). You can import as many Info fields as you need.

Inventory planning

To plan inventory you should provide data types listed below.

  • Last on hand – the item on-hand at the time of import.
  • Lead time – defined in days and represents the interval of time between replenishment order placement and its receipt. Should be given in days.
  • Order cycle represents how often the item is ordered from your supplier. It can be given in the data aggregation periods (days, weeks or month), or the Lead times. It must be an integer. For example, if inventory is replenished semi-monthly, then enter number 2 and use weekly sales data.
If Lead time or Order cycle data have gaps or are not given at all, Streamline uses default values: 30 days for the Lead time and 1 data aggregation period for the Order cycle. You can also reset these in Streamline after the project has been created.
  • Qty to ship (optional) – the amount of the item that is on all open sales orders or backorders.
  • Qty to receive (optional) – the amount of the item that is on all open purchase/transfer orders.
  • Delivery date (optional) – expected delivery date of the item from your supplier.
If your in-transition information can be only represented by one transaction per item (or item-location), you can use such data types as Qty to receive and Delivery date of the Item information to import it into Streamline. Otherwise, we strongly recommend importing your in-transition information separately from the Item information.
  • Lead time variance (optional) –- the variance of the item lead time.
  • Rounding (optional) allows Streamline to take into account how many items are included in a carton, allowing the application to suggest exactly the right amount of the item to order.
  • Min lot and Max lot (optional) represent optional constraints on how few or how many of this item can be purchased from your supplier.
  • Supplier code (optional) –- the identifier of the supplier.
  • Supplier's item code (optional) – item code in supplier's stock-list that corresponds to your Item code. It allows Streamline to create purchase orders in the supplier's item codes.
  • Supplier’s currency (optional) – the currency in which purchase orders are placed.
  • Supplier’s min. qty/weight/volume/cost (optional) - the minimal quantity/weight/volume/cost of ordered items in total necessary in order to supplier accept the purchase order.
  • Weight/unit (optional) – the weight of one unit of an item.
  • Volume/unit (optional) – the volume of one unit of an item.
If Min lot is greater than Max lot, Streamline neglects Min lot and uses Max lot in the optimization.
Order constraints such as Supplier’s min. weight or Supplier’s min. volume should be given to Streamline along with the Weight/unit and Volume/unit relatively.
Constraints on a purchase order are incompatible with the product shelf life limitation. It means that Streamline's purchase order recommendations come from the given purchase order constraints, not the shelf life limitation if both are given.
2019/04/03 09:29 · admin
  • Shelf life, periods or Shelf life, days (optional) – the desired time the item should be in stock. There are two ways to import Shelf life depending on the unit of measure.
  • Purchase price/unit – the current purchase price for one unit of the item in the supplier's currency.
  • Inventory value/unit –- the balance value for one unit of the item in stock.
  • # of periods for safety stock – the number of future periods which demand is used as the safety stock. It should be given in the data aggregation periods.
  • Display qty - the minimum number of units a shelf to display. This parameter typically arises in the retail business. You can control how this amount is used when the final safety stock is calculated.

Orders-to-receive information

Orders-to-receive information describes the details of purchase/transfer orders are being delivered now. This information should include:

  • Item code.
  • Qty to receive – the item quantity that is being delivered currently.
  • Delivery date – expected delivery date of the item.
  • Sendout date (optional) – the date when the purchase/transfer order was placed. This date is used as a fallback when the Delivery date is unknown.
  • Location (optional).
  • Lot cost (optional) – the cost of the PO/TO line.
  • Order number (optional) – The system's PO/TO number. This information is used only for display purposes in the Purchase order preview dialog.
2018/11/08 12:35
The Sendout date should be given for open POs only, not TOs.
A purchase order Sendout date allows Streamline to calculate Next order by date for the first time as you import your data and forecast the project.

It is strongly recommended to provide Streamline with one of the Delivery date or Sendout date. The former is preferable.

If neither the Delivery date or Send out date is given, Streamline will determine that the order should be delivered at the beginning of the first forecasted period. Thus, if you use incomplete period forecasting, the Qty to receive is added to the Last on hand and this result is used to generate the forecasts and inventory plans for the current period.
2019/04/03 14:36 · admin

Orders-to-ship information

Orders-to-ship information describes the details of the sales orders that are open or backordered. It should include:

  • Item code.
  • Qty to ship.
  • Date – the date when the item should be shipped to the customer. In the case of backorders, this can be some promised date.
  • Location (optional).

Bill of Materials

Bill of materials information describes the components of finished products. Components can be considered as sub-assemblies (at the intermediate levels of the production process) or as raw materials (at the lowest level of the process). You can import an unlimited number of assembly levels.

Streamline also supports material requirements planning for batches. I.e. when a BOM describes ingredients that are used to produce a number of finished products.

To get a material requirements plan, you should provide Streamline with the following information:

  • Finished good's code – the code of a finished product or a sub-assemblied item.
  • Material's code – the material's or component's code.
  • Material qty/batch – the quantity of material or component that is required to produce a batch of the finished good.
  • Finished batch size – the size of a finished good batch, i.e. the number of finished goods to be produced. This data is optional. If it is not given, the batch size is equal to 1 by default.

Information on future promotions

Streamline allows you to account for given future promotions of a product automatically when it generates the forecasting model.

To do this, you should provide the data types shown below.

  • Item code.
  • Location (optional).
  • Channel (optional).
  • Start date – the date the promotion starts.
  • End date – the date the promotion ends.
  • Discount – the promotion discount for the item code given as a fractional number. For example, if the discount is 30%, you should provide 0.3.

In order to Streamline be able to account for the future promotional discounts for a product, you must also provide the history of the past promo discounts for this product in the same format as for the future discounts (see an example).

Data format

Streamline allows importing the data through an Excel or CSV file. Since Excel files are multi-sheet documents, they allow importing all the data types we described earlier.

CSV files

A CSV-file should contain data types from both Transactional data and Item information (see the table below).

Transactional data Item information
Required
  • Date
  • Quantity sold
  • Item code
  • On hand change (for inventory planning)
  • Lead time (for inventory planning)
  • Order cycle (for inventory planning)
  • Last on hand (should be matched to the On hand option when importing)
Optional
  • Location
  • Channel
  • Sales price/unit
  • Transaction revenue
  • Transaction profit
  • Item category
  • Location category
  • Item description
  • Qty to receive
  • Delivery date
  • Qty to ship
  • Lead time variance
  • Rounding
  • Min lot
  • Max lot
  • Supplier code
  • Supplier's item code
  • Shelf life, periods
  • Purchase price/unit
  • Inventory value/unit
  • Info field

As you see, Orders-to-receive information is imported here through the Qty to receive and Delivery date data types, and Orders-to-ship information, through the Qty to ship.

An example of the format is presented in the figure below.

You can download this example file.

Excel files

Excel files allow importing all the data types we have described. Each of the data pieces should be on a separate sheet of the Excel file. The first sheet must contain Transactional data. Below, we show the format for each piece of the data in an Excel file. The sequences of the data type columns and spreadsheets in the file can be arbitrary.

Transactional data

The table must contain one transaction per row.

Item info

Rows of this table must be unique by the planning item identifier. If locations are used, the identifier is represented by (Item code, Location) pair, otherwise, it is Item code.
Data of this table is used as a filter for imported planning items, i.e. only those planning items that are in this table will be imported, regardless of any other planning items that may appear in the other pieces of data that are going to be imported.

Orders to receive

Orders to ship

Bill of materials

Date formats

If you import data from Excel files (XLS, XLSX), Streamline understands any date that is formatted by Excel standards. For text files like CSV, the date should be in one of the following formats:

  • dd.mm.yy
  • m/d/yy
  • yyyy/mm/dd
  • yyyy-mm-dd
  • yyyy_mm_dd
  • yyyymmdd

Streamline also recognizes these formats in Excel files.

2017/04/12 13:58

You can download an example of the data formatted in Excel file.

Special case

Streamline allows you to avoid the Item information sheet and import these data types through the Transactional data spreadsheet (see figure below). In this case, the table will contain a lot of duplicated data.

You can download an example of the data formatted in Excel file with merged Transactional data and Item information.

Connecting your data

To create a new project based on transactional data, do the following:

  1. Go to the toolbar New > Spreadsheet connection > Transaction data.
  2. Select your data file in the Import window and click the Open button.
  3. Configure the Transactional data connection dialog.
  4. Click OK.

Transactional Data Connection Dialog

The Transactional data connection dialog is divided into two parts. The part on the left contains dialog settings, and the right part – a preview of the data that will be imported.

The preview also shows how Streamline understands the data: the dates are green, the text is blue, and the numbers are black. Thus, if dates have the incorrect format, they will be shown in blue or black color in the preview.

The preview has five tabs:

Dialog settings

CSV delimiter – allows setting the data delimiter used to parse the CSV file. Streamline always tries to determine the delimiter automatically, and in most cases, it works just fine. If Streamline cannot parse the data correctly, you can choose one of the predefined delimiters (comma, semicolon, space, or tab) or enter a custom delimiter manually. If you are importing an Excel file, the option is disabled.

Number of header rows parameter allows setting how many rows in your data table takes the table header. The default value is 1. To ensure that the data table header is loaded properly, take a look at the header of the preview table. If it contains the labels of your header – everything is Ok, if not – adjust the parameter so that the labels will get into the header of the table preview. If the data table has no header, set the control to zero, then the header displays the x1, x2,… labels.

Number of header rows parameter can also be used to skip a given number of transactions from the beginning of the table, thus importing a slice of your data. Assume that you have a transactional report of 5 years length ranging from 2014 to 2018 year. However, Streamline needs you only to provide the last 36 months (2016-2018). In this case, set the Number of header rows to the table row number before the first occurrence of the transaction having 2016 year.

Group timestamps by – used to aggregate the transactions history in the given periods. The options are the Day, Week, and Month. For example, if you want to get monthly forecasts and plans, use the Month option.

Starting from parameter works together with the Group timestamps by and sets the starting day of the Group timestamps by period. For instance, if you need to aggregate your data by month, you can set up Streamline to perform grouping from a given day of the month. The default option is the first day of the month. In the case when you group the data by week and your week doesn't start from Monday, you can choose the proper day of the week in the Starting from drop-down.

Compose date from – used when the date in the data is represented by two columns. For example, year in one column, and month number in another. There are two options: Year + month and Year + week (ISO 8601). To compose date properly Streamline requires the two columns to be one after another, and the first one must contain year. Then, choose the appropriate option in the control and set the meaning of the first column to Date. If the preview depicts the Date column in green, dates are read by Streamline properly.

Unite locations into a single inventory combines all locations into a single virtual location. The locations' on-hand is summed. This option is handy if your warehouses are located next to each other, so they can be treated and planned as a single aggregated warehouse.

Setting the meaning of the columns

The table of the preview has a special row with a drop-down list in each column. The list displays the data that Streamline understands and can import in the selected tab. The description of the lists’ options is given in the Data that can be imported section. Thus, to import your data properly, match the meaning of your data column to the most appropriate option in the drop-down list.

Configuring item information

To get item information imported, do the following:

  1. Go to the Item info tab.
  2. Select the Excel sheet that contains the information using the Sheet control.
  3. Set the meaning of the columns.

Impoting categories

As soon as the Item category is selected from the drop-down list for a specific column, the option changes to Item category 2 for subsequent columns, then to Item category 3 and so on. This sequence forms a hierarchy of categories in Streamline. In other words, the Item category 2 is a subcategory of Item category, the Item category 3 is a subcategory of Item category 2, and so on. This behavior applies to importing location categories too.

2017/11/10 14:18

Configuring Orders-to-receive Information

To get in-transition information imported, do the following:

  1. Go to the Orders to receive tab.
  2. Select the Excel sheet that contains the information using the Sheet control.
  3. Set the meaning of the columns.

Configuring Orders-to-ship Information

To get pending sales orders imported, do the following:

  1. Go to the Orders to ship tab.
  2. Select the Excel sheet that contains the information using the Sheet control.
  3. Set the meaning of the columns.

Configuring bill of materials

To get the bill of materials imported, do the following:

  1. Go to the Bill of materials tab.
  2. Select the Excel sheet that contains the information using the Sheet control.
  3. Set the meaning of the columns.


Next: Databases

Download PDF

order-list-connection.txt · Last modified: 2019/05/17 18:51 by admin