Speaker 1: If your profession is in finance, or you're planning to work as a financial analyst, then creating forecasts and budgets is a constant exercise. Now usually this involves some form of predicting a future value based on existing historical values. So it could be sales, manufacturing, quantities, or other KPIs and trends. In today's video, I'm going to show you a great feature in Excel that can help you create these forecasts with just a few clicks. I'll be calculating a quick sales forecast for Home Depot. Sound good? Then let's get doing. Here's my data set. It's Home Depot's quarterly sales from 2012 to 2021. They have a fiscal year that ends on January 31st. I added the quarter numbers in column A just to make it easier for us. Now our task is to create a sales forecast for the upcoming quarters. When you get a data set like this, it's a good idea to quickly plot this on a line chart just to get a better overview. So let's highlight the date and the sales values, go to insert and insert a line chart. Notice that there are lots of peaks and valleys. So there's definitely some seasonality involved. We can't really use some linear trend to predict the next quarters. We need a solution that takes the cyclicality of the historical data into consideration. So this is where the forecast sheet in Excel comes into play. It's available from Excel 2016 onwards, and this is how it works. First, highlight the historical values again. So I'm going to highlight the date and the sales columns, then go to data. Here on the forecast section, click on forecast sheet. This plots our data on a line chart. Now the blue line is our actual data and the orange lines here are the predictions. The middle one is the forecast and the thinner orange lines are the lower and the upper confidence bounds. So if your confidence interval is 95%, the 95% of the future data points are going to fall between these two lines. With forecast end, you can select a different end date for your forecast. You can just expand the options and adjust your selection. In the more options here, we get to decide when the forecast starts. Now I'm going to pick a date that's before the last actual data point. This way I get a better idea of the forecasting accuracy because I can compare the forecast with the actual. So I'm going to change this to January 31st, 2021. The confidence interval indicates the range that's likely to contain your estimates. So the default value is 95% and I'm just going to go with that. For seasonality, you have a choice to let Excel detect it automatically or enter it yourself. So by looking at the forecast in the chart, I can already see that it did pick up the seasonality in the data. So we're going to leave it on automatic. If the algorithm wasn't able to establish a pattern, you're going to need to enter it manually. So for instance, in my case, I would set it to four because for my data here, one season consists of four quarters. In this case, it works. I'm going to go back to detect manually. Timeline and the values range is already picked up because I highlighted that range before I opened the forecast sheet. In case you need to make adjustments, you can do it manually here. I'm also going to leave interpolation and average as the default values. Now let's take a look at what happens when I click on create. I'm going to get a new sheet inserted here with my historical values and my forecast values on the bottom. I also get the chart that we saw before. So if I zoom out, we can see that chart is inserted here and it's referencing the series on this table. So if you want, you can make adjustments to this data and it's going to automatically adjust your chart. Now let's scroll down and see the forecast values that we got. When we take a look at the values for the last two quarters for which we actually had data for, we can see that the forecast came in too low. So if I just move this out of the way, here's the lower bound and this is the upper bound. So now if I was going to predict the next two quarters, I'm probably going to consider a sales value that's between the forecast value and the upper confidence bound. Now when I click in this cell for forecast, we can see that Excel has automatically created these formulas with the forecast.ets function. So this function is used to predict future values by using an exponential triple smoothing algorithm. So without knowing much about statistics or function syntax, we were able to create the seasonal forecast. So as you can see, it's quite easy to create forecasts based on seasonal historical data. All you need is a timeline with data points that have consistent steps between them. And this can be months, it can be quarters or years. And the data doesn't have to be perfect. The function can handle up to 30% of missing data and it's going to automatically make adjustments for it. I hope this is going to be helpful for you when you have to do your next forecast. Please give this video a thumbs up if you liked it and do subscribe if you haven't already done so. Many thanks for watching and I'm going to see you in the next video.