Forecasting in Excel 2016

Forecasting is important in many circumstances to be able to do effective and efficient planning.

The future electricity needs forecast vital to planning building more power stations; scheduling employees in a call centre next week requires forecasts of call volume; just imaging how much forecasting the planners behind London 2012 Olympic Games must have done. Forecasts can be needed months or years in advance, or only a few minutes beforehand. Whatever the situations or time horizons involved, accurate forecasts are vital part of preparation and planning.

A key step in forecasting is knowing if something can be forecast accurately, or if it is better just tossing a coin. Good forecasts capture the authentic patterns and relationships which exist in the historical data, but do not replicate events from the past that will not happen again.

In all businesses where data are collected and employees make use of the data the capability to forecast may be required. It doesn’t really matter if it is sales figures, expenses, man-hours, growth, market shares etc. to create a budget you need to forecast.

WhatIf Analysis

Excel offer a lot of tools to do accurate forecasts based on historical data and analysis tools as the What If Analysis tools to help predict the future.

Some of the useful forecasting tools in Excel are:

  • For trend analysis you can use the Trend function and to visualize and calculate trends, Excel charts can show trends and the equation for the trend. You can calculate the accuracy of the trend or Excel can provide you with this information in a chart. The Trend function will only return a accurate result if you work with linear data.

forecast trend

  • For forecasting of linear data you can use the Forecast function. The Forecast function can forecast any number of periods into the future.
  • If you work with exponential data you can use the Growth function to forecast and an exponential trends can also be visualized in a Excel chart.

forecast exponential

  • the Solver tool in Excel can be a huge help if you want to forecast seasonal data.
  • The Analysis Toolpak provide us with Exponential Smoothing, Moving Average and Regression which are all tools we need for creating the right forecast model.
  • The Scenario Manager and the Goal Seek tools can be a great help to get a accurate forecast.
  • Excel also has all the options and functions to measure the accuracy of our forecast to make it possible to continuously develop our forecast model to get a very precise forecast.

Forecasting Chart

In Excel 2016 Microsoft has made it much easier to forecast. Microsoft has added the Forecast Sheet tool to Excel. This tool can give you a forecast in few seconds.

In Excel 2016, select your two corresponding sets of data and go to “Data>Forecast>Forecast Sheet”. In “Create Forecast Worksheet” box, choose either a line chart or column chart, pick an end date, and then click “Create”.

Forecast Sheet dialog

For more advanced options click “Options” in “Create Forecast Worksheet”. Here you can set “Confidence Interval” and set how you want Excel to handle seasonality in your data. You can include statistics in your forecast worksheet and change the input range if needed.

Forecast Sheet expanded dialog

In the “Fill Missing Points Using” list, you can tell Excel how to handle missing data. “Interpolation” will calculate missing data.

In the “Aggregate Duplicates Using” list, select how you want Excel to calculate duplicate entries.

 forecastETS function

Best STL offer a Forecasting & Data analysis course where all the different methods & models are explored

Excel is an amazing tool also when you need to forecast.

Charts from a dropdown list

Charts from a dropdown list in Excel

Many people who use Excel can generate a chart or two on their spreadsheets. This is a relatively easy task to perform in Excel. These charts will either sit in data sheets or in their own sheets.

Don’t you sometimes wish you had a button to click in Excel which gives you a list of charts to display on your sheet? Well, I can show you how to do this!

First of all, you will need some charts. It doesn’t matter which types of charts they are. The screenshot below shows an example of a sheet containing data and charts:

Charts from a dropdown list

Once you have your charts up and running, you can begin to build your interactive button. The first thing you need to do is to select the cells which are behind the first chart, as shown below.

Charts from a dropdown list
Sales+Costs Chart

After doing this, click in the Name box (top left next to the Formula bar). In here, give this selection of cells a name, for instance Sales. Do the same for all the other charts you wish to add to the list. These names will be used later to choose the right chart from the list. Now, in a different place on the sheet, or in another sheet, type a list of your chart names you want to display in the dropdown box (see my example below ).

Charts from a dropdown list

Select this list and give it a name (in the Name box again), as shown above. This name will be used to generate the dropdown list. We are now ready to create the dropdown button. Add a new sheet to the workbook and name it Output. Activate the Developer tab in the ribbon. Click File – Options – Customize Ribbon, then tick the box next to Developer in the right hand side box. Select the Developer tab in the ribbon, then click the Insert button.

Charts from a dropdown list

This opens a toolbox from which you select the Combo Box item. Now, in the Output sheet, draw the Combo Box near the top of your sheet with your mouse. Afterwards, press Ctrl+1 (format control). A dialogue box opens as shown below:

Charts from a dropdown list

Click in the Input range box and then type the name which you gave earlier to your list of charts  (I called mine ChartTypes). Next, click in the Cell link box, then click in any empty cell in your spreadsheet. This cell reference now sits in the Cell link box. We shall use this cell later to pick a chart.

The next thing we need to do is to define a name which uses Excel’s Choose function to pick the correct chart from the list.

Step 1: Select the Formulas tab, then select Define Name.

Step 2: Type SelChart into the Name box. (SelChart is my example)

Step 3: Type the following into the Refers To box:    =choose(                                                            Now select the cell you selected for the Cell link box earlier. Next, type the names you gave your charts in the Name box.

=choose(selected_cell,Name1,Name2,Name3)  (selected_cell is the cell you used in the Cell link box earlier. The other names are the names you gave the cell ranges behind your charts.)

Step 4: Click OK.

Almost there! We now need to again select the cells behind each chart, (one chart at a time), then right-click the selection. In the dialogue box, click Copy, then go to your Output sheet. Right-click below your dropdown combo box and select Paste Special – Linked Picture. (See below)

Charts from a dropdown list
Paste Special

Hopefully, you now have an image of your chart in that location on your sheet. Click in the formula bar and type =SelChart (again, this is my example). Press Enter. Do this for all your charts. Now you can create Charts from a dropdown list.