Question

The Magnetron Company manufactures and markets microwave ovens. Currently, the company

produces two models: full-size and compact. Production is limited by the amount of labor

available in the general assembly and electronic assembly departments, as well as by the demand

for each model. Each full-size oven requires 2 hours of general assembly and 2 hours of

electronic assembly, whereas each compact oven requires 1 hour of general assembly and 3

hours of electronic assembly. In the current production period, there are 500 hours of general

assembly labor available and 800 hours of electronic assembly labor available.

In addition, the company estimates that it can sell at most 220 full-size ovens and 180 compact

ovens in the current production period. The earnings contribution per oven is $120 for a full-size

oven and $130 for a compact oven. The company would like to find an earnings-maximizing

production plan for the current production period.

Formulate the above problem as a linear optimization model and solve via Excel’s solver. Please

turn in both a written formulation and an Excel file with the solution.

Answer 1

Decision variables: as shown in green in fig1

how much to produce for full size and compact models:

full size = X1

Compact = X2

Objective function: a shown in yellow in fig1

maximize total earnigs contribution:

120*X1 + 130*X2

C9 = SUM(C7:C8)

Constraints:

1. general assembly maximum 500 hours

2*X1 + 1*X2 500, in excel: D4 500

2. electronic assembly maximum 800 hours

2*X1 + 3*X2 800, in excel: F4 800

3. full size maximum 220 ovens

X1 220, in excel: B2 220

4. compact maximum 180 ovens

X2 180, in excel: B3 180

5. X1,X2 0

fig1 & fig2 are given below which show the solution and solver box

fig1

fig2