How to Perform Excel Data Analysis: Forecasting (3 Easy Ways)

This article will help you understand how to use Excel and its widely used features to perform data analysis and forecasting values. I hope all these tools, functions, and charts greatly impact your level of knowledge and understanding in the field of data analysis and forecasting.

Microsoft Excel has made a revolutionary impact in the field of data analysis and forecasting. It plays quite an important role in the world of finance and business. It helps individuals and organizations find trends, get insights, and make proper decisions for the future.

Download Practice Workbook

You can download the workbook, where we have provided a practice section on the right side of each worksheet. Try it yourself.

Benefits of Using Excel Data Analysis for Forecasting

Excel offers a wide range of built-in functions and tools designed for data analysis and forecasting. Especially in the field of data analysis and forecasting, the availability of widely used tools such as Moving Average, Exponential Smoothing, Trend Analysis, regression analysis, and growth rate forecasting made our professional lives quite easy.

Besides, Excel is easy to use. It has a user-friendly interface, which makes the task easy for many new Excel users.

It can also visually represent the results of the data in charts and graphs. As a result, viewers, data scientists, or data analysts can easily identify trends and patterns. It enhances their understanding of the data structure and helps uncover important insights.

In sum. Excel allows us to use formulas and integrate the data source to make a better output by offering users the flexibility to conduct a deep and thorough analysis. It empowers businesses to adapt, change, and make decisions based on valuable insights.

Perform Excel Data Analysis: Forecasting (3 Handy Ways)

There are different types of forecasting techniques and methods available in the fields of statistics and mathematics. We are going to use a sample dataset for each to demonstrate how the forecasting methods work.

1. Using Moving Average Method for Forecasting Data Analysis in Excel

Generally, professionals or business personnel use the moving average method to forecast values based on time series data. It predicts or forecasts with greater accuracy. Such as the trend of a certain share, seasonal variation of price or commodity, etc.

There are two ways we can use the moving average method in Excel for forecasting data analysis.

1.1 Using Data Analysis Command

The Moving Average method in forecasting is like looking at the average of a group of numbers to understand the general pattern. It smooths out the ups and downs in the data by calculating the average of nearby values.

By doing this, we can see the overall trend more clearly. It helps us make predictions about what might happen in the future based on how things have been going in the past

We will use the following dataset to demonstrate how time series analysis and forecasting are done using the moving average method.

Assume that a retail company has collected sales data from 1-10-2010 to 21-10-2021. They are interested in forecasting the sales value for the next day. To accomplish this, we can utilize time series analysis techniques in Excel.

To smooth out daily sales changes over a week, we’ll add a new column called Average and calculate the average of seven consecutive days. We’ll repeat this process by shifting the 7-day window each time to create a series of averages.

In Excel, there are two ways we can use the moving average method. The first approach is the following.

• Begin by navigating to the Data tab and selecting Data Analysis from the Analysis group. This action will open a new window displaying a range of available data analysis techniques. Locate and choose the Moving Average option from the list, then click OK to proceed.

• Now from the Moving Average window, specify the input range, which in this case is the sales column ($C$5:$C$25). Since we are calculating the average of 7 days, set the interval to 7.
• Next, define the output range as $D$5:$D$26. If you want to visualize the results in a chart, check the chart output option. Once you have made these selections, click OK to generate the moving average.

Excel will calculate the averages in the specified output range, which in this case is the Average column ($D$5:$D$26). Additionally, Excel will generate a chart that displays the moving average curve and the forecasted values.

1.2 Using Forecast Sheet Command

To use the moving average method in another way, follow these simple steps.

• Go to the Data tab and select the drop-down menu of the Forecast option.
• Choose the Forecast Sheet option to proceed.

• In the Create Forecast Worksheet window, select the forecast end date. For example, in this case, it is 27-10-2010.
• You have the option to check the confidence level, which shows the upper and lower confidence values.
• Click Create to generate the forecast.

• We will obtain the forecasted values for the next seven days, from 21-10-2010 to 27-10-2010 in the forecast column. Additionally, the graph will display the distribution of the data, providing a visual representation of the forecasted values.

