- In a worksheet, enter two data series that correspond to each other: …
- Select both data series. …
- On the Data tab, in the Forecast group, click Forecast Sheet.
- In the Create Forecast Worksheet box, pick either a line chart or a column chart for the visual representation of the forecast.
The forecasts we make can have a significant impact on our business decisions. Excel is an incredibly powerful tool when it comes to forecasting, and the data that you can use to create accurate forecasts is virtually unlimited. With Excel, you can easily create a forecast for sales, budgets, expenses, and even future trends. In this blog post, we’ll explore how to use Excel to create a forecast and discuss the advantages of using Excel for forecasting. We’ll also provide you with a step-by-step walkthrough of the process, so you can easily apply these techniques to your own data. Excel’s forecasting capabilities are incredibly useful, and following the tips outlined in this blog post will help you get the most out of them.
The Excel FORECAST Function
How to use FORECAST in Excel
Heres how to use the FORECAST function in Excel:
1. Create two columns
Two columns in a spreadsheet should be named after the variables in your data. Fill in the first column with the x values, or independent variables. These values are typically dates in the form of days, months, quarters, or years for forecasting purposes. Put the dependent variables’ y values in the second column. Each value in the y direction corresponds to a value in the first column. Take this data set, which shows the annual value of a home, as an illustration.
A
BC1
Year**Home Value**2
2016$304,5543
2017$320,8524
2018$321,4465
2019$319,7926
2020$325,6377
2021$327,4888
Forecast:
2025
2. Choose a date that you want to forecast
The following step is to select the date you want to forecast. In the cell below the last cell in the first column, you can enter the date of your choice. If you wanted to know the home’s projected value in 2025 in the example above, you would enter 2025 in cell B8.
3. Choose a FORECAST function
Choose the cell that is adjacent to the cell containing the date you want to project. In the example above, it would be cell C8. On your keyboard, type “=FORECAST,” pick a feature, and press “Enter.” Choose “FORECAST” if you are unsure of the nature of your function. You can select one of the specific types if you are aware of the kind of function you possess. For instance, you could pick “FORECAST. LINEAR” if you have a linear function or “FORECAST. ETS” if you have an exponential function.
4. Enter the FORECAST arguments
The FORECAST function has three arguments. By selecting the cell that contains the x value you want to forecast, you can enter the first argument. You could select cell B8 in the preceding illustration to make it the first argument by clicking on it. Add a comma and highlight the range of cells that contain the known y values after the first argument. This second argument should be followed by a comma, and the range of cells containing the known x values should be highlighted. Add a closing parenthesis. The following would be the function in cell C8 of the preceding example:
“=FORECAST(B8, C2:C7, B2:B7)”
5. Press “Enter”
After entering the three arguments, press “Enter” on your keyboard. The function predicts the value of the given y value based on the data you entered for the x value. The function would display the anticipated home value in 2025 in the aforementioned example.
Why create a forecast in Excel?
A forecast in Excel is helpful in many professions because it enables you to forecast future performance based on current data. To forecast the future value of a home, for instance, a realtor might use forecasting. They can use this knowledge to plan their selling strategies and target the right buyers. Other professionals that may find forecasting useful are financial analysts. They can forecast a company’s future performance using its sales data, and then use the results to hire more staff, make budgets, and upgrade infrastructure.
How to create a forecast graph using the “Trendline” feature
Here’s how to use the “Trendline” feature to create a forecast graph:
1. Insert a basic line chart
Make two columns: one for the x values of your data and the other for its y values. Dates are typically the x values because they are the independent variables, and the y values represent how well those dates performed. Enter this information in the columns, then select the “Insert” tab and “Charts” group. Select “Line” under “2-D Line” by hovering your cursor over the “Insert Line or Area Chart” icon. “.
2. Choose the appropriate trendline option
Right-click on the chart and click “Add Trendline. Check the box next to “Display R-squared value on chart” in the menu on the right. This causes the coefficient of determination, a number between zero and one that represents the precision of a trendline, to appear on the chart. View each trendline’s R-squared or coefficient of determination values by clicking on it. Choose the trendline with the R-squared value that is closest to 1.
3. Designating a forecast period
Choose “Forecast” from the same menu on the right. By specifying the number of periods, you can tell the trendline how far forward or backward it should move. Consider, for illustration, a data set with a y-value for each day. You could enter 30 in the field next to “Forward” if you want the trendline to display predictions one month from now. “Shut the right-hand menu and view your forecast graph.
How to create a graph using the “Forecast Sheet” feature
The “Forecast Sheet” feature can be used to make a graph as shown below:
1. Enter your data in a spreadsheet
Open your spreadsheet and enter your data. Making two columns for the x values and y values is ideal. Click “Forecast Sheet” in the “Forecast” group by opening the “Data” tab.
2. Designate the forecast period
The calendar icon next to “Forecast End” in the “Create Forecast Worksheet” dialog box should be clicked. The left and right arrows at the top of the calendar can be used to navigate through the months. Select the desired end date for the forecast period.
3. Adjust “Options”
Near the bottom of the “Create Forecast Worksheet” dialog box, click “Options.” Here are some useful settings you may want to adjust:
4. Click “Create”
After completing the settings, click “Create” in the dialog box’s lower right corner. The result of this action is the creation of a new sheet with a trendline graph, a table with the previous data, and new predictions. The trendline graph automatically updates if any values in this table are changed.
FAQ
What is the formula for forecast?
The formula is as follows: previous month’s sales x velocity = additional sales; subsequent month’s forecasted sales are calculated by adding the additional sales to the previous month’s rate.
Which forecast function should I use in Excel?
ETS function. The FORECAST. Forecasts using exponential smoothing are made using the ETS function and are based on a number of current values.
Is Excel Good for forecasting?
For instance, they could be daily, monthly, or yearly data. Excel is excellent for getting started with forecasting because it automates many processes, such as identifying the seasonality cycle. The seasonality and other inputs can be entered manually, but only if you are an expert forecaster and businessperson.
