Forecasting with Excel

Summary

This script explores Excel's 'Forecast' function, which performs linear regression to predict future values. Using a height and weight dataset, the tutorial demonstrates converting units to metric, plotting data, and applying the function to estimate weights for given heights. It compares 'Forecast' and 'Trend' functions and highlights their limitations with non-linear data, such as traffic patterns. The script suggests 'Forecast.ETS' for cyclical data, illustrating its application with a traffic dataset to show improved predictions over linear methods.

Takeaways

• 📊 The 'FORECAST' function in Excel performs a linear regression to predict future values based on existing data trends.
• 📈 'FORECAST.LINEAR' is a synonym for 'FORECAST' and they both use the same linear regression method for predictions.
• 📈 The script uses a height and weight dataset from Google to demonstrate the 'FORECAST' function, converting units from inches and pounds to metric for easier understanding.
• 📊 To visualize the data, a scatter plot is used to show the relationship between height and weight, indicating a roughly linear increase in weight with height.
• 🔢 The 'FORECAST' function requires known Y values (dependent variable) and known X values (independent variable) to make predictions.
• 🤔 The script highlights the limitations of linear regression when dealing with data that has seasonal patterns, such as traffic data, which shows spikes and trends that a linear model cannot accurately predict.
• 📉 The 'TREND' function is introduced as an alternative to 'FORECAST', which can handle arrays of X values for predictions, but it also suffers from the same limitations with non-linear data.
• 🔄 The script points out that 'FORECAST', 'FORECAST.LINEAR', and 'TREND' functions are also available in Google Sheets, allowing for easy data manipulation across platforms.
• 🔄 The 'FORECAST.ETS' function is introduced for handling cyclical data, allowing for the inclusion of seasonality in predictions, which is more suitable for data with patterns that repeat over regular intervals.
• 📅 'FORECAST.ETS' requires specifying the seasonality period, or it will attempt to determine it automatically, and can handle missing values by interpolation or by setting them to zero.
• 📈 The script concludes that for non-linear and seasonal data, 'FORECAST.ETS' provides better predictions than simple linear regression, although it's not as advanced as some specialized forecasting tools.

Q & A

What does the Excel function 'FORECAST' do?

The 'FORECAST' function in Excel performs a linear regression analysis on a dataset and uses the resulting model to predict future values based on the trend of the input data.

What is the synonym for the 'FORECAST' function in Excel?

The 'FORECAST.LINEAR' function is a synonym for the 'FORECAST' function, and they perform the same linear regression analysis for prediction purposes.

What type of data set is used in the script to demonstrate the 'FORECAST' function?

The script uses a height and weight dataset from Google, containing measurements for about 25,000 people in inches and pounds, which is then converted to centimeters and kilograms.

How is height converted from inches to centimeters in the script?

To convert height from inches to centimeters, the height in inches is multiplied by 2.54.

What is the approximate formula used to convert weight from pounds to kilograms in the script?

The script uses a formula where weight in pounds is divided by approximately 2.204 to convert it to kilograms.

How does the script visualize the relationship between height and weight?

The script visualizes the relationship between height and weight using a scatter plot, which shows how weight varies with height.

What is the predicted weight for a person who is 170 cm tall according to the 'FORECAST' function in the script?

According to the 'FORECAST' function in the script, a person who is 170 cm tall is predicted to weigh about 56 kg.

How does the 'TREND' function in Excel differ from the 'FORECAST' function?

The 'TREND' function is similar to 'FORECAST' in that it also performs linear regression, but it can be used to predict multiple future values at once by passing an array of new X values.

Why might a linear regression model not be suitable for all types of data?

A linear regression model might not be suitable for data that exhibits non-linear patterns or seasonality, as it assumes a straight-line relationship between variables.

What is the 'forecast.ETS' function in Excel, and when is it more appropriate to use than 'FORECAST'?

The 'forecast.ETS' function in Excel is used for time series data that has a seasonal pattern. It is more appropriate to use when the data shows cyclical behavior, as it can better capture the seasonality compared to a simple linear model.

How does the 'forecast.ETS' function handle seasonality in data?

The 'forecast.ETS' function can explicitly specify seasonality through a parameter, or it can automatically detect the seasonality in the data. It then uses this information to make predictions that account for the seasonal fluctuations.

Outlines

