Question

Given the below information

Zales	Rubies	Sapphires	Labour time	Price	Market demand
Type 1 Ring	2	3	1	R    1 200.00	20
Type 2 Ring	3	2	2	R    1 500.00	24
Current Sales	100	120	70

Extra rubies if required can be bought at R300 per ruby.

1. How do you maximise profit using solver linear programming?

2. What will the optimal solution be?
3. Suppose that instead of R300, each ruby costs R750. Will Zales still buy extra rubies? If so, will the production plan and profit change?

Answer 1

(1) Design

Variable

1. type 1 ring produced = X

2. type 2 ring produced = Y

Constraints

1. Maximum Sapphires available are 120, so 3X + 2Y < 120

2. Maximum labor time available is 70, so X + 2Y < 70

3. X and Y are non-negative integers

please note there is no limit on the number of rubies available, however there will be additional cost when more than 100 are used

Objective

Maximize profit = Type 1 rings x 1200 + type 2 rings x 1500 - additional rubies cost

= 1200X + 1500Y - (2X + 3Y - 100)*300

=600X + 600Y + 30,000

Note the above function is only applicable when 2X + 3Y > 100, so when the optimal solution has to be checked with this condition

(2)

Use solver functionality of the excel where variable, constrains and objective are defined

so

optimal type 1 rings = 24

optimal type 2 rings = 23

(check : 24*2+23*3 = 117, so 17 additional rubies were bought)

3.

Objective function changes here

Maximize profit = Type 1 rings x 1200 + type 2 rings x 1500 - additional rubies cost

= 1200X + 1500Y - (2X + 3Y - 100)*750

=75000 - 300X - 750Y

(remember to check the condition or apply the objective function as if (2X+3Y > 100, obj1, obj2)

where obj1 = 75000 - 300X - 750Y

obj2 = 1200X + 1500Y

Answer is

var	X	26
	Y	16
const	3X + 2Y < 120	10
	X + 2Y < 70	12
Objective	75000 - 300X - 750Y	55200

so in this case production plan changes

optimal type 1 rings = 26

optimal type 2 rings = 16