Monthly Sales Forecast with Seasonality and Trend - EXCEL regression with dummy variables

Подписаться 2,6 тыс.

Просмотров 46 тыс.

50% 1

Welcome to our comprehensive tutorial on Monthly Sales Forecasting using Excel Regression with Dummy Variables, where we'll guide you through the intricacies of forecasting sales with both seasonality and trend. Whether you're a business analyst, data enthusiast, or just looking to enhance your Excel skills, this video is your gateway to mastering the art of accurate sales predictions.
📊 In this step-by-step tutorial, you'll learn:
1️⃣ The fundamentals of regression analysis in Excel, leveraging the powerful Data Analysis Tool Pack.
2️⃣ How to effectively capture and incorporate seasonality into your sales forecasts using dummy variables.
3️⃣ The importance of recognizing and accounting for trends in your data to make more accurate predictions.
4️⃣ A deep dive into Winter's Smoothing Method, demystifying this widely-used forecasting technique.
5️⃣ Practical tips and best practices to ensure your forecasts are reliable and actionable.
Sales forecasting is a critical aspect of business planning, helping you make informed decisions on inventory management, resource allocation, and overall business strategy. Whether you're dealing with historical sales data, financial planning, or simply want to gain a deeper understanding of predictive analytics, this tutorial will provide you with the knowledge and skills you need.
===== CHAPTERS =====
2:01 - Create the Dummy Variables
3:31 - The Regression tool in Excel's Analysis Tool pack
7:01 - Create the Regression Model
10:00 - Visualize with Excel Chart
#ExcelRegression #SalesForecasting #DataAnalysis #DummyVariables #WinterSmoothing #BusinessAnalytics #ExcelTutorial #DataScience #TrendAnalysis #SeasonalityForecasting #BusinessIntelligence

Наука

Опубликовано:

14 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 88

@chilarmah Год назад

Great work! Solved a problem I have been working on for days.

@Data.Analytics.Central Год назад

Thank you Hussain for the appreciation!

@JohnKamauNjenga Год назад

Simple and elegantly presented. Was working on a forecast and other descriptions online were abhorrent to say least. The error range was HUGE, but thanks to you my standard error reduced to 2%. Asante Sana!!!🤗

@Data.Analytics.Central Год назад

Dear John, thank you very much for the appreciation!

@EricD_192 Год назад

Great content explained in detail! Amazing!

@Data.Analytics.Central Год назад

Dear Erick, thank you very much for your feedback!

@mariusalexandrugogorita 3 месяца назад

Best video ever! Thank you very much!

@Data.Analytics.Central 2 месяца назад

Thanks a lot for the appreciation! 🤝

@Tiramisu2024 3 месяца назад

The best lecture, Thank you so much!

@Data.Analytics.Central 2 месяца назад

Thanks a lot the kind feedback!

@merjenorazmammedova6516 7 месяцев назад

Thank you for the video, it is really helpful!

@Data.Analytics.Central 7 месяцев назад

Thank you Merjen for the appreciation. I am glad you have found it useful!

@EkundayoOnifade 2 месяца назад

Thanks for the video was very useful.

@Data.Analytics.Central 2 месяца назад

Thanks a lot for the appreciation! 🤝

@ahuhokay 3 месяца назад

u are the best bro thank you for the help🙏

@Data.Analytics.Central 2 месяца назад

Thanks a lot the kind feedback!

@hafizelbadawi5409 6 месяцев назад

I would like to thank you so much

@Data.Analytics.Central 6 месяцев назад

Hi, Hafiz. Thanks a lot for the feedback! 🤝

@adityavedam1174 16 дней назад

Could you share the data file for Practice please ?

@caseykirkup 2 месяца назад

Amazing

@Data.Analytics.Central 2 месяца назад

Thanks a lot for the appreciation! ❤️

@sacca9156 6 месяцев назад

Hello Sir Where can I get the Excel worksheet to follow your presentation. Thanks

@davidjosevarelagarcia7011 8 месяцев назад

Great video, is very usefull, thanks. i have a question, why dont use december when you transpose the months?

@Data.Analytics.Central 8 месяцев назад

