hi everyone my name is kevin today i
want to show you how you can do
forecasting in microsoft excel and as
full disclosure before we jump into this
i work at microsoft as a full-time
employee so why would you ever want to
do forecasting in microsoft excel and
what does that even mean well you might
have a whole bunch of data let's say you
work at say a workplace or maybe you
have a youtube channel and you have a
bunch of data you could look at how
you've performed in the past and you
could use that to predict or forecast
what the future might look like
all right well why don't we jump on
excel and i'll show you step by step how
you could do this it's a neat thing to
learn how to do
all right here i am on my pc and what
i've done is we're going to have some
fun here and what i've done is i've
downloaded my views on youtube through
the last year so this goes back to may 7
2019 all the way up to may 5th 2020 and
you can see how my views have changed
over time and so let's say for example i
want to forecast well hey what will my
views look like in 30 days or 90 days or
or maybe even a year from now what will
things look like and that's where
forecasting comes into play so here i
could see that you know last year i was
at 5000 views a day and then here i'm at
about a hundred eleven thousand views uh
per day so based on this growth can
excel help me forecast what the future
looks like and the answer is yes excel
can do that so how do we do this well
i'm going to show two different methods
or two different techniques that we
could use to forecast the first one is
pretty simple and this is one that i've
used for a long time what we're going to
do is we're going to go up onto the
pivots up here and we're going to click
on insert
and on insert what we want to do is we
want to insert a line chart and so i'm
just going to insert a very simple 2d
line chart and let's go ahead and throw
this in so now this gives me a nice
visualization of what my views have
looked like over time so you know it
started out there was some growth a
little bit of growth and then especially
recently it's grown quite a bit more and
so this is what the past year looks like
for me
and now to be able to forecast into the
future what does the future hold what i
can do is i'm going to just click on
this line with my left mouse button and
now i'm going to right click on it and
what i can do here is i can add a
something called a trend line so let's
go ahead and throw that in
and within trendline i have a whole
bunch of different options we're going
to walk through what these all different
mean what all these different options
mean and which ones you should use so
one of the things is you'll see this
trend line here and the trend line tries
to match my data as closely as possible
so right now the default is just a
linear
line or trend line and so linear line is
just a straight line and this is the
best attempt at matching the data
one of the things that you'll see though
is it doesn't perfectly match the data
here it's a little above and then more
recently it's below so this probably
doesn't
reflect the growth accurately and so i
might want to try some of these other
trend lines so here i can look at an
exponential growth line and this one
matches the data much more closely here
it's pretty much identical up until
recently now it's a little below but
this one matches the data a lot better
and so perhaps on youtube growth is more
exponential as you grow as you get more
videos the growth tends to be more
exponential nature
but what i can also do is i can i can
click into different trend line options
and see which ones match my data so
here's a log
logarithmic
um i could also look at polynomial
and then you could adjust the numbers
here so this one
here this
is close to the data performance so i
could go through here i could also look
at power and then you could also do a
moving average so here i could say you
know hey give me a moving average say
over a 20 day period so this is
different ways i could look at my data
but i think probably at least going
through these different ones the one
that appeared to match my data the best
was this exponential line so i'll go
ahead and choose this now some of the
things that i can also look at is
there's something called an r squared
value i'm going to go ahead and click on
this and what that stands for this is
the coefficient of determination and
it's a value between zero and one in
essence if it's closer to zero that
means the line doesn't match your data
that well the closer to one you are uh
the more closely that the line matches
your data so here if i go back to the
trend line and i can click through these
different options so here with a linear
line it's a 0.7 so really it doesn't
match the data as well and i could go
through and i could see how it matches
the data but here exponential seems like
the best match so i'm going to go ahead
and choose that one
so now i mentioned that we want to do
forecasting and so to forecast what i
want to do is i'm going to scroll down
here and here you see a section called
forecast and so you could forecast both
forward uh and backward
and what i want to do is i want to
forecast into the future to see what
things might look like and now it says
zero periods what is a period well my
data in the spreadsheet i go day by day
by day and so a period then if i click
back in a one period would be one day so
here i'm forecasting one day into the
future
here i could forecast 30 days into the
future so let's say a month into the
future
and then maybe i want to even say let's
say 90 days into the future so about
three months and then maybe let's say
half a year from now so about 180 180
days from now
what this will do is it shows me
approximately assuming that you know i
do have exponential growth on the
channel this will show approximately
where i would be in a in about a half a
year so here it's predicting about 300
000 views uh if i look at it here now
one of the big questions is is it
exponential growth or does it actually
tend to be more linear if it's more
linear i'll be at about a hundred
thousand or so so really depends on
what type of trend line best matches
your data uh and one of the things
that's also interesting so here i'm
forecasting forward what i can also do
is i can display an equation on the
chart and so here's my equation
and the x value so once again a little
refresher course back from math class
this is the x axis going across here and
this is the y axis that's the vertical
line and so if i want to figure out what
value i'll be at
i could insert the day in this example
as the x value and that'll tell me the y
how many views i will have so that's
another way where you can get the
equation and then you could simply type
in your value and it'll tell you where
you'll be at that point in time or just
as a simpler approach you could simply
type your number in here so let's say a
year from now where am i going to be
and here it's saying at about 1.2
million although given that i've only
seen this level of growth going another
year in the future uh probably has a lot
of uncertainty uh so that's one just
quick way how you could uh add
forecasting into uh charts in microsoft
excel and now i also want to show you
it's another way to do forecasting in
microsoft excel so i'm going to go ahead
let's delete this for now
here's my data and what excel has is
they have a built-in dedicated formula
or a forecasting view and the way we're
going to get to that is we're going to
go up back up to these pivots across the
top and we're going to click into data
so let's click on data
and then across the top here one of the
views here one of the options on the
ribbon is called forecast sheet so excel
has an entire sheet that will help you
forecast into the future so let's go
ahead and click on this and this brings
up a dialog now where it says create a
forecast worksheet and what it'll do is
it'll use historical data to create a
visual forecast worksheet and so here's
all my historical data of my views over
time and what it shows me then is it
shows me the forecasted value here is
this orange line and then it also has an
upper bound and a lower bound so with 95
confidence uh or 95 of the time it's
expected that the value would fall
within this range
so something that i could see down here
here i could see the forecast end so
here's forecasting through
august 5th so we're going about three
months into the future but i could also
say hey by the end of the year where do
i expect to be so i'm going to go ahead
and change the date and so here's
another view of where it expects me to
be by the end of the year
what i can also do is i'll click into
options here
and here i can see when the forecast
starts and so the forecast start date is
basically the last date of data in my
data here
and so it'll kick off there i could also
indicate the confidence interval the
more confident that you are the wider uh
the interval will get as you see above
and the
the smaller it is if i'm less confident
you'll see that the range narrows so the
more confidence the wider it is the less
confident uh the more narrow that it is
but i'll go at 95 that's a pretty
standard confidence interval percent to
use what's interesting too is if there's
any seasonality in the data so let's say
on you know weekends it goes down or
during the week it goes up it'll detect
that and it'll apply it to the forecast
i don't tend to have all that much
seasonality you have a little bit of
seasonality on weekdays and weekends but
overall that doesn't affect this too
much you could also include forecast
statistics and then also indicate this
is my timeline range the values range so
just simply the data that i'm using from
the spreadsheet and then you could also
fill missing points so let's say i'm
missing a few days here and there you
could either use zeros or interpolation
which will figure out approximately what
the value would be
and then also what you want to do with
any duplicate values in the data and so
what i'll do now all of that looks good
to me and i'm going to go ahead and
create the forecast sheet so what it
does is it creates a new table with all
the forecast data so here if i scroll
down i can see all the forecast data and
table format and here what it's done is
it's created a nice
chart for me that shows what the
forecast looks like and here this is
through the end of the year
it's expecting that if i keep up the
current growth rate i'd be at about a
hundred eighty one thousand views or
anywhere between uh two hundred thirty
thousand approximately and 135 000 so it
expects or forecasts the views to be
within that range all right well that
was just a really quick look at two
different ways you can forecast in
microsoft excel you could either insert
a chart and then you could forecast
forward or you could use forecast sheets
and excel will take care of most of the
heavy lifting for you either way it's a
kind of fun way you could predict what
the future might look like with some
amount of confidence and of course it's
always hard predicting the future but if
you use past data that's your best
indicator of what the future might hold
anyway if this video helped you learn
how you could forecast into the future
please give this video a thumbs up if
you want to see future videos like this
hit that subscribe button that way
you'll get a notification anytime new
content like this comes out and lastly
if there are any other topics or any
other videos that you want to see me
cover in the future
simply leave a comment down below and i
will add it to my list of videos to
create all right well that's all i had
for you today i hope you enjoy it and i
hope to see you next time bye