• After selecting the chart, you will notice three options at the top right corner. Click on Chart Elements represented by a plus sign. From the list, choose Trendline. A menu will appear with various trendline options. Select More Options.

• A small window called Add Trendline will appear. Choose the Sales ($) option and click OK.

• Now, on the right side of Excel, you will see a new option called Trendline Format. This option allows you to customize and format the trendline according to your preferences.
• To select the moving average option, click on the Moving Average checkbox. Next, enter the value 7 in the box beside the Period option. This indicates that we are calculating the average of 7 days.

• After making the specified selection, you will notice changes in the graph. A new trendline will be added, representing the moving average of the sales data.

This trendline will also forecast future values based on the previous average data, providing insights into potential trends.

2. Applying FORECAST.ETS Function for Forecasting Data Analysis in Excel

Excel offers another method for forecasting called Exponential Smoothing. It involves smoothing past data trends and considering seasonality patterns and confidence intervals.

This feature is available in Excel 2016 or later versions.

• To use Exponential Smoothing in Excel, Simply navigate to the Data tab, select the drop-down menu from the Forecast option, and choose Forecast Sheet.

• In the Create Forecast Worksheet window, specify the forecast end date (e.g., 27-10-2010). Optionally, you can check the confidence level to view the upper and lower confidence values.
• Finally, click Create to generate the forecast.

• The resulting dataset will include three additional columns. The first column will display the forecasted sales values. If you click on the columns, you can see that the values are calculated using the FORECAST.ETS function. The second column will show the lower confidence bound, and the third column will display the upper confidence bound.

The generated graph will display the forecasted sales values, including the upper and lower bounds. It will also feature an exponential trendline to visualize the overall trend.

Thus, you can use exponential smoothing (ETS) to forecast values based on any given data.

3. Using Regression Analysis for Forecasting Data Analysis in Excel

There are also two ways we can do regression analysis in Excel. We will be using the following dataset to demonstrate both approaches.

Consider that a company wants to forecast its monthly revenue based on its advertising expenses. The company believes that there is a relationship between the amount spent on advertising and the resulting revenue. By analyzing historical data, the company aims to build a regression model to predict future revenue based on advertising expenses.

• To perform a regression analysis in Excel, navigate to the Data tab and select Data Analysis from the Analysis group. In the Data Analysis window, choose Regression from the list and click OK.

• This will open the Regression window. From there, select the input Y range, which represents the revenue column, and the input X range, which represents the advertising expense column.
• Check the confidence level option to include confidence intervals. Finally, specify the output range, such as cell B18, where the regression results will be displayed.

• After performing the regression analysis, you will find the output summary below the data table in cell B18.

• To forecast the revenue for a given advertising expense, we will use the linear equation (y = mx + c). The intercept value represents the constant term (C), and the coefficient of the independent variable 1 (X) represents the slope (m) in the equation.
• In cell H5, we will use the following formula to forecast the revenue based on an advertising expense of $2000.

The equation calculates the forecasted revenue as $9332.36 when the advertising expense is $2000. This enables you to forecast the revenue for any given advertising expense.

The second method involves using FORECAST.LINEAR function to forecast revenue based on regression analysis. This function is available in Excel 2016 and later versions, while earlier versions used the FORECAST function.

We can demonstrate this process using the same dataset. Consider that our objective is to predict the revenue when the advertising expense is $2000. To accomplish this, we will enter the following formula in cell D17.

The FORECAST.LINEAR function will return the forecasted revenue value of $9332.36. The value is the same as in the earlier approach.

This function simplifies the regression analysis and forecasting process, allowing for quick and easy predictions based on known independent and dependent values.

How to Customize Options While Forecasting 

When customizing your forecast in Excel, you can utilize various options depending on your needs.

In the top right corner, there are options for choosing either a line chart or a column chart.

You can always change the Forecast End date, as this is the output you are looking for.

Choose the starting point on the timeline for the forecast by selecting the Forecast Start date. This allows you to focus on specific periods and compare the forecasted series with actual data.

