Manufacturing at Dorian Auto

1
Example 6.3

• Manufacturing at Dorian Auto

2
Background Information

• Dorian Auto is considering manufacturing three
  types of cars (compact, midsize, and large) and
  two types of minivans (midsize and large).
• The resources required and the profits yielded by
  each type of vehicle are shown in this table.

3
Background Information -- continued

• At present, 6500 tons of steel and 65,000 hours
  of labor are available.
• If any vehicles of a given type are produced,
  production of that type of car is economically
  feasible only if at least a minimal number of
  that type are produced.
• Dorian wants to find a production schedule that
  maximizes its profit.

4
Solution

• The variables and constraints for the Dorian
  model are shown below. Dorian must decide not
  only how many of each type of vehicle to produce,
  but also which types to produce.
• Of course, after it decides to produce small
  minivans, say, then it must produce at least 200
  of them.

5
EitherOrManufacturing.xls

• The spreadsheet model is shown below.
• This file can be used to complete the model.

6
Developing the Model

• To develop the model, follow these steps
• Inputs. Enter the input data into the shaded
  ranges.
• Numbers of vehicles produced. Enter any trial
  values for the number of vehicles of each type
  produced in the Units_produced range.
• Binary variables for minimum production. Enter
  any trial 0-1 values in the Produce_at_least_minim
  um? in range. If a value in this range is 1, it
  means that Dorian must produce at least the
  minimum number of the corresponding vehicle type.
  A value of 0 in this range means that Dorian must
  produce 0 of the corresponding vehicle type.

7
Developing the Model -- continued

• Lower limits on production. The either-or
  constraints are implemented with the binary
  variables in row 13 and the inequalities
  indicated in rows 15-19. To obtain the lower
  limits on production, enter the formula B7B13
  in cell B15 and copy it across row 15. This lower
  limit implies that if the binary variable in row
  13 is 1, then Dorian must produce at least the
  minimum number of that vehicle type. However, if
  the binary variable is 0, then the lower bound in
  row 15 is 0 and is essentially redundant it
  just says that production must be nonnegative.

8
Developing the Model -- continued

• Upper limits on production. To obtain upper
  limits on production, enter the formula
  B13MIN(D23/B5,D24/B6) in cell B19 and copy
  it across row 19. To summarize the lower and
  upper limits, if the binary variable is 1, the
  production limits becomeMinimum production
  required ? Production ? Maximum production
  possible
• If the binary variable is 0, the limits become
  0 ? Production ?0Exactly one of these cases
  must hold for each vehicle type, so they
  successfully implement the either-or constraints.
  These lower and upper limits are the key to the
  model.

9
Developing the Model -- continued

• Steel labor used. Calculate the tons of steel and
  number of labor hours used in the Resources_used
  range by entering the formula SUMPRODUCT(B5D5,Un
  its_produced) in cell B23 and copying it to cell
  B24.
• Profit. Calculate the profit in the Profit cell
  with the formula SUMPRODUCT(B9F9,Units_produced)
  .

10
Using the Solver

• The completed Solver dialog box is shown here.
• The objective is to maximize profit, the changing
  cells are the production limits and resource
  availabilities.

11
Solution

• The optimal solution shown in the earlier figure
  indicates, by the 0 values in row 13, that Dorian
  should not produce any compact or large cars.
• The number of 1s in this row, however, indicates
  that Dorian must produce at least the minimum
  number (1000) of compact cars and the minimum
  number (200) of each type of minivan.
• More specifically, the company should produce
  just enough compact cars and midsize minivans to
  meet the minimal production quantities. The
  company should make as many large minivans as it
  can, after producing the compact cars and midsize
  minivans, until it runs out of labor hours.