Forecasting in Excel Using Simple Linear Regression

Summary

This video tutorial demonstrates how to perform a regression forecast using simple linear regression in Excel. It guides viewers through calculating the intercept and slope using the first year of data, then applying the regression to forecast future values. The process includes creating a regression equation, copying the formula for forecasting, and evaluating the forecast's accuracy using various error measures and the USE statistic. The tutorial emphasizes the importance of adjusting formulas correctly to maintain accuracy when forecasting.

Takeaways

• 📈 The video demonstrates how to perform a regression forecast using simple linear regression for data with a growth component.
• 🔢 It focuses on using only the first year of data (12 months) to estimate the intercept and slope for the regression model.
• 💼 Excel's Intercept and Slope functions are introduced as an easy method to calculate the regression coefficients.
• 📋 The script guides through the process of highlighting the correct data ranges for known y's (demand) and known x's (time periods).
• 💡 The intercept is calculated first, followed by the slope, with the values being 80.667 and 7.57 respectively.
• 📊 A forecast is created by applying the regression equation to the entire dataset, ensuring the formula is correctly copied down.
• 📝 The script emphasizes the use of the ROUND function to avoid forecasting in half units, maintaining accuracy.
• 📉 The video explains how to calculate forecast accuracy measures such as error, absolute error, percentage error, and squared error.
• 📊 It introduces the use statistic (U统计量) as a measure to compare the forecast's accuracy against a naive forecast method.
• 📊 The script concludes with calculating overall accuracy measures like mean error, mean absolute error, mean squared error, and the use statistic.

Q & A

What is the main purpose of the video?

The main purpose of the video is to demonstrate how to perform a regression forecast using simple linear regression in Excel, focusing on estimating an intercept and a slope to create a forecast based on data with a growth component.

Why is only the first year of data used for creating the regression coefficients?

Only the first year of data is used to create the regression coefficients to establish a baseline for the forecast, ensuring that the model is built on initial data trends without being influenced by potential changes in later data.

How does the video suggest calculating the intercept in Excel?

The video suggests using the INTERCEPT function in Excel, which requires the known y's (demand) and known x's (time periods) as inputs to calculate the intercept.

What function is recommended for calculating the slope in the regression?

The video recommends using the SLOPE function in Excel, which similarly requires the known y's (demand) and known x's (time periods) to calculate the slope of the regression line.

How is the forecast created in the video?

The forecast is created by typing the regression equation into the forecast column in Excel, which involves the intercept, the slope, and the period number, and using the ROUND function to ensure whole units are forecasted.

Why is the ROUND function used in the forecast equation?

The ROUND function is used to ensure that the forecast does not result in half units, which would not be practical for the data being analyzed.

What is the significance of the F4 key mentioned in the video?

The F4 key is used to apply absolute cell referencing ($ signs) to the intercept and slope in the formula, ensuring that these values remain constant when the formula is copied down the column.

How does the video address the change in slope in the second year of data?

The video acknowledges that the slope changes slightly in the second year, indicating that the simple linear regression model fits better for the first year of data, and this is a limitation of the model.

What is the purpose of calculating forecast accuracy measures in the video?

Calculating forecast accuracy measures, such as forecast error, absolute error, percentage error, and squared error, helps to evaluate the performance of the regression forecast and identify areas where the forecast may be less accurate.

How is the Mean Squared Error (MSE) calculated in the video?

The Mean Squared Error (MSE) is calculated by taking the average of the squared errors, which is the sum of the squared forecast errors divided by the number of observations.

What is the role of the USE (Unbiased Sample Error) statistic in the video?

The USE statistic is used to compare the accuracy of the regression forecast to a naive forecast. It is calculated by taking the square root of the ratio of the sum of squared errors to the sum of squared errors that would have been obtained using the naive method.

Outlines

00:00

📊 Introduction to Running a Regression Forecast

The speaker begins by introducing the process of creating a regression forecast using simple linear regression. The focus is on data with a growth component, aiming to estimate an intercept and a slope. The first year of data, consisting of 12 months, is selected for creating the regression coefficients. The speaker demonstrates how to use Excel's Intercept and Slope functions to calculate these values. The known y's, representing demand, and known x's, representing time periods, are inputted into the functions to obtain an intercept of 80.667 and a slope of 7.57. The next step is to apply the regression equation to the entire dataset to create a forecast, ensuring that the forecast values are rounded to avoid half units.

05:02

📈 Evaluating Forecast Accuracy with Error Metrics

