Question

A chemical company manufactures three chemicals: A, B, and C. These chemicals are produced via two production processes: 1 and 2. Running process 1 for an hour costs $400 and yields 300 units of A, 100 units of B, and 100 units of C. Running process 2 for an hour costs $100 and yields 100 units of A and 100 units of B. To meet customer demands, at least 1000 units of A, 500 units of B, and 300 units of C must be produced daily.

Use Solver in Excel to determine a daily production plan that minimizes the cost of meeting the company’s daily demands.

Use a solver in Excel to see what happens to the decision variables and the total cost when the hourly processing cost for process 2 increases in increments of $0.50. How large must this cost increase be before the decision variables change? What happens when it continues to increase beyond this point? (hint: reduced cost may help to answer this question.)

Include steps for solver in excel in answer as well as the formula for the objective cell

Answer 1

Let x1 be number of hours needed to process 1

and x2 be the number of hours needed to process 2

The objective function is

Min Z = 400x1 + 100x2

Subjective constraints

300x1 + 100x2 >=1100

100x1 + 100x2 >= 500

100x1 + 0x2 >= 300

x1,x2 >= 0

Excel Output:

Formula related work

From the above table, x1 = 3 and x2 =2 then Min Z = 400*3+100*2 = 1400

If the hourly processing cost for process 2 increases in increments of $0.50 then machine constraints are not satiesfied