Harbour Chemicals (SHC) manufactures chemicals used in the paint industry. The process involves three departments. Chemical A, which is purchased for $3 per liter, is processed through Department 1 in batches of 100 liters. Each batch of chemicals processed through Department A produces 70 liters of chemical B and 30 liters of chemical C. Chemical B is sold for $10 per liter. Chemical C is used in Department 2 to produce chemicals D, E, and F. Department 2 processes chemical C in batches of 200 liters. Each batch-processed through Department B produces 100 liters of chemical D, 60 liters of chemical E, and 40 liters of chemical F. Chemical D is sold for $12 per liter. Chemical E is a waste product that is donated to the local municipality to be spread on gravel roads to keep down the dust. Chemical F is a hazardous waste product that must be disposed of at a cost of $6 per liter. Alternatively, chemical F can be processed through Department 3 to produce chemical C. Department 3 processes chemical F in batches of 40 liters.
For each batch of chemical F processed, 20 liters of chemical C is produced. In the past, this operation has had a tendency to build up stocks of chemical C. The maximum storage capacity for chemical C is 1,000 liters. The accompanying diagram summarizes the production activities at SHC. The sales manager indicates that sales of chemical B cannot exceed 35,000 liters in the upcoming period, and sales of chemical D cannot exceed 10,000 liters. The production manager advises that 7,000 labor-hours are available for the upcoming period. Workers are paid $10 per hour worked. The production manager indicates that the labor-hours required for each batch in Departments 1,2, and 3 are 10, 15, and 10, respectively. Moreover, because of constraints relating to the mixing vats and storage, the maximum number of batches in Departments 1, 2, and 3 are 600, 80, and 40, respectively. The estimated variable overhead costs per batch in Departments 1, 2, and 3 are estimated at $250, $750, and $100.
Formulate an LP to determine the optimal production plan at SHC for the upcoming period.
Using Excel, solve the LP formulated in requirement 1 to determine the optimal plan.