In the second paragraph, the speaker discusses the evaluation of the forecast's accuracy using various error metrics. The forecast error is calculated as the difference between actual demand and the forecasted value. Absolute error, percentage error, and squared error are derived from this forecast error. The Mean Squared Error (MSE) is introduced as a measure of the forecast's accuracy, comparing it to a naive forecast method. The speaker sets up columns for error, absolute error, percentage error, and squared error with conditional formatting to visually assess the forecast's performance. The Mean Error, Mean Absolute Error, Mean Squared Error, and Mean Absolute Percentage Error are calculated to provide an overall measure of the forecast's accuracy. The Mean Squared Error (MSE) is also calculated to quantify the forecast's performance relative to the naive forecast method.

Keywords

💡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 to observed data. In the video, linear regression is used to forecast data that has a growth component, with the goal of estimating an intercept and a slope based on the first year of data. The script mentions using the intercept and slope functions in Excel to perform this regression, which are essential for creating the forecast.

💡Intercept

The intercept in a linear regression model is the value of the dependent variable when all the independent variables are equal to zero. It represents the starting point of the regression line on the y-axis. In the context of the video, the intercept is calculated using the first 12 months of data, which is then used as a part of the regression equation to forecast future values.

💡Slope

The slope in a linear regression model represents the rate of change of the dependent variable with respect to the independent variable. It indicates how much the dependent variable is expected to change when the independent variable increases by one unit. In the video, the slope is calculated using the first 12 months of demand data and the corresponding time periods, and it is used alongside the intercept to predict future values.

💡Forecast

A forecast in the context of the video refers to the prediction of future values based on historical data using a statistical model, such as linear regression. The video demonstrates how to create a forecast by applying the regression equation to the entire dataset, which includes using the calculated intercept and slope to estimate future demand values.

💡Time Series

A time series is a sequence of data points ordered in time. In the video, the time series data represents the demand over a period of months, and the script describes how to use the first 12 months of this time series to create a linear regression model for forecasting future demand.

💡Excel Functions

Excel functions are pre-built formulas that perform calculations on data within a spreadsheet. The video script specifically mentions using the 'INTERCEPT' and 'SLOPE' functions in Excel to calculate the necessary parameters for the linear regression model. These functions simplify the process of performing regression analysis within the Excel environment.

💡Round Function

The 'ROUND' function in Excel is used to round a number to a specified number of decimal places. In the video, the round function is applied to the regression equation to ensure that the forecast values are presented in whole units, avoiding fractions which might not be meaningful in the context of the data.

💡Forecast Error

Forecast error refers to the difference between the actual observed value and the forecasted value. In the video, the script describes calculating forecast error by subtracting the predicted demand from the actual demand. This error is then used to evaluate the accuracy of the regression model.

💡Mean Absolute Error (MAE)

Mean Absolute Error (MAE) is a measure of accuracy for a forecast method, calculated as the average of the absolute differences between the forecast and the actual values. The video script includes calculating MAE as part of the process to evaluate the forecast's accuracy, which helps in understanding how well the model is performing.

💡Mean Squared Error (MSE)

Mean Squared Error (MSE) is a risk metric corresponding to the expected value of the squared (quadratic) error or loss. In the video, MSE is calculated by squaring the forecast errors and then averaging them. It is used as a measure of the quality of the linear regression model, with lower values indicating better fit.

💡Mean Squared Error (MSE) for Naive Forecast

The naive forecast method assumes that the next period's value will be the same as the last observed value. In the video, the MSE for the naive forecast is calculated to compare it with the MSE of the linear regression model. This comparison helps in determining the effectiveness of the regression model over a simpler forecasting method.

Highlights

Introduction to running a regression forecast based on simple linear regression.

Explanation of using the first year of data to create regression coefficients for the intercept and slope.

Demonstration of using Excel's Intercept function to calculate the intercept.

Guidance on selecting known y's (demand) and known x's (time periods) for the Intercept function.

Calculation of the slope using Excel's SLOPE function.

Instructions on applying the regression equation to the entire dataset.

Use of the ROUND function to avoid forecasting in half units.

Detail on setting up the regression equation in the forecast column.

Ensuring the regression formula remains static when copied down.

Visual representation of the forecast as a straight line on the graph.

Discussion on the fit of the forecast for the first and second year of data.

Introduction to calculating forecast accuracy measures like error, absolute error, and percentage error.

Explanation of the squared error and its role in the forecast accuracy.

Calculation of the USE (Unit Squared Error) statistic for forecast evaluation.

Methodology for calculating the mean error and mean absolute error.

