Optimization

Problem 1 (20 points)

The Mom and Pop Ice Cream Company makes three kinds of chocolate ice cream: regular, premium and super premium. The properties of 1 gallon (gal) of each kind are shown in the table:

Regular

Premium

Super Premium

Flavoring

24 oz

20 oz

22 oz

Milk-fat products

12 oz

20 oz

24 oz

Shipping weight

5 lbs

6 lbs

7 lbs

Profit

$0.75

$0.95

$1.05

In addition, current commitments require the company to make at least 1 gal of premium for every 4 gal of regular. Each day, the company has available 725 pounds (lb) of flavoring and 425 lb of milk-fat products. If the company can ship no more than 3000 lb of product per day, how many gallons of each type should be produced daily to maximize profit?

Provide the complete linear programing formulation (do not solve). Clearly specify decision variables, objective function and constraints.

Problem 2 (20 points)

Consider the following linear program, which maximizes daily total profit for two products, regular (R), and super (S):

MAX Z = 50R + 75S

s.t.

1.2R + 1.6 S 600 assembly (hours)

0.8R + 0.5 S 300 paint (hours)

.16R + 0.4 S 100 inspection (hours)

R, S 0

Implement the LP problem in a spreadsheet model and invoke Solver to obtain the optimal solution. Use label and formatting to enhance readability of model and Solver reports.

a.Create spreadsheet model

b.Invoke Solver to find optimal solution

c.What is the optimal solution?

d.What is the maximum daily profit?

e.What resources are binding at optimality?

Problem 3 (10 points)

The production manager for the Whoppy soft drink company is considering the daily production of two kinds of soft drinks: number of cases of regular (R) and number of cases of diet (D). The main constraints are production time, availability of syrup and shipment capacity. Profit for regular soft drink is $3.00 per case and diet soft drink is $2.00 per case.

The formulation for this problem is given below.

MAX Z = $3R + $2D

s.t.

2R + 4D 480 (Production in minutes)

5R + 3D 675 (Syrup in gallons)

R +D 200 (Shipment capacity in cases)

R>= 0, D>= 0

The sensitivity report is given below.

Adjustable Cells

Cell

Name

Final Value

Reduced Cost

Objective Coefficient

Allowable increase

Allowable decrease

$B$6

Cases of Regular =

90.00

0.00

3

0.33

2

$C$6

Cases of Diet =

75.00

0.00

2

4

0.2

Constraints

Cell

Name

Final Value

Shadow Price

Constraint R.H. Side

Allowable Increase

Allowable Decrease

$E$3

Production (minutes)

480.00

0.07

480

420

210

$E$4

Syrup (gallons)

675.00

0.57

675

525

315

$E$5

Shipment (cases)

165.00

0.00

200

1E+30

35

Complete the following sentences using the information from the Sensitivity Report.

a) The optimal number of cases of regular drink to produce is __________, and the optimal number of cases of diet drink to produce is __________, for total profits of __________.

b) If the profit per case of Regular drink decreases by $1.50, what would be effect on the optimal solution and total profit?

c) If it costs the same to increase production time by 3 hours or availability of syrup by 10 gallons, which one should the company increase?

d) If downtime reduced the production time by 200 minutes (from 480 to 280 minutes), what would be effect on the optimal solution and total profit?