ElektroTek manufactures a range of ballasts for fluorescent lighting and has over the years acquired a number of small manufacturing plants in North America. This business is still significant, but has been declining as both residential and industrial applications are being converted to LED lighting. As a result, management believes it should close one or more of its plants to reduce costs. All the plant locations are leased, so closing a location is relatively simple although there are costs that will be incurred. These shutdown costs are assumed to be pretty much the same regardless of the location and are considered to be necessary for continuing business, so they don’t need to be included in the analysis. The plant locations, their allowable working capacities, and their lease costs are shown below. All figures are on a per-week basis. Production costs also vary between locations, so this will figure into the decision on which plant(s) to close. Shipments are made to the distribution centers of five major customers. These locations and their average weekly demands are given below, along with a table of shipping costs from each plant to each customer.

The company management has asked you to analyze the situation and report on which plant (if any) would be the most advantageous to close and how much per week could be saved by doing so.

Hints: this is a transportation problem, generally similar to the examples from class. To find the savings, you will have to first optimize the current network to establish a baseline. Once the status of the plants comes into question, this also becomes a fixed cost problem, similar to examples in the recorded lectures and in class. This means you will have to add a set of binary decision variables (Y_i) denoting whether plant i is open or closed. These will figure into the objective function (multiplied by the weekly lease cost) and can be used to adjust the actual plant capacities where the “Big M” value is the capacity of the plant stays open: actual capacity = Theoretical Capacity C_i*Y_i so that you can’t ship from a closed plant. Additional hint: the 5% default tolerance on integer solutions may not be tight enough for this problem – try 0.1% and see if it makes a difference.

Assignment Deliverables:

Your deliverables for this assignment consist of an Excel workbook that is clear enough to be used as an appendix to your report. By “clear enough” I mean that you won’t necessarily be around to explain it, so it has to be well-enough labeled to stand on its own. You will also prepare an executive summary report that will be a 2-page Word document. The first page will be simply a title page with a descriptive title, date, your name, and possibly some tasteful graphics if you are so inclined. The second page will be an executive summary report that tells a busy executive everything he or she needs to know about the situation. Remember that not all readers will have the same understanding of what it was you were asked to do, so you will need to frame the question as well as present the recommendation. Readers are also apt to have questions that they neglected to mention, so part of your skill as analysts is to anticipate the more obvious ones. A couple that you might consider: suppose you make a recommendation to close a plant that proves to be unacceptable to management for some reason they don’t want to talk about – what would you recommend then, and what would the difference in savings be? Also, if the company follows your initial recommendation to close a plant, and if business continues to decline, (use a constant 25% decline across the board) could you justify closing an additional plant, and which one would it be? If that decline was anticipated, might it have been better to keep the first plant open to having more flexibility when the decline materializes?

Put this into excel:

Data:

Plant Location	Weekly Lease Cost	Capacity	Unit Production Cost
Pontiac	$4,000	32,000	$3.80
Dayton	$6,000	35,000	$4.02
Milwaukee	$7,000	45,000	$4.01
Bristol	$7,000	42,000	$3.05
Juarez	$3,000	30,000	$2.15
Matamoros	$2,500	20,000	$2.05

Shipping Costs per Unit		Customer Distribution Centers
		Des Moines	Lexington	Indianapolis	Reno	Atlanta
Plant Locations	Pontiac	$0.42	$0.36	$0.41	$0.52	$0.50
	Dayton	$0.32	$0.25	$0.30	$0.49	$0.43
	Milwaukee	$0.44	$0.37	$0.28	$0.43	$0.45
	Bristol	$0.42	$0.45	$0.38	$0.46	$0.27
	Juarez	$0.54	$0.51	$0.50	$0.62	$0.57
	Matamoros	$0.59	$0.57	$0.50	$0.66	$0.49
	Weekly Demand (Units)	15,000	20,000	25,000	30,000	19,000

Answer rating: 100% (QA)

Answer Question 1 The company management has asked you to analyze the situation and report on which plant if any would be the most advantageous to close and how much per week could be saved by doing s... View the full answer