Discuss (0)

Print

RSS

Products » Safety Stock Calculator » Here

Importing Excel sales data in LSSC

Lokad Safety Stock Calculator (LSSC) can import inventory and sales data from a regular Excel spreadsheet. In order to do that, the data in the spreadsheet must follow a specific layout. This guide explains how to organize data within the Excel spreadsheet for a later import in LSSC.

Download: SampleInventoryData.xls
(illustrative spreadsheet)

SkuDetails sheet

The first sheet of the Excel spreadsheet must be named SkuDetails. This sheet contains the SKU (Storage Keeping Unit) definitions. If you do not have multiple storage locations, then SKUs may just be product references.

The screenshot above illustrates such a sheet. The following columns are expected:

• SkuId: unique identifier.
• ProductName: human readable name of the product.
• Supplier: name or identifier of the supplier.
• StockOnHand: numerical value indicating the number of items readily available.
• LeadTime: lead time expressed in days.
• ServiceLevel: a value between 0 and 1, the probability of not having a shortage.
• ReorderPoint: the amount of inventory that should trigger a replenishment.
• Tags: optional space-separated tags associated with the SKU.
  

Note that you don't have to populate the ReorderPoint column yourself. This column will be populated by LSSC once the demand forecasts have been made.

Sku Orders sheet

The second Excel sheet should contain all information about the actual orders. There are two possible (mutually exclusive) data layouts that could be used:

• Vertical layout lists all data in a flat table (handy when the data is automatically exported from a database toward Excel).
• Horizontal layout lists all data in format that is more suitable for the manual data entry and editing (handy when the data has been manually edited from the beginning).
  

Vertical Layout

The second sheet of the Excel spreadsheet must be named SkuOrders. This sheet contains the past orders (usually sales orders) associated to the SKUs.

The screenshot above illustrates such a sheet. The following columns are expected:

• SkuId: must match the values provided in the SkuDetails sheet.
• OrderDate: date of the order.
• Amount: quantity ordered. The unit must match the StockOnHand unit.
  

Now that you have completed your spreadsheet, you can save your Excel file, and open LSSC.

Horizontal Layout

LSSC can also recognize a different layout of orders data, called horizontal. This layout may be more convenient for you.

Excel import module will expect the orders to be located on a spreadsheet named SkuOrdersHorizontal and look like the screenshot below:



The following layout is expected:

• First column contains SkuId items matching values provided in the SkuDetails sheet.
• Second column and all the following columns must have OrderDate in the header
  

• Date columns are expected to have quantities in the cells, matching the SkuId values in the first column.
  

SkuEvents sheet

Optional SkuEvents sheet can contain events describing various marketing promotions and campaigns associated with the inventory. This optional information will help Lokad to deliver better forecasts.



The following column layout is expected:

• SkuId: unique identifier.
• Name: name of the event.
• Date: date of the event.
• Duration: duration of the event in days.
• Known: optional date indicating time, since which the event has been known about.
  

Getting the data into LSSC

Open LSSC, create a new report (File » New), enter your Lokad settings, and select Excel or Excel Horizontal as inventory adapter (depending on the data layout that you use. Browse and select your Excel file as connection string. Finally, hit Refresh, and your report is ready.