User Tools

Site Tools


Sidebar

Back to Main Website

Video Tutorials

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 data:

Transaction data

Description of data types that describe a transaction and imported into Streamline is given below.

Demand forecasting data

  • Date – the transaction date.
  • Quantity sold – the item amount that was sold in the transaction.
  • Item code – the item identifier is also known as an SKU.
  • Item description – an item description, used to better explain what a particular item code means.
  • Item category – the category such as a product group. The number of categories is unlimited - as soon as the category is selected, a new option appears as Item category 2, then Item category 3 and so on. The sequence of them 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. The sales history should be broken out into these categories.
  • Location (such as a store, warehouse, or region) is used to forecast consumption of each Item code in each location.
  • Location category is used to create a hierarchy of locations, for example, Region > City > Store. The location category hierarchy is imported in Streamline the same way as for the item category hierarchy.
  • Channel represents a channel via which an item is distributed or sold. For example, e-commerce, direct sales, distributors, or a single customer. This column should be used when you need to plan your demand by channels or customers.
  • Sales price/unit – the price of one item in the sale transaction, used to forecast revenue and perform ABC analysis.
  • Sales price/order – the price of the sale transaction. You should provide Streamline with either the Sales price/unit or Sales price/order when importing.
  • Order's profit – the profit obtaned from transaction. This allows Streamline to calculate total gross profit in each data aggregation period and also over the last twelve months.

Inventory data

  • On hand change – how much the On hand has changed. When this column comes into play the request must return not only the sales transactions but all the transactions impacting the On hand.
  • On hand – is the On hand remaining after the transaction. When both On hand and On hand change are given, the former is the Last on hand.
  • In transition - the amount of the item currently in transition.
  • Delivery date – expected delivery date of the item from your supplier.
  • Lead time – is defined in days and represents the interval of time between replenishment order placement and its receipt.
  • Lead time variance – the variance of the item lead time.
  • Order cycle represents how often the item is ordered from your supplier. It can be given in sales history periods (weeks or month), days, or 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 order cycle column has empty cells or is completely missing, the default value of 1 month is loaded from the inventory report settings.
  • Order rounding 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 represent optional constraints on how few or how many of this item can be purchased from your supplier.
  • Supplier code – the identifier of the supplier.
  • Supplier’s currency – the currency in which the purchase orders are placed.
  • Shelf life is given in the data aggregation periods (days, weeks or months) and is the desired shelf life time.
  • Item purchase price – the last purchase price of the item in the supplier's currency.
  • Inventory value/unit – the balance value of the item in stock.
  • Item’s info field – any additional characteristic of the item (e.g. purchasing preferences, comments, color, size).
Some inventory management systems start from zero On hand and store On hand change, not the remainder. Streamline can build On hand history based on this information but you must provide every single transaction, and that can be slow. If you import inventory planning data, some transactions are not sales and you should set their Quantity sold to zero.
If your In transition information has only one transaction per item (or item-location), you can optionally use such columns as the In transition and Delivery date of the transaction data to import it into Streamline. This situation is actually pretty specific and the fields are left for compatibility and highly deprecated. We strongly encourage you to import the In transition information separately from transaction data.

To get forecasts, you must provide Streamline with at least the Date, Item code, and the Quantity sold.

To get an accurate inventory purchase plans, you should provide at least the:

Of course, the more inventory information you provide, the more precise plans Streamline builds.

In transition information

In transition information describes the details of purchase/transfer orders being delivered now. This information should include:

  • Item code
  • Qty in transition
  • Delivery date – expected delivery date of the item.
  • PO sendout date – the date when the purchase/transfer order was placed. This date is used as a fallback when the Delivery date is unknown (optional).
  • Location (optional)
  • Lot cost – the cost of the PO/TO line (optional).
  • PO number – The system's PO/TO number (optional). This information is used only for display purposes in the Purchase order preview dialog.

We strongly recommend providing Streamline with one of the Delivery date or PO sendout date. If both dates are given, the Delivery date will be used.

If none of the Delivery date or PO send out date is given, Streamline implies that the order should be delivered at the beginning of the first forecasted period. Thus, if you use Incomplete period forecasting, the Qty in transition is added to the Last on hand and this result is used to generate the forecasts and inventory plans for the current period.
2018/11/08 12:35

Pending sales orders

Pending sales orders information describes the details of the sales orders that are open or backordered. It should include:

  • Item code
  • Location (optional)
  • 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.

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 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.

Data format

Transaction data

To import the transaction data from Excel or CSV file, the data should be formatted as shown in the table below.

The table must contain one transaction per row. Information about the transaction is imported from corresponding columns of the table.

In transition information, pending sales orders and bill of materials can be imported from an Excel file only. In this case, it must be on a separate spreadsheet. The sequences of the columns in spreadsheets and the spreadsheets' sequence in an Excel file are arbitrary.

In transition information

Pending sales orders

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.

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 Transaction spreadsheet connection dialog.
  4. Click OK.

Transaction Spreadsheet Connection Dialog

The Transaction spreadsheet 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 four 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 the 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.

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 in transition information

To get in transition information imported, do the following:

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

Configuring information on pending sales orders

To get pending sales orders imported, do the following:

  1. Go to the Pending sales orders 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/03/13 10:46 by admin