FORECAST.LINEAR & FORECAST.ETS Excel 2016 Functions

Excel 2016 includes 5 new forecast functions.

FORECAST.LINEAR

This function predicts a y value (eg sales) for a given x value (eg a forecast date). You need to provide known y’s and known x’s which could be for example sales and sales date respectively.

=FORECAST.LINEAR(x, known_y’s, known_x’s)

The forecast is predicted on a linear basis and is therefore not particularly useful if your data follows seasonal variances.

FORECAST.ETS

This function predicts a future value based on historical values. It uses the Exponential Triple Smoothing (ETS) algorithm, which:

estimates trends
estimates seasonality
gives most weight to recent data points (declining exponentially)

=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

Target_date – the date for which you want to predict a value.

Values – the historical values – for example past sales

Timeline – the range of cells that show corresponding dates for your values. The dates in the timeline must have a consistent step between them eg month, day, year – you can’t mix the steps between the dates.

Seasonality – (Optional). If not specified Excel will automatically detect seasonality. 0 would indicate that there is no seasonality, meaning the prediction will be linear. Seasonality (the length of the season) can be specified with a positive whole number (number of date periods).

Data completion – (Optional). FORECAST.ETS will automatically adjust for up to 30% missing data. Enter 1 or leave omitted to calculate an average of neighbouring values. 0 to treat missing points as zeros.

Aggregation – (Optional). FORECAST.ETS will aggregate multiple points which have the same time stamp. Enter 0 or leave omitted to use AVERAGE, or specify one of the following: SUM, COUNT, COUNTA, MIN, MAX, MEDIAN.

FORECAST.ETS.SEASONALITY

Can be used to identify which automatic seasonality was detected and used in FORECAST.ETS.

=FORECAST.ETS.SEASONALITY(values, timeline, [data_completion], [aggregation])

FORECAST.ETS.CONFINT

Returns a confidence interval (how close to the predicted value the forecast is likely to be). The lower the confidence interval the more accurate the forecast. So a confidence interval of 5 would indicate the forecast value could be 5 points above or below the forecast value on a particular date. The confidence interval is linked to the confidence level which is automatically set at 95%. By stating a confidence of level of 95% we are saying that 95% of future date points will lie within upper and lower bounds of the confidence interval.

=FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])

Confidence_level – (Optional). The default is 95%. A numerical value between 0 and 1 (exclusive), can be used to indicate another confidence level.

FORECAST.ETS.STAT

Returns a statistical value relating to forecasting.

Statistic type indicates which statistic is requested by this function.

=FORECAST.ETS.STAT(values, timeline, statistic_type, [seasonality], [data_completion], [aggregation])

See Microsoft’s page on this function.

https://support.office.com/en-nz/article/Forecasting-functions-897a2fe9-6595-4680-a0b0-93e0308d5f6e#_FORECAST.ETS.SEASONALITY

Check out our video on the new forecast functions. Download the featured file here.

ON-SITE OR ONLINE TRAINING
info@bluepecan.co.uk
0800 612 4105

          
   Tina McGrath 
 
 
 
   Chester was a great trainer, I personally learnt so much in one day and it was all down to his calm, friendly demeanour and huge knowledge - we covered many areas which will help us to develop ourselves and the work we do.  Thank you Chester, was lovely to meet you :-)
  
 
 
       
   emmie BL 
 
 
 
   Myself and my team had an absolutely fab day with Chester going over excel - it was quite revelatory, with lots of really helpful hints & tips, great clear explanations and plenty of time to ask questions. Being able to work on things in real time, use our own data spreadsheets and his practice ones and have things properly explained has been so useful. Chester's a great instructor/facilitator - pitches to your needs and is very flexible. I would completely recommend him if you're needing to get a grip on all things excel!
Thanks very much, Chester!
  
 
 
       
   Martyn Lee 
 
 
 
   Chester was a great tutor understanding our needs and allowing time to understand and digest. Great humor too which always lightens a training day. Look forward to another session soon.
  
 
 
       
   Angus Brown 
 
 
 
   Detailed, informative and fun, Chester made the class enjoyable and engaging - i have learnt a huge amount from the time we had together
  
 
 
       
   Sisii de Winter 
 
 
 
   We booked Chester for several in-house training sessions including Fundamentals, Intermediate and Advanced Excel. He was brilliant with each, very inclusive and patient with all team members. Would fully recommend.
  
 
 
       
   Lucy Maxwell Scott 
 
 
 
   A great online one on one intermediate training session in excel - the tools and activities Chester has developed and used are great.  His teaching style was highly responsive to my needs (applications I wanted, my learning style).  I would strongly recommend him - whatever your level of excel expertise and whatever level you are aiming for!
  
 
 
       
   Louise Walpole 
 
 
 
   The online Excel Intermediate course I attended was really high quality. It was pitched at the right level with opportunity to work through examples as part of the course. Course material and follow up support has been excellent. I would highly recommend.
  
 
 
       
   Savannah Baldry 
 
 
 
   Excellent training with Chester who's both knowledgeable and a great teacher. He takes the time to make sure you've understood before moving on and I was lucky enough to get some bespoke training specific to my needs. Thanks very much
  
 
 
       
   ECA Account 
 
 
 
   I had an intermediate/bespoke excel course with Chester back last year, and enjoyed it so much and thought so highly of the course, I subsequently arranged beginners, fundamentals, intermediate excel courses and Microsoft PP/word courses for my colleagues. Even in a virtual setting Chester is clear in his delivery and guidance, exceptionally knowledgeable, and very adaptable to ever changing attendee lists or capabilities and requirements within the team. It will be great to put our new skills to good use. My advise to people, would be to take notes!

Thanks Chester! Kathryn P.
  
 
 
       
   Roofing Contractors Cambridge Ltd 
 
 
 
   An employee recently completed an online days training with Chester. He thoroughly enjoyed it and had a great learning experience. Fully recommend Blue Pecan Computer Training and would definitely use them again for future training.
  
 
 
  
  Load more