Products »
Excel Sales Forecasting » Here
Tutorial for Excel Sales Forecasting
Install the add-in
- Install Lokad ExcelSales Forecasting through the regular Windows installer.
- 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.