Hi David, thanks for the appreciation! We do not use December because the 12-th category is captured by the intercept, and is specified when all the 11 dummy variables are all set to zero. This is the general rule: one variable less then the number of categories. If you have just 2 categories (yes and no) you put just one dummy (yes).

@sisayzewde1728 6 месяцев назад

I think residual should be zero or close to zero! right? but in your case it is too much; so, can we say your forecast is good?

@MichaGouszka Год назад

Thank you for great content! What if we would like to add another variable - i.e., a change in product prices (let's assume cyclical price increases, as well as occasional promotions, for example, for a month of time)? How would your model then need to be modified?

@Data.Analytics.Central Год назад

Hi Michal, thank you for the question! If you have a one-off event, such as a price promotion, that you want to include in your Holt-Winters exponential smoothing model, there are a few different approaches you can take: 1. Include the event as a predictor in the model: If the event has a clear effect on the time series being forecasted, you can add it as an additional predictor in the model. For example, if you are forecasting sales and you have data on the price of the product, you can add a binary predictor to the model to indicate whether the promotion is occurring in a given month. 2. Adjust the trend component to account for the event: If the event has a more complex effect on the time series, you may need to adjust the trend component of the model to take it into account. For example, if the promotion is expected to have a significant impact on sales, you could adjust the trend component to reflect this. 3. Incorporate the event into the seasonality component: If the event has a seasonal effect on the time series, you can incorporate it into the seasonality component of the model. For example, if the promotion is expected to have a particularly strong impact on sales in a particular month, you could adjust the seasonality component for that month to reflect this. I hope this helps!

@HessaM-bc6tn Год назад

This’s really useful thank you! Could you please do a tutorial on that?

@marcelporcescu4675 11 дней назад

But how can we do daily. I tried but it show only 16 variable maximum, but we have 30 days

@roshandhumal1193 5 месяцев назад

Sir, could you please explain us why we have to lock 🔒 intercept, please explain and please explain me about p value.

@Data.Analytics.Central 5 месяцев назад

Hello, we lock the Intercept because in the equation the intercept is only one, while the coefficients are multiple. Please read here about the p value: www.investopedia.com/terms/p/p-value.asp

@emilytran84 2 месяца назад

Thank you for your great video! Do you think this work correctly with 12 months data historical and some month value = 0 as well ? I look forward to hearing your advices. Then how can we apply for multi sku products.

@emilytran84 Месяц назад

Hello, I look forward to hearing from you :)

@aapriyanka8323 8 месяцев назад

Excellent sir. Can we use the same process for 5 year forecast. Please do reply sir

@Data.Analytics.Central 8 месяцев назад

Yes, for sure. If you have monthly seasonality

@kiamarieamedao1398 8 месяцев назад

Hello! Don't know what I did wrong but when I tried to use the Regression tool in Excel's pack, it said "The number of rows and columns in X range cannot be the same."

@Data.Analytics.Central 8 месяцев назад

Hello, these can be the causes: Case 2: The number of rows is less than the number of x-columns It is not statistically valid for the number of rows to be less than the number of x (variable) columns. The number of rows of data must be larger than the number of columns of data (x-columns plus y-columns). Case 3: You specify a zero constant Do not specify a zero constant (b=0) in the function.

@user-cb1sc7se8g 2 месяца назад

If I have to do the same thing but on a day level How can I achieved that?

@sayednab Год назад

would you mind explain, why to exclude the last month on your dummy variable?

@Data.Analytics.Central Год назад

Hi Nab, thanks for the question. That is because the 12-th category is captured by the intercept, and is specified when all the 11 dummy variables are all set to zero. This is the general rule: one variable less then the number of categories. If you have just 2 categories (yes and no) you put just one dummy (yes).

@sayednab Год назад

@@Data.Analytics.Central I see. It makes sense. thanks for the explanation sir. could we use this model for stock price as well?

@Data.Analytics.Central Год назад

@@sayednab It is available for any pattern that has seasonality.

@sisayzewde1728 6 месяцев назад

and also, why you used three years data? what will be wrong if I use two- or four-years data?

@Hanspeterbretti Год назад

