This is an unformatted preview. Please download the attached document for the original format.
For the Ferdinand Feed LP problem presented in the 'Problem Formulation' worksheet, set up and solve the LP using
Solver in the 'Solver Solution' worksheet to find the optimal feed mix and generate the Answer and Sensitivity
Reports.
Once complete, answer the questions below and upload the complete spreadsheet with the Solver set-up and
solution/sensitivity analyisis intact to D2L.

AFTER FINDING THE SOLUTION, USE THE ANSWER AND SENSITIVITY REPORTS TO ANSWER THE FOLLOWING QUESTIONS
What would the required minimum cost for Grain 4 before we would include any in the final product mix?
What is the final raw material cost of the 1-lb mix? (give units and report final answer to three digits)
What would be the resultant cost of the 1-lb mix if we reduced the the iron requirement by 2 units?
How high could the cost of grain 1 be and still have our current optimal solution would remain optimal?

Ferdinand Feed Company receives four raw grains from which it blends its dry pet food. The pet food
advertises that each 1-lb packet meets the minimum daily requirements for vitamin C, protein and iron.
The cost of each raw grain as well as the vitamin C, protein, and iron units per pound of each grain are
summarized in Table 1.
Ferdinand is interested in producing the 1-lb packet at minimum cost while meeting the minimum daily
requirements of 6 units of vitamin C, 10 units of protein, and 14 units of iron. The LP formulation to
determine the optimal blend for Ferdinand Feed based upon the above requirements is given below.

Table 1
Grain

Decision Variables
xi = the pounds of grain 'i' used in the mixture, i=1,…,4
Objective Function
Minimize the total cost for a 1-lb mixture =

Vitamin C
units/lb

Protein
units/lb

Iron
units/lb

1
2
3
4

9
12
10
10

12
12
8
8

4
14
15
10

0.72x1 + 1.00x2 + 0.80x3 + 0.75x4

Constraints
Total weight of the mix is 1 pound:
Total amount of Vitamin C in the mix is at least 6 units:
Total amount of protein in the mix is at least 10 units:
Total amount of iron in the mix is at least 14 units:
Non-negativity: xi > 0, i=1,…,4

(1)
(2)
(3)
(4)

x1 + x2 + x3 + x4 = 1
9x1 + 12x2 + 10x3 + 10x4 > 6
12x1 + 12x2 + 8x3 + 8x4 > 10
4x1 + 14x2 + 15x3 + 10x4 > 14

Cost ($)/lb
0.72
1.00
0.80
0.75

Set-up the spreadsheet model and run Solver to find the optimal solution for LP formulated in the previous worksheet for Ferdinand Feed.
Cleary label or identify the decision variables, objective function and constraints.
Find the optimal solution and generate the Answer and Sensitivity reports.