Time series analysis with advanced analysis in Copilot

- [Instructor] Time series analysis allows organizations to identify patterns, trends, and fluctuations over time. These insights are essential for accurate forecasting, strategic planning, and informed decision-making. Although Excel has traditionally faced challenges with time series data, integrating Python within Excel has expanded its capability significantly. In this video, we'll delve into conducting time series analysis using Python and Excel, empowered by Copilot and advanced analysis. You can start by downloading the exercise file, advanced analysis time series start, which contains monthly shampoo sales data spanning over three years. We'll input this data set into Copilot and initiate a Python powered analysis. Let's head to Copilot. We will go to advanced analysis and let's start the advanced analysis. Now, again, these results are very unpredictable. I couldn't tell you what Copilot is going to give us this time. It looks like we are getting a visualization in this case. We are going to get some statistics now it looks like, and now the analysis is done. Good idea to briefly scroll through here. Understand what we're doing here, exploratory data analysis. Okay, we're getting a plot of sales by month. We are getting descriptive statistics for shampoo sales. Things like the average, the median and sales forth for monthly sale. Okay, so that makes sense to me. Let's dive a little bit more into the descriptive analysis here. So for example, maybe we are interested more so in sales by quarter than sales by month. I'm going to ask Copilot to give us a chart showing total sales by quarter. (keyboard clacking) Okay, now it looks like Copilot was able to make quick work of that. Let's scroll out here a little bit and you will see that this quarterly plot has been created. You can dive into the Python code that was used to generate it either over here or you can dive right into the worksheet and see the code that was generated directly in that worksheet. Okay, now this is called advanced analysis, so we're not going to stop at just exploratory plots. One next step we might want to do is to examine the stationarity of the dataset. If you're not familiar, and time series means that the statistical properties, things like the mean and the variance things that we actually just looked at in an earlier step remain consistent over time. And this is really important because many forecasting models are going to assume that our dataset is stationary to ensure accuracy. So let's have copilot assess the stationarity of this data. (keyboard clacking) Okay, so Copilot was able to do some diagnostics here. It's running something called a Dickey-Fuller test. It is giving us things like P values. So if these terms aren't familiar to you, again, it's a good idea to make sure you understand the methods that are being used by Copilot and by Python before you just take this and and run. In this case, it is telling us that this data is not stationary. Let's go ahead and continue on here and get co-pilot's opinion about what we should do next. So again, my goal is really to build a forecast here. I know now that the data is not stationary, although it would be a good idea just to kick the tires on this and understand at a deeper level how Copilot and Python got to that decision. But let's just take this as correct for right now. So I'm going to ask Copilot, given what you found about this data not being stationary, what sort of model might be good to forecast sales over the next four months? So this is kind of an open-ended question for a Copilot, and we'll see what it gives us. Great, so we do get a result here. It's suggesting that we use what's called an ARIMA model. This is a pretty common forecasting technique, autoregressive, integrated moving average is what that stands for. We are able to build this model again using Python code generated by Copilot, and we see those predicted values there. Time series analysis, traditionally a challenging area for Excel has become significantly more powerful with the integration of Copilot and Python. However, again, make sure you fully understand the models and techniques being used before presenting them as definitive solutions to your team.