Hi. In this Excel tutorial, we’ll go over how to use the Forecast Sheet in Excel. The Forecast Sheet button became available with Excel
2016 and is also available in Office 365. It helps you do a quick analysis of data on your sheet at, well, the click of a button. Plus, it also
charts what it’s forecasting. You can then use this chart as a visual to help you understand the forecasted results better or as a quick
visual for a presentation. So, let’s go over how to use this feature.
And, by the way, if you enjoy this tutorial, don’t forget to Like the video and subscribe to the channel for even more tips.
You’ll find the Forecast Sheet button in the Data tab, under the Forecast section.
To use the Forecast Sheet tool, you’ll need 2 pieces of information: 1. a range of dates and 2. the data results for those dates. The dates
should be consistent – so, like, a series of days, weeks, months, etc. They should also be in chronological order.
For this example, I’ll be using months. So, let’s highlight our data and select Forecast Sheet.
This creates a line chart showing your results to-date and a short forecast for the upcoming 3 months. You can extend this forecast by using
the Forecast End option and selecting however many more months
you'd like to forecast out for. You can click this button up here to switch to a column chart, if you like. It’s currently projecting future results
for Sales Revenue. This is important to notice because the Forecast Sheet tool automatically defaults to forecasting the right-most data –
which, in this example, is Revenue. It’s displaying what it calculates to be the most likely forecast along with forecasts
for upper confidence and lower confidence bound scenarios.
Or, in plain English, the likeliest, best-case and worst-case scenarios based on the data we have. Click on Options and, here, we can
make some changes to the forecast. So, let's say we instead want to forecast Number of Sales. To do so, come to the Values Range
box and highlight your new data.
If you have a different time range you’d like to forecast, you would update that here in the Timeline Range box. You can change the start
date for your data here and update the confidence interval using the Confidence Interval box. It defaults to 95%, which is
likely the more commonly used, but you can update to a different number if you need to.
This tool is intended to automatically detect seasonality in your data, but you can always update it if your forecasting needs are
different than what you're seeing in the results. To set it manually, you would just count the number of
up / down cycles in your data and then enter that here. The number just needs to be 2 or higher.
If you have missing points in your data, you can come here and select interpolation, which just fills in a likely number, or set it to zero.
If you have multiple values for the same date, you can set here how to treat them. It defaults to
averaging, but you can choose to do something different.
When we click Create, this will create a spreadsheet to go along with our chart. You can select whether to include Forecast
Statistics or not in that new sheet. I'm going to select it just so we can see what they are.
Clicking Create gives us this new sheet with details of the projections along with our chart. Here, to the right, you can see the Forecast
Statistics we selected to show. You can then format this chart just like any other chart in Excel. And, actually, let's move it from here
so that we can see it better. And, like with other charts, you can update the Chart Elements. So, let's say we want to add Data
Labels to this. You can filter data, if you like or edit your design. And, actually, I prefer to use the Chart Styles up here.
There are several designs to choose from. You can change your Chart Type, if you so desire.
So, I hope you enjoyed this tutorial on how use the Forecast Sheet in Microsoft Excel. If you
did, please make sure to Like the video and don’t forget to subscribe to this channel. Thanks!