Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:00,004 --> 00:00:02,004
- [Instructor] When you own or operate a business,
2
00:00:02,004 --> 00:00:05,003
you're always wondering what happens next.
3
00:00:05,003 --> 00:00:06,001
In this movie,
4
00:00:06,001 --> 00:00:07,007
I will show you a couple of techniques
5
00:00:07,007 --> 00:00:09,008
you can use to answer those questions,
6
00:00:09,008 --> 00:00:12,009
at least as far as current trends allow.
7
00:00:12,009 --> 00:00:16,000
My sample file is 02_05_Forecast,
8
00:00:16,000 --> 00:00:17,009
and you can find it in the Chapter 02 folder
9
00:00:17,009 --> 00:00:20,002
of the Exercise Files collection.
10
00:00:20,002 --> 00:00:23,004
I'm on the Trend worksheet in this workbook,
11
00:00:23,004 --> 00:00:26,002
and you can see that I have quarterly sales data
12
00:00:26,002 --> 00:00:31,002
for the years of 2021 and 2022.
13
00:00:31,002 --> 00:00:33,008
What I'd like to do is to get a forecast
14
00:00:33,008 --> 00:00:39,003
based on those values for the quarters in 2023.
15
00:00:39,003 --> 00:00:41,009
And you can do that using the fill handle.
16
00:00:41,009 --> 00:00:43,006
If you're not familiar with the fill handle,
17
00:00:43,006 --> 00:00:47,000
I will give you a quick demonstration of how it works.
18
00:00:47,000 --> 00:00:49,008
In cell D2 I'll type a 1,
19
00:00:49,008 --> 00:00:52,009
and then in cell D3 I'll type a 2.
20
00:00:52,009 --> 00:00:56,002
Then I will select cells D2 and D3.
21
00:00:56,002 --> 00:00:58,002
If you haven't used the fill handle before,
22
00:00:58,002 --> 00:01:02,001
it is the green square at the bottom right corner
23
00:01:02,001 --> 00:01:05,002
of a selected cell or cell range.
24
00:01:05,002 --> 00:01:09,000
If you want to drag the fill handle to extend values,
25
00:01:09,000 --> 00:01:11,002
select the two cells.
26
00:01:11,002 --> 00:01:13,003
You can also do more as you'll see in a moment.
27
00:01:13,003 --> 00:01:15,003
Move your mouse pointer over the fill handle,
28
00:01:15,003 --> 00:01:18,000
it changes to a black crosshair.
29
00:01:18,000 --> 00:01:23,008
And then drag down, and you see that it extends the series.
30
00:01:23,008 --> 00:01:27,009
If I make the second value a 3
31
00:01:27,009 --> 00:01:30,008
and then select the two cells and then drag again,
32
00:01:30,008 --> 00:01:34,001
you see that it goes up, keeping the increment.
33
00:01:34,001 --> 00:01:37,002
This is a way of extending the trend linearly,
34
00:01:37,002 --> 00:01:42,000
so it's based on the line of best fit for this data.
35
00:01:42,000 --> 00:01:44,006
I'll go ahead and delete the values that I have selected
36
00:01:44,006 --> 00:01:47,000
just so they're not cluttering up the worksheet.
37
00:01:47,000 --> 00:01:51,002
And then I can extend the values in B2 through B9
38
00:01:51,002 --> 00:01:53,004
by selecting those cells.
39
00:01:53,004 --> 00:01:57,003
And then I will grab the fill handle and drag down.
40
00:01:57,003 --> 00:02:01,000
And you can see that based on the previous values,
41
00:02:01,000 --> 00:02:03,002
if we have a linear trend in our data
42
00:02:03,002 --> 00:02:08,007
we'll have 412,000, 426,000, 439 and 453.
43
00:02:08,007 --> 00:02:10,005
So even though there were increases
44
00:02:10,005 --> 00:02:13,009
and decreases based on quarters,
45
00:02:13,009 --> 00:02:16,003
overall the trend is up.
46
00:02:16,003 --> 00:02:19,000
So this analysis is useful in that it tells you
47
00:02:19,000 --> 00:02:21,009
that your sales are generally trending positively.
48
00:02:21,009 --> 00:02:24,003
You're making more money per quarter.
49
00:02:24,003 --> 00:02:28,003
However, it doesn't account for any increases or decreases.
50
00:02:28,003 --> 00:02:30,009
For example, do you sell more in quarter three
51
00:02:30,009 --> 00:02:33,007
or less in quarter four?
52
00:02:33,007 --> 00:02:37,001
So all this does is give you a general idea.
53
00:02:37,001 --> 00:02:38,007
Now I'll switch to the forecast worksheet,
54
00:02:38,007 --> 00:02:41,001
and I will show you how to use a formula
55
00:02:41,001 --> 00:02:44,000
to create a different kind of forecast.
56
00:02:44,000 --> 00:02:46,005
So click the Forecast sheet tab,
57
00:02:46,005 --> 00:02:51,001
and I can see here that I have data about the distance
58
00:02:51,001 --> 00:02:54,000
that an individual traveled to get to a store
59
00:02:54,000 --> 00:02:57,003
and then the amount that they spent once they got there.
60
00:02:57,003 --> 00:02:59,000
And I can scroll down,
61
00:02:59,000 --> 00:03:03,007
and I see that I have about 26 rows of data.
62
00:03:03,007 --> 00:03:06,005
Let's say that I'm interested in predicting
63
00:03:06,005 --> 00:03:09,004
how much individuals who live 30 miles from the store
64
00:03:09,004 --> 00:03:11,004
would spend if they came.
65
00:03:11,004 --> 00:03:12,008
The scenario I have in mind
66
00:03:12,008 --> 00:03:18,000
is that we might target online ads to potential customers
67
00:03:18,000 --> 00:03:20,009
who live in a neighborhood about 30 miles away.
68
00:03:20,009 --> 00:03:26,007
So I will go to cell D2 and type 30.
69
00:03:26,007 --> 00:03:31,002
And then in cell E2, I will type my forecast formula.
70
00:03:31,002 --> 00:03:34,005
So I'll start by typing forecast,
71
00:03:34,005 --> 00:03:38,000
and then I will use FORECAST.LINEAR.
72
00:03:38,000 --> 00:03:41,001
So I'll move down to highlight that because I don't,
73
00:03:41,001 --> 00:03:44,008
in this case, want to use any sort of exponential smoothing,
74
00:03:44,008 --> 00:03:46,003
which is an advanced technique
75
00:03:46,003 --> 00:03:48,006
and is beyond the scope of this course.
76
00:03:48,006 --> 00:03:52,002
So I have linear forecast, press Tab.
77
00:03:52,002 --> 00:03:55,009
And then the X is in cell D2,
78
00:03:55,009 --> 00:03:58,006
that's the number of miles driven, then a comma,
79
00:03:58,006 --> 00:03:59,008
the known Ys.
80
00:03:59,008 --> 00:04:04,002
And it is easy to reverse the order of these two columns.
81
00:04:04,002 --> 00:04:07,002
The known Ys are the amount that were spent,
82
00:04:07,002 --> 00:04:10,009
so this is the dependent variable,
83
00:04:10,009 --> 00:04:14,008
and it is dependent on the distance driven.
84
00:04:14,008 --> 00:04:18,009
So I'll click cell B2, then Control + Shift + down arrow.
85
00:04:18,009 --> 00:04:24,002
And I've selected B2 through B27, scroll back up,
86
00:04:24,002 --> 00:04:25,008
and then type a comma.
87
00:04:25,008 --> 00:04:29,008
And the known Xs are in A2 through A27,
88
00:04:29,008 --> 00:04:32,001
and I can just type that in.
89
00:04:32,001 --> 00:04:33,009
Right parenthesis and Enter.
90
00:04:33,009 --> 00:04:39,004
And I see that the predicted amount spent is about $56.29
91
00:04:39,004 --> 00:04:42,003
if they came from 30 miles away.
92
00:04:42,003 --> 00:04:44,006
And looking at the data around that point,
93
00:04:44,006 --> 00:04:47,008
I see that I have people who traveled 50 miles,
94
00:04:47,008 --> 00:04:51,003
and they spent 67, 125, and 75.
95
00:04:51,003 --> 00:04:53,003
Okay, a little bit more.
96
00:04:53,003 --> 00:04:57,001
45 spent 35, 22 40,
97
00:04:57,001 --> 00:04:59,004
20 25, and 20 50.
98
00:04:59,004 --> 00:05:02,006
So the predicted amount of 56.29, makes sense,
99
00:05:02,006 --> 00:05:06,008
giving the data that surrounds that point.
100
00:05:06,008 --> 00:05:10,009
One thing you shouldn't do is to use your data
101
00:05:10,009 --> 00:05:13,006
to make a prediction or forecast
102
00:05:13,006 --> 00:05:16,008
for a value that is outside of your data range.
103
00:05:16,008 --> 00:05:22,003
So if I click cell D2 and type in 200 and Enter,
104
00:05:22,003 --> 00:05:26,005
you see that I get a predicted amount spent of 248.16.
105
00:05:26,005 --> 00:05:29,007
And that is a valid amount.
106
00:05:29,007 --> 00:05:32,004
The problem of course, is that my data only goes up,
107
00:05:32,004 --> 00:05:38,000
as we see here in cell A27, to 152 miles.
108
00:05:38,000 --> 00:05:41,000
And the question is whether someone
109
00:05:41,000 --> 00:05:42,007
who drives from a long way away
110
00:05:42,007 --> 00:05:44,009
would spend that much money.
111
00:05:44,009 --> 00:05:50,000
For example, if I type in 300 and Enter,
112
00:05:50,000 --> 00:05:52,001
we get a predicted amount of 361,
113
00:05:52,001 --> 00:05:55,001
which is almost twice as much as the amount
114
00:05:55,001 --> 00:06:00,002
that the individual who came in from 152 miles away spent.
115
00:06:00,002 --> 00:06:02,003
You can use the FORECAST function
116
00:06:02,003 --> 00:06:05,003
to predict amount spent, in this case,
117
00:06:05,003 --> 00:06:06,007
for miles driven values
118
00:06:06,007 --> 00:06:09,009
that are a little bit outside of the collected data range.
119
00:06:09,009 --> 00:06:12,001
But in most cases, you will be safest
120
00:06:12,001 --> 00:06:14,009
if you stay well within the parameters of the data
121
00:06:14,009 --> 00:06:16,000
that you collected earlier.
9601
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.