How to Forecast Data in Excel 2016

Within the data tab in Excel,  there is a forecast sheet button where you can select a time series and forecast out what the projection will look like. This is perfect if you are looking to make a quick forecast for your team, or you are curious about how you are trending with your own goals. The best part of this button is that the calculations are automatically done for you, so you don’t have to do any math or calculations that are normally associated to a task like this. Excel will give the projected numbers by the day, month, or year depending on the given data that you present. Just like any confidence rating, Excel will break down the low, the high, and the expected results with a confidence rating of 95%. Let’s get started!

 

Supply the Data that you Want to Forecast

 

For this example, I want to trend out what my organic session growth will be for TM Blast. Within Google Analytics, I downloaded all of my organic traffic from the start of the blog to the end of October 2016. Once the data is all in an order that I would like to trend, I am going to select everything and forecast it out. With the data selected, I am going to go to Data –> Forecast Sheet. With this selected, I am brought to a screen like this below. This is where it comes to the customization of what you want the forecast to look like. You can change the forecast from a line graph to a bar graph, or even change the forecast date to. As a note, you can change the graph at anytime, so you don’t have to make this decision right here. Also, you can manually drag down the data in the forecast sheet for when you wanted to break out more numbers. Here is an example of what my organic traffic looks like.

 

how to forecast quickly in excel for office 365

 

As a note, you need to manually check on the box that says include forecast statistics to see the projections if you want to see those numbers in another sheet.

 

Forecasted Sheet

 

Right after you click on create, Excel will put those projected totals in another sheet for you. You can click into the projected number columns to see the math that went into the calculations for the low, high, and the expected forecast for your numbers. As I mentioned above, you can select the row of data from above and drag down all the columns. Excel will automatically give the calculations for you and you can see more dates trended out. By doing that, I found out that I am projected to hit 2 thousand organic visits a month by August of 2017 which is both really cool and also kind of depressing as I originally wanted to hit this by January 2017. However, this is a projected tool so there are things that I can do to hit this goal earlier, but at least this gives me a good baseline.

 

forecasted numbers with calculations in excel for office 365

 

Conclusion

 

Before Excel 2016 for Office 365, you had to use the forecast formula to perform a forecast and trend out data which took up time. With this version, Excel uses something called ETS (Exponential Smoothing) which detects seasonality and confidence intervals.

 

As another note, this is a forecast tool and needs to be presented as such. This tool within Excel should be more of a guide not a true number prediction. Based on the data that you have, your file might be very large. Here are the steps to make excel smaller and run faster for you.

Leave a Reply

Your email address will not be published. Required fields are marked *