In: Statistics and Probability
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 |
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.
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?
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 |