Process of determining the Mean Squared Error (MSE) and USE statistic for overall forecast accuracy.

Final thoughts on the effectiveness of simple linear regression for forecasting in Excel.

Transcripts

okay in this video I'm going to show you

how to run a reg forecast based on a

linear regression a simple linear

regression and the idea is basically

that we have data with uh some kind of a

growth component in it and uh it's uh

moving

upwards so we are going to estimate an

intercept and a slope that will then be

used to create the forecast we only only

going to use the first year of data

which is the first 12 months that we

have here to create um our uh regression

uh coefficients to estimate the

intercept and the slope and then we're

going to apply the regression to to the

entire set of

data now let's get started so first of

all we have to calculate our intercept

and our slope there are several ways to

do uh to run a regression in in Excel

but the easiest one for this case is to

just use the Intercept in the slope

function so to use the intercept

function we type in

intercept and then we open the

parentheses and then it asks us for two

things our known y's and our known X's

our known y's are basically the uh uh

output that we want the forecast to

achieve so in this case that would be

our demand so we are going to

highlight the first 12 months of demand

then our known X's are going to tell us

where in the time series we are so for

that I've created this column here

that's called period I'm going to have

highlight there again the first 12

periods going close the parentheses and

then it will give us a intercept of 80.

667 now to calculate the slope it works

very similarly we're going to type slope

of parentheses and then the function uh

uses the same logic we highlight our

first 12 months of

demand and then we highlight our first

12 periods that are numbered

sequentially and close our parentheses

hit enter and here we go our slope is

7.57

7 all right so now in order to uh create

the forecast we have to you uh basically

type in the

regression equation into uh our forecast

column

so before we type in the actual

regression equation we want to make sure

that we don't forecast half units so

we're going to use the round

function and we're going to say round

and then the general way of regression

is uh is set up is the forecast equals

The

Intercept Plus the slope times how many

periods we are

in so we are going to say

intercept we want to make sure that when

we copy this formula down it stays put

so I'm going to hit F4 it's going to

place two dollar signs in there and that

will ensure that when we copy the

formula will stay

put

plus slope

times the period number again for slope

we have to go back and also hit the four

and make sure that it stays put when

we um copy the formula down and then I

am just finishing up this formula here

by making sure that the round function

is complete we close the parentheses and

there we go that's our regression

equation now all I need to do is I need

to copy this down and here we go we see

on our graph we just created a straight

line

forecast for 2 years worth of data it

fits a little better for the first year

than for the second year because for

some reason the slope changes slightly

in the second year however that is

basically uh how a simple linear

regression would work okay now just to

ensure that we uh um created a good

forecast I'm going to leave out the

first period for

because in order to calculate the use

statistic we uh we need uh one period

before so I'm going to just consistently

calculate all my uh forecast accuracy

measure for starting in Period to if you

really wanted to you could even start in

year two because that's when you when it

really matters but we're we're going to

start in Period two in this case so our

forecast error is basically demand minus

forecast our absolute error is the

absolute value of that forecast

error our percentage error we're going

to do the uh absolute percentage error

in this

case equals our absolute error divided

by

demand our squared error is basically

our error term that we raise to the

second

power and then finally the denominator

for our use statistic which the use

statistic basically Compares how good

this forecast is to the naive

forecast so we the the numerator of that

is the squared error we just need the

denominator which would basically be the

squared error we would have obtained if

we would have used uh the na method so

that is to open up

parentheses demand of the current period

minus then one period earlier because

that's what the na method would using a

forecast and we raise that to the second

power okay so now all we need to do is

we need to copy all of these formulas

down here and I set up uh the error the

absolute error the percentage error and

the squared error colums with

conditional for formatting so we can see

a little bit um where most of the errors

occur and it uh just visually makes it a

lot easier to to see how good our

forecast is okay okay now to to

calculate overall accuracy measures we

have to calculate the mean eror which

basically means we take the

average of column e which is we take the

average of our

errors then we have to calculate our

mean absolute error which basically the

average

of that column here which just call

F our mean absolute percent

error again the average

of column

G our MSC is basically the average of

column H which is all of square Earth

and then finding the use statistic is

the square root of the sum of the

squared errors divided by the

sum of the squared errors we would have

obtained with if we would have performed

the use statistic we see s qrt

parentheses and then we want to sum be

careful to actually sum them and not

average them like for the other

ones sum the squared errors and then

divide them that by the

sum of the square for the use

statistic and close two parentheses and

there we

go so this was a forecast using simple

linear regression in

Excel