The production department for an aluminum valve plant is scheduling its work for next month. Each valve must go through three separate machines during the fabrication process.
After fabrication, each valve is inspected by a human being, who spends 12 minutes per valve. There are 450 inspection hours available for the month.
The time required (in hours) by each machine to work on each valve is shown in the following table. Also shown are the minimum number of valves that must be produced for the month and the unit profit for each valve. PRODUCTS DEPARTMENT V231 V242 V784 V906 CAPACITY OF EACH DEPARTMENT (hours) DRILLING 0.60 0.30 0.45 0.35 700 MILLING 0.60 0.55 0.52 0.52 950 LATHE 1.10 0.60 0.50 0.65 1100 MINIMUM OF EACH PRODUCT TYPE NEEDED 200 300 700 450 PROFIT ($/UNIT) $14 $10 $11 $12 Formulate and solve the problem in Excel to determine the number of each product to manufacture that meets the requirements and maximizes profits.
a) What is the maximum profit based on your optimal solution (the value of the objective function)?
b) How many V231's should be manufactured based on your optimal solution?
c) How many V242's should be manufactured based on your optimal solution?
d) How many V784's should be manufactured based on your optimal solution?
e) How many V906's should be manufactured based on your optimal solution?
f) What is the total number of hours used in the drilling department based on your optimal solution?
g) What is the total number of hours used in the milling department based on your optimal solution?
h) What is the total number of hours used in the lathe department based on your optimal solution?
i) What is the total number of hours used in the inspection department based on your optimal solution?