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

The Plots are as Shown Below

Purchasing Dept Cost Vs merchadised Purchase

Plot of Purchasing dept. Cost vs No. of Purchasing orders

Plot of Purchasing Dept. cost Vs No of Suppliers

Purchasing Dept. Cost Vs Places

The regression Model

Model1( All Variables Taken into consideration)

Equation

Purchasing Dept.cost= 376315.101+ .00107914 Merchandise Purchased+ 119.051597 No.of Purchase Orders+ 3924.12891 no.of Suppliers

The regression Output is as shown below

But we see that none of the Independent variables are significant as the p values all >5% and since there is a sign change between the lower limit & upper Limit so All the variable( Merchandise Purchase, No of Purchase Orders , No of Suppliers) can take a value of 0

So this is not a significant model

Model 2

Purchasing Dept Cost= 455582.458 +114.932 No.of Purchase Orders + 3937.625 No.of Suppliesr

the regression output

The Adjusted R² for this model is

So based of adjusted R² value and the P value of the variables Model 2 should be used

The Main Cost Drivers are:- No of Purchase Orders & No of Suppliers

So based on Model we can infer that as the each reduction in No of Purchase Orders results in cost reduction of $114 and reduction is No of Supplier by 1 results in cost saving of $ 3937/-

The Company uses a decentralized Purchasing system as the fashion requirement varies from place to place

Decentralized system works well when the requirements of each center varies widely

Change in human Behaviour is not easy.