00:00

📊 Excel Forecast Function and Linear Regression

This paragraph introduces the Excel 'Forecast' function, which performs a linear regression to predict future values based on existing data. The example uses a dataset of heights and weights from Google, converting from inches and pounds to centimeters and kilograms, respectively. The author demonstrates how to use the function to predict a person's weight based on their height and vice versa. The paragraph also explains that 'Forecast' and 'Forecast.Linear' are synonymous and that the function can be applied to an array of values for more efficient predictions, as opposed to running the formula independently for each value.

05:02

📈 Comparing Forecast Functions in Excel

The second paragraph delves into the limitations of linear regression for non-linear data, using traffic data as a counterexample. The author shows how the 'Trend' function in Excel can be used similarly to 'Forecast' but fails initially due to an incorrect formula range. After correcting the range, the 'Trend' function successfully predicts values across a range of X values. The paragraph also touches on the 'Forecast.ETS' function, which is better suited for cyclical data, and demonstrates its use with optional parameters for seasonality and data completion. The author compares the effectiveness of linear forecasting, 'Trend', and 'Forecast.ETS' by plotting them against actual traffic data.

10:02

🚦 Evaluating Forecast Accuracy with Traffic Data

In the final paragraph, the author evaluates the accuracy of the 'Forecast.ETS' function by plotting it alongside actual vehicle counts and a linear prediction. The 'Forecast.ETS' function is shown to perform better than a simple linear forecast, especially during peak traffic times, although it still has some inaccuracies. The author emphasizes that while 'Forecast.ETS' is an improvement for cyclical data, it is not as advanced as other forecasting tools but is a significant step up from linear regression.

Keywords

💡Forecast Function

The 'Forecast Function' in Excel is a tool that performs a linear regression analysis to predict future values based on existing data. It is central to the video's theme of data analysis and prediction. The script uses the 'Forecast Function' to predict a person's weight based on their height, demonstrating its application in a real-world context.

💡Linear Regression

Linear Regression is a statistical method used to model the relationship between a dependent variable and one or more independent variables by fitting a linear equation. In the video, linear regression is the underlying mechanism of the 'Forecast Function', which is used to predict values such as weight based on height.

💡Data Set

A 'Data Set' is a collection of data points, often used for analysis and modeling. The script refers to a specific 'Heights and Weights' data set from Google, which contains information on thousands of people, illustrating the use of data sets in statistical analysis.

💡Metric System

The 'Metric System' is an internationally recognized decimal system of measurement. The video script mentions converting height from inches to centimeters and weight from pounds to kilograms, which are both part of the metric system, to facilitate analysis and comparison across different units of measurement.

💡Scatter Plot

A 'Scatter Plot' is a type of graph that displays the values of two variables for a set of data. The script describes using a scatter plot to visualize the relationship between height and weight, helping to understand the correlation between these two variables.

💡Trend

The 'Trend' in the context of the video refers to a general direction in which something is developing or changing. The 'Trend' function in Excel is similar to 'Forecast' and is used to predict future values based on existing data trends, as demonstrated when predicting a person's height based on their weight.

💡Array Formula

An 'Array Formula' is a formula in Excel that performs a calculation on a range of values. The script discusses using an array formula to apply the 'Forecast' function to multiple values at once, showcasing a more efficient way to handle large data sets.

💡Seasonality

Seasonality refers to the predictable and recurring fluctuations that occur within a market or a set of data due to seasonal changes. The video script mentions using 'Forecast.ETS' to account for seasonality in traffic data, highlighting the importance of considering seasonal patterns in predictive modeling.

💡Cyclical Data

Cyclical Data are data points that follow a repeating pattern over time. The video script uses the example of traffic data, which has a weekly cycle of high and low traffic, to illustrate the need for specialized forecasting methods like 'Forecast.ETS' to handle cyclical patterns.

💡Interpolation

Interpolation is a method of estimating unknown values between two known points in a data set. The script briefly mentions interpolation as an option in the 'Forecast.ETS' function, which can be used to fill in missing data points by estimating their values based on surrounding data.

Highlights

The forecast function in Excel performs a linear regression to predict future values based on existing data.

The forecast function is synonymous with the forecast.linear function in Excel.

A dataset of 25,000 people's heights and weights from Google is used for demonstration, with a sample of 1,000 rows.

