Microsoft Office 2016: The Complete Guide (2015)

CREATE A FORECAST

TIP: - in the event that you have data that have been stored over a period of time, then it therefore means that it would be beneficial in creating a forecast. Whenever that is carried out, a new worksheet is created with a table of previously recorded data and predicted results and charts depicting this. It is useful especially in the business field as key stakeholders can more or less accurately predict future sales, inventory requirements, and consumer trends.

How to Create a Forecast:?

In a worksheet, enter two data series that are parallel to each other.

A sequence of events with either date or time entries for that specific timeline.

A sequence of events very similar values.

The resulting values will be useful in predicting future dates.

TIP: - the timeline needs coherent periods of time on which to base data points. For instance, monthly intervals with values such as pay day which is the 25th of each month, targeted months of the year or numerical values. Don’t be alarmed if timeline series is missing even up to 30% of data points or has varying numbers with the exact identifying time.

When you select any cell, by default, Excel highlights the residual data.

Click Data> Forecast Sheet.

Located inside the Create Forecast Worksheet box, select either line chart or column chart as the pictorial representation of the Forecast.

In the Forecast End box, choose a preferred date and time then select Create.

If you choose to modify any Advanced Setting, select Options.

Forecast Start- Select the date for the commencement of data. If you select a date prior to the end of the pre-recoded data, only the data which was recorded before the start date will be employed in the prediction.

Confident Intervals- the user here has the opportunity with Confidence Intervals to present or conceal data. The confidence interval is the limit attributed to every predicted value, in which 95% of future points are expected to be actual, according to the forecast providing that normal distribution is in play. This determines how close actual value was to predicted value. A smaller interval implies more confidence in the prediction for the specific point. The default value can be adjusted, which means it can go above or beyond the default value.

Seasonality- This is the number attributed to the length of the seasonal pattern and is instantly detected. For example, a monthly pay cycle is 30 days, you can set each point using a weekday seasonality which would be 30. You can exercise greater control and choose Set Manually then select a more sited number.

Timeline Range- This is where you can modify timeline ranges to suit your needs. This needs to be in parallel with Value Ranges.

Values Ranges- Change the range used for value series here.

Fill in Missing Points Using-to more or less accurately determine missing pints, interpolation is used. The missing point will be completed as the weighted average of its neighboring points as long as fewer than 30 points are missing.

Duplicate Aggregates Using- when data has varying value with exact time stamps. Excel will calculate the mean of the numbers. Other operations such as Median are available as other calculation methods.

Include Forecast Statistics- Select this box if you want to have greater access to even more statistical information on the forecast within a new worksheet. This results in a table of statistics being generated using the FORECASTS.ETS.STA. Function and includes measures such as smoothing coefficient (Alpha, Beta, Gamma) and error metrics (MASE, SMAPE, MAE, RMSE).

Formulas used in forecasting data

Whenever you use a formula to create a forecast which in most cases is very important, it is presented in a table with the past and expected data, and a chart. The forecast expects potential values with the use of your current time-based data and the AAA version of the Exponential Smoothing (ETS) algorithm.

The table can contain the following columns, three of which are calculated columns:

•A column using time as its classifying key (series of event classified by time.)

•A column for values recorded in the past (your corresponding values data series)

•Prediction of values column (calculated using FORECAST.ETS)

•Two columns which signify how likely it is that these values will actually be or closely resemble expected values.