How to Forecast & use Trendlines in Microsoft Excel

hi everyone my name is Kevin today I want to show you how you can do forecasting in Microsoft Excel and is 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 gonna 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 could 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 could see that you know last year I was at 5,000 views a day and then here I'm at about a hundred 11,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 gonna show two different methods or two different techniques that we can use to forecast the first one is pretty simple and this is one that I've used for a long time what we're gonna do is we're gonna go up onto the pivots up here and we're gonna click on insert and on insert what we want to do is we want to insert a line chart and so I'm just gonna 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 since the future what does the future hold what I can do is I'm gonna 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 trendline so let's go ahead and throw that in and within trendline I have a whole bunch of different options we're gonna 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 trendline tries to match my data as closely as possible so right now the default is just a linear line or trendline 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 could 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 could I can click into different trendline options and see which ones match my data so here's a log logarithmic and 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 and 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 but 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 go ahead and click on this and what that stands for this is the coefficient of determination and it's a value between 0 and 1 in essence if it's closer to 0 that means the 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 point seven so really it doesn't match the data as well and I could go through and I can 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 a 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 the 180 days from now what this will do is it shows me approximately assuming that you know I do have exponential growth on the channel this will show approximately where I would be in a 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 a hundred thousand or so so really depends on what type of trendline 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 albeit 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 you 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 gonna 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 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 it's another way to do forecasting in Microsoft Excel so I'm gonna 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 gonna get to that is we're gonna go up back up to these pivots across the top and we're gonna 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 will 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% at a time it's expected that the value would fall within this range so something that I could see down here here I can see the forecast ends so here is 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 can see when the forecast starts and so the forecasts 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 the smaller it is a 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 you know 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 effect this too much you could also include forecast statistics and then also indicate this is my time line 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 gonna 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 can see all the forecast data in table format and here what it's done is its 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 180 1000 views or anywhere between 230,000 approximately and 135 thousand so it expects or forecast 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 can 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 the subscribe button that way you'll get a notification anytime new content like this comes out and lastly if there 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 alright well that's all I had for you today I hope you enjoy it and I hope to see you next time bye

Channel: Kevin Stratvert

Views: 61,809

Rating: 4.9580603 out of 5

Keywords: forecast, trend line, trend lines, forecasting, microsoft excel, excel, office 365, excel 365, future, predict, analyze, data analysis, worksheet, workbook, work sheet, linear, exponential, line chart, chart, r2, logarithmic, polynomial, power, moving average, forward, backward, trend, trends, confidence, confidence interval, seasonality, interpolation, average

Id: QB4_pBlpPwM

Length: 11min 22sec (682 seconds)

Published: Tue May 12 2020