Predicting Future Data Trends in Excel with Forecasting Techniques

Forecasting. In this section, we're going to take a look at forecasting at a more extensive level. We want to calculate future data based on historical data.

There are two mechanisms that are going to be used to forecast values in Excel using the methods I'm just about to describe. The first method is the least sum of squares and the second is exponential smoothing. So let's start with the least sum of squares.

I want to predict a future value based on existing values that follow somewhat a linear trend. So for that, I'll use forecast.linear, that particular Excel function. We have some information regarding revenue for the first eight weeks.

This is historical information that is information that's actual information. But in week eight, we want to predict what our revenue is going to be for weeks nine to 13. We're going to use the forecast.linear function to predict what that is going to be.

So I'm going to type equal forecast.linear. And then I need to select some information. Now I'm going to choose the period that represents the ninth week. And that's the number nine.

I'm going to enter a comma. And then I need to select the revenue, the past revenue. I'm going to lock that so that I'm just using historical information.

I'm not going to be using the information that I'm forecasting as part of that historical data. So I'm going to press F4 to lock that. Then I'll enter a comma and then I'll select the periods for those for that revenue.

In this case, weeks one to eight, I'll press F4. All right. Now I'll press ENTER.

And I get my first projection, which is 13,429. I'll take this and auto fill down. And there is the projection going forward based on the historical data and how much the growth is from week to week.

We're predicting that in the future, these are the values that we're approximately going to make in revenue for weeks nine to 13. So that is one method of forecasting. I'm going to scroll down here.

Let's take a look at exponential smoothing. I want to predict a future value based on existing values that can incorporate seasonal trends. Now, there are three types of exponential smoothing that exist.

Single exponential smoothing. Alpha value is close to zero. So the smoothing is very slow.

Double exponential smoothing. And this is used for trend calculations. And then triple exponential smoothing.

This is used for trend and seasonality calculations. We're going to use a different calculation. We're not going to use forecast ETS.

We're not going to use forecast linear. We're going to use forecast ETS. So I'm going to apply exponential smoothing with a seasonality of four to examine the trend in the values.

So that means that four values are going to be taken into account to predict the next four values. So the values are going to increment in batches of four. So I'll type equal forecast.

Let me just change the font for this and make it black. All right. So this is going to be equal to forecast ETS.

So again, I'm going to choose the period that I want this revenue to represent. And that is week number nine. I'm going to enter a comma and then I'm going to select the historical revenue amounts.

I'll lock that. I'll enter a comma. And then I'll choose the period for that historical revenue amount, which is weeks one to eight.

I'll press F4 and lock that. Now, so far, this is pretty much the same as what we did for forecast linear. But now we're going to add some seasonality to this.

I'll enter a comma. And the seasonality I want for this is for a period of four. So I'm looking at the values from one thousand, two thousand, three hundred, three thousand and six thousand.

And as you can see, when we start the period for week number five, it goes down and starts up again at a lower amount and then increases. So the first four are a batch and the second four are a batch. So this could be spring, summer, winter, fall.

And then we'll look at the next year and we're looking at comparable amounts. So that is going to be four. I'll enter a comma.

Data completion. Am I going to see any missing values in that range? Do I want to treat those as zeros? I don't have any missing values, so I'll just type one and I'm going to enter a comma. Aggregation.

What type of calculation do I want here? I'm going to say average, so I'll also choose one for that. Close parentheses. I'll press ENTER.

And so now again, this is the start of the next four values. So I go from twelve thousand to eight thousand because I'm starting a new season. I'm going to auto fill this down and there are my values.

And they pretty much closely match what we have here. The only difference is this calculation takes into account future values. I'm just relying on the historical information.

I'm not looking at my projections as part of historical information. So there's a slight difference. It's a safer bet to use all the historical information without including future projections.

All right. So let's take a look at another calculation here related to the forecast group. Lots of forecasts here.

So this calculation is going to use forecast ETS. So I want to predict the future values while including confidence intervals. The default confidence interval is 95 percent.

And I'm going to add a confidence interval to establish an upper limit and a lower limit. So whatever value I return, I will subtract the confidence interval. Add or subtract the confidence interval.

So I'm going to go over here. Let me just see what value this is referring to. C36.

