4.2. Aggregated spreadsheets

Streamline is able to import data from an Excel spreadsheet or CSV file. There are two spreadsheet connections that differ in the way the data should be organized before the import.

This article describes the Aggregated spreadsheet connection. This connection requires data (sales, on-hand, and revenue histories) to be aggregated in certain periods:

• Any equal periods: days, weeks, 2 days, 4 days…
• Specified days within a week: workdays, holidays…
• Months
• Specified months within a year: winter months, quarters…

If Streamline cannot find a pattern within periods, it will work, but the future periods will be denoted as +1, +2…

Further we describe:

• data can be imported,
• format of this data, and
• how to create a new project using the Aggregated spreadsheet connection.

Depending on what you are going to do – generate demand plans, perform revenue forecasting, or optimize your inventory – Streamline needs different data to be imported. The file format, Excel or CSV, restricts the data that can be imported. In this section, we will describe the necessary types of data for each case and the limitations of the file formats.

Sales history information is necessary to forecast demand. Sales history data includes the following:

• Item code – item identifier is also known as an SKU.
• Item sales history – a set of the item quantities that were sold in particular periods.

If you need to forecast by location (store, warehouse, or region) and/or by channel (or customer), the item sales history should be additionally broken down along those dimensions. In other words, you should provide sales history for every Item code-Location-Channel triple in the most general case.

To plan your sales by item category (such as a product group) and/or location category (for example, build a hierarchy of Region > City > Store), you should provide the following data:

• Item category, Item sub-category, and so on.
• Location category, Location sub-category, and so on.

You can import as many categories as you need.

In addition to the Item code, you can import:

• Item description – an item description, used to better explain what a particular item code means.
• Info field – any additional characteristic of the item (e.g., color, size, texture). You can import an unlimited number of such fields.

You should provide Sales price/unit – the current selling price of the item, to forecast revenue. It is also used to perform the ABC analysis.

To balance your demand with supply, generate purchase and procurement plans, and identify potential stockouts and overstocks, Streamline needs the following data:

• On hand – the item amount currently in stock.
• Lead time – is defined in days and represents the interval of time between purchase order placement and its receipt.
• 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 the inventory is replenished semi-monthly, then enter the number 2 and use weekly sales data.

If the Order cycle data and Lead time data have gaps or are completelly missing, the default values (Order cycle = 1 month and Lead time = 30 days) are loaded from the program settings.

The accuracy of inventory reports can be improved, if you additionally import the Lead time variance.

Orders-to-receive Information

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

Orders-to-ship Information

To calculate purchase plan and projected inventory levels more accurately, Streamline can account for Qty to ship – the quantity of an item on open sales orders or back-orders. When making the calculation, Streamline assumes that a backorder should be fulfilled as soon as the required quantity will be in stock.

Accounting for Constraints in Optimization

If your business logic includes constraints, for instance, minimal or maximal item lot, or order rounding, Streamline can take this information into account when it generates a purchase plan. To do this, provide the following data types:

• Order rounding describes how many items are included in a carton, allowing Streamline 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.

Data for Inventory KPIs

To calculate indicators such as expected stockout and overstock value, gross margin, and other KPIs, you should provide one of the following:

• Inventory value/unit – the balance value of one unit of the item in stock.
• Inventory value – the balance value of the item in stock.
• Item purchase price – the last purchase price of the item.

The Inventory value data is preferable. The Item purchase price is also used to calculate the value of the purchase orders' lines recommended by Streamline.

Revenue history (preferred) or the Sales price/unit is used to calculate revenue-based metrics such as annual revenue, revenue next year, and other sales price-dependent indicators.

Streamline has a setting which manages how the inventory value is calculated when an item has some quantity in transition. Particularly, to calculate the inventory value more precisely, Streamline allows you to import In transition cost – the value of the item in transition.

Stock on hand history is used to calculate the inventory turnover index, This data can be imported via Excel files only and includes the following:

