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 first screenshot here 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.

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

SkuOrders sheet

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 second screenshot here above illustrate 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.

Getting the data into LSSC

Open LSSC, create a new report (File » New), enter your Lokad settings, and select Excel as inventory adapter. Browse and select your Excel file as connection string. Finally, hit Refresh, and your report is ready.

Horizontal Layout

Introduced in version 2.3 (to be released mid-July 2009)

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

In this case Excel Horizontal inventory adapter has to be selected. It will expect the orders to be located on a spreadsheet named SkuOrdersHorizontal (you do not have to provide SkuOrders spreadsheet) 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 to the SkuId values in the first column.