Question

In: Statistics and Probability

Store Location Purchasing Dept. Cost Merchandise Purchased No. of Purchase Orders Number of Suppliers Sheridan $575,000...

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?

Solutions

Expert Solution

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


Related Solutions

Store Locaton Purchasing Dept. Cost ($) Merchandise Purchased ($) # of Purchase Orders # of Suppliers...
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...
The number of days required for two suppliers to deliver orders is as follows. Supplier A:...
The number of days required for two suppliers to deliver orders is as follows. Supplier A: 10.0 4.0 13.0 1.0 17.0 7.0 11.0 16.0 6.0 19.0 Supplier B: 9.0 4.0 13.0 2.0 23.0 8.0 12.0 15.0 6.0 24.0 (A) Which supplier provides more consistent and homogenous delivery times A or B? ... Average Number of Days for Supplier A =   days, and Standard Deviation =   day. Coefficient of Variation for Supplier A = Average Number of Days for Supplier B =   days, and...
The number of days required for two suppliers to deliver orders is as follows. Supplier A:...
The number of days required for two suppliers to deliver orders is as follows. Supplier A: 10.0 4.0 13.0 1.0 17.0 7.0 11.0 16.0 6.0 19.0 Supplier B: 9.0 4.0 13.0 2.0 23.0 8.0 12.0 15.0 6.0 24.0 (A) Which supplier provides more consistent and homogenous delivery times A or B? ... Average Number of Days for Supplier A =   days, and Standard Deviation =   day. Coefficient of Variation for Supplier A = Average Number of Days for Supplier B =   days, and...
An online retailer of small gifts orders products from a number of suppliers, stores them, packs...
An online retailer of small gifts orders products from a number of suppliers, stores them, packs them to customers’ orders, and then dispatches them using a distribution company. Although broadly successful, the business is very keen to reduce its operating costs. A number of suggestions have been made to do this. There are as follows: Quality: Make each packer responsible for his or her own quality. This could potentially reduce the percentage of mis-packed items from 0.5% to near zero....
An online retailer of small gifts orders products from a number of suppliers, stores them, packs...
An online retailer of small gifts orders products from a number of suppliers, stores them, packs them to customers’ orders, and then dispatches them using a distribution company. Although broadly successful, the business is very keen to reduce its operating costs. A number of suggestions have been made to do this. There are as follows:  Quality: Make each packer responsible for his or her own quality. This could potentially reduce the percentage of mis-packed items from 0.5% to near...
Sheridan Cosmetics Inc. had a number of transactions during the year relating to the purchase of...
Sheridan Cosmetics Inc. had a number of transactions during the year relating to the purchase of various inventory items as noted below. For each of the below independent transactions determine what amount should be included in inventory. Lipstick products counted in the physical inventory amount to $21,500 which include $1,100 of duty charges for importing the goods and $2,600 of recoverable taxes (i.e. HST). (If an answer is zero, please enter 0. Do not leave any fields blank.) Inventory $...
(Expenditure Cycle) Create a Rea Data Model Purchase orders are made with vendors by a purchasing...
(Expenditure Cycle) Create a Rea Data Model Purchase orders are made with vendors by a purchasing clerk at Upscale, Inc. If all items on the purchase order are not in stock at the vendor, some vendors send partial shipments and then ship the remaining order when items become available. A shipping clerk inspects items upon receipt from the vendor and forwards them to the appropriate department. Once an invoice from the vendor has been approved for payment, a cash disbursements...
Current Attempt in Progress This information relates to Skysong Co. 1. On April 5, purchased merchandise from Sheridan...
Current Attempt in Progress This information relates to Skysong Co. 1. On April 5, purchased merchandise from Sheridan Company for $27,600, terms 3/10,n/30. 2. On April 6, paid freight costs of $770 on merchandise purchased from Sheridan. 3. On April 7, purchased equipment on account for $34,300. 4. On April 8, returned $5,300 of April 5 merchandise to Sheridan Company. 5. On April 15, paid the amount due to Sheridan Company in full. (a) Prepare the journal entries to record the transactions listed above on Skysong Co's...
Exercise 5-1 This information relates to Crane Co.. 1. On April 5, purchased merchandise from Sheridan...
Exercise 5-1 This information relates to Crane Co.. 1. On April 5, purchased merchandise from Sheridan Company for $26,000, terms 3/10, n/30. 2. On April 6, paid freight costs of $520 on merchandise purchased from Sheridan Company. 3. On April 7, purchased equipment on account for $31,400. 4. On April 8, returned $4,100 of April 5 merchandise to Sheridan Company. 5. On April 15, paid the amount due to Sheridan Company in full. (a) Prepare the journal entries to record the transactions listed above on Crane Co.'s...
Sheridan Ltd. purchased a new machine on April 4, 2017, at a cost of $164,000. The...
Sheridan Ltd. purchased a new machine on April 4, 2017, at a cost of $164,000. The company estimated that the machine would have a residual value of $16,000. The machine is expected to be used for 14,800 working hours during its four-year life. Actual machine usage was 1,400 hours in 2017; 2,100 hours in 2018; 2,400 hours in 2019; 2,300 hours in 2020; and 2,000 hours in 2021. Sheridan has a December 31 year end. Calculate depreciation for the machine...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT