How much ice cream will Sally sell on any given day? Todays Episode 624 tries to figure out a relationship between temperature, rain and ice cream sales.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast. I'm Bill Jelen. Today is my last podcast about forecasting.
I promise until I get email from the last four or five episodes.
Today we have a series of sales that really make no sense at all.
It just spikes up and down up and down and neither linear nor geometric forecasting is going to work here.
There's something else causing this so we go back and take a look at the data.
Well, this is sales at an ice-cream stand.
It's an ice-cream stand at a park and we talked to the owner of the stand says, you know, what seems to be the factors, what happens on the days when you sell a lot of ice cream?
She says well obviously if it's hot I do very very well, and if it's raining, I do very very poorly so we took these sales data, and we plotted it along with a couple other columns.
1 if it rains, 0 if it didn't and then also the high temperature of the day.
And we want to see if there's some relationship between temperature, rain and sales of ice cream.
Now I'm going to use the LINEST function, but I'm going to use the larger version of LINEST that will give me all of the statistics.
So we start out with =LINEST. The known Y's were the sales. These were the sales.
The known X's are both the columns. The rain and the temperature column.
And I'm going to ask for stats, I'm going to put true here as the fifth item and because this is going to return several answers.
It's actually going to return 5 rows, and it's gonna return 3 columns.
Now the reason it's three columns is because we have two different known axis.
We have temperature and rain plus it has to return one more column for the intercept, so you always choose one more column, then you have columns of x data.
Control shift enter is how we creat this.
It's kind of hard to figure out how this works, but basically you start at the top right corner and that's the y-intercept, so in that case at 0 the temperature factor is immediately to the left of that.
Okay So in my case temperature was the first known x that actually starts to appear backwards so we have y-intercept at the right column.
Then working backwards from that temperature and rain and we can build a prediction now, that basically says 0 plus today's temperature times 2.13 plus today's rain times -110.
I've already created that formula here, and you'll see that, we get a prediction and when we plot the prediction and the sales they actually seem to come fairly close to each other.
Now if you had taken any statistics, you'll know that there's a whole bunch of statistics about this regression that we can get.
I've drawn in here a little map showing all of the values that returned when we asked for statistics.
And the next line we get is the standard error the one that I always pay attention to is R-Squared.
R-Squared says how well is this model predicting.
It's a value from 0 to 1. 1 means that it's perfectly predicting what's going on.
So a 0.88 means it's pretty good, but there's probably some other factors, maybe weekends do better than weekdays or vice-versa or something like that also a whole bunch of other statistics here, in case you would need those "Residual Sum of Squares", "Degrees of freedom", the "F-Statistic".
All get returned. The frustrating part is you have no clue exactly which order things get returned in.
I always have to turn back to the [ inaudiable ] to figure out which each of these statistics mean.
So, using multiple regression is a bit more realistic than just assuming a straight line growth, you can set up some independent variables and see if your dependent variables can be predicted using the regression.
Hey thanks to Leon for sending in a question a week ago, that got me started on this whole forecasting thing and thanks to you for stopping by.
Stop back tomorrow for another netcast which I promise will have nothing to do with forecasting.
from MrExcel.
I promise until I get email from the last four or five episodes.
Today we have a series of sales that really make no sense at all.
It just spikes up and down up and down and neither linear nor geometric forecasting is going to work here.
There's something else causing this so we go back and take a look at the data.
Well, this is sales at an ice-cream stand.
It's an ice-cream stand at a park and we talked to the owner of the stand says, you know, what seems to be the factors, what happens on the days when you sell a lot of ice cream?
She says well obviously if it's hot I do very very well, and if it's raining, I do very very poorly so we took these sales data, and we plotted it along with a couple other columns.
1 if it rains, 0 if it didn't and then also the high temperature of the day.
And we want to see if there's some relationship between temperature, rain and sales of ice cream.
Now I'm going to use the LINEST function, but I'm going to use the larger version of LINEST that will give me all of the statistics.
So we start out with =LINEST. The known Y's were the sales. These were the sales.
The known X's are both the columns. The rain and the temperature column.
And I'm going to ask for stats, I'm going to put true here as the fifth item and because this is going to return several answers.
It's actually going to return 5 rows, and it's gonna return 3 columns.
Now the reason it's three columns is because we have two different known axis.
We have temperature and rain plus it has to return one more column for the intercept, so you always choose one more column, then you have columns of x data.
Control shift enter is how we creat this.
It's kind of hard to figure out how this works, but basically you start at the top right corner and that's the y-intercept, so in that case at 0 the temperature factor is immediately to the left of that.
Okay So in my case temperature was the first known x that actually starts to appear backwards so we have y-intercept at the right column.
Then working backwards from that temperature and rain and we can build a prediction now, that basically says 0 plus today's temperature times 2.13 plus today's rain times -110.
I've already created that formula here, and you'll see that, we get a prediction and when we plot the prediction and the sales they actually seem to come fairly close to each other.
Now if you had taken any statistics, you'll know that there's a whole bunch of statistics about this regression that we can get.
I've drawn in here a little map showing all of the values that returned when we asked for statistics.
And the next line we get is the standard error the one that I always pay attention to is R-Squared.
R-Squared says how well is this model predicting.
It's a value from 0 to 1. 1 means that it's perfectly predicting what's going on.
So a 0.88 means it's pretty good, but there's probably some other factors, maybe weekends do better than weekdays or vice-versa or something like that also a whole bunch of other statistics here, in case you would need those "Residual Sum of Squares", "Degrees of freedom", the "F-Statistic".
All get returned. The frustrating part is you have no clue exactly which order things get returned in.
I always have to turn back to the [ inaudiable ] to figure out which each of these statistics mean.
So, using multiple regression is a bit more realistic than just assuming a straight line growth, you can set up some independent variables and see if your dependent variables can be predicted using the regression.
Hey thanks to Leon for sending in a question a week ago, that got me started on this whole forecasting thing and thanks to you for stopping by.
Stop back tomorrow for another netcast which I promise will have nothing to do with forecasting.
from MrExcel.
