Forecast Function in Excel(Table of Contents)
Forecast in Excel
The FORECAST function is built in function in excel which comes under Statistical Function which is used to return a prediction of future value based on existing given values. In Financial accounting, this FORECAST function will be useful to calculate forecasting reports. For example, if we know the sales of the last two months values we can easily predict the next month sales using this FORECAST function Syntax for the FORECAST Function is given below
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:
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.
Forecast function will come under the category of a Statistical function here we are going to see step by step procedure on how to use it.
- Go to formula menu and click 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 FORECAST Function in Excel?
FORECAST Function is very simple to use. Let us now see how to use FORECAST function in Excel with the help of some examples.
FORECAST in Excel – Example #1
In this example, we are going to use FORECAST function which will predict the next month sales data. Assume that 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
Here in the below example, we can see that year wise sales data which has given great revenue, now we will use forecast function to predict how the sales will be there for the year 2009.
In the above sales data, we can see the product sales item that has earned revenue Year wise. In order to predict the 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 predicted value for the year 2009 has been shown in the below graph format.
Result:
The below sales data of Line chart shows clearly that for the year 2009 we have got more revenue where the blue line goes up and it indicates the exact prediction of 6222222.
FORECAST in Excel – 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 figure 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
Now we can see that forecast in excel has predicted some sales values for the year 2019 month wise which shows that sales will be get increased for the corresponding month
The above sales and forecasting data can be displayed in a dynamic line chart format so that it can be reached easily to the management.
To insert the line chart follow the below steps.
- Select month and sales data column
- Go to insert menu. Choose 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 line graph chart has been displayed as shown below
In order to display the forecast graph select 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 blue line shows the previous year sales and 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 Excel Forecast function. Here we discuss the Forecast Formula and how to use Forecast function along with practical examples and downloadable excel template. You can also go through our other suggested articles –
