Book Creator

Remove this page from your book

Manage book (0 page(s))

Help

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

Transactional spreadsheet connection is able to import the same data types as Database connection. To learn more about them, address to the Data types article.

Data Format

Streamline allows importing the data through an Excel or CSV file. Since Excel files are multi-sheet documents, they allow importing almost all the data types. Currently only the Substitutions information can't be imported using Excel files. CSV-files are able to import a significantly narrower range of the data types than Excel files.

CSV Files

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

	Demand Planning	Inventory Planning
Transactional data	Date Quantity sold Item code Location Channel Sales price/unit or Transaction revenue Transaction profit	On hand (as remaning) or On hand change
Item information	Item categories Location categories Item description Location description Info field Sales price/unit	Last on hand Lead time Lead time variance Order cycle Qty to receive Delivery date Rounding Min lot Max lot Supplier code Supplier's currency 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.

In this case, you can import the on-hand level using three ways. The data types for each of them are:

1. On hand change, and the Last on hand that should be matched to the On hand option when importing.

2. Last on hand by matching to the On hand meaning when importing. Use this way if you can only provide the on-hand at the time of import.

3. On hand (as remaining).

Unlike the second method, the first and third methods import on-hand history.

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

You can download this example file.

Excel Files

Excel files allow importing almost all of the data types. Each of the data pieces should be on a separate sheet of the Excel document. The first sheet must contain Transactional data. Below, we show the format for each piece of 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 Information

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.

Pay special attention to importing the following replenishment parameters:

Lead time
Order cycle
Min lot
Max lot
Rounding
Lead time variance

These characteristics depend on the echelon the planning item resides at. If a planning item is sourced from a DC, these parameters define the replenishment process from that DC. In this case:

Lead time – the average time interval to deliver an item from DC to the location at the lower echelon.
Lead time variance – the variance of the Lead time defined above.
Order cycle – how often you replenish from DC.
Min lot, Max lot, and Rounding – the constraints that are put on an item when you order it from DC.

If a planning item is sourced from a supplier:

Lead time – the average time interval to deliver an item by your supplier.
Lead time variance – the variance of the supplier Lead time.
Order cycle – how often you send purchase orders to the supplier.
Min lot, Max lot, and Rounding – the constraints that are put on an item when you order it from the supplier.

If your supply chain is described by a two-echelon model and there is a shelf life limitation on an item at the lower echelon, you should also provide a shelf life for the DC that supplies that item. DC's shelf life must then safisfy the condition:

DC shelf life ≥ Lead time + Shelf life,

where:

Lead time – the time to deliver the item from the DC to the location at the lower echelon.
Shelf life – the shelf life limitation at the lower echelon.

If the Supplier code is not given, Streamline attaches those items to an empty supplier code.

2019/09/05 08:15 · admin

Orders to Receive

Orders to Ship

Bill of Materials

Promotions Information

Batches Information

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
mm/dd/yyyy
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, however, the table will contain a lot of duplicated data. Thus, we encourage you to use two sheets – one for Transactional data and another one for Item information.

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:

Go to the toolbar New > Spreadsheet connection > Transaction data.
Select your data file in the Import window and click the Open button.
Configure the Transactional data connection dialog.
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 the following tabs:

Transactions displays the first sheet of the Excel file automatically. This tab is designed to import Transactional data.
Item Info is used to import Item information.
Orders to receive is designed to import Orders-to-receive information.
Orders to ship is intended to import Orders-to-ship information.
Bill of materials is purposed to import Bill of materials.
Promotions is used to import Information on promotions.
Batches is designed to import Batches information.

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

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

Importing categories

Configuring Orders-to-receive Information

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

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

Configuring Orders-to-ship Information

To get pending sales orders imported, do the following:

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

Configuring Bill of Materials

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

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

Configuring Information on Promotions

To get information on your promotions imported, do the following:

Go to the Promotions tab.
Select the Excel sheet containing promotions using the Sheet control.
Set the meaning of the columns.

Configuring Batches Information

To import batches information, do the following:

Go to the Batches tab.
Select the Excel sheet containing batches information using the Sheet control.
Set the meaning of the columns.

Next: Databases

Download PDF

GMDH Streamline Docs

Sidebar

User Guide

Table of Contents

4.3. Transactional Spreadsheets

Data That Can Be Imported