Products »
Excel Sales Forecasting » Here
Tutorial for Excel Sales Forecasting
Prerequisites
Microsoft® Excel® 2007 should be installed on your machine in order to use this software.
Please contact
Lokad Support if you have a different version of Microsoft® Office® and would like to use Lokad services with it.
Install the add-in
- Download the setup file for Lokad Add-in for Excel 2007 and follow the installation process. Internet connection will be required. Additional components might be required to install in the process
- 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.
Forecast menu should now appear in Excel within
Data tab.
 Lokad menu items |
Creating Sales Data in Excel
Your initial data should be organized in tables with a simple format. Here's a simple layout for it:
| Product Name | 2009-01-01 | 2009-01-02 | 2009-01-03 | 2009-01-04 |
| Product A | 10 | 20 | 5 | 15 |
| Product B | 3 | 4 | 2 | 4 |
| Product C | 8 | 6 | 8 | 6 |
For the purposes of the tutorial, we suggest you to create a new Excel spreadsheet with a small sales listing, like the one listed above.
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 is a simple table, where:
- first column holds product names;
- first row holds dates;
- cells hold the amount of sold items (or their total value) for the specified product within the specified date.
Tip: We recommend to save Excel workbook at this point.
Configure Connection to Lokad
Now we need to configure connection to Lokad, that this Excel workbook will use:
- Select "Data | Forecast | Configure" in the Excel Ribbon UI. Lokad Forecasting panes should appear.
- Click on "Account: Configure".
- Enter login and password for your Lokad account.
 Configuring the account |
You may get a
free account right now, if you don't have it yet.
- Сlick Test to verify the entered data. Fix the issues, if there are any.
- Hit "OK" to accept the changes.
Congratulations, you have configured connection to Lokad forecasting services for the workbook. This would be a good time to press
{Ctrl}+{S} to save it.
Configure your first forecasting task
Now we have a valid connection to Lokad forecasting services. And we also have some data that we want to forecast. The next step would be to configure the actual forecasting task:
- Click Add and enter name for your new forecasting task.
- Click on the Input field.
- Enter range dialog will pop-up asking for a source data. Select your table and hit 'OK'.
- Adjust the Forecasting Period field to match the desired type of forecast to be delivered. In our sample scenario this would be a daily forecast.
- Select the proper Number of Periods for the forecast.
- Click on the Output field. Enter range dialog will pop-up asking for a place to print forecasts to. Select an empty table that has
Number of Products + 1 rows and Number of Forecasting Periods + 2 columns. - Hit OK to accept the changes.
 Selecting the Output range for a task |
Note: any changes to Account or Tasks are applied to the Excel workbook currently being opened. Don't forget to save the workbook if you want to keep them around.
Retrieve your forecasts
In order to retrieve forecasts from Lokad services we simply need to hit
Refresh button. This will:
- Upload the numbers to your Lokad account.
- Determine the time by which Lokad services will prepare your forecast.
- Wait till that time comes.
- Download forecasts and print the results into the Excel range indicated in the Output field.
Let's see how this actually works out:
- Click Refresh button. The progress dialog should appear.
- If there are any issues with the connection or data, then Details button will indicate that. Clicking on it will display more information.
 "Details" in bold indicates that some warnings or errors were found. |
Yellow circles represent warnings or potential problems, while red circles are errors. Although warnings could be ignored, it is better to get rid of them.
If you are forecasting over fresh data (that's the case with our tutorial), then the progress will eventually stop with the
Waiting for the results message. Time of the expected forecast delivery will also be indicated.
 Progress dialog indicates that data has successfully been scheduled for forecasting |
At this point you can close the progress dialog, save your workbook and even close it to return to it later.
When the scheduled time comes, you can simply re-open the saved workbook and hit
Refresh again. If you haven't made any changes that require forecast recalculation, then the forecast results will be downloaded and printed out to the Excel cells selected in the
Output field.
 Forecasts were retrieved |
Congratulations, that's the end of the tutorial.
Important: sample data being used in this tutorial results in flat forecasts. Real-world scenarios with more data will result in different forecast models being applied.
What next?
- You may be interested in other software products that allow to leverage our forecasting technologies in various scenarios.
- Support would be glad to provide assistance and receive feedback on technologies and products. Additionally there are forums to share experience and knowledge.
- Developers might be interested in the Open Source project containing full source code for this Lokad Add-in for Microsoft Excel.