When you own or operate a business, you're always wondering what happens next. In this video, explore a couple of techniques that you can use to answer those questions at least as far as current trends allow.
- [Voiceover] When you own or operate a business you are always wondering what happens next. In this movie, I will show you a couple of techniques that you can use to answer those questions at least as far as current trends allow. My sample file is the forecast workbook. And you can find it in the chapter two folder of the exercise files collection. I'm starting on the trend worksheet. And on here you can see that I have sales values for all of 2017 and 2018 broken down by quarter.
Let's say that I want to see what the values would be for each of the four quarters in 2019 based on my previous values. There are a number of ways to do that, but the one I want to show you here is to use the fill handle to extend an existing data series. So I have my values in B2 through B9. I'll select those. And then I'm going to drag the fill handle at the bottom right corner of the selection. It looks like a green square. So I'll move my mouse pointer into position.
I know I'm in the right place when it changes to a black crosshair. And then I will drag the fill handle down to cell B13 and there I have my values. So for quarter number one of 2019, if trends continue, I would expect to sell 412,750 dollars. And then it would go up to 426,333 in quarter number two, and so on. Now of course you don't see the same up and down variation that you saw in the previous values. Instead what Excel has done is say, okay, what you can expect to see is a slight increase of about 13,500 dollars each quarter based on previous data.
But of course, that's just a prediction it probably won't mirror reality. Now let's switch over to the forecast worksheet. And you can see that I have another data set. This data set maps the distance a customer drove versus the amount they spent. And if I scroll down using the mouse scroll wheel, you can see that my values go all the way up to 152 miles. And if I scroll back up, you can see that the shortest distance was one mile and the amount spent corresponds to each of the distances.
So let's say that I want to predict how much a customer will spend if they drive 30 miles to my store. So in cell D2, which I've already selected, I'll type the number 30 and then press Tab. And then in cell E2, I'll create a forecast formula. So I'll type in "=" and now I need to define my forecast function. So I'll type "forecast" or F-O-R-E and you can see that I have a number of functions available to me.
In this case I want to create a linear forecast, so I have two values that are mapped together and I don't need to worry about confidence intervals, seasonality, or anything else. So I will click FORECAST.LINEAR and press Tab to accept it. Now I need to enter in a set of values. The first is my input value, called x, and that is the miles driven in cell D2. So I'll click D2 then a comma. Now I have the known_y's.
The known_y's are what are called the dependent variables. And the dependent variables are the result of an independent variable. So the amount that you drive is considered to be an independent variable and we're assuming that there's a causal relationship between it and the amount spent which is the dependent variable. So the known_y's start in cell B2. I'll click there and then I'll press Control + Shift + down arrow to select the rest of the cells in the range.
I'll click back on the formula bar and type a comma, go back up, and the known_x's are the distances driven. So I'll click cell A2, Control + Shift + down arrow. So A2 through A27 is selected, type a right parenthesis and Enter and scroll up. And I see that for miles driven of 30, we would predict that that customer would spend 56 dollars and 29 cents. If they were to drive 110 miles, so I'll edit cell D2 so it reads 110, Enter.
And we would anticipate a spend of about 146 dollars and 58 cents. One thing to note about the forecast function, because we're dealing with data that is not based on time, we can't make any predictions outside of our data range. So on the previous worksheet of the trend, we were saying if current trends continue, what would it look like. In this case we're trying to make a prediction so that means that our predictions are only accurate for values within our collected data range.
It could be interesting for informational purposes to look at what would happen at values outside of your data range, such as if someone drove 200 miles. But in this case it's best to stay within the data range and make reasonably accurate predictions.
- Distinguish between the mean, median, and mode.
- Describe the relationship between variance and standard deviation.
- Identify a nondirectional hypothesis.
- Point out the difference between COVARIANCE.P and COVARIANCE.S.
- Explain correlation.
- Analyze Bayes’ rule.