We notice that the period (t) variable has a high p-value. This confirms the fact that our baseline (Q4) is where GoPro performs best in terms of sales revenue. You can also see that the coefficients for all variables are negative. Generally, the idea is that most (or ideally all) of those should be below 0.05 (for the same confidence interval of 95%). We won’t go into too much detail on the p-values, as we want to keep this a simple example. If it were higher than 0.05, it would’ve meant this set of independent variables wouldn’t give us a good model.Īnother thing we can look at is the p-values of the independent variables. If it is less than 0.05 (remember, we left our confidence interval at 95%), our model is significant. To ensure our model is reliable (statistically significant), we take a look at the ‘Significance F’ metric of our Regression.
For an overall sales forecast (the task at hand), I would find 46% to represent a good-enough fit. Generally, we consider models with higher R-squared better, but this is not always the case. First, let’s look at the value of R-squared.Īdjusted R-squared varies between 0% and 100% and shows how well the model explains the variability in the data.
Once we generate the regression analysis, we can examine some basic metrics. Don’t forget to tick the Labels checkbox if you selected these as well. Here we add the period (our time-series variable) and the three dummy variables for Q1, Q2, and Q3. We also choose our X-Values, the independent variables. The Regression analysis tool opens, and we need to select our Y-Values, which is the dependent variable, or GoPro’s sales revenues for each quarter. Once you run the Data Analysis tool, select Regression, and click OK to move forward. In case you don’t have this visible in your Excel installation, you might have to enable it from the Options menu ( File > Options > Add-ins > Excel Add-ins > Analysis ToolPak). We will use Excel’s Data Analysis tools ( Data Tab > Analyze > Data Analysis). If they match, we show one (1) in the respective column, and if they don’t, we show zero (0). However, if we want to stick to Excel, we need to add these manually.įor the three variables we added for quarters one to three, let’s add a formula to check whether the quarter in each next row matches one of the dummy variables.
#Excel trendline in the bacl software
Keep in mind that if you use specialized statistics software (e.g., R or Python libraries), you won’t have to create the dummy variables, as these frameworks recognize the quarter as a categorical variable. The period (t) variable will be the fourth variable in the regression model and will represent our time series. We will have three dummy variables (n-1) for Q1, Q2, and Q3, while Q4 will remain our baseline. We are going to treat every quarter as a separate dummy variable. To capture both the seasonality and potential underlying trend in the data, we will rely on the regression analysis functionality that is part of Excel. The last quarter of the year is where the company performs best in terms of generating sales revenue. We can calculate these averages with the AVERAGEIFS function in Excel: Some years show a more prominent jump in sales revenue than others, but we can safely conclude there’s some seasonality in the data series.Īnother way to test for seasonality is to calculate the average sales revenue for each quarter in the different years. As soon as we look at the line chart above, we notice some spikes in Q4. One way to detect seasonality is by visually examining the data. If we add the quarters to the data and plot it on a simple line chart we get: The aim is to create a model that can help us forecast the revenue of GoPro for the next financial year, 2020. We have the data for the period 2013 to 2019. Let’s look at the quarterly sales revenue of the electronic cameras manufacturer GoPro (source: ). Today we will use regression analysis in Excel to forecast a data set with both seasonality and trend. We went over an example Excel model of calculating a forecast with seasonality indexes. Originally there was no line associated with the points.In our last article, we discussed Seasonality in Financial Modeling and Analysis.
The trick turns out to be to slightly change the format for the dataseries associated with the points that should be behind the lines (In this case the black points). However, whatever order I used, the lines were behind the points, ie like the graph on the left rather than the graph on the right Normally the way to put some data in front of others the order of data sources should be changed using the select data source dialog. I found myself wanting to produce an x-y plot where some of the sets of data were points, and some were lines, and I wanted to have the lines in front of the points.