Courses
Free Excel
Blog
About us

Forecasting in Excel in 3 Clicks: Complete Tutorial with Examples

Table of Contents

To progress in Supply Chain, mastering Excel is essential. Forecasting with Excel will allow you to automate and simplify your work.

In this article, I will tell you how to make forecasts in Microsoft Excel automatically and simply. I advise you to download the Excel template to follow this tutorial step by step.

Excel template supply chain download
Download the template

Forecasting in Excel: Visualizing a Trend

For our first example, we’re looking at COVID-19 cases around the world. You will find the data needed to follow this example in this Excel forecasting template.

Forecast-excel-table-1
COVID-19 Cases data

The purpose of the exercise is to use Excel to forecast and estimate future COVID cases using a trend, which we will add to the graph. To create a trend line on a graph (here in red), right-click directly on the graph and select add a trend line.

forecast-excel-linear-2
Worldwide COVID cases data with a linear trend line

The forecast menu will appear, allowing you to select the desired trend line.

Forecast menu – curve type selection

What is interesting about this forecast menu is that you can easily make new forecasts by adding future calculation periods of COVID cases. Just add forecast periods (in days) in the lower part of the menu.

This will generate a new line chart graph, with a new curve, which will show future predictions.


Forecasting: How to make an Automatic Forecast?

For this exercise, we will select new data in our table. Here, we want the world data (World cases) and the number of days (Day).

When the columns are selected, click on Data, then Forecast Sheet in the top menu. You will get the following forecast sheet:

It is possible to change the number of days of predictions by clicking on Options below the graph. Simply add or remove the desired number of days in the Start and End boxes of the forecast.

The other interesting option you can play with is the confidence interval. What is the confidence interval? It is simply the probability of the forecast. The orange part of the graph shows a high forecast hypothesis and a low forecast hypothesis. If your confidence interval is 70, then there is a 70% chance that the prediction is between the high and low assumptions.

The last option to look at is Seasonality, which means the seasonality of your forecast. Microsoft Excel detects patterns of change in the data and includes them in its forecast. For example, in our COVID cases, there is a drop in test results on Sundays and Mondays because the number of tests performed drops at the weekend. This change is taken into account when Excel makes its future forecast.

Once you have selected the desired parameters, click Create to see the forecast data calculated by Excel spreadsheet in a table format. The Day column shows the days, the World column shows the COVID cases in the world up to day 467, where our data points end. The Forecast column is the beginning of your future forecast, the Lower Confidence Bound column represents the low hypothesis, and finally, the Upper Confidence Bound column indicates the high hypothesis.


Forecast sheet: the Case of India

For the rest of our tutorial, let’s look at the case of India. By selecting the specific case column for India in our original data table, as well as the number of days, we obtain this forecast:

The case of India

However, the forecast looks strange compared to historical data. Why is that?

The selected period is too long and Excel can’t detect a seasonal pattern. We can therefore define it manually (7 days). Also, we must ask ourselves: does the past period reflect what will happen in the future? Here, unfortunately, it will not, so we will focus on the second peak instead of the first.

To select the period of interest, click on Cancel. Click on the COVID CASES 2021 tab at the bottom of the spreadsheet, then select the Day and India columns again.

Then, click on Data and generate a new Forecast Sheet.

Manually redefine the end of the forecast and the seasonality and you will get something that looks much more like reality. It is always important to visualize the low and high hypotheses (companies don’t do this enough) as they correspond to the Best-Case and Worst-Case scenarios, which can be critical information for your performance.

Here’s how to make a forecast in Excel in just a few clicks. In the next part of our tutorial, we will try to forecast car sales in the USA for the next few months.

Sales Forecast (U.S. Car Since 1976)

This is the database to work with on this sale forecast. It’s interesting to see this curve, which shows a slight increase right now and helps us visualize the different crises that have hit the US since 1976 (the oil crisis, the 2008 crisis, and finally the COVID crisis).

Once again, to see the forecast, select the sales per month and the total sales (the last 2 columns).

To have a more coherent forecast, let’s revise the end of the prediction downwards and put a confidence interval of 50%. We can see that Excel has detected a seasonality in this graph. Thanks to the data, we can see that there is a crisis about every 181 months. You can change this as you wish. Here I have changed the seasonality to show a change every 12 months.

And so here is the new forecast. Of course, these predictions are not perfect. You can always add new trends, new mathematical models, market research, competitive research into your data analysis. All of which will impact your sales forecast.

But the important thing in this exercise is to understand how to do make your own automatic forecasts in Excel, to see how simple it is to master this tool. Indeed, mastering the art of forecasting is the best way to develop the profitability of a company, to improve performance and customer satisfaction.

Would you like to go further?

If you want to boost your career and develop your Excel skills in the long term, registration is open for our SCM Excel Expert training.

online course excel macro power query pivot

This program is for you if you want to …

  • Gain efficiency in Excel to focus on the essentials
  • Automate your Excel tasks, reporting and improve the performance of your company
  • Generate files automatically in 1 click thanks to macros and Power Query / Pivot (without knowing how to code)
  • Improve your performance and boost your career in Supply Chain & Logistics
Become an inventory management expert
Join my free 7-day mini training: “How to Reduce Stockouts and Overstock.”
Share
Start boosting your forecast accuracy now !

Join my free 7-day mini training: 
“Track and boost your performance Automatically.”

  • The 5 main indicators for your Supply Chain (without falling into the expert syndrome)
  • How to prioritize them in 4 levels
  • How to automate your reporting without spending a fortune on IT development
  • Case studies of large groups: Zara, H&M, Amazon…
Ready to boost your skill ?
Discover our courses
See all courses
SMC Foundation Course
SCM Foundation
View the course
Excel training label
Excel Expert
View the course
SCM Analytics training label
SCM Analytics
View the course
Inventory management training label
Inventory Management
View the course
Forecasting training label
Forecasting Expert
View the course
Scroll to Top

FIND OUT HOW MUCH YOU ARE WORTH.

You will receive an email with an estimate of your salary along with some advice on how to boost it.

Your information is confidential and will never be given to third parties. You can unsubscribe in 1 click from any of my emails.