Question

Not much instruction given by teacher.

We were told to enter the data given and answer

questions Q1-Q8. I’m lost, please help

Excel Project 1 Miami Baltimore New York Toronto Detroit Chicago St. Louis Phoenix Los Angeles Houston San Francisco Seattle Vancouver Purchasing Merchandise Number of Department Purchased Purchase Number cost in SIA] inSIB OrdersIC] Suppliers ID] 2,059,000 119,566,000 5,944 1,533,000 68,315,000 4,357 1,066,000 33,505,000 2,793 1,622,000 139,312,000 1,707 875,000 128,811,000 2,125 1,110,000 33,456,000 2,550 1,755,000 38,674,000 3,617 539,000 29,854,000 1,327 557,000 121,160,000 1,433 1,729,000 2,523,000 877 1,519,000 86,225,000 1,954 1,548,000 102,875,000 7,586 1,267,000 130,944,000 4,731 190 132 208 168 168 104 201 Q1. Run an OLS regression of A on B Q2. Run an OLS regression of A on C Q3. Run an OLS regression of A on D. Q4. Find the best cost driver for the cost of the Purchasing Department. Q5. What are your decision criteria? Q6. According to your regression model, what is a variable cost and a fixed cost of A? Q7. How much variations in A were explained by your cost driver? Q8. What is the relevant range of your model?

Answer 1

data

A	B
2059	119566
1533	68315
1066	33505
1622	139312
875	128811
1110	33456
1755	38674
539	29854
557	121160
1729	2523
1519	86225
1548	102875
1267	130944

here A is dependent variable

B is independent variables

using Excel

Data -> data analysis - > regression

A1 1 A 059 119566 1533 68315 1066 33505 1622 139312 875 128811 1110 33456 1755 38674 539 29854 557 121160 2523 1519 86225 1548 102875 1267 130944 Regression Input OK SA$1:SA$14 Input Y Range: Input X Range: Labels 6 Cancel $B$1:$B$14 Help Constant is Zero 95 Confidence Level: 10 1729 Output options 12 13 O output Range New Worksheet Ply: O New Workbook Residuals 15 16 17 18 19 20 21 Residuals Standardized Residuals Residual Plots Line Fit Plots Normal Probability Normal Probability Plots

result

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.023932528
R Square	0.000572766
Adjusted R Square	-0.090284255
Standard Error	489.0463371
Observations	13
ANOVA
	df	SS	MS	F	Significance F
Regression	1	1507.712939	1507.712939	0.006304035	0.938142118
Residual	11	2630829.518	239166.3198
Total	12	2632337.231
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	1302.645712	273.0522263	4.770683359	0.000580042	701.6618142	1903.62961
B	0.000236284	0.002975943	0.079397956	0.938142118	-0.006313723	0.006786291

A = 1302.6457 + 0.000236284 *B