Forecast Sheet In Excel 2016

Definition of Forecast: To predict (a future condition or occurrence); calculate in advance:

If we have data based on time, we can create a Forecast Sheet in Excel 2016. In order to create a Forecast in Excel we must have two data series that correspond to each other:

  • A series with time or date entries for the timeline
  • A Series with corresponding values

When we create a Forecast, Excel creates a new worksheet that contains a table of the historical and predicted values and also a chart that expresses the data. With the Forecast, it can help us to predict future sales, consumer trends and etc.

As mentioned above we must have filled in the necessary data in order to create our Forecast Sheet, as shown in the image below. Once created we must select the data.

Excel Table

Then we must select the Data tab and from the right area of the ribbon we locate the area Forecast and select the Forecast Sheet button as shown in the image below.

Data - Forecast Sheet

Once the Forecast Sheet command is selected, a new window appears named Create Forecast Worksheet as we can see below. In this window we are prompted to Use Historical Data To Create A Visual Forecast Worksheet. At the right corner of the window we can select to visualize our Chart either with the Line or Column Type Chart. All we have to do is to click on the desired one. At the bottom of the window and under the Chart, we can change the Forecast End button by clicking on the Calendar button or we can select the Options button in order for us to view more commands.

Create Forecast Sheet

Once we select the Options command more commands will appear that are available for us to use:

  • Forecast Start: We Can Select Where To Start Our Forecast
  • Confidence Interval: Is A Type Of Interval Estimate Of A Population Parameter
  • Seasonality: Indicates The Number Of Points In A Recurring Seasonal Pattern
  •      Detect Automatically
  •      Set Manually
  • Include Forecast Statistics: Includes A Table Of Forecast Accuracy Metrics And Smoothing Factors
  • Timeline Range: The Historical Timeline Used To Calculate The Forecast
  • Values Range: The Historical Values Used To Calculate The Forecast
  • Fill Missing Points Using
  • Aggregate Duplicates Using

Once we have finished all the adjustments needed we just press the Create button in order for the Forecast Sheet to be created.

Create Forecast Sheet

Immediately, a new sheet is created where a pop-up dialog box appears notifying us with the following:

Forecast Sheet:

  • The table contains a copy of your data with additional forecasted values at the end.
  • You can manually edit the forecasting formulas in this sheet, or return to your original data to create a different forecast worksheet

Forecast Chart

In the image below, we can see the Forecast Chart that has been created among with a Table of Data that contains Forecast (Sales), Lower Confidence Bound (Sales) and Upper Confidence Bound (Sales). We can analyze the Forecast Sheet depending on the Data that it was created with, or we can create a New Forecast Sheet.

Forecast Chart

So, the procedure of creating a Forecast Sheet is quite simple and easy. It all depends on the Data that we insert in our Worksheet in order for it to be created.

Below you can check out the video describing the procedure on how to create a Forecast Sheet in Excel 2016.

About officesmart

MVP: Honored with the MVP (Most Valuable Professional) for OFFICE SYSTEM title for the years 2011, 2012, 2013, 2014 and 2015 by Microsoft, for my contribution and commitment to the technical communities worldwide. Microsoft Master Specialist: This certification provides skill-verification tools that not only help assess a person's skills in using Microsoft Office programs but also the ability to quickly complete on-the-job tasks across multiple programs in the Microsoft Office system

Posted on June 1, 2016, in Excel 365 English, Microsoft Office 365 ProPlus English and tagged , , , , , , , , , , , , , , . Bookmark the permalink. Comments Off on Forecast Sheet In Excel 2016.

Comments are closed.