If you already have Excel 2016 open, you can view the templates by clicking the File tab, then going to New. You can then see the templates just as you did on the start screen. As you can see, the blank workbook template appears first, followed by a tour of Excel 2016. After this, you can see all the templates.

Excel 2016 has a handful of new functions to help you forecast numbers – typically sales data – more accurately than before. The older FORECAST function still exists for compatibility with worksheets created in older versions, but if you’re creating a new sheet, you’ll want to use one of these FORECAST.LINEAR: creates a straight-line forecast FORECAST.ETS: estimates a trend using seasonality FORECAST.ETS.SEASONALITY: shows the length of a seasonal cycle FORECAST.ETS.CONFINT: the confidence interval of the estimate FORECAST.ETS.STAT: calculates 8 statistical algorithms These work the same in both the Windows and Mac versions of Excel 2016. Once you have the results, you can create a chart in the Windows version that shows the forecasts and expected margins of error. The Forecast Sheet chart isn’t available on the Mac edition of 2016.

If you’d like a video of this article, watch it here: If you’d like to follow along with my file, and extract the workbook. Straight Line Forecasting The workbook has two tabs: straight line and seasonality. Start with the straight line tab. This type of data doesn’t have a cycle, which means the sales don’t depend on the time of year. The sheet has two columns of monthly data: dates and units sold. You need dates and their corresponding numbers for all the forecasting functions. The last date for which we have data is May 2017.

We want to know what the units sold will be for June through December. The syntax of a straight line forcast is: =FORECAST.LINEAR(date to forecast to, range of current sales, range of current dates) On this sheet, the range of current sales is A5:A33 and the current dates are in B5:B33. To make the calculations easier, I gave these range names of dates_sheet_1 and units_sheet_1. You can see all the range names for this workbook by clicking the Name Box in the upper left corner of the sheet. (If you want, click one of the names to select its range.) Click in B34 and calculate the first forecasted units, which is for June 2017. Enter the formula: =FORECAST.LINEAR(A34, units_sheet_1, dates_sheet_1) Hint: you don’t have to type the range names manually. When you start typing a name, Excel displays a list of hints.

Select one: Or press the F3 key on the keyboard (Fn + F3 on the Mac) to display the Paste Name box: a list of all range names on the sheet. Double-click the one you want. (Do it twice when writing this formula: once to insert the units name and once to insert the dates name.) The function’s result should be 773.

Use AutoFill to copy the formula down to the bottom: Seasonal Forecasting How do you forecast data if they are effected by the date? Typical examples are consumer electronics, where sales spike in the 4th quarter of the year, and vacation rentals, where sales spike in the summer. Notice the Seasonality sheet has 3 full years of data, and in each year, the units sold are significantly higher in the 4th quarters.

The seasonality function usually needs 3 years of data for good results. The syntax for the Seasonality forecasting function has the same 3 arguments as the straight-line function, and 3 optional arguments: =FORECAST.ETS(date to forecast to, range of current sales, range of current dates, [number of seasonal data points], [data completion], [aggregation]) ETS stands for Exponential Triple Smooth. Excel estimates the numbers based on trends and seasonality, giving the most weight to recent data, declining exponentially. Optional arguments Number of seasonal date points If number of date points = 0, Excel assumes no seasonality If number of date points = blank, Excel guesses the number of seasonal date points Data completion If you don’t have a sales value for a period, choose 0 or 1 1: default. Excel will fill in the blank by averaging the previous and next values 0: missing values treated as zeros Aggregation If you have multiple values for the same period, what should Excel do? Make a publisher template a color for mac. Options: 1=Average (default) 2=Count 3=Counta 4=Max 5=Median 6=Min 7=Sum As in the previous sheet, I created range names for you to make this easier.