Regression Decomposition

1. Regression Decomposition

Hello All,

Please can someone try to assist?

After running a regression of sales data (y) against various independent variables like price, discount price, advertising etc I have my regression results.

Now is sales changed by +\$10000 year on year - is there a way I can understand what each of the independent variables contributed to that change? I typically refer to it as a decomposition but it may go by a different name.

Example being: sales grew by \$10 000 year on year - \$4000 of that was due to price decrease, \$3000 due to advertising and \$3000 due to promotions.

Please can someone try to help? Any guidance would be greatly appreciated!

Thank you

2. Re: Regression Decomposition

Hi and welcome to the forum,

Basically it is not really excel question. It's rather statistics. Excel could be just used to do the calculations (as a tool).

If the factors are independant and no synergy is observed you can do multiple regression (se how to do it with excel: http://www.real-statistics.com/multi...sion-analysis/ ) and then just calculate change in factor multipled by appropriate equation coefficient.

3. Re: Regression Decomposition

Hello Kaper,

Thank you for the response.

When running a regression on sales data with Independent variables like price, promo, distribution of products etc - there would undoubtedly be synergy of these variables right?

With regards to your suggestion - perhaps I can send you an example sheet of data (as you suggested ) to see if I understand your last sentence?

Thank you for the feedback!

4. Re: Regression Decomposition

How to attach a sample workbook:
* Make sure there is just enough data to demonstrate your need.
* Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.
* Make sure your desired results are shown, mock them up manually if necessary.
* Remember to desensitize the data.
* Click on GO ADVANCED and use the paperclip icon to open the upload window. View Pic

5. Re: Regression Decomposition

Hello again Kaper,

Thank you for the walk through. New on this forum.

I have attached the example data .

Thank you!!

6. Re: Regression Decomposition

Small disclaimer: I'm not statistician.

Having said that, please look at p-value for regular price (cell N20). It is almost 0.9 while in this test only values < alfa (statistical significance - usually 0.05) lead us to rejection of hipotesis of no influence of given factor. So from the statistical point of view we cannot reject hipotesis that regular price has no influence on sales value.

7. Re: Regression Decomposition

I realise the P-vales for this model are greater 0.05 - but for the specific question of a decomp... can we just assume for now that it is significant?

Just so that I can understand how to achieve the decomp please?

Thanks.

8. Re: Regression Decomposition

It's probably not in my competence field. Sorry. All my teachers told me: if there is no correlation, don't even think of regression. So have no idea how to approach such decomposition task.

There are currently 1 users browsing this thread. (0 members and 1 guests)