Forecast Function in Excel(Table of Contents)
Forecast in Excel
The forecast function available in excel is the simplest ever forecasting function that we could have. This function predicts the selected iteration sequence, but we must have all the rest knowns sequences, and the rest is known values. The forecast function simply uses the Moving average forecast method to predict the next demand. If we do not have known X, then we have entered the number manually, giving 1 for the first value and so on.
FORECAST Formula in Excel
Below is the FORECAST Formula in Excel:
Arguments of FORECAST Function in Excel :
- X-: This is a numeric value where we need to forecast new y value
- Known_ Y-: This Known y_values is a dependent array or range of data.
- Known_ X-: This Known x_values is an independent array or range of data.
Features of Forecasting:
- This function is normally concerned with future events
- It is used as a statistical tool & techniques
- It is mainly used in predicting futures sales data
- It analyses the present and past data.
Forecasting:
The forecasting function is mainly used in FMCG, financial, accounting and risk management where we can predict the future sales figure; assume that FMCG company has huge sales in order to find out the next month or next year sales this FORECAST function is very useful to predict the exact result which will be useful for the management in analyzing revenue and other promotion reports.
The forecast function will come under the category of a Statistical function here; we will see a step-by-step procedure on how to use it.
- Go to the formula menu and click the insert function. A dialogue box will be displayed. Choose the category statistically. Once you choose the statistical, you will find a list of a function. Choose forecast function
In the advanced version, we can use the shortcut wherein the formula menu we can see the more function.
Go to formulas–>Choose More Function–>Statistical–>Forecast
How to use the FORECAST Function in Excel?
FORECAST Function is very simple to use. Let us now see how to use the FORECAST function in Excel with the help of some examples.
Example #1
In this example, we are going to use the FORECAST function, which will predict next month sales data. Assume that a popular FMCG company has sales data month-wise, and the management needs to find out the sales forecasting to get the next future month sales. In this scenario forecasting function will be very useful in pointing out future sales.
4.8 (9,153 ratings)
View Course
Here in the below example, we can see that year-wise sales data has given great revenue; now, we will use the forecast function to predict how the sales will be there for 2009.
In the above sales data, we can see the product sales item that has earned revenue Year wise. In order to predict next year data, we use the FORECAST function. For the year 2008 Product Name “ Grommer” has earned sales value of 6000000 In order to predict the next year, i.e. 2009 Sales value we can use the FORECAST in excel which is shown with the below sales data.
So we have to use the FORECAST in excel to derive the next year sales value where
D12 is x-Numerical Value.
C3:C11 –Know_Y Value.
D3:D11- Known_X Value.
Result:
The Sales revenue for Groomer Product for the Year 2009 is 6222222. So we can easily predict the sales revenue for a given product.
Let see the above sales data in a dynamic graph format so that it can be viewed easily to understand
In order to display the above sales data in graphical chart format, follow the below steps:
- Select cell from C3:D12.
- Select Insert Tab, then select Line chart option. In-Line chart option select first option.
- The chart has been displayed, and the predicted value for the year 2009 has been shown in the below graph format.
Result:
The below sales data of the line chart clearly shows that for the year 2009, we have more revenue where the blue line goes up, indicating the exact prediction of 6222222.
Example #2
In the below example, we are now going to see month wise sales by using the forecast in excel where we have seen year wise sales in the previous example. Consider the below sales, which have month wise sales data; now, we need to predict the next year month wise sales using the forecast function.
Here we changed some sales value figures to get the accurate forecasting result as shown below.
- Insert forecast function
- Select x value as B14
- Select known y’s from C2:C13
- Select known x’s from B2:B13
=FORECAST(B14,C2:C13,B2:B13)
The Result is:
Similarly, we find other values
We can see that forecast in excel has predicted some sales values for the year 2019 month-wise, which shows that sales will be increased for the corresponding month.
The above sales and forecasting data can be displayed in a dynamic line chart format to reach the management easily.
To insert the line chart, follow the below steps.
- Select month and sales data column
- Go to the insert menu. Choose a line chart type
- Select 3D line chart graph format
Now select the month and sales data column for graph selection; after that, we need to choose the line chart type, then the line graph chart has been displayed as shown below.
In order to display the forecast graph, select the forecast column along with the month and sales data. So you will get the below forecasting chart as shown below.
In the above graph, we can see the difference where the blue line shows the previous year sales and the orange line shows the future forecasting sales, which denote that growth will be get increased for the next three months compared to the previous year 2018
Advantages of using FORECAST in Excel:
- Useful in predicting month wise and year wise sales
- Commonly used in all FMCG, Financial and Investment companies to determine the growth of future sales.
Recommended Articles
This has been a guide to the Excel Forecast function. Here we discuss the Forecast Formula and how to use the Forecast function along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