The dataset is converted from inches and pounds to centimeters and kilograms for metric consistency.

A scatter plot is used to visualize the relationship between height and weight.

The forecast function predicts a person's weight based on their height using the known dataset.

The trend function in Excel can be used for an array of values, providing a more efficient method than individual forecasts.

The transcript demonstrates how to use the trend function with an array formula for multiple predictions.

The forecast.ETS function is introduced for handling cyclical data, such as traffic patterns.

Forecast.ETS allows specifying seasonality and can automatically detect it if not provided.

The transcript compares linear forecasting with forecast.ETS for traffic data, showing the latter's superiority.

The importance of selecting the right forecasting method for different types of data is emphasized.

The transcript illustrates the process of plotting predictions alongside actual data for visual comparison.

Excel's forecast functions are available in Google Sheets, allowing for easy data transfer and manipulation.

The limitations of linear regression for certain datasets, such as traffic data with seasonal patterns, are discussed.

The transcript provides a step-by-step guide on converting imperial measurements to metric for data analysis.

The practical application of Excel's forecasting functions in real-world scenarios, such as predicting traffic flow, is explored.

The transcript concludes by highlighting the need for advanced forecasting tools for more accurate predictions in complex datasets.

Transcripts

we're going to explore the forecast

function in Excel what the forecast

function does is a linear regression

behind the scenes and then uses that to

predict what the next value or a new

value is going to be uh a synonym for

this is forecast. linear they are

exactly the same thing to do this I'm

going to take a data set specifically a

Heights and weights data set from gagle

what this provides is for several people

for about 25,000 people their height in

inches and their weight in pounds I'm

going to take the same data set except

that I'm not going to use the full data

set I'm only going to use a sample

because I would just want to illustrate

the point in our sample we have 1,000

rows and for each of these we have the

height and weight in inches and pounds U

I'm not very familiar with the inch and

pound system so I'm going to convert

this to metric specifically I'm going to

convert the height to CM and and the

weight to kg to convert the height to

centimet I would take the height in

inches and multiply it by 2.54 to

convert uh to kgs I don't know what the

exact formula is U but I do have it uh

looked up here it's yeah

2.204 something so let me divide the

weight in pounds by 2.204 whatever and

drag this all the way to the end great

so now we have all of the these values

let me reduce the number of decimals so

I can read it

out all right so what this is saying is

there's somebody who was 167 cm who

weighed 51.3 kgs and so on now just to

get a feel for what this looks like let

me plot this and a way of uh doing that

that will be intuitive is using a

scatter plot so this scatter plot tells

me that okay if you look at the height

on the x-axis as the height VAR from say

about 160 cm to about 185 CM the weight

grows roughly from around 50 kgs to

maybe around 65

kgs okay that's useful to know so let's

see if we can do an explicit forast if I

said forecast of uh now let's see what

the formula actually expects it's saying

pass an x value for which we want to

forecast so let's say we want to

forecast for a person who who has a

height of 170 cm the known Y in this

case the thing that we want to forecast

the Y is the weight so what would be the

height of somebody who's 170 cm tall so

I've taken all of the Y values which are

the weights and then it's saying known

X's which is all of the height values

pass that and click okay that says that

a person of height 170 cm should be

about 56 kgs which incidently would make

me massively overweight um now look at

the formula again we have the height

value that we want to predict for all of

the weights and all of the heights we

can flip this around I could say uh for

forecast for a person who is let's say

75 kgs of weight what are all the known

Ys in other words what we now want to

predict the height which has become the

Y value so take all the heights pass all

the weights as the other parameter and

it's saying that somebody who's 75 kgs

should be about 6 feet tall 180 cm is

about 6 ft so that is how forecast Works

forecast is exactly the same as

forecast. linear what it's doing behind

the scenes is linear regression and then

using that linear regression coefficient

and slope to calculate the the actual

predicted value for whatever X we are

targeting now supposing you want to do

this for an array U let's say I have a

person of height 150 somebody of height

155 let's say this plus 5 cm and so on

up to maybe let's see uh 6

ft I could to and equals forecast for

each one of these and put in the formula

but there's a better way let let me show

you how I do that so equals forecast of

the x value which is this and the known

y values which is the uh weights and the

known X values which is the Heights and

press enter and copy the same formula

all the way across but this can be

