The Bubbly Soda (BS) Company produces popular sodas. A special type of soda cons

Question

The Bubbly Soda (BS) Company produces popular sodas. A special type of soda consists of regular and diet soda. The company has received an order for a minimum of 400.00 litres of the special type. The customer specified that the order must contain at least 40% of regular soda and not more than 250,000 litres of diet soda. The customer also specified that the blemd should be mixed in the ratio of 2 parts regular to 1 part diet. The BS Company can produce 500,000 litres per week, regardless of the blemd. The production manager wants to complete the order in 1 week. The blend is sold for $12 per litre. The BS company's cost per litre is $4 and $2 for diet. The company wants to determine the blend mix that will meet customer requirements and maximize profits.

a.) Formulate algebraically a linear programming model for this problem.
b.) Solve this problem using excel

Explanation / Answer

a) First, we assign letters to the most basic quantities and then build upon relations given to build equations.

Litres of regular soda required to be made in a week -> R

Litres of diet soda required to be made in a week -> D

Litres of special soda required to be made in a week -> S

Cost of making special soda -> C

Money collected after selling special soda -> M

Profit -> P

Now, we list the linear relations between these variables.

S = a * R + b * D (In this problem, since customer wants a = 2 and b = 1),

S = 2 * R + D

C = 4 * R + 2 * D

M = 12 * S

P = M - C    

There are also some constraints involved.

S > 400.00 (customer wants more than 400.00 litres)

S <= 500000 (maximum production capacity in the week to complete order)

2 * R >= 40 * S / 100 which means R >= S / 5 (another customer requirement)

D <= 250000 (another customer requirement)

b) In Excel, after entering all the equations, use Solver add-in to put in the constraints, and add what quantity is to be optimised (profits) and click Solve. You should get this set of solutions

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.