• Item code – item identifier is also known as SKU.
• Item on hand history – a set of the on-hand quantities at the end of particular period.

If locations are used, the on-hand history should be provided for every Item code-Location pair. Stock on hand history should be aggregated at the same time intervals as the sales history.

Streamline allows you to plan products having a limited shelf life. The only data you should provide is the item Shelf life. It should be given in the data aggregation periods.

To perform revenue-based ABC analysis you should provide Streamline with the history of the revenue obtained by selling an item. This data can only be imported via Excel files. To perform the analysis, provide data for the preceding 12 months. If a shorter history length is given, Streamline will scale the data up to 12 months.

The data you should provide is the following:

• Item code – item identifier is also known as SKU.
• Item revenue history – a set of revenue values that were obtained in particular periods.

If locations are used, the revenue history should be provided for every Item code-Location pair.

Depending on the data source – Excel or CSV file - the capabilities of the data connection to Streamline differ. To explain the capabilities, let’s denote all the data described above, except the Stock on hand history, In transition information, and Revenue history, as Sales and Properties. Excel files allow importing all the data, while CSV files, the Sales and Properties only.

To import Sales and Properties data from Excel or CSV file, it should be formatted as shown in the table below.

The table consists of two sections, the SKU properties and Sales, in order that is shown. The sequence of the columns in the SKU properties section is arbitrary. The Sales section contains sales history. As you see, the history is given for every Item code-Location pair. The periods should be formatted as dates in the section header.

The format shown in the table above uses columns representing individual features of your data and each row is a sample. Streamline can also accept data in transposed table – data features are rows and samples are columns.

Dates can be also imported when formatted using two rows.

In this case, Streamline requires date rows to be one under another and with the top row reflecting year. The bottom row should represent either the number of the month or week, depending on the data aggregation period.

Stock on hand history can be imported using Excel files only. This data should be on a separate worksheet (see figure below).

The history format is simple – the first two columns make up the Item code-Location identifier, and the following columns represent the on-hand quantities at the end of each period. The periods should be formatted as dates in the header.

The format of the revenue history is identical to the stock on-hand history format (see figure below).

The format of the in-transition information is presented in the figure below.

You can download an Excel file with test data in the proper format that can be used to create a new project in Streamline using the Aggregated spreadsheet connection.

To import the data using the Aggregated spreadsheet connection, do the following:

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

The connection dialog is divided into two parts. On the left are the dialog settings, on the right, a preview of the data is shown. The preview also shows how Streamline understands the data: text is blue and numbers are black (see figure below).

In the preview section, you see four tabs. The first tab is used to import the primary data for Excel and CSV formats, the Sales and Properties. The others are used to import the Stock on hand history, Revenue history, and In transition information from Excel files.

Transpose option turns columns of the data table into rows and vice versa. It is used when the data features in the table are represented in rows, not columns. In this case, check this option to import the data correctly into Streamline.

CSV delimiter is a character that separates data columns in CSV file. Streamline always tries to determine the delimiter automatically and in most cases, it works well. If Streamline is not able to 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 import data from an Excel file, this option is disabled.

Compose date from is used when the date in the sales history section is represented by the two rows. There are two options, Year + month and Year + week (ISO 8601). To import the data, choose the appropriate option in the control (see figure below).

The table in the preview has a row showing cells with a drop-down list. The list displays the data that Streamline understands and can import in the particular tab. To import your data correctly, match the meaning of your data column to the most appropriate option in the drop-down list (see figure below).

To import the stock on hand and/or revenue history:

1. Go to the appropriate tab of the dialog.
2. Choose the Excel worksheet containing your information in the Sheet control (see figure below).

To import the in transition information:

1. Go to the Orders to receive tab of the dialog.
2. Choose the Excel worksheet containing your information in the Sheet control (see figure below).

Once you have specified all the parameters for your data, click OK to import the data.

Next: Files Extracted from Databases

Download PDF