Is is usable for other figures like ebt, ooe, etc?

@Data.Analytics.Central Год назад

It applies to any metric that has seasonality and trend.

@ericamartin98 Год назад

Hello. When using this method, Excel is showing the value function but I don't know what I did wrong. Any idea?

@Data.Analytics.Central Год назад

Hello. It maybe from the relative/fixed cells references in the formula Mmult(). Please check what column/rows must be fixed and what relative. I hope this helps.

@tanvisharma924 Год назад

SAME , DID U FIND THE ANSWER?

@stefan-kk8ln 9 месяцев назад

Yes, I spent 15 minutes confused about why I got it as well, and it's because you didn't fill out the binary numbers on the forecast sections, but only on the input before forecast that is

@rahuldhali7681 Год назад

Hey, what if we have to forecast yearly sales. How many years would we need to enter as the dummy variable ?

@Data.Analytics.Central Год назад

Hi Rahul, the number of dummy variables is equal with the number of seasons minus 1. So if your seasonality is 5 years, you use 4 dummy variables.

@nurul.alifiaa 11 месяцев назад

@@Data.Analytics.Central Hello, i want to ask if i'm going to forecast annual prices for 6 years (2023-2029) so the row for the dummy variable should be (2023-2028), right? pls kindly enlighten me, thank you very much!

@Data.Analytics.Central 11 месяцев назад

@@nurul.alifiaa Hi, Nurul. The number of dummy variables are related to the number of seasons that you have in your data. So, if you have 4 seasons, you use 3 dummies. And then you can extrapolate on how many years you want. In my model I have forecasted only one extra year vs. the actual data, but we can drag down the formula for another 6 years to forecast.

@nurul.alifiaa 11 месяцев назад

@@Data.Analytics.Central aaaa so if i have datas from jan 2013-juli 2023, i can use jan-nov (11) dummies? i mean i can use the same formulas like yours (?) anyway thank you so much for your answer!!

@Data.Analytics.Central 11 месяцев назад

