Question
(Q1-Q8)
Excel Project 1 Purchasing Merchandise Number of Department Purchased Purchase Number cost in SIA] inSIBI Orders IC] Suppliers IDI 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 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 Miami Baltimore New York Toronto Detroit Chicago St. Louis Phoenix Los Angeles Houston San Francisco Seattle Vancouver 208 121,160,000 1,433 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?
0 0
Add a comment Improve this question Transcribed image text
Answer #1

The procedure is - select 'data' tab -> select 'data analysis' -> select 'regression' - > Put variable A as 'Input Y range' and appropriate variable at 'Input X range' and press ok.

Q.1. regression of A on B

Regression Statistics
Multiple R 0.023933
R Square 0.000573
Adjusted R Square -0.09028
Standard Error 489046.3
Observations 13
Coefficients
Intercept 1302646
B 0.000236

Q.2. OLS regression of A on C

Regression Statistics
Multiple R 0.466848039
R Square 0.217947092
Adjusted R Square 0.146851373
Standard Error 432605.9498
Observations 13
Coefficients
Intercept 977182.6025
C 109.1589514

Q.3. OLS regression of A on D

Regression Statistics
Multiple R 0.678408
R Square 0.460237
Adjusted R Square 0.411168
Standard Error 359398.2
Observations 13
Coefficients
Intercept 800753.1
D 4181.106

Q. 4 Best cost driver for the cost of purchasing department (A) is Number of Suppliers (D).

Q.5. The decision criteria is to select the OLS regression model having largest R square and Adjusted R Square values among all 3 models. Thus, the decision in Q.4. is reached. Thus, the model becomes -

A = 800753.1 + 4181.106*D

i.e. cost of purchasing dept. = 800753.1 + 4181.106*Number of Suppliers

Q.6. The fixed cost A is intercept term of the model, i.e. 800753.1 and the variable cost is slope of the model i.e. 4181.106

Q.7. 46% of the variation in A is explained by the cost driver (i.e. D) as observed by R-square of the model.

Add a comment
Know the answer?
Add Answer to:
(Q1-Q8) Excel Project 1 Purchasing Merchandise Number of Department Purchased Purchase Number cost in SIA] inSIBI...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
  • Not much instruction given by teacher. We were told to enter the data given and answer...

    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...

  • I posted this earlier. I need help with how to do each step. I don’t know...

    I posted this earlier. I need help with how to do each step. I don’t know where to begin. Can you explain as you go and answer the questions please? Excel Project 1 Purchasing Merchandise Number of Department Purchased Purchase Number cost in SIA] inSIBI Orders IC] Suppliers IDI 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 1,729,000 2,523,000 877 1,519,000 86,225,000 1,954...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT