multivariate regression ?

#1
Hello everyone,

I would like to check whether gross margin% is different in different segments. With segments I mean f.e. quantity, weight, geographical location of customer, market segment of customer, etc.

My little knowledge about statistics gave me the idea of doing a regression analysis. I have some trouble however converting the categorical variables numerical variables. What I did was give each different value of the categorical variables a unique number ranging from 1 to... (f.e. country A = 1, country B = 2,...) I'm not sure this is the correct way. I'm also not sure the regression is the right way to go but this is so far what I've come up with.

When I make a pivot table of the data in all different segments I see there are (in my opinion) some significant differences. However I would like to know which variables play a significant role and which ones I can leave out.

Any help is more than welcome.

Thanks in advance.

PJ
 
#2
Most of the statistics programs I've come across will handle categorical variables.
However, maybe you're trying it in something like excel (given you mention pivot tables).

In which case you may want to have a read around the use of "dummy variables".
 
#3
Thanks, I'm indeed working with excel. I've searched for "dummy variable" and think I know how to convert them now.

The thing is I'm not quite sure multivariate regression is the correct answer for my problem? From business knowledge I believe that there is a connection between gross margin % and geography, order quantity, material structure,... I would like to figure out if the data confirms this or not. When I run a regression with excel (data analysis --> regression) the model gives a very low R². However I think this is a linear regression, and I believe there are other nonlinear regressions as well which I could test. I've used the solver function before however when I plot the data there is not really a visible pattern (to me). Could anyone help me find out whether there is a connection between gross margin % and geography, order quantity etc in my data?

NetSales GM Gross Margin Percentage sqm kgs CstGroup BillToCountry MarketSubSegment MaterialStructure
100 15 15,0% 1.600 1.000 A Lithuania Segment 1 Structure1
10.000 100 1,0% 71.000 3.000 B Belgium Segment 1 Structure2
150.000 30.000 20,0% 800 100 C Turkey Segment 2 Structure3
9.000 2.500 27,8% 4.500 500 C Turkey Segment 2 Structure3
3.000 500 16,7% 4.600 500 C Turkey Segment 2 Structure3
210.000 20.000 9,5% 144.000 9.200 D Belgium Segment 3 Structure3
18.000 6.000 33,3% 4.700 510 D Belgium Segment 3 Structure3]
 

Miner

TS Contributor
#4
There appears to be a greater impact from the discrete variables CstGroup and/or MarketSubSegment (there is a lot of commonality between the two) than from any of the continuous variables. A 1-way ANOVA might be a better approach.