inefficient because each formula is

running independently so what I could

instead do is use another formula called

Trend which is exactly the same thing uh

except the way in which it works is I

pass the known y values which is all of

the weights I pass All the known X

values which is all of the Heights and

then I pass the new X values that is

this set of values and here's where

instead of passing One X value we pass a

range of X values and press enter and

that failed

uh oh I think that's because I got the

formula wrong it's D2 to d01 this should

also be D2 to uh C2 to

c0001 and yes now you'll notice that it

automatically filled the entire column

that's because it's an array formula but

what it's doing behind the scenes is

calculating the regression at one shot

and then applying it for all of the Y

all of the X values and the results are

exactly the same both these functions

that is forecast and forecast. linear

which is a synonym as well as Trend are

available in Google Sheets as well so

you could just save this open it in

Google Sheets make edits save it load it

back back in Excel it works perfectly

fine but this is a linear regression and

it doesn't always apply for all series

very well uh let's take an example or

counter example if you supposing I took

traffic data so here for different

Junctions we have for every hour the

number of vehicles at that Junction now

I'm going to take a subset of this data

and open it so this is traffic data set

and yeah so now for each R we have the

number of vehicles now let's plot this

if I were to look at what the number of

vehicles looks like over time as a line

chart you can see that it's very spiky

understandably because each day there is

a peak and a truff and as the days

progress there's also weekly pattern so

weekends relatively low traffic uh but

okay we also have Christmas relatively

low traffic so that's a 3-day weekend

probably but otherwise it's usually a

two-day weekend middle of the we is

relatively higher so these are seasonal

patterns and if I tried to do a

prediction a linear prediction it

wouldn't be such a good fit let's in

fact try and do that and see what we get

so if I said equals trend of the known Y

which are the vehicle values and the

known X's which are the date times and

let's put in the actual date times CS uh

again to predict what the value will

look like so you'll see that it's

consistently predicting okay about 20

Vehicles 20 Vehicles it's gently may be

increasing up to 21 vehicles and if you

were to plot this let's call this linear

prediction and if we were to plot a

comparison of these two we'll insert a

line

chart and that's what it looks like a

gentle slope upward slope though I can

barly see it but it's yeah like a clock

that is broken it gets it right twice a

day but that's about it to bring in a

certain amount of seasonality we can use

forecast. ETS that's slightly better

than the linear prediction for cyclical

data the way forecast. ETS works is u i

can pick a Target date the date date

that I want to predict for so let's say

I put this value itself the all the Y

values which in this case is the number

of

vehicles and all the X values which is

the timeline and then there are a bunch

of optional values which I'm going to

ignore uh let me just put in the dollars

here because I'm going to drag the

formula uh but I'll just tell you what

the other parameters are the fourth

parameter is seasonality and if you

specify a seasonality like let's say 24

then it means every 24th item

is or 24 items is the seasonality in

this case it would be every 24 hours so

we can explicitly specify 24 hours a

seasonality but if we did not then it

would guess what the seasonality is by

itself and it would probably figure out

that it's 24 data completion if we put

it as one then it replaces the values

missing values by interpolation in our

case we don't have missing values but if

we did then it would replace it by

interpolation and that is the default

and instead if you want missing values

to be zero then you put in zero in our

case leaving both of them at the

defaults is perfectly fine and it

produces a forecast now let's apply

let's extend this forecast to the entire

series and that has yeah filled in the

result and now let's plot this uh ETS

prediction alongside our number of

vehicles prediction so we now have three

series let's see what they look

like okay um that's a little hard to

read so I'll make it bigger and I'll

also make the lines a little thinner so

we can see it better let me click on

each of these click on for mat and

change the weight of this to maybe half

a point um yeah sure the formatting and

change the weight of this one as well to

about half a

point and that allows us to compare

things a little better so the green line

is our prediction the blue line is the

actual you can see that the green line

is not doing a great jum it's on this

weekend is kind of doing an okay job uh

but let's assume this a Sunday Monday

yeah here's a Tuesday where the traffic

was pretty high and it was not able to

match that but it's doing a much much

better job than the orange line which

was just straight linear forecasting so

when it comes to cyclical data you're

better off using forecast. ETS it's not

as good as more advanced forecasting

tools but definitely better than

something like linear uh linear

regression

now in this case the forecast.