Question

Store Locaton	Purchasing Dept. Cost ($)	Merchandise Purchased ($)	# of Purchase Orders	# of Suppliers
Sheridan, WY	575,000	47,239,000	1708	61
Denver	1,226,000	102,364,000	2519	95
Salt Lake City	1,710,000	100,162,000	2506	139
Kansas City	881,000	95,760,000	1719	91
Omaha	1,544,000	51,466,000	2883	155
Milwaukee	794,000	50,631,000	647	75
Minneapolis	1,341,000	84,753,000	2978	103
Phoenix	794,000	103,464,000	3761	117
Las Vegas	2,216,000	96,162,000	2584	73
Albuquerque	2,030,000	62,364,000	5497	176
Tucson	1,338,000	65,635,000	4347	130
Houston	856,000	88,524,000	2878	62
Oklahoma City	1,122,000	72,645,000	819	129
Tulsa	863,000	61,638,000	1247	145
Dallas	1,085,000	105,666,000	2162	141
San Antonio	952,000	59,437,000	2822	105
Austin	1,134,000	38,542,000	5115	51
El Paso	1,042,000	33,020,000	382	131
Nashville	1,634,000	36,322,000	5293	172
Memphis	699,000	34,121,000	967	34
Indianapolis	875,000	31,920,000	2425	48

Requirements Joe asked you, the managerial cost specialist on his management team, to examine the data and to recommend some courses of action to reduce purchasing department costs. Prepare a statistical analysis of the costs provided. a. Plot the purchase department cost vs. each cost driver. Are they linear? Save them on one worksheet labeled Scatterplots. b. Do a High-Low analysis of each cost driver. Give the cost equation using each cost driver. Save them on one worksheet called HighLow. c. Use both simple and multiple regression analysis to develop cost models for all potential cost drivers. Put each result on a new worksheet and label the sheets. d. Identify the best model, and explain why on a worksheet called Results. I'm reuploading question. Can you show me in an excel format?

Answer 1

Note: We are not allowed to upload excel files as Answers. Follow the below steps to get the answer.

a)

1) Select the two columns (on which we want to draw the scatterplot)

2) Then go to Insert Tab and select Scatterplot.

3) Add Axis and Chart Labels.

b)

Steps

1) Find the highest and the lowest value.

2) Calculate Variable Cost = (Y_Max – Y_min)/(X_max- X_min)

3) Fixed Cost = Cost(Y) – Variable * Activity Level(X) (Choose either Maximum or Minimum Level)

No of Purchase Orders

Lowest Value at No of Purchase Order = 319,20,000, Purchasing Dept. Cost = 875000

Highest Value at No of Purchase Order = 1056,66,000 , Purchasing Dept. Cost = 10,85,000

Variable Cost = (10,85,000-875000) / (1056,66,000 - 319,20,000) = 351.17

Fixed cost = 10,85,000 - 351.17 * 1056,66,000 = -371057,95,171

Using this we get the regression equation:

Y = -371057,95,171 + 351.17 * X

Similarly, can be done for the other two variable. (Not a very accurate technique)

c)

Steps

1) Go to Data Tab and select data analysis tool.

2) Select Regression

3) Select Purchasing Dept. Cost Data in Y-values and All the Required X Variables.

4) Select Label in 1^st row option.

5) Select OK.

6) This gives the required regression model.

Model 1

Purchasing Dept. Cost ($) v/s MerchandisePurchased($)

Since the P-value from ANOVA table is greater than 0.05, we reject the model.

Model 2

Purchasing Dept. Cost ($) v/s No of Purchase Orders

This Model is significant. Value of R Square is 0.255 ie only 25.5% of the variation in Y variable can be explained by the X variables.

Regression Equation:

Purchasing Dept. Cost ($) = 7,88,370.40 + 147.58 * No of Purchase Orders

Model 3

Purchasing Dept. Cost ($) v/s No of Suppliers

This Model is significant. Value of R Square is 0.255 ie only 24.4% of the variation in Y variable can be explained by the X variables.

Regression Equation:

Purchasing Dept. Cost ($) = 6,29,017.55 + 5,150.75 * No of Purchase Orders

Model 4

Purchasing Dept. Cost ($) v/s No of Purchase Orders and No of Suppliers

This Model is significant. Value of R Square is 0.385 ie 38.5% of the variation in Y variable can be explained by the X variables.

Regression Equation:

Purchasing Dept. Cost ($) = 6,29,017.55 + 5,150.75 * No of Purchase Orders

d)

Model 4 is the best model as the value of R Square is maximum for this model. Also the p-value for the overall model is least.