Skip to content

Hands-on Walkthrough Scenario

This section of the documentation provides a hands-on demo walking you through a scenario for which the TIM Forecasting add-in for Excel is used. Before proceeding, make sure you have installed the TIM Forecasting add-in to Excel. This section will also cover the built-in forecasting capabilities in Excel, and the differences with TIM's forecasting capabilities.

The demo dataset

This demo handles a use case with the goal to forecast daily sales, based on historical values of the daily sales as well as information regarding the stock price and the product price. A prepared version of the dataset can be downloaded here. The Overview tab in the RetailSales.xlsx file contains more information about the file.

image.png

In the Dataset tab, you can take a first look at the data. Note that not all variables have the same availability: the product price for the next week is already known, but the stock price is not.

Using the TIM Forecasting add-in

Let's start by creating a forecast with the TIM Forecasting add-in. Stay in the Dataset tab and open the TIM Forecasting add-in. If this is the first time you use the add-in, you will need to provide your credentials before proceeding. When this is done, the add-in will try to deduce the range of the dataset.

In this case, the proposed range will be _A1:D944. This range covers the whole dataset, so you can click "Confirm" to proceed.

After confirming the range, the name of the target variable and predictor variables will be extracted from the worksheet. The first column is excluded from the extracted variables, as it is assumed to contain the timestamps.

image.png

You are free to select any variable as target variable. In this demo, the Sales will be forecasted, thus the target selection can be left as it is.

Now, you can select which of the additional variables you'd like to include for calculating the forecast. Let's start by calculating using only historical values of the target variable and no additional predictors. Since Excel's built-in forecasting capabilities cannot take additional variables into account, this can serve as a first basis for comparison.

The next step is to indicate the amount of samples you'd like to be forecasted.

image.png

Given this dataset, it might be interesting to forecast the expected daily sales for the next week. Since this dataset contains daily data, this means forecasting 7 future samples. The last decision to make is whether you want an additional worksheet to be created containing more detailed information - such as prediction intervals and predictor importances - and visualizations.

click "Forecast" for the actual forecast to be calculated. You will see a message and a progress bar indicating the progress.

image.png

When the calculation is done, you will see a message confirming this and the forecast will be added to the worksheet.

image.png

Since the area that contains the forecasted values is included in the selected area for the detailed graph at the top of the worksheet (called Sales and Forecast), this graph will automatically be updated to include the forecast. To increase ease of use, the forecasted values are added to the same column as the historical target data. Therefore, both appear in the line graph as one line, in one and the same color.

image.png

If you selected the extensive visualization option, a new tab (called Visualization Dataset) will be created. It will contain a table with the timestamp column, the target column, the forecast and lower and upper prediction intervals. The worksheet will also contain the following line chart, visualizing the last 35 observations of the target variable, the forecast and the prediction intervals. In this case, it is not relevant to add a table containing predictor importances and visualize them in a treemap. However, when relevant this information will also automatically be added to the visualization worksheet. Feel free to experiment with other datasets and try it out for yourself!

image.png

Excel built-in forecasting

Excel provides users with built-in forecasting capabilities. These capabilities can be called directly with the FORECAST formula, as well as in the Data tab, by selecting "Forecast Sheet" under the Forecast group.

Annotation 2020-05-26 180633.png

The forecast Excel calculates is based on historical values of the target variable. This is the first difference with TIM, which can base its forecasts on additional predictor variables in addition to historical values of the target variable; by taking more information into account when calculating the forecast, it is often possible to achieve higher accuracy. Further along in this demo, you will see how you can include predictors with TIM and the difference in results that come from this.

Excel has two types of forecasting built-in: FORECASTING.LINEAR and FORECASTING.ETS. The first option predicts future values by using linear regression; the second option predicts future values using the AAA (additive error, additive trend and additive seasonality) version of the Exponential Triple Smoothing algorithm. Both are fairly simple techniques. TIM also uses smoothing algorithms, but only as a small part of the total set of possible transformations that are applied to the data. This is a second difference between TIM's forecasting capabilities and Excel's built-in forecasting capabilities.

In the Excel Forecast tab you will find an example of Excel's forecasting capabilities. The sales for the next week (7 days) are forecasted. Compared to the FORECASTING.LINEAR method, the FORECASTING.ETS method is better suited for non-linear data models with seasonal or other recurring patterns. In this demo, the dataset is non-linear and is likely to include some seasonal and/or recurring pattern(s). Therefore, the FORECASTING.ETS method is used.

image.png

Comparison

In the Comparison tab, you get an overview of both TIM's forecast and Excel's forecast.

image.png

This tab shows the historical actuals of the sales, next to both forecasts. The absolute differences are also calculated, to be used for calculating the MAE (Mean Average Error). In the small table on the right, you can already see that the MAE of TIM's forecast is lower than the MAE of Excel's forecast, thus TIM already generates the best results. The forecasted values and absolute differences are also visualised in two graphs.

Including predictors

Now, you can allow TIM to include predictor data and find out if this enables an even better forecasting quality. Go back to the Dataset tab, and remove the previous forecast (select the 7 forecasted values, i.e. cell B938:B944, right click and choose "Clear Contents").

In the add-in's sidepane, check whether the range selection is still accurate (A1:D944). Then, click "Select all" in the Predictors section of the sidepane to include all predictors.

image.png

Make sure the desired forecasting horizon is indicated (7 samples) and click "Forecast". Once again, you will see a progress message before receiving confirmation that the forecast has been added to the worksheet.

image.png

Move back to the Comparison tab, and you will see the comparison based on the new forecast.

image.png

Note that the MAE (Mean Average Error) of TIM's forecast has decreased even more, from 36.83 to 32.98.

After following along with this demo scenario, you're now ready to further experiment or use the add-in on your own datasets. In the Solution templates section of the TIM Forecasting documentation, you can find additional datasets to use in your experiments.