Speaker 1: Hi everyone, my name is Kevin. Today I want to show you how you can do forecasting in Microsoft Excel. And as full disclosure before we jump into this, I work at Microsoft as a full-time employee. So why would you ever want to do forecasting in Microsoft Excel and what does that even mean? Well, you might have a whole bunch of data. Let's say you work at say a workplace or maybe you have a YouTube channel and you have a bunch of data. You could look at how you've performed in the past and you could use that to predict or forecast what the future might look like. All right, well why don't we jump on Excel and I'll show you step by step how you could do this. It's a neat thing to learn how to do. All right, here I am on my PC and what I've done is we're going to have some fun here and what I've done is I've downloaded my views on YouTube through the last year. So this goes back to May 7th, 2019 all the way up to May 5th, 2020 and you can see how my views have changed over time. And so let's say for example I want to forecast, well hey, what will my views look like in 30 days or 90 days or maybe even a year from now? What will things look like? And that's where forecasting comes into play. So here I can see that last year I was at 5,000 views a day and then here I'm at about 111,000 views per day. So based on this growth, can Excel help me forecast what the future looks like? And the answer is yes, Excel can do that. So how do we do this? Well, I'm going to show two different methods or two different techniques that we could use to forecast. The first one is pretty simple and this is one that I've used for a long time. What we're going to do is we're going to go up onto the pivots up here and we're going to click on insert. And on insert what we want to do is we want to insert a line chart. And so I'm just going to insert a very simple 2D line chart and let's go ahead and throw this in. So now this gives me a nice visualization of what my views have looked like over time. So you know it started out there was some growth, a little bit of growth, and then especially recently it's grown quite a bit more. And so this is what the past year looks like for me. And now to be able to forecast into the future, what does the future hold? What I can do is I'm going to just click on this line with my left mouse button and now I'm going to right click on it. And what I can do here is I can add something called a trend line. So let's go ahead and throw that in. And within trend line I have a whole bunch of different options. We're going to walk through what these all different mean, what all these different options mean, and which ones you should use. So one of the things is you'll see this trend line here and the trend line tries to match my data as closely as possible. So right now the default is just a linear line or trend line. And so linear line is just a straight line and this is the best attempt at matching the data. One of the things that you'll see though is it doesn't perfectly match the data. Here it's a little above and then more recently it's below. So this probably doesn't reflect the growth accurately. And so I might want to try some of these other trend lines. So here I can look at an exponential growth line and this one matches the data much more closely. Here it's pretty much identical. Up until recently now it's a little below, but this one matches the data a lot better. And so perhaps on YouTube growth is more exponential. As you grow, as you get more videos, the growth tends to be more exponential in nature. But what I can also do is I can click into different trend line options and see which ones match my data. So here's a logarithmic. I could also look at polynomial and then you could adjust the numbers here. So this one here, this is close to the data performance. So I could go through here. I could also look at power and then you could also do a moving average. So here I could say, you know, hey give me a moving average say over a 20-day period. So this is different ways I could look at my data. What I think probably at least going through these different ones, the one that appeared to match my data the best was this exponential line. So I'll go ahead and choose this. Now some of the things that I can also look at is there's something called an r-squared value. I'm going to go ahead and click on this. And what that stands for, this is the coefficient of determination and it's a value between zero and one. In essence, if it's closer to zero, that means the line doesn't match your data that well. The closer to one you are, the more closely that the line matches your data. So here if I go back to the trend line and I can click through these different options. So here with a linear line it's a 0.7. So really it doesn't match the data as well. And I could go through and I could see how it matches the data. But here exponential seems like the best match. So I'm going to go ahead and choose that one. So now I mentioned that we want to do forecasting. And so to forecast what I want to do is I'm going to scroll down here and here you see a section called forecast. And so you could forecast both forward and backward. And what I want to do is I want to forecast into the future to see what things might look like. And now it says zero periods. What is a period? Well my data in the spreadsheet, I go day by day by day. And so a period then, if I click back in, one period would be one day. So here I'm forecasting one day into the future. Here I could forecast 30 days into the future. So let's say a month into the future. And then maybe I want to even say, let's say 90 days into the future. So about three months. And then maybe let's say half a year from now. So about 180 days from now. What this will do is it shows me approximately, assuming that I do have exponential growth on the channel, this will show approximately where I would be in about a half a year. So here it's predicting about 300,000 views if I look at it here. Now one of the big questions is, is it exponential growth or does it actually tend to be more linear? If it's more linear, I'll be at about 100,000 or so. So it really depends on what type of trend line best matches your data. And one of the things that's also interesting, so here I'm forecasting forward. What I can also do is I can display an equation on the chart. And so here's my equation and the X value. So once again, a little refresher course back from math class. This is the X axis going across here, and this is the Y axis. That's the vertical line. And so if I want to figure out what value I'll be at, I could insert the day in this example as the X value. And that'll tell me the Y, how many views I will have. So that's another way where you can get the equation. And then you could simply type in your value and it'll tell you where you'll be at that point in time. Or just as a simpler approach, you could simply type your number in here. So let's say a year from now, where am I going to be? And here it's saying at about 1.2 million. Although given that I've only seen this level of growth going another year in the future, it probably has a lot of uncertainty. So that's one just quick way how you could add forecasting into charts in Microsoft Excel. And now I also want to show you another way to do forecasting in Microsoft Excel. So I'm going to go ahead, let's delete this for now. Here's my data. And what Excel has is they have a built in dedicated formula or a forecasting view. And the way we're going to get to that is we're going to go up, back up to these pivots across the top, and we're going to click into data. So let's click on data. And then across the top here, one of the views here, one of the options on the ribbon is called forecast sheet. So Excel has an entire sheet that'll help you forecast into the future. So let's go ahead and click on this. And this brings up a dialog now where it says create a forecast worksheet. And what it'll do is it'll use historical data to create a visual forecast worksheet. And so here's all my historical data of my views over time. And what it shows me then is it shows me the forecasted value. Here is this orange line. And then it also has an upper bound and a lower bound. So with 95% confidence or 95% of the time, it's expected that the value would fall within this range. So something that I could see down here, here I could see the forecast end. So here's forecasting through August 5th. So we're going about three months into the future. But I can also say, hey, by the end of the year, where do I expect to be? So I'm going to go ahead and change the date. And so here's another view of where it expects me to be by the end of the year. What I can also do is I'll click into options here. And here I could see when the forecast starts. And so the forecast start date is basically the last date of data in my data here. And so it'll kick off there. I could also indicate the confidence interval. The more confident that you are, the wider the interval will get, as you see above. And the smaller it is, if I'm less confident, you'll see that the range narrows. So the more confidence, the wider it is, the less confident, the more narrow that it is. But I'll go at 95%. That's a pretty standard confidence interval percent to use. What's interesting too is if there's any seasonality in the data. So let's say on weekends, it goes down or during the week, it goes up. It'll detect that and it'll apply it to the forecast. I don't tend to have all that much seasonality. You have a little bit of seasonality on weekdays and weekends. But overall, that doesn't affect this too much. You could also include forecast statistics and then also indicate, this is my timeline range, the values range. So it's just simply the data that I'm using from the spreadsheet. And then you could also fill missing points. So let's say I'm missing a few days here and there. You could either use zeros or interpolation, which will figure out approximately what the value would be. And then also what you want to do with any duplicate values in the data. And so what I'll do now, all of that looks good to me, and I'm going to go ahead and create the forecast sheet. So what it does is it creates a new table with all the forecast data. So here, if I scroll down, I could see all the forecast data and table format. And here, what it's done is it's created a nice chart for me that shows what the forecast looks like. And here, this is through the end of the year. It's expecting that if I keep up the current growth rate, I'd be at about 181,000 views or anywhere between 230,000 approximately and 135,000. So it expects or forecasts the views to be within that range. All right. Well, that was just a really quick look at two different ways you can forecast in Microsoft Excel. You could either insert a chart and then you could forecast forward, or you could use forecast sheets and Excel will take care of most of the heavy lifting for you. Either way, it's a kind of fun way you could predict what the future might look like with some amount of confidence. And of course, it's always hard predicting the future, but if you use past data, that's your best indicator of what the future might hold. Anyway, if this video helped you learn how you could forecast into the future, please give this video a thumbs up. If you want to see future videos like this, hit that subscribe button. That way you'll get a notification anytime new content like this comes out. And lastly, if there are any other topics or any other videos that you want to see me cover in the future, simply leave a comment down below and I will add it to my list of videos to create. All right. Well, that's all I had for you today. I hope you enjoy it and I hope to see you next time. Bye.