So this work has already been done for us. I was just a little confused about why these were already filled in. This is using the same information that we have up here.

And here we're making our projections. We've already made our projections. But then we're saying what the best case scenario is as far as upper and worst case scenario as lower.

Now, the confidence interval is being calculated by using the confidence interval of 95 percent, the time periods from 1 to 9, in this case, including future projections and historical information. We're not seeing values for row for week 9 because we're showing you the formula here, but we haven't actually taken out the single quote to actually calculate the value. So I'm predicting $8,000 for week 9. In the best case scenario, that would be $10,084.17. In the worst case scenario, that would be $6,641.43. The confidence interval tells me the value that is going to be either added or subtracted from the base of $8,363.

And so this is a new calculation. We don't have to worry about the projected value. We're interested in what the confidence interval is going to be.

We're using a function that's already been written for us, forecast.ets.confidenceinterval. All right. So let's take a look at another forecasting exercise here. We're just going to do the good old regular forecast linear.

So that's going to be equal to forecast.linear. This time, the period is a period of years. I'll enter a comma. I want to select the historical information, and I'm going to lock that.

I'm going to be conservative in my estimates. I'll enter a comma, and then I'll select the years from 2014 to 2021. I'll also lock that.

I'll press ENTER, and now I will make my projections for the years 2022 to 2030. All right. Now we'll actually do a forecast.ets seasonality forecast.

This one is a little bit more dramatic. You can really see how the forecast.ets is going to take into account different, quote, seasons. So for enrollment, we have enrollment in the spring, summer, and fall, and the trend is that in the summer, enrollment goes down, and then it catches up in the fall and can actually be higher than what it is in the spring.

We want to replicate that seasonality, so we'll use forecast.ets. So equal forecast exponential smoothing. First, we're going to select the period, which in this case is 13. I'll enter a comma, and I'm going to select the historical enrollment numbers.

I'll lock that. Then I'm going to enter a comma, and I'm going to select the periods 1 to 12. I'll press F4 and lock that.

Then we're going to take a look at the seasonality. The seasonality is every three months, spring, summer, and fall. So that's a seasonality of three.

I don't have any missing values that I need to be treated as zero, so I'll just select one. I'll enter a comma. Aggregation.

I want the average. I'll type one. Close parentheses.

I'll press ENTER. Now you're going to see that 40,000 is where we're starting at. There's going to be a dip when we go to the summer value.

That is a big dip. And then we're going to jump back to numbers that are equal to or better than the spring. And so now I'll drag this all the way down.

This will keep track of those time periods. And as you can see, this is consistent with the type of enrollment we have seen in the past. We'll do one more exercise.

Let's say you wanted to graph this information. Specifically, you wanted to graph the future projections, but also the upper and lower confidence interval amounts. Well, you can do this using a feature that's only available in Excel for the PC.

Unfortunately, this is not available on the Mac. Now we have an example of the forecast sheet that we're going to create. It looks like this, but we're going to generate it ourselves so you can see what it looks like.

I'm going to select the weeks and the revenue. I'm then going to head over to the data tab and I want to look at the forecast group. From the forecast group, I'm going to choose forecast sheet.

When I choose forecast sheet, I see a preview of what the information looks like. And here we have a forecast for up to 14 weeks. I can increase that to 19.

The line above is the best case scenario, upper limit. The line below is the worst case scenario, lower limit. And the line in the middle is just a traditional forecast.

As you can see, it's following the pattern of historical information. There are other options that I can adjust here, but most of the defaults are fine here. So I'm just going to click create.

When I click create, this then generates the chart with all the values, including the lower confidence, upper confidence bound, and what is forecasted in the future. So very simple to create. That is the forecasting sheet create using the forecast sheet tool in Windows, in Microsoft Windows version of Excel from the data tab and over in the forecast group forecast sheet.

So in this section, we covered a lot. We looked at forecast linear for linear trending. We looked at exponential smoothing here.

The period is for a period of four values, incrementing four values at a time. We then created an upper or lower confidence bound. We did another forecast linear example.

And then we looked at a seasonality across three seasons, spring, summer, and fall. And we're able to see the consistency in the values that we generated. And then we created a forecast sheet using the forecast sheet command.

So this section covers forecasting. We looked at all the tools that are available for us to use. Thank you for watching.