Excel Statistics nerds, need your help!

[St. Jerry]

How do I figure this out using Microsoft Excel (too many years since I took stats in grad school to remember and I'm too embarrased to admit to my employees that I don't remember how to do this).

Given that I have:

1. Historical year-over-year monthly sales growth rates for product A and

2. Historical year-over-year monthly sales growth rates for product B and

3. A forecast for next month's year-over-year sales growth rate for product A.

How do I run an equation on Excel to help me forecast what next month's year-over-year sales growth rate for product B is estimated to be based on the historical correlation between the sales growth rates of product A & B?

thanks much.

[iamTRuTH]

Plot product B in a line chart/graph( quantity v. month), then right click and use the forecast tool. you can also show the equation. plug in numbers and it should give you what you are looking for. there might be a shorter way, but that works for me.

[chatton18]

1. If sales data for product A is in Column A, the first month is in row 1, the Yr/Yr sales growth type in a new column =A13/A1-1, this is your yr/yr change for the first month the data is available for. If you then put the cursor on the bottom right of this cell and drag it down until you run out of data points. Do the same thing for product B. To get the Correllation between the two products, if they are in columns a & b from rows 1-100 you would type in =corr(a1:a100,b1:100) This is your correlation. I'd graph up the data to see how the yr/yr growth looks. If its showing some sort of trend, I'd go with adding a trend line (right click on the graph click add trendline. You'll want to click on view equation and R squared, you also may want to . You'll be able to chose between different type of trendlines. WIth a yr/yr graph either polynomial or linear will be your best bet. If the trendline doesn't make sense looking at it, don't use it, but if both make sense, use the one with the higher R squared. If neither of them make sense, simply go with an average of the yr/yr growth for future growth. This can be done by using the formula =average(a1:a100). To then get the corresponding levels for product b multiply each future months value by the correlation value.

[St. Jerry]

Thanks! Will give it a try.

[St. Jerry]

Tried the above suggestions and not getting it to work. thanks to all for your help with this. Needless to say, I am on a serious O.C.D. quest to get this figured out.

So, here is my data set, and what I need to figure out is what ZZZZ (see below) might be:

Independent Dependent
X, Y
-19%, -4.9%
-24%, -5.8%
-25%, -5.2%
-25% , -4.7%
-28% , -4.7%
-28%, -4.1%
-27%, -4.3%
-20%, -4.6%
-13%, -3.5%
-13%, -3.0%
-13%, -2.7%
-9%, -2.0%
-8%, -1.9%
-5%, -1.5%
-3%, -1.6%
-3%, -1.6%
-1%, -1.7%
-1%, -1.5%
1%, -1.2%
3%, -0.6%
1.3%, -0.2%
3.5%, 0.4%
4.0%, 0.9%
1%, 1.6%
2%, ZZZZ?

[Long duc dong]

You could regress y on x, if you want a prediction for ZZZZ, that will give you a model to predict with. You can then plug in x, and get the predicted y. It would be simple linear regression, since you only have one varable. You will get something like this:

yi = b0 + b1(xi)

I would not use excel for this, I would use R. just create an x variable, in which you store the x data, and a y variable in which you store the y data. Then create another variable using the lm command:

results <- lm(y~x)

The results variable will give you your b0(intercept) and b1(x) coefficients.

Here is a tutorial on using R. You really only need to look at the first lesson, and the lesson on linear regression.

http://www.cyclismo.org/tutorial/R/

Hope this helps.

R is free, so don't worry about paying for it.

[wendigo]

ZZZZ = -0.356% for a linear regression.

A regression on that data has a great R^2 at 0.86, but the residuals are making weird patterns...

LDD - why not use Excel for a simple regression? Hell of a lot easier than learning a new programming language for a simple problem. If you don't like the stats package in excel then add in Poptools or just run it via Solver. R is surely cleaner and more powerful, but for a simple linear regression I don't really see the point. YMMV.

St J

Open Excel ->Data->Data Analysis->Regression
Input your Y values - all of them except for ZZZZ
Input your X values - all of them except for the one associated with ZZZZ
Your output will have an Intercept = (b) and an X variable = (m)

Regression is Y=mX+b
ZZZZ=(m * X) + b

[Long duc dong]

...

LDD - why not use Excel for a simple regression? Hell of a lot easier than learning a new programming language for a simple problem. If you don't like the stats package in excel then add in Poptools or just run it via Solver. R is surely cleaner and more powerful, but for a simple linear regression I don't really see the point. YMMV.

For me, I have not used excel for stat calculations, so I like R better.

The calculations in R are pretty simple. You don't really have to learn very much. All you have to do is create the variables, which is not tough, then do results <- lm(x~y), so in this case, there is not much to learn.

the only lines necessary are:
> x <- c(put your data in here...with each entry separated by a comma)
> y <- c(put your y data in here...)
> results <- lm(x~y)

So there really is not all that much to learn to do this. The OP could use R with relative ease, even without a background in it. As I said before, I can't really comment on excel since I don't use it for stat calculations.

[BeanDip4All]

If you want advice, I wouldn't do it this way at all. I would take monthly sales for A, B and build a simple linear model of sales B = offset + alpha * sales A
This should be really easy in excel. Then I would turn my prediction for growth for A into actual values and just apply my simple model and see what the numbers looked like.

So basically, you use the function trend which takes known_y, known_x and returns estimated Y sales B = offset + beta_1 * sales A
ie Y = beta_0 + beta_1 * X
X is known
So you create a column of X where the top of the column is known X and the bottom is predicted X
Next to known X you put known Y
Then below the known Y next to the predicted X you use the trend function to fill in predicted Y
Where the predicted Y is now actual sales, not sales growth you can divide month over month and subtract 1 to get predicted sales growth back from actual sales

You can pm me if you want to send a sample spreadsheet.

Cheers

[St. Jerry]

thanks. got enough to work with here.

[chatton18]

Doing a linear regression, ZZZZ = -0.36%, however, I'm not convinced that this is the appropriate way to go about forecasting this data as the relationship has broken down slightly in the most recent months. Without knowing what the two products are, it is somewhat difficult for me to say. Is there a good reason for the sales of the two products to be related to each other (like if a ski shop sells more skis, they will probably sell more bindings as well) or are they both increasing simply because sales/store traffic or some other variable in general is causing them to increase (like a ski shop is selling more skis and more hats, but that might be because people just have more money to spend). In the first case a linear regression of the two on a scatter chart is the appropriate way to get this estimate. In the second case I would do a regression against time to get your predicted sales for the upcoming month since as far as I know that 2% number is coming out of thin air. Tough to tell you exactly what to do with the information you are providing us. Forecasting (and really statistics in general) is more art than science.