If you are looking to use the FORECAST function with multiple variables in Excel, then you have come to the right place. Here, we will walk you through 6 easy examples to do the task smoothly.
How to Use FORECAST Function with Multiple Variables in Excel: 6 Examples
In the following dataset, you can see the ‘Year’ and corresponding ‘Sales’. Here, we will have to determine the sales for the year 2022. To do so, we will use the FORECAST function with multiple variables. Here, we used Excel 365. You can use any available Excel version.
1. Using FORECAST Function with Multiple Variables in Excel
In this method, we will use the FORECAST function with multiple variables to determine the sales for the year 2022.
Steps:
- First of all, we will type the following formula in cell C12.
=FORECAST(B12, C5:C11, B5:B11)
Formula Breakdown
- FORECAST(B12, C5:C11, B5:B11) → The FORECAST function determines the future value based on a current value.
- B12 → is the current value of the Year.
- C5:C11 → is the known range of Y values. In this case, these are the known Sales.
- B5:B11 → is the known X value. In this case, these are the known Years.
- FORECAST(B12,C5:C11,B5:B11) → becomes
- Output: $20971
- Â At this point, press ENTER.
- Therefore, you can see the result in cell C12.
2. Applying FORECAST.LINEAR Function
In this method, we will use the FORECAST.LINEAR function with multiple variables to determine the sales for the year 2022.
Steps:
- First, we will type the following formula in cell C12.
=FORECAST.LINEAR(B12,C5:C11,B5:B11)
Formula Breakdown
- LINEAR(B12,C5:C11,B5:B11) → the FORECAST.LINEAR function determines the future value based on a current value.
- B12 → is the current value of the Year.
- C5:C11 → is the known range of Y values. In this case, these are the known Sales.
- B5:B11 → is the known X value. In this case, these are the known Years.
- LINEAR(B12, C5:C11, B5:B11) → becomes
- Output: $20971
- Â At this point, press ENTER.
- Hence, you can see the result in cell C12.
3. Inserting FORECAST.ETS Function with Multiple Variables
In this method, we will use the FORECAST.ETS function with multiple variables to determine sales for the year 2022.
Steps:
- First, we will type the following formula in cell C12.
=FORECAST.ETS(B12,C5:C11,B5:B11)
Formula Breakdown
- ETS(B12,C5:C11,B5:B11) → the FORECAST.ETS function determines the future value based on existing historical value.
- B12 → is the target_date.
- C5:C11 → are the historical values.
- B5:B11 → is the timeline or known years.
- ETS(B12, C5:C11, B5:B11) → becomes
- Output: $ 21030
- At this point, press ENTER.
- Hence, you can see the result in cell C12.
4. Use of FORECAST.ETS.CONFINT Function
In the following dataset, you can see the ‘Year’ and ‘Sales’ values. However, the sales for the year 2019 are missing. We will use FORECAST.ETS function to determine FORECAST.ETS for the year 2019. Then, we will use the FORECAST.ETS.CONFINT function to determine Confidence Interval. After that, we will determine Lower Bound and Upper Bound.
Steps:
- In the beginning, find out the FORECAST.ETS value, we will type the following formula in cell D9.
=FORECAST.ETS(B9, C5:C8, B5:B8)
- At the moment, press ENTER.
- As a result, you can see the result in cell D9.
- Furthermore, to find out the Confidence Interval, we will type the following formula in cell E9.
=FORECAST.ETS.CONFINT(B9,C5:C8,B5:B8)
Formula Breakdown
- ETS.CONFINT(B9, C5:C8, B5:B8) → determines the Confidence Interval for a forecast value at a specified target date.
- B9 → is the target_date.
- C5:C8 → are the historical values.
- B5:B8 → is the timeline.
- ETS.CONFINT(B9, C5:C8, B5:B8) → becomes
- Output: 288.5851827
- Then, press ENTER.
- As a result, you can see the Confidence Interval for the Year 2022 in cell E9.
- Moreover, to find out the Lower Bound, we will type the following formula in cell F9.
=D9-E9- This simply subtracts the Confidence Interval from FORECAST.ETS value.
- At this point, press ENTER.
- As a result, you can see the Lower Bound in cell F9.
- Moreover, to find out the Upper Bound, we will type the following formula in cell G9.
=D9+E9- This simply adds the Confidence Interval from FORECAST.ETS value.
- Afterward, press ENTER.
- Therefore, you can see the Upper Bound in cell G9.
5. Employing FORECAST.ETS.SEASONALITY Function
In this method, we will use FORECAST.ETS.SEASONALITY function with multiple variables to determine the Season Length.
Steps:
- In the beginning, we will type the following formula in cell C17.
=FORECAST.ETS.SEASONALITY(C5:C16,B5:B16,1,1)
Formula Breakdown
- ETS.SEASONALITY(C5:C16, B5:B16,1,1) → determines the Season Length for specific repetitive time length.
- C5:C16 → are the historical values.
- B5:B16 → is the timeline.
- 1 → is the Data Completion
- 1 → is the Aggregation
- ETS.SEASONALITY(C5:C16, B5:B16,1,1) → becomes
- Output: 4
- Â After that, press ENTER.
- Hence, you can see the result in cell C17.
6. Using FORECAST.ETS.STAT Function with Multiple Variables
In this method, we will use FORECAST.ETS.STAT function for multiple variables to determine the statistical type values.
We have 8 statistical argument types. These are:
- Alpha (base value) – is a smoothing value between 0 and 1, which controls how the data points are weighted. A higher value indicates that recent data is weighted more heavily.
- The beta (trend value) – values between 0 and 1 determine the trend calculation. Recent trends are given more weight when the value is higher.
- Gamma (seasonality value) – A value between 0 and 1 that controls ETS forecast seasonality. An increasing value indicates that the recent seasonal period should be given more weight.
- MASE (mean absolute scaled error) – a way to evaluate how accurate a forecast was.
- SMAPE (symmetric mean absolute percentage error) – an evaluation of accuracy based on the proportion or magnitude of errors.
- MAE (mean absolute error) – independent of their direction, calculates the average size of the forecast errors.
- RMSE (root mean square error) – an evaluation of the discrepancies between the observed and projected values.
- Step size detected – the timeline’s detected step size.
Next, we will determine the Value of these argument types.
Steps:
- First of all, to find out the value of Alpha, we will type the following formula in cell E5.
=FORECAST.ETS.STAT(C5:C12,B5:B12,1)
Formula Breakdown
- ETS.STAT(C5:C12,B5:B12,1) → the FORECAT.ETS.STAT function returns the statistical value.
- C5:C12 → are the historical values.
- B5:B12 → indicate the timeline.
- 1 → indicates the Statistics_type which is Alpha in that case.
- ETS.STAT(C5:C12, B5:B12,1) → becomes
- Output: 0.126
- Â Then, press ENTER.
- As a result, you can see the output in cell E5.
- Furthermore, to find out the Beta value, we type the following formula in cell E6.
=FORECAST.ETS.STAT(C5:C12, B5:B12,2)- Â Then, press ENTER.
- Therefore, you can see the result in cell E6.
- In addition, to find out the Gamma value, we type the following formula in cell E7.
=FORECAST.ETS.STAT(C5:C12, B5:B12,3)- Then, press ENTER.
- Therefore, you can see the result in cell E7.
- Along with that, to find out the MASE value, we type the following formula in cell E8.
=FORECAST.ETS.STAT(C5:C12, B5:B12,4)- Then, press ENTER.
- As a result, you can see the result in cell E8.
- Moreover, to find out the SMAPE value, we type the following formula in cell E9.
=FORECAST.ETS.STAT(C5:C12, B5:B12,5)- Then, press ENTER.
- As a result, you can see the result in cell E9.
- Afterward, to find out the MAE value, we type the following formula in cell E10.
=FORECAST.ETS.STAT(C5:C12, B5:B12,6)Â
- After that, press ENTER.
- As a result, you can see the result in cell E10.
- After that, to find out the RMSE value, we type the following formula in cell E11.
=FORECAST.ETS.STAT(C5:C12, B5:B12,7)- Then, press ENTER.
- Therefore, you can see the result in cell E11.
- Finally, to find out the Step Size value, we type the following formula in cell E12.
=FORECAST.ETS.STAT(C5:C12, B5:B12,8)
- Furthermore, press ENTER.
- Therefore, you can see the result in cell E12.
- Also, you can see the complete Value column.
Practice Section
You can download the above Excel file and practice the explained methods.
Download Practice Workbook
You can download the Excel file and practice while reading this article.
Conclusion
Here, we show you 6 examples of using the FORECAST function with multiple variables in Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section.
Related Articles
- TREND vs FORECAST Function in Excel
- How to Use VBA Forecast Function in Excel
- [Fixed!] FORECAST Function Not Accurate in Excel
<< Go Back to Excel FORECAST Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
