• View ChangeLogs
• Troubleshooting

Lokad News

Products » Excel Sales Forecasting » Here

Tutorial for Excel Sales Forecasting

Install the add-in

1. Install Lokad ExcelSales Forecasting through the regular Windows installer.
2. Get a Lokad account (unless you already have one).

Getting a Lokad account is free. No payment will be required for the first 30 days. See our pricing page for more details.



You can check that the add-in is correctly installed by simply launching Microsoft Excel. The Lokad toolbar should now appear in Excel.

Creating raw sales data in Excel

Lokad Excel Sales Forecasting supports more than 20 applications where the sales data can be retrieved from. Yet, it is also possible to retrieve sales data directly from your Excel spreadsheet.

This section explains the expected format format for your raw sales data in Excel. Here below is a sample that illustrates the expect layout for the raw sales data.

Product Name	Sku	Date	Amount
Gizmo (small)	GIZ-MO-001	2007-10-01	17
Gizmo (large)	GIZ-MO-002	2007-10-01	38
Gizmo (small)	GIZ-MO-001	2007-11-01	19
Gizmo (large)	GIZ-MO-002	2007-11-01	34

We suggest that you create open an new Excel spreadsheet and create a small sales listing. Ex: sales of a chosen product for the last 12 months (one line per month).

Tip: You can copy-and-paste this table from this web page directly into Microsoft Excel. Just make sure to use Edit \ Paste Special and select Unicode Text.

As you can see, the raw sales data includes 4 columns

• product name
• SKU (or product ref)
• date
• amount

The first line of the table, i.e. Product Name, SKU, Date, Quantity is provided for the sake of clarity, but this line is not actually required (also, we suggest that you include such headers as it will help you to track of your data later one).

The Product Name column is provided for the ease of use. The data contained in the column will NOT be transmitted to Lokad. But it will be used by Lokad Excel Sales Forecasting internally when creating the sales report.

The SKU column is used as a product identifier by Lokad; in other words, Lokad uses the SKU to determine whether two lines are referring to the same product or not. Note that Lokad Excel Sales Forecasting will transmit the SKU to your Lokad account (later on, at the Upload step). If you want to ensure a maximal level of confidentiality, you can use obfuscated identifiers such as PROD1, PROD2, PROD3 ...

The Date column indicates, well, the date of actual transaction (or the reference date for a set of transactions over a certain period).

The Amount indicates the number of sold products (or eventually the total value of the sales as it makes no difference for Lokad).

The order of the lines in the raw table has no importance. In particular, it is NOT required to get the sales sorted in a chronological order. It is NOT required to gather the sales by product either. We suggest to list the raw sales data in the order that you think to be the most convenient for you.

Get your first sales retrieval

We assume here that you have inserted some minimal raw sales data as specified in the previous section.

Caution: Make sure your Microsoft Excel document is saved (CTRL+S) at this point. The add-in Lokad Excel Sales Forecasting retrieves the data from the saved version of the Excel document (the changes won't be retrieved until the spreadsheet is saved).

In order to create a new sales report through the Lokad Excel Sales Forecasting add-in,

• select a cell located on the right of your raw sales data (later on, we will see why it is wiser to do so).
• once the cell is selected, click the button New in the Lokad toolbar. The settings dialog box appears.

As you can see, there are 3 main blocks of settings, namely Lokad connection, Forecasting task and Commerce connection. We will quickly review those blocks.

Lokad connection

• User name: The Lokad user name is the e-mail address that you have provided when creating your Lokad account.
• Password: It's the same password that the one used to access your Lokad account.
• WS URL: Do not change unless you know what you are doing (see the developer guide and the sandbox).

Forecasting task

• Period: The period defines the type of forecast to be delivered (ex: daily, weekly or monthly forecasts).
• Past periods: Indicates how many periods should be displayed as sales history in your report.
• Future periods: Indicates how many periods should be displayed as forecasted sales in your report.
• Max tasks: Maximal number of forecasting tasks. Lokad is priced by the forecasting task. Basically, each product in your catalog has its own sales history, and potentially its own forecasting task. Yet, some products may not be worth to be forecasted due to their low sales activities. The max tasks indicates that only the top N most active products will be forecasted; thus, avoiding the merchant to get charged for less than useful forecasts.
• Period start: The date used as a reference to define the period boundaries. The default value is Monday, Jan 1st, 2001. Thus, if you choose weekly forecasts (resp. monthly forecasts), all the periods will start on Mondays (resp. the 1st day of the month). The period start could be changed if you want to adjust the definition of the periods to your business needs.

Commerce connection

This block contains the information related to the sales data retrieval.

• Application: Indicates the commerce application where the sales data will be retrieved from. See section below for more details.
• Connection string: The connection string used to connect to your commerce application database. connectionstrings.com is a directory listing the connection string syntaxes for all major database systems.
• Application option: Some commerce applications need additional settings. This field can be left empty.

Back to the current walkthrough (where you have put some raw sales data in your spreadsheet, see here above), select Microsoft Excel within the Application list. Then the connection string should be MyFile.xls$Sheet1$A1 where you substitute MyFile by the actual name of your spreadsheet and A1 by the upper-left corner or your raw sales data. Typically, it is the location of the cell that contains Product Name. Leave blank the Application option.

Once the settings dialog has been completed, you can click Save; and your settings get saved as arguments for the custom Excel function ForecastSales.

Now, if you click the Retrieve button within the Lokad toolbar, you get a dialog box indicating the progress of the retrieval of your sales data.



Tip: the progress bar is not very useful in the case where the sales data is stored into the Excel spreadsheet directly because the data retrieval is likely to be very fast. Yet, Lokad Excel Sales Forecasting supports also the retrieval of sales data remotely available (ex: PayPal sales). In that case, the retrieval can be significantly slower, especially if there is a large amount of data.

Get your first sales forecast

At that point, a report has just appeared below the cell that contains the ForecastSales function.

Tip: This is why we suggest to select a cell on the right of the your raw sales data in order to avoid the report to overlap with your actual sales data.

The historical data appears over a yellow background. You can now use the remaining buttons in the Lokad toolbar:

• click Upload to send the sales data to your Lokad account.
• click Download to download the forecasted sales from Lokad.

The forecasted sales appears over an orange background. The final result looks like


Congratulations.

At this point, you have finished your first walkthrough with Lokad Excel Sales Forecasting. This add-in supports many other applications. You can refer to setup guide of Lokad Desktop Sales Forecasting for additional information concerning the syntax of the connection strings required to connect to commerce applications (i.e. that contains sales data), beyond Microsoft Excel itself.