How do you use the EXCEL2016 Forecast worksheet forecast?

Excel 2016 has a function called "forecast Worksheet", which can analyze the future trend of things from historical data, and show them in the form of graphs, which is helpful to visually observe the development direction or development trend of things.

Note: This experience is based on the number of experiences a writer has completed each month as raw data and, of course, can be converted into sales figures to make predictions more meaningful.

1, for example, a writer in Baidu experience published experience in the figures. Data starts from January 1, 2014 and ends on April 1, 2016.

2, make sure the active cell is in the datasheet, and then click the Data tab--The forecast group--the Prediction sheet button.

3, pop-up the Create Forecast Worksheet wizard window. The window presents us with a chart of historical data and future predictions, where the blue polyline is the historical data and the Orange polyline is the future prediction data. As you can see, the orange polylines that represent future prediction data are basically flat (Linear or linear) and are not correctly predicted.

4, this situation occurs because the correct "seasonal" parameter is not set. We click the Options button in the lower-left corner of the wizard window to see more parameters for the forecast.

5. Select the "forecast End" date to "2017/4/1", change "season setting" from "Automatic Detection" to "manual setting" and set its value to "12". The reason for this change is that our original data is the number of experiences published by the user each month, starting in January 2014, ending in April 2016, each cycle is 12 months, and starting from the last data (April 2016) and predicting the data within 1 years.

6, click on the "OK" button in the wizard window, Excel will insert a new worksheet, the table contains the original data, as well as "trend forecast", "confidence lower limit", "confidence limit" three column prediction data, of course, also includes a forecast chart. The chart can be seen intuitively, according to 2014, 2015 two years of data, the experience of the author of May, October is the peak of experience creation, because these two months have holidays, basically determined that the author is a otaku.

7. To learn more about the operating mechanism of the Excel Data Prediction worksheet, let's take a closer look at the other options. In addition to the predicted end and season settings mentioned above, there are several key parameters to Excel's forecast worksheet:

Forecast start: Which period of data in the historical data begins to predict.

Confidence interval: Sets the upper and lower limits of the forecast value; the smaller the value, the smaller the range between the upper and lower limits.

The missing interpolation points are filled in the following ways: By default, interpolation is based on the weighted average value of the data, or it can be set to "0", that is, no missing value interpolation calculation.

Aggregate duplicates using the following methods: In the case of data from this experience, if there are several data in April 2015, Excel will "aggregate" multiple values within one months when calculating the forecast value, including the average (default), COUNT, max/min/median value, and so on.

8, with the above mentioned "prediction start" as an example, if the historical data periodicity is strong, you can set the start of the forecast time earlier than the last historical data, this can detect the accuracy of the prediction. The period of advance to the end of the cycle, such as the last issue of the second year, also helps to improve the accuracy of the projections.

As pictured, we set the "prediction Start" on December 1, 2015, which is the last issue of the 2nd data cycle.

As you can see, Excel predicts the data (orange polyline), with the actual existence of 2016 years 4 months of data (blue polyline), basically consistent, have a certain predictive accuracy.

9, Excel predicts the function of the worksheet, is based on a call FORECAST. ETS functions, you can learn more about this function through Baidu information.

Note: The more cycles of historical data, the higher the accuracy of predictions.