Question

Formulate and then solve a linear programming model of this problem, to determine how many
containers of each product to produce tomorrow to maximize profits. The company makes four
juice products using orange, grapefruit, and pineapple juice.
Product Retail Price Per
Quart
Orange Juice $1.00
Grapefruit juice .90
Pineapple juice .80
All-in-One 1.10

Answer 1

Follow the steps to solve the linear programming model using excel as shown below:

Calculate the profit per quart as shown below:

Cost for all in one juice will be calculated as shown below:

Total cost of all the three juice = Cost of orange juice + cost of grapefruit juice + Cost of pineapple juice

Therefore,

Total cost of all the three juices = $(2.00 +1.60+1.40)

= $5

Total number of quarts produced =12 quarts

Therefore cost of 1 quart of all-in –one juice

Formulate the given problem as shown below:

Decision variables:

Consider juices as:

Orange juice = x₁

Grapefruit juice = x₂

Pineapple juice = x₃

All-in-one juice = x₄

Objective function:

Subject to constraints:

Restriction variable

x₁, x₂ x_3,x_4,≥0

Step 1 Enter the values of different variables, constraints, and objective in Excel as shown below:

Step 2: Use the formulas for each row and column as shown below:

Step 3: Enter the value of the variables, constraints, and objective function in Excel Solver, as shown below:

Step 4: After putting the values of objective function, constraints and variables in the above window, click on “Solve” tab.

The resultant values of the given problem are as shown below:

Therefore, the optimal product mix will constitute of 800 quarts of orange juice and 400 quarts of grapefruit juice, 0 quarts of pineapple juice and 2424.24 quarts of all-in-one juice. At this level of production, company will earn maximum profit of $2,248.485