A gear manufacturer is planning next week's production for four types of gears. Because

there are limited resources in the plant for production, the manufacturer can outsource the gears by

purchasing these gears from a regional supplier. The regional supplier can supply a maximum of 400

units of each type of gear. The table below shows the exact demand for the gears, the revenue per unit,

and the outsource cost per unit if the gears are purchased from the supplier. The manufacturer

generates the same revenue per unit for the gears regardless of whether the gear is manufactured in

their plant and then sold to their customers or outsourced from their supplier and then sold to their

customers.

PRODUCT

GEAR TYPE GEAR A GEAR B GEAR C GEAR D

Demand 650 500 450 550

Revenue $13.75 $12.50 $16.90 $18.50

Outsource Cost $9.20 $9.75 $11.00 $11.75

When the gears are manufactured in the own plant, the gears must be processed through three

different departments: forming, hardening, and deburring. The table below shows the processing time

(in hours) for each type of gear in the departments as well as the capacity for each department and the

cost per hour for processing the gears in those departments. The cost per hour for processing the gears

is provided so that you can calculate the manufacturing cost.

PROCESS GEAR A

(hrs./unit)

GEAR B

(hrs./unit)

GEAR C

(hrs./unit)

GEAR D

(hrs./unit)

DEPARTMENT

CAPACITY

(hours)

COST

($/hr.)

Forming 0.37 0.43 0.45 0.52 500 $9.50

Hardening 0.30 0.25 0.31 0.40 400 $8.75

Deburring 0.40 0.37 0.42 0.32 400 $7.90

Formulate and solve this problem in Excel to determine the production and/or outsource plan which will

meet the requirements and maximize the profit. (Hint: processing costs in the second table effect only

the profit for the gears that are manufactured and not the gears that are outsourced)

a) How much profit does the company for all gears they make and buy in your solution (the value

of the objective function)? (enter to the nearest integer)

b) If you could add one hour of capacity to any department to increase profit - adding one hour of

capacity to which department would generate the biggest increase in profit: Forming,

Hardening, or Deburring?

c) Which of the following constraints have slack? (Choose all constraints with slack): Forming,

Hardening, or Deburring

d) In your solution, how many Gear C's should the company make?

e) If the cost per hour of the hardening process increases to $12/hr. - how many Gear D's should

the company make with this new process cost?