Toggle the display of the Confidence Interval, which shows the range where future data points are expected to fall with 95% confidence.

Next, determine the length of the seasonal pattern in your data through the Seasonality option. Excel can automatically detect this pattern, or you can set it manually.

If you check the included forecast statistic, the opt will display additional statistical information on the forecast, including smoothing coefficients (Alpha, Beta, Gamma) and error metrics (MASE, SMAPE, MAE, RMSE).

Specify the range that contains the timeline values in the Timeline Range, ensuring it matches the Values Range.

Define the range that contains the actual values, which should be identical to the Timeline Range

You can also decide what to do if there is any missing value. Choose between Interpolation, where missing points are filled based on neighboring values, or Zeroes, where missing points are represented as zeros.

Aggregate duplicates Using the option determines how Excel handles multiple values with the same timeline value, such as calculating the average or using other methods like Median or Count.

How to Demand Forecast in Excel

We have a dataset representing the price and demand of a commodity from 2010 to 2015. Our goal is to forecast the demand for the commodity in 2017 when the price is $900.

Begins by selecting the dataset. Navigate to the Insert menu and go to Charts. From the drop-down menu of scatter chart options, select the second one.

This action will generate a chart, visually representing the data. Right-click on the data line and select Add Trendline from the menu.

In the Format Trendline menu, mark the checkbox named display equation on chart.  It will reveal the equation inside the curve.

Now, in cell D13, apply the following equation, replacing ‘x‘ with the cell reference of C13 (which contains the value $900) to forecast the demand against the price.

It will return the demand value of 1283.74.

By utilizing the FORECAST.LINEAR function, we can conveniently forecast the demand for the commodity. In cell D13, you can directly enter the following formula to obtain the desired result instantly.

The output of the formula, which is 1283.74, represents the forecasted demand for the given price of $900.

How to Forecast Sales in Excel Based on Historical Data

Given the dataset of monthly crude oil prices per barrel, we aim to forecast the sales value for the months of May and June.

When the dataset exhibits a linear trend, whether it is increasing or decreasing, we can utilize the Fill Handle tool to generate a swift forecast.

To do this, select the range of cells containing the prices (C5:C17), hover over the bottom right corner of the last cell (C17), and drag the fill handle tool to extend the forecast until the desired cells (C19 in this case).

By applying the Fill Handle tool, we can forecast the price per barrel for the months of May and June as $112.57 and $113.29, respectively.

Frequently Asked Questions

• Is Excel good for forecasting?

Yes, Excel is a powerful tool for forecasting. It offers different types of functions, such as FORECAST, FORECAST.ETS, and FORECAST.ETS.CONFINT. Besides, it can also help users visually present the data in charts, which also helps to make accurate predictions based on historical data.

• What are forecasting tools?

The use of previous data to forecast future patterns is known as forecasting. For precise forecasting, Excel offers specialized functions, graphs, charts, and coding algorithms. They are normally known as forecasting tools for Excel.

• What are some popular forecasting techniques used in Excel?

Excel offers a range of forecasting techniques, including moving averages, exponential smoothing, regression analysis, and time series analysis. These methods analyze data patterns and relationships to accurately predict future outcomes.

• Which chart is best for forecasting?

Line charts and scatter plots are popular choices for forecasting in Excel. These chart types effectively illustrate the trends and patterns in data over time for accurate forecasting. Combination charts with trendlines are also helpful for showcasing historical data and identifying trends simultaneously.

• What is the difference between Trend and Forecast in Excel?

In Excel, the trend helps identify the underlying behavior or trendline in the data, allowing for a better understanding of its overall direction.

On the other hand, the forecast utilizes the trend to estimate and project future values or outcomes.

Conclusion

In conclusion, Excel is excellent software for data analysis and forecasting. It provides a wide range of functions and the ability to create visually appealing charts and graphs.

With its various techniques and methods for forecasting data, Excel offers convenience and flexibility for effective data analysis and forecasting.

Whether you prefer statistical models, time series analysis, or other methods, Excel has the tools to meet your needs and make accurate predictions.

If you like this article, check out Exceldemy for more relevant content.