Question

Regression Analysis to Understand Cost Drivers in a Purchasing Department – Business Case

Each year Joe reviews the financial information for all the CWWR stores. This past year was a relatively good year; company profits were up despite the huge July Fourth fire in Las Vegas, Nevada, that shut down the store for four months and required replacements of all inventory. Joe did notice however that purchasing department costs varied considerably between stores. The minimum was $575, 000 and the maximum was $2.2 Billion. This was perplexing, and he thought this be an area where efficiencies could be achieved. Currently each store has its own purchasing department with full autonomy. In the western wear industry, regional customers have regional tastes and desires. Local purchasing agents are thought to be best able to understand the desires of local customers and to those needs.

              On his management team, Joe has a managerial cost specialist with skills in data analytics. Together they discussed the purchase department cost problem and identified three potential cost drivers: merchandise purchased, number of purchase orders, and number of suppliers. To verify these ideas, Joe contracted purchasing managers from three different stores who agreed that these were potentially good cost drivers and they no others were readily apparent. The managerial cost specialist gathered the data for four variables from last year’s financial information and reported in Table 1. The data was also entered into an Excel spreadsheet (See appendix) By the team’s administrative assistant.

Store Location	Purchasing Dept. Cost	Merchandise Purchased	No. of Purchase Orders	Number of Suppliers
Sheridan	$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
Minnealops	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
Albaqurque	2,030,000	62,364	5497	176
Tucson	1,338,000	65,635,000	4347	130
Houston	856,000	88,524,000	2878	62
Oklahoma	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

1. Prepare a statistical analysis of the costs provided.

Plot the purchase department cost vs each cost driver (Graph per page)

Analyze the data for the potential problems, correct data problems if necessary and report any changes made.

Use the regression analysis to develop cost model for all potential cost drivers.

Identify the best model and explain why.

Explain what the model means from an economic perspective.

2. Use the model to make two recommendation to the CWWR management team for improving the efficiency of the purchasing operations

Be specific with the details of the recommendations

Estimate the cost saving from the implementation of your recommendations.

Consider the secondary implications, quantitative and/or qualitative.

Indicate how these changes (recommendations) should be implemented.

Discuss

1. What cost drivers are useful for predicting the purchasing departments costs? What is the recommended model? What does it mean? How can this model be used to reduce costs?

2. Does CWWR use a centralized or decentralized purchasing system? Why would the company use this strategy? Under what circumstances would a decentralized strategy be more valuable?

3. You developed recommendations, which are essentially are changes to human behavior. Is change in an organization easy? How can human behaviors be changed in a management setting?

Answer 1

	X Variable= Independent Variable
	Y Variable=Dependent variable
	X -Variables			Y -Variable
	Merchandise Purchased	Number of Purchase orders	Number of suppliers	Purchasing Department Cost
	$47,239,000	1708	51	$575,000
	$102,364,000	2519	85	$1,226,000
	$100,162,000	2506	139	$1,710,000
	$95,760,000	1719	91	$881,000
	$51,466,000	2883	156	$1,544,000
	$50,631,000	647	75	$794,000
	$84,753,000	2978	103	$1,341,000
	$103,464,000	3761	117	$794,000
	$96,162,000	2584	73	$2,216,000
	$62,364,000	5497	176	$2,030,000
	$65,635,000	4347	130	$1,338,000
	$88,524,000	2878	62	$856,000
	$72,645,000	619	129	$1,122,000
	$61,638,000	1247	145	$863,000
	$105,666,000	2162	141	$1,085,000
	$59,437,000	2822	105	$952,000
	$38,542,000	5115	51	$1,134,000
	$33,020,000	382	131	$1,042,000
	$36,322,000	5293	172	$1,634,000
	$34,121,000	967	34	$699,000
	$31,920,000	2425	48	$875,000
	Merchandise Purchsed Vs Purchase Department Cost
	Merchandise purchased arranged in ascending order
	Mercahndise Purchased	Purchase Dept Cost
	$31,920,000	$875,000
	$33,020,000	$1,042,000
	$34,121,000	$699,000
	$36,322,000	$1,634,000
	$38,542,000	$1,134,000
	$47,239,000	$575,000
	$50,631,000	$794,000
	$51,466,000	$1,544,000
	$59,437,000	$952,000
	$61,638,000	$863,000
	$62,364,000	$2,030,000
	$65,635,000	$1,338,000
	$72,645,000	$1,122,000
	$84,753,000	$1,341,000
	$88,524,000	$856,000
	$95,760,000	$881,000
	$96,162,000	$2,216,000
	$100,162,000	$1,710,000
	$102,364,000	$1,226,000
	$103,464,000	$794,000
	$105,666,000	$1,085,000
	Number of purchase orders Vs Purchase Department Cost
	Number of purchased orders arranged in ascending order
	Number of Purchase orders	Purchase Dept Cost
	382	$1,042,000
	619	$1,122,000
	647	$794,000
	967	$699,000
	1247	$863,000
	1708	$575,000
	1719	$881,000
	2162	$1,085,000
	2425	$875,000
	2506	$1,710,000
	2519	$1,226,000
	2584	$2,216,000
	2822	$952,000
	2878	$856,000
	2883	$1,544,000
	2978	$1,341,000
	3761	$794,000
	4347	$1,338,000
	5115	$1,134,000
	5293	$1,634,000
	5497	$2,030,000
	Number of Suppliers Vs Purchase Department Cost
	Number of Suppliers arranged in ascending order
	Number of suppliers	Purchase Dept Cost
	34	$699,000
	48	$875,000
	51	$575,000
	51	$1,134,000
	62	$856,000
	73	$2,216,000
	75	$794,000
	85	$1,226,000
	91	$881,000
	103	$1,341,000
	105	$952,000
	117	$794,000
	129	$1,122,000
	130	$1,338,000
	131	$1,042,000
	139	$1,710,000
	141	$1,085,000
	145	$863,000
	156	$1,544,000
	172	$1,634,000
	176	$2,030,000
			From The graph,it is clear that there is no correlation between Mechandise Purchased and PurchaseDepartment Cost
			For Number of Purchase orders VsPurchase Department Cost:
			The data of 2584 /$2216000 appears to be incorrect
			We remove this date and remainining data is written below