Excel might be one of the most accessible tool available on the market to build a forecast. Before reading this blog post about using Excel and forecast, you might want to have a look at our central post around sales and demand forecasting. This post will show you many aspects around forecasting and sales forecasting and might enrich your general knowledge and make you aware of the forecasting techniques.
Why using excel to make a forecast?
Excel is a fabulous tool to build a forecast.
Easy access to data and numbers
Excel offers many possibilities when it comes to upload the data. You can either copy paste the dataset, directly from your datawarehouse. You can decide to import the data using the data import functions. You can directly connect to a cube. You could merge and combine multiple dataset and let's say, run an automatic forecast and adjust it manually in excel.
Excel is pretty flexible and complete to import and play with the data, in case you need to run and maintain a forecast.
Excel has many embedded visualization possibilites
You can decide to build great data visualization with Excel. Charts, lines, stacks, pies and many others possibilites. Graphics on Excel are easy to build. They offer you the possibility to quickly change view and control the quality of the dataset.
Excel already has native forecasting functions.
Among all the possibilities offered by Excel, you can use the following formulas:
Upload a CSV and start generating sales forecasts at scale with time series analysis.
- =FORECAST.ETS, which is an Exponential Triple Smoothing algorithm ( more information here). This functionality is available on Excel 2016 and further versions and you will find more details about it further in this post.
- Leverage existing formulas, such as moving average, exponential, logarithmic and polynomial or build a linear regression
- Build your own formulas and decide to apply your mathematical functionals direcly on your dataset: quarter triangulation combined with day of the week and so on.
Excel is a universal tool, company-wide, convinient to share with other stakeholders
Finance, sales, marketing, management... all departments are using Excel. Having a forecast built totally or partially on Excel, allows almost everyone and all stakeholders to have a look at the model and to comment on it.
You can also connect it to PowerPoint to showcase your result to a non initiate audience.
Step 1/ import your data in Excel
The first step consists in importing the dataset. As mentioned earlier, you can:
- Copy paste your data (not recommended)
- Import your data (recommended)
Importing your data will make it possible to refine your forecast at ease. When you import your data, the default format is a table. While disturbing at first usage, it's actually pretty great. When you use a pivot based on table, if in the future your dataset has more rows in the future, the pivot will automatically adjust.
Chances are that you will need to re-adjust your dataset many times before having the right set-up. This means, back and forth between your database and your pivot, multiple copy- paste, erase, delete, update, pivot source adjustement. Again and again.
However, if you "import" the data and keep the table formatting, the whole actualization process can be done in just 2 clicks: if you have many forecasts to build, this will be highly appreciated! Just by clicking on 'full update' your pivot and data source will be refreshed. That's a great time saving trick and the assurance to have a robut data pipeline.
Step 2/ Visualize historical trends
This is called the data discovery phase.
You need to visualize them, group the data per year, per month. Play with the data. Understand weekday versus week-end patterns. This data discovery phase is critical before building any projection.
You will also be able to judge the overall quality of the data you have as raw material. You need to answer questions such as:
Is the perimeter chosen to build the forecast correct?
Do you need to include more historical data or just 3 years of data are enough to capture a trend? Is the trend you see showing visually a pattern - or is what you see totally random, which would be a problem?
Imagine that you had 10 years of continuous growth. However, last year was a special year. Whether or not you choose to use 10 years of historical data or only the last 3 years, will have an impact on the quality of your forecast.
Are there are any outliers?
At this stage, do you see any weird peak or deep in the data? Should you include it or exclude them? Is this peak recurring, e.g. coming back every 30 days or every year? What could be the business reason of this outlier data point?
You need to pay attention to these outliers. A peak could be the sign of a special event, a marketing promotion or a data bug. A deep could be the sign of a product shortage which occurred and the sales level should have been higher.
You can identify outliers using whisker and box plots. It might sounds technical, whiskers are actually the best option to quickly identify outliers - and remove them.
Step 3/ Choose a method to build your forecast
If you are not sure about the right methodology, why don't you give it a try to our forecasting solution?
Combine multiple forecasting methods
Using excel to build a forecast doesn't mean that you can't use a third-party algorithm to crunch the numbers! Excel can be used just to adjust the forecast generated.
Also: don't limit yourself to only one forecast methodology. You should always try to combine 2 or 3 forecasting approachs and pick-up the one which makes more sense. Our platform simplifies the usage of the Prophet forecasting algorithm. You could perfectly use it, combined with the ETS forecasting formula in Excel for instance.
Using multiple forecasting methodologies has the benefit offer you multiple scenarii and to check which methodology presents the best results.
Fine-tune the ETS Excel forecasting formula
Regarding the ETS forecasting formula which comes with Excel, you can play with the following parameters:
- Confidence interval. While the default confidence interval is set at 95%, it's up to you to adjust it to.
- Forecast start date. You can let it at its default... or want to make it start earlier in time, so that you can compare the forecast with the actuals. That's kind of a back-test forecast.
- Seasonal pattern. You can let again Excel decide or test a narrower seasonal pattern to be set manually.
- Include Forecast statistics. This is quite an interesting option to check as it will shows the multiple coefficient used (beta, gamma) but also the acurracy metrics.
Adjust manually your forecast
Forecasts are just mathematical projections. Even if you use a perfect algorithm and have removed the outliers and the noise, you will still need to adjust it. Background: You know more about your business. You will need to manually adjust your projections. In order to do so:
- Add another column named "manual corrections"
- In this new column, add your estimation for this date.
- Don't overwrite the forecast's output directly. This is always a useful information and if you plot the curve, you will be able visually to compare the original forecast output with your manual correction.
Step 4/ Cross check with third parties information
This part is where you try to make the link between your forecast and other information from your company. In other words, this is where you triangulate your results to check the implications of your forecast.
- What is the impact on the EBITDA?
- What this forecast level would mean in terms of consumption per active consumer?
- What would be the evolution of the transaction per active consumer?
- How would evolve the average basket size?
- How many sales people would be required to support the effort?