Integrate AI into Microsoft Excel for sales forecasting

Get AI where you work

This tutorial will show you how to leverage the Peltarion Platform from Excel.
You will use a spreadsheet with historical sales data to train a deep learning model, and get predictions for future dates back into the spreadsheet.

Person - Target audience: Beginners
Clock - Estimated time: Setup - 10 min | Training - 5 min | Deployment - 5 min

You will learn to
Peltarion logo - Create a training dataset from your spreadsheet.
Peltarion logo - Install Peltarion’s Excel add-in.
Peltarion logo - Fill your spreadsheet with predictions from an AI model.
Peltarion logo - Optional: Build a deep learning model, with the Sales forecasting tutorial.

Rather watch?


The data

Download a copy of this Excel spreadsheet containing historical sales data.

The document has two sheets (tabs). The sheet called Dataset contains the data we’ll use to train a model.
We’ll use the Test predictions sheet later to test the add-on.

You can have a look at the data and get a feel for how different features affect the daily revenue.
For example, a shop has 0 daily revenue when it isn’t Open, but not all shops close on weekends or on a Holiday.

Export the spreadsheet as a CSV file

The data in the sheet must be organized in a way that can be used for training an AI model:

  • The first row contains the name of the different features, one feature per column.

  • The following rows contain the values for each feature, one row per example.

  • No empty cells are allowed.

Data available as an Excel spreadsheet

The sheet Dataset is already well organized, so you don’t have to change anything.

Save the sheet as a CSV file by clicking on FileSave As and select CSV UTF-8 from the Save as Type dropdown.


Train a model

Now is the time you would head to the Peltarion Platform to train and deploy a model from this data.

If you want, follow the Sales forecasting tutorial to learn how to do it and come back here afterward.

We have also deployed the forecasting model, so just keep reading if you prefer to see how to use that model in your spreadsheet.


Install the AI for Excel add-in

The first time we use the Excel add-in, we need to install it.

You can install the Peltarion AI for Excel add-in directly within an open Excel file:

  • Click on InsertGet add-ins

  • Search for Peltarion

  • Click on the Peltarion AI for Excel result, then click on the Add button

Install the Peltarion AI for Excel add-in

Get AI predictions in Excel

Fill your spreadsheet with test data

Open the Test predictions sheet (tab) from your own copy of the Excel spreadsheet.

This sheet contains the same kind of input information that the model was trained on, but for dates in the future. This means that the model has never seen these exact combinations of promotion, holidays, advertisement, etc., while it was training, but it will make predictions based on what it has learned from the past data.

Feel free to edit some of the variables, or to add more rows.

Get your model’s API information

Anyone can’t just use the model that you built. To get predictions from a deployed model, you will need two pieces of information:

  • The URL of the model

  • The Token of the model, which is like a password without which the model will not run

You can get this information in the API information on the Deployment view.

If you did the Sales forecasting tutorial, copy your very own URL and Token to use a model that only you have access to!
Otherwise, you can use these to get going:

Using the Peltarion AI for Excel add-in

In your Excel spreadsheet, click on the Home button and press the AI for Excel button. A sidebar will appear on the right side to help you request predictions from your model.

To get predictions:

  • Press Enter credentials. In the dialog that appears, enter the URL and Token from the deployment’s API information.
    Press Connect then Done.

  • Select the headers we want as input (C1:K1 in our case) and press From selection under the Header range input on the panel.

  • Select the data we want to use as input (C2:K100 in our case) and press From selection under the Data range input on the panel.

  • Press Call model to send the specified data to your model for predictions. The result will be inserted into the L:M columns.


Recap

You’ve learned how to blur the boundary between data science and everyday spreadsheet work. You can use the data that you have in Excel to train an AI, and get smart predictions back directly inside your cells. All of that without a single line of code!

Was this page helpful?
YesNo