@@nurul.alifiaa First you have to understand how many seasons you have. If it is 12 months, you will have 11 dummies, but but you need histroric data for at least 12 months, so you can train the model (it is not enough just jan23-july23

@sushmabasnet4593 Год назад

What about daily forecast, how do we create matrix?

@Data.Analytics.Central Год назад

Hi Sushma, You must create separate sets of dummy variables for different seasonality: - To capture day of the week seasonality, create 6 dummy variables. - To capture day of the month seasonality, create 30 dummy variables - To capture month of the year, create 11 dummy variables.

@roshandhumal1193 6 месяцев назад

Sir what If we have Jan to March instead of Jan to Dev Because of I have value from Jan to March and when I am doing the method the out is not showing right could you please give me a hint

@Data.Analytics.Central 2 месяца назад

Hello, I hope you follow the rule: one variable less then the number of categories, so in your case of 3 months you use 2 variables

@Mavis_1991 8 месяцев назад

When I used the Regression function, the numbers in the P-Value is #NUM! Any idea why?

@Data.Analytics.Central 8 месяцев назад

Hello, please check these: support.microsoft.com/en-us/office/how-to-correct-a-num-error-f5193bfc-4400-43f4-88c4-8e1dcca0428b and these: The "NUM!" error in regression analysis usually occurs when there is a problem with the data in the cells that are being used for the analysis. This error message indicates that Excel is unable to perform the calculation because it is encountering a value that is not a number. There are a few reasons why you might be seeing this error message in your regression analysis. One possibility is that there are blank cells or cells with text in the range of data that you are using for the analysis. Another possibility is that there are cells with errors, such as #DIV/0! or #VALUE!, in the range of data. To fix this issue, you can try the following steps: Check the range of data that you are using for the analysis and make sure that there are no blank cells or cells with text in the range. Check for any cells with errors in the range of data and correct them if necessary. If you have deleted some fields of data, make sure that you have not accidentally deleted any cells that are being used in the analysis. Check the formatting of the cells in the range of data and make sure that they are all formatted as numbers. If none of the above steps work, try copying the data to a new worksheet and performing the analysis again.

@Mavis_1991 8 месяцев назад

@@Data.Analytics.Central I still get #NUM! on my P-values.

@Seftehandle 6 месяцев назад

P value error #num and some very low coefficients for significance f 0.8465 f 0.56 p greater than 0.05. please do similar videos on different. Stat methods

@Data.Analytics.Central 6 месяцев назад

Hi Tina, thanks for watching the video!

@nikomou3426 7 месяцев назад

so "t" is for trend and "jan" thru "nov" is for seasonality. am i correct

@Data.Analytics.Central 7 месяцев назад

Exactly!

@ramchandranemade5676 11 месяцев назад

How we can analyse the forecast with second order linear function with seasonality.

@ramchandranemade5676 11 месяцев назад

Possible?

@Data.Analytics.Central 11 месяцев назад

Hello, can you give more context to your question?

@ramchandranemade5676 11 месяцев назад

I have a data which follows the quadratic graph. 2nd order linear equation fit the data. If I apply this trend+seasonality method starting forecast (from period 1 to 6) getting negative.

@ramchandranemade5676 11 месяцев назад

Above method follow single order trend. is there same method which consider the period square term also.(2nd order)

@Data.Analytics.Central 11 месяцев назад

@@ramchandranemade5676 To clarify, the dummy variable method is typically used for linear regression models to represent categorical variables as binary variables (0 or 1). However, it is not directly applicable to quadratic models. In a quadratic regression model, you can still include categorical variables by converting them into numeric form (e.g., using integer encoding) and incorporating them as independent variables with quadratic terms if necessary. For example, if you have a categorical variable with three levels (A, B, C), you can use two numeric variables (e.g., 0 and 1) to represent this variable and then include both linear and quadratic terms for these numeric variables in the model. So, while the dummy variable method is not directly used in quadratic regression models, you can still include categorical variables in a modified numeric format and consider their linear or quadratic effects in the model.

@KhinMohMohSoe 10 месяцев назад

why time period t is used ?.

@Data.Analytics.Central 10 месяцев назад

t is the trend. The rest are the seasons.

@tanvisharma924 Год назад

why dummy variable was used

@Data.Analytics.Central Год назад

Dummy variables are useful because they allow us to include categorical variables in our analysis, which would otherwise be difficult to include due to their non-numeric nature.

@hipernet 10 месяцев назад

why don't you use December?

@Data.Analytics.Central 10 месяцев назад

Hello, thanks for the question. That is because the 12-th category is captured by the intercept, and is specified when all the 11 dummy variables are all set to zero. This is the general rule: one variable less then the number of categories. If you have just 2 categories (yes and no) you put just one dummy (yes).

@hipernet 10 месяцев назад

thanks@@Data.Analytics.Central . All my p values are bigger than 0.05, even my f and r2 are good. Then I cannot use this way, right? finally im using =forecast.ets()

@vijaymane34 4 месяца назад

Disaponted. You didn't tell how to get trend (series) out from the time series.

@vijaymane34 4 месяца назад

Intercept + time trend * period number (1 to n)

@JayJay-fz7sw 8 месяцев назад

Its giving a biased forecast line

@Data.Analytics.Central 8 месяцев назад

Hi, I hope your data has a linear relationship between the independent variables and the dependent variable.

@catalin.ardeleanu Год назад

interesting example. Science based :) What do you think about latest forecast functions included in the "pack" =FORECAST.ETS.SEASONALITY()?

@Data.Analytics.Central Год назад

Dear Catalin, Thank you for the appreciation! We can arrive at the same results as the ones I did in the video by simply using the Excel function FORECAST.ETS(). For example the forecasted value of 01-Jan-18 (380.432$ in cell D38) can be calculated like this: =FORECAST.ETS(A38,$C$2:$C$37,$A$2:$A$37,1,1,1) and the result would be 378.675$, very close to the manual method. The related function FORECAST.ETS.SEASONALITY() is just telling you what is the seasonality that the previous function FORECAST.ETS() was based on. If we aply it to our case: =FORECAST.ETS.SEASONALITY(C2:C37,A2:A37) we get the result 12, just what we have expected (12 months). I hope it helps!