Discover the power of our pipeline tracking spreadsheet for accounting

How to create outlook signature

If your profession is in finance or you're  planning to work as a financial analyst,   then creating forecasts and budget 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. But 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 open 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 the 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 the 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.