(Please show a detailed Excel spreadsheet as part of your answer(s) to all appli

Question

(Please show a detailed Excel spreadsheet as part of your answer(s) to all applicable questions)

Honda has four automobile plants. Each is capable of producing 3 different vehicles but it can produce only one of the three. The fixed costs of operating each plant and the variable costs of production are included in the data file below. Here are the restrictions: Each plant can only produce one type of car The total production of each vehicle must be at a single plant Each year at least 5 million of each vehicle must be produced

a) Determine how to minimize the cost of producing these vehicles

b) Use Solver Table to show how a change in demand for Accords changes the optimal solution

Honda Auto Problem (Fixed costs are in billions and variable costs are in 1000's) Inputs Plant 1 Plant 2 Plant 3 Plant 4 Fixed Costs 70 60 40 20 Variable Costs (Civic) 11.5 11.7 12.4 12.7 (Accord) 16.8 17.2 16.3 17.9 (Odyssey) 21.3 20.9 21.4 22.5

Explanation / Answer

a. Minimum cost of production

To determine the minimum cost of production we will have to decide which plant will produce which vehicle to minimize the overall cost production, to do this we use solver for the problem formulated below;

Here,

Decision variables (marked in yellow) are to decide which plant will produce which model of car. Hence the decision variable is binary, either 1 or 0.

Objective Function (marked in green) calculates total cost of production considering which plant will produce which model. Here cost is zero is plant is not operating to produce any of the models.

Constraints,

all the decision variables are binary.

All plants must produce not more than one model.

sum of decision variables for plant (1,2,3) column <= 1

Any model shall be produced at only one plant.

sum of all decision variables for model (Civic, Accord, Odyssey) = 1

Now, using solver we get a following solution;

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.