The CoolAire Company manufactures air conditioners that are sold to five differe
ID: 460983 • Letter: T
Question
The CoolAire Company manufactures air conditioners that are sold to five different retail customers across the United States. The company is evaluating its manufacturing and logistics strategy to ensure that it is operating in the most efficient manner possible. The company can produce air conditioners at six plants across the country and stock these units in any of four different warehouses. The cost of manufacturing and shipping a unit between each plant and warehouse is summarized in the following table along with the monthly capacity and fixed cost of operating each plant.
Warehouse
1
Warehouse
2
Warehouse 3
Warehouse 4
Fixed Cost
Capacity
Plant 1
$700
$1,000
$900
$1,200
$55,000
300
Plant 2
$800
$ 500
$600
$ 700
$40,000
200
Plant 3
$850
$ 600
$700
$ 500
$45,000
300
Plant 4
$600
$ 800
$500
$ 600
$50,000
250
Plant 5
$500
$ 600
$450
$ 700
$42,000
350
Plant
6
$700
$ 600
$750
$ 500
$40,000
400
Similarly, the per-unit cost of shipping units from each warehouse to each customer is given in the following table, along with the monthly fixed cost of operating each warehouse.
Customer
1
Customer
2
Customer 3
Customer 4
Customer 5
Fixed Cost
Warehouse 1
$40
$80
$60
$90
$50
$40,000
Warehouse
2
$60
$50
$75
$40
$35
$50,000
Warehouse 3
$55
$40
$65
$60
$80
$35,000
Warehouse 4
$80
$30
$80
$50
$60
$60,000
The monthly demand from each customer is summarized next:
Customer 1
Customer 2
Customer 3
Customer 4
Customer 5
Demand
200
300
200
150
250
CoolAire would like to determine which plants and warehouses it should operate to meet demand in the most cost-effective manner.
a. Create a spreadsheet model for this problem and solve it.
b. Which plants and warehouses should CoolAire operate?
c. What is the optimal shipping plan?
(PLEASE BE SPECIFIC AS TO ALL FORMULAS AND WHAT CELLS THEY GO IN)
Warehouse
1
Warehouse
2
Warehouse 3
Warehouse 4
Fixed Cost
Capacity
Plant 1
$700
$1,000
$900
$1,200
$55,000
300
Plant 2
$800
$ 500
$600
$ 700
$40,000
200
Plant 3
$850
$ 600
$700
$ 500
$45,000
300
Plant 4
$600
$ 800
$500
$ 600
$50,000
250
Plant 5
$500
$ 600
$450
$ 700
$42,000
350
Plant
6
$700
$ 600
$750
$ 500
$40,000
400
Explanation / Answer
If consider that Plant 1 produces customer 1 Demand, uses Warehouse 1 to store and dispatch the stock . the total cost of producing , warehousing and logistics will be = 200 ( units ) * 700 $ ( units cost of plant )+55000 ( Fixed cost of WH) + 200 ( Demand )* $ 40 ( unit cost of logistics) + $ 40000 (Fixed cost of logistics) = $ 243000
Similar way Below is the table calculated cost of all the customers in all the plants and WH
Total Cost including Wh and logistics
Customer 1 ( Demand is 200 units)
Customer 2 ( Demand is 250 Units )
Customer 3 ( Demand is 200 units
Customer 4 ( Demand is 150 units)
Customer 5 ( Demand is 250 units)
Capacity
Warehouse 1
Warehouse 2
Warehouse 3
Warehouse 4
Warehouse 1
Warehouse 2
Warehouse 3
Warehouse 4
Warehouse 1
Warehouse 2
Warehouse 3
Warehouse 4
Warehouse 1
Warehouse 2
Warehouse 3
Warehouse 4
Warehouse 1
Warehouse 2
Warehouse 3
Warehouse 4
Plant 1
300
243000
303000
283000
343000
329000
419000
389000
479000
247000
307000
287000
347000
213500
258500
243500
288500
282500
357500
332500
407500
Plant 2
200
248000
188000
208000
228000
252000
192000
212000
232000
213500
168500
183500
198500
Plant 3
300
263000
213000
233000
193000
364000
289000
319000
259000
267000
217000
237000
197000
226000
188500
203500
173500
310000
247500
272500
222500
Plant 4
250
218000
258000
198000
218000
222000
262000
202000
222000
193500
223500
178500
193500
252500
302500
227500
252500
Plant 5
350
190000
210000
180000
230000
256000
286000
241000
316000
194000
214000
184000
234000
170500
185500
163000
200500
219500
244500
207000
269500
Plant 6
400
228000
208000
238000
188000
314000
284000
329000
254000
232000
212000
242000
192000
198500
183500
206000
168500
267500
242500
280000
217500
The demand for Customer 2 is 300 units .But Plant 2 and Plant 4 capacity is less than 300 units. So they are not considered for production for customer 2 demand.
Now as per above table
Customer 1: Plant 5 is having lowest cost of $ 180000 for producing customer 1 demand and using Warehouse 3 as storage area
Customer 2: Plant 5 is having lowest cost of $ 241000 for producing customer 2 demand and using Warehouse 3 as storage area
Customer 3 :Plant 5 is having lowest cost of $ 184000 for producing customer 2 demand and using Warehouse 3 as storage area
Customer 4 :Plant 5 is having lowest cost of $ 163000 for producing customer 2 demand and using Warehouse 3 as storage area
Customer 5 :Plant 5 is having lowest cost of $ 207000 for producing customer 2 demand and using Warehouse 3 as storage area
So Coolaire should operate in plant 5 and Warehouse 3 as per aboive data.
Total Cost including Wh and logistics
Customer 1 ( Demand is 200 units)
Customer 2 ( Demand is 250 Units )
Customer 3 ( Demand is 200 units
Customer 4 ( Demand is 150 units)
Customer 5 ( Demand is 250 units)
Capacity
Warehouse 1
Warehouse 2
Warehouse 3
Warehouse 4
Warehouse 1
Warehouse 2
Warehouse 3
Warehouse 4
Warehouse 1
Warehouse 2
Warehouse 3
Warehouse 4
Warehouse 1
Warehouse 2
Warehouse 3
Warehouse 4
Warehouse 1
Warehouse 2
Warehouse 3
Warehouse 4
Plant 1
300
243000
303000
283000
343000
329000
419000
389000
479000
247000
307000
287000
347000
213500
258500
243500
288500
282500
357500
332500
407500
Plant 2
200
248000
188000
208000
228000
252000
192000
212000
232000
213500
168500
183500
198500
Plant 3
300
263000
213000
233000
193000
364000
289000
319000
259000
267000
217000
237000
197000
226000
188500
203500
173500
310000
247500
272500
222500
Plant 4
250
218000
258000
198000
218000
222000
262000
202000
222000
193500
223500
178500
193500
252500
302500
227500
252500
Plant 5
350
190000
210000
180000
230000
256000
286000
241000
316000
194000
214000
184000
234000
170500
185500
163000
200500
219500
244500
207000
269500
Plant 6
400
228000
208000
238000
188000
314000
284000
329000
254000
232000
212000
242000
192000
198500
183500
206000
168500
267500
242500
280000
217500
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.