The Case Chemicals spreadsheet We use cells B2 and C2 to contain the values for the decision variables, x 1 and x 2 respectively. maximize 300x x 2 subject to 2x 1 + x 2 230, (blending) (1) x 1 + 2x 2 250, (purification) (2) x 2 120, (CS-02 limit) (3) x 1, x 2 0 Using EXCEL Solver page 1Дў The Case Chemicals problem is entered into an EXCEL spreadsheet in the following format. Let: x 1 be the number of thousands of gallons of CS-01 to produce, and x 2 be the number of thousands of gallons of CS-02 to produce. As a Production- Planning Manager, you want to determine the optimal weekly manufacturing plan for Case Chemicals. Because all employees are salaried and thus paid the same amount regardless of how many hours they work, these salaries and the costs of machines are considered fixed and are not included in the computation of the profit margin. The accounting Department estimates a profit margin of $0.30 per gallon of CS-01 and $0.50 per gallon of CS-02. It can sell any amount of CS-01, but demand for the more-specialized product CS-02 is limited to at most 120 thousand gallons per week. The hours required in the Blending and Purification Departments to produce one thousand gallons of each of the solvents are listed in the table below: CS-01 CS-02 Blending 2 1 Purification 1 2 Blending and Purification Requirements (hr/1000 gallons) Case Chemicals has a nearly unlimited supply of raw materials to produce the two solvents. This work force provides up to 250 hours of available labor in the Purification Department. The products, once blended, are refined in the Purification Department, which currently has seven purifiers and employs six full-time workers and one part-time worker who puts in 10 hours per week. This work force provides up to 230 hours of available labor in the Blending Department.

The plant operates 40 hours per week and employs five full-time and two-part-time workers working 15 hours per week to run their seven blending machines that mix certain chemicals to produce each solvent. Production Planning at Case Chemicals Case Chemicals Company produces two solvents, CS-01 and CS-02, at its Cleveland plant. Mason, University of Auckland To illustrate how we can use Microsoft EXCEL to solve linear programming problems, consider the following production planning problem. 1 Solving Linear Programs using Microsoft EXCEL Solver By Andrew J.

Download solver for excel 2010