Microsoft Excel can be used for predicting and forecasting future values by looking at historical data. You can execute predictive analysis and forecasting with the help of the FORECAST function or by using the Forecast Sheet option in the data tab.
Steps to Forecast in Microsoft Excel
Let’s get started with this step-by-step tutorial to determining forecasts in Excel.
1. Calculating forecast values via FORECAST function
The FORECAST function is used to calculate forecast values in a tabular dataset. Let’s see how we can forecast future sales for a given sales data in Excel.
We have the sales data of a women’s magazine available for 7 years i.e., from 2000 to 2007. We are looking forward to predicting the future sales volume from 2008 to 2013. The FORECAST function can come in handy here.
- Type the FORECAST formula in the blank cell in the Sales column besides the 2008.
- Type =FORECAST(
- There are 3 arguments that are required for the FORECAST formula to function.
- The first argument is x, which is a numerical value point to calculate a prediction. Here, x is 2008. Select the cell containing year “2008”.
- Put a comma to move to the next argument.
- The second argument is known_ys, which is the dependent variable range in the table. Here known_ys will be sales data of the previous years as sales volumes are dependent upon years. Select the sales data from 2000 to 2007 at this step.
- Put a comma and move to the third argument.
- The third argument is known_xs, which is the independent variable range in the table. Here known_xs will be the previous years for which the sales data is already available. Select the years 2000 to 2007 at this step.
- Now, close the bracket to complete the function.
- Hit ENTER to view the results.
You can see that a forecasted value has been determined by the function in the formerly blank cell. Drag or double-click the cell till 2013 to get the predicted sales volumes of the rest of the years.
Note- Functions may show errors if cells in which the formula is applied are merged.
2. Visual representation of forecasts via Forecast Sheet
Let’s begin with visually representing the calculated forecast data on a chart in Excel.
- Select a cell in the series or you may select both year and sales ranges to continue.
- Go to the Data tab.
- Under Forecast group, select Forecast Sheet.
- A window named Create Forecast Worksheet opens.
- Choose an end for the forecast under Forecast End, we chose 2013 as the end date.
- Choose a proper start year by clicking Options under forecast end. We chose 2008 as the start date of the forecast.
- Click Create to continue.
You can see that a new sheet has been created containing a table and a line chart. The blue line indicates historical sales volume and the orange line indicates forecasted sales volume on the chart.
Also read: How to Create and Customize a Line Chart in Excel?
You can adjust the data in the table that is created automatically in the sheet. This is how the data in the table should be arranged for the chart to represent this data appropriately.
3. Customizing series and legends of the chart
To change the color of your legends and series in the chart, follow these steps.
- Double-click on the legends.
- A window on the right opens called Format Legend Entry.
- Click on the paint bucket icon.
- Under Border options, change the color under Color.
Recommended read: How to Edit Legends in Excel?
You can see that our chart series and legend color have been changed.
Conclusion
This article was a detailed guide step-by-step guide on calculating forecasts and representing forecast data visually on a chart in Excel. If you have any doubts regarding forecasts in Excel, make sure to drop a comment below so that we can help you out!
References: Microsoft