anyone familiar with regression analysis in excel?

[Benjamin Baites]

Hey, I have a range of data.

Revenue, People, Income, Competitors, Price.

I need to do a regression analysis to find the best location for a new store... which depends on these variables...


Number of people living within1 km of the store ([FONT=Times New Roman,Times New Roman][FONT=Times New Roman,Times New Roman]PEOPLE[/FONT][/FONT])
Mean income of the households within 1 km of the store ([FONT=Times New Roman,Times New Roman][FONT=Times New Roman,Times New Roman]INCOM[/FONT][/FONT]E)
Number of competitors within 1 km of the store ([FONT=Times New Roman,Times New Roman][FONT=Times New Roman,Times New Roman]COMPTORS[/FONT][/FONT])
Rental price of a newly released movie ([FONT=Times New Roman,Times New Roman][FONT=Times New Roman,Times New Roman]PRICE[/FONT][/FONT])

I need to do a randomrandomly select 50 video stores and record the value of each of the variables listed above plus annual gross revenue ([FONT=Times New Roman,Times New Roman][FONT=Times New Roman,Times New Roman]REVENUE[/FONT][/FONT]).

then use this data to list a series of information like explain how people, income, competitors and price affect the dependent variable.

Anyone have an idea where to start with this? I've never used excel to do something like this and my regression analysis is pretty scratchy, haven't done it in years.

Cheers

 

[venkat1926]

there is a function in excel

LINEST

you can try.,

also search under excel help "regression analysis"

 

[excelsishya]

Hi,

May be this is one of the best place to start

http://www.youtube.com/watch?v=qPga0OBV-O8

Bionicturtle in you tube has series of regression video starting from basic to complex.

 

[Benjamin Baites]

hey guys, thanks for the tips, looking at them now... the learning curve looks abit big and as a law student I don't know if I have the time, would anyone want to complete this for abit of money? Message me (if thats allowed on this forum) if not I'll give it a crack.

Cheers

 

[venkat1926]

can you post a very small extract of your data and again clearly what you want. which is dependent variable and which is independent variable. If you want regression I am sure you are conversant with statistical terms.

 

[Benjamin Baites]

​

Thats a small sample, ideally I need to randomly select 50 from the list, then report the summary statistics of the variables selected as determinants of success of a store and explain the distributions of these variables and what these tell you about the underlying data.

I also need to report the estimated model and explain how peopl,icncome,competitors and price affect the dependant variable.

I also have to report which independant variables signifcantly explainn the dependent variable.

​

Then based on the estimated model ive got to predict the gross annual revenue of a store for the mean value of the variables PEOPLE, INCOME, COMPTORS and PRICE.

Sort of a headache for me too do at the moment why I offered above haha, but ive been doing abit of research and was going to start on it this arvo, just not entirely sure how to do it in excel.

 

[Benjamin Baites]

http://www.2shared.com/file/RMgPiq3b/store_data_reg.html

 

[venkat1926]

I assume column A is dependent variable and columns B,C,D,E are independent variable.

I think what you require an assistance of both in Statistics and excel

do you want simple regression with each one of the dep variables or multiple regression based on all the dep. variables.

suppose simple regression between col. A and COl E

now highlight two cells(e.g. I1 and J1) and copy this formula
=LINEST(A2:A51,B2:B51)
You will get I1 as 969.8088 this is the slope
and J1 as <table width="64" border="0" cellpadding="0" cellspacing="0"><colgroup><col width="64"></colgroup><tbody><tr height="20"> <td style="height:15.0pt;width:48pt" width="64" align="right" height="20">338457.2</td></tr></tbody></table>which is intercept

you can find estimated value in the first row in F1
in F2 copy this formula
=$I$1*E2+$J$1
you will get 343791.1 .
you can copy F2 down.
this is the estimated value for row1 where as actual value is 323581
this is a simple straight line curve on the variable.

straight line variable may be appropriate or not which you have to find by drawing scatter diagram.

So I suggest you get hold of an expert(even if a good student ) of Statistics and also excel (both are same it is very good)

in excel 2003 under tools there is a sub menu "data analysis".if you click that various statistical functions come up and one is regression
if you click "regression" you will get a download window which you have to fill and click ok and you get the required parameters.

I am not sure whether such facility available in excel 2007 which is your version.
anyhow see this url
http://cameron.econ.ucdavis.edu/excel/ex01access.html
greetings.

 

[venkat1926]

small correction
see

now highlight two cells(e.g. I1 and J1) and copy this formula
=LINEST(A2:A51,B2:B51)

do not hit enter but hit control+shift+enter

 

[Benjamin Baites]

wow cheers Venkat, that info will come in handy. I'm going to give it another shot today, I think I need to do a table first before I use excel to do anything for me. Then once the table is up I'll use those that 'data analysis' and see what I get.

I will also look at those formulas you posted, I think I can do this haha.

Cheers mate!