Question

	Do not use this data if your class time is not 9:30AM Monday and Wednesday
Location	Sales (in thousands)	Advertising(in thousands)	Adv^2	Years in Business	Adv * Years	Dwest	Dsouth
South	340	8		4
North	651	42		6
West	459	35		2
West	1400	64		16
North	943	40		11
South	425	24		4
West	589	10		8
South	492	17		7
North	490	15		8
South	920	33		15
North	1040	45		14
West	403	30		3
South	594	27		8
South	599	30		10
West	293	12		4
North	855	40		12
South	343	19		6
West	891	45		12
North	395	20		3
West	862	40		12
South	350	12		5
West	871	48		11
North	405	22		2
South	193	8		3
South	185	10		2
	***To use the Data Analysis feature with Excel 2016, click the File tab on the top left; Excel Options; Add-ins; Manage Add-ins; and the Analysis Toolpak.
	***With a MAC, only the last version of Excel has the Data Analysis button. Use the 'TOOLS' menu at the top, and then select Add-ins.
	Assignment: We are trying to explain the variation in Sales from storefront to storefront using Advertising budgets, Years in Business, and store location. Adv^2 is a quadratic term for Advertising. Adv*Years is an interaction term. For store location, North will be the omitted category or null state, and DSouth and DWest are Dummy Variables for stores located in the South or West.
	Complete the data for Adv^2, Adv*Years, South, and West. Regress Sales on Advertising, Adv^2, Years in Business, Adv*Years, South, and West. Place the Regression Output on it's own worksheet.
	Type your name on the Regression Output. This signifies that you completed the assignment. The penalty for submitted a work not your own or allowing another to copy your work is a 0 for the course.
	1. Type on the Regression output page which regressors are significant at the 5% level of significance and why.
	2. Predict Sales for a store in the West with $30,000 in advertising expenditures and 5 years in business.
	3. Rank the regions based on sales from highest to lowest, holding adv and years in business constant. HOW DO I PUT THIS IN EXCEL AND MAKE A REGRESSION? i tried usuing the data analysis button and it wont pull up my regression table and id ont know if its because i dont know what the Y and X are or if it is because there are blanks?

Answer 1

data

fill all the columns as per requirement

adv^2 = adv*adv

adv*years ,

Dwest = if ( location = "west" , 1,0) in excel

Location	Sales (in thousands)	Advertising(in thousands)	Adv^2	Years in Business	Adv * Years	Dwest	Dsouth
South	340	8	64	4	32	0	1
North	651	42	1764	6	252	0	0
West	459	35	1225	2	70	1	0
West	1400	64	4096	16	1024	1	0
North	943	40	1600	11	440	0	0
South	425	24	576	4	96	0	1
West	589	10	100	8	80	1	0
South	492	17	289	7	119	0	1
North	490	15	225	8	120	0	0
South	920	33	1089	15	495	0	1
North	1040	45	2025	14	630	0	0
West	403	30	900	3	90	1	0
South	594	27	729	8	216	0	1
South	599	30	900	10	300	0	1
West	293	12	144	4	48	1	0
North	855	40	1600	12	480	0	0
South	343	19	361	6	114	0	1
West	891	45	2025	12	540	1	0
North	395	20	400	3	60	0	0
West	862	40	1600	12	480	1	0
South	350	12	144	5	60	0	1
West	871	48	2304	11	528	1	0
North	405	22	484	2	44	0	0
South	193	8	64	3	24	0	1
South	185	10	100	2	20	0	1

you can copy paste in excel directly below formulas

Location	Sales (in thousands)	Advertising(in thousands)	Adv^2	Years in Business	Adv * Years	Dwest	Dsouth
South	340	8	=C2*C2	4	=E2*C2	=IF(A2="West",1,0)	=IF(A2="South", 1,0)
North	651	42	=C3*C3	6	=E3*C3	=IF(A3="West",1,0)	=IF(A3="South", 1,0)
West	459	35	=C4*C4	2	=E4*C4	=IF(A4="West",1,0)	=IF(A4="South", 1,0)
West	1400	64	=C5*C5	16	=E5*C5	=IF(A5="West",1,0)	=IF(A5="South", 1,0)
North	943	40	=C6*C6	11	=E6*C6	=IF(A6="West",1,0)	=IF(A6="South", 1,0)
South	425	24	=C7*C7	4	=E7*C7	=IF(A7="West",1,0)	=IF(A7="South", 1,0)
West	589	10	=C8*C8	8	=E8*C8	=IF(A8="West",1,0)	=IF(A8="South", 1,0)
South	492	17	=C9*C9	7	=E9*C9	=IF(A9="West",1,0)	=IF(A9="South", 1,0)
North	490	15	=C10*C10	8	=E10*C10	=IF(A10="West",1,0)	=IF(A10="South", 1,0)
South	920	33	=C11*C11	15	=E11*C11	=IF(A11="West",1,0)	=IF(A11="South", 1,0)
North	1040	45	=C12*C12	14	=E12*C12	=IF(A12="West",1,0)	=IF(A12="South", 1,0)
West	403	30	=C13*C13	3	=E13*C13	=IF(A13="West",1,0)	=IF(A13="South", 1,0)
South	594	27	=C14*C14	8	=E14*C14	=IF(A14="West",1,0)	=IF(A14="South", 1,0)
South	599	30	=C15*C15	10	=E15*C15	=IF(A15="West",1,0)	=IF(A15="South", 1,0)
West	293	12	=C16*C16	4	=E16*C16	=IF(A16="West",1,0)	=IF(A16="South", 1,0)
North	855	40	=C17*C17	12	=E17*C17	=IF(A17="West",1,0)	=IF(A17="South", 1,0)
South	343	19	=C18*C18	6	=E18*C18	=IF(A18="West",1,0)	=IF(A18="South", 1,0)
West	891	45	=C19*C19	12	=E19*C19	=IF(A19="West",1,0)	=IF(A19="South", 1,0)
North	395	20	=C20*C20	3	=E20*C20	=IF(A20="West",1,0)	=IF(A20="South", 1,0)
West	862	40	=C21*C21	12	=E21*C21	=IF(A21="West",1,0)	=IF(A21="South", 1,0)
South	350	12	=C22*C22	5	=E22*C22	=IF(A22="West",1,0)	=IF(A22="South", 1,0)
West	871	48	=C23*C23	11	=E23*C23	=IF(A23="West",1,0)	=IF(A23="South", 1,0)
North	405	22	=C24*C24	2	=E24*C24	=IF(A24="West",1,0)	=IF(A24="South", 1,0)
South	193	8	=C25*C25	3	=E25*C25	=IF(A25="West",1,0)	=IF(A25="South", 1,0)
South	185	10	=C26*C26	2	=E26*C26	=IF(A26="West",1,0)	=IF(A26="South", 1,0)

run regression

PLEASE RATE