Explore BrainMass

Using Solver for Cost of Corn, Fishbones, and Sawdust

You are in business of producing and selling 100-pound bags of health food for pet pigs. You plan to advertise that each bag will provide a pig its minimum weekly requirements of protein (200 grams), calcium (300 grams) and fiber (100 grams), and will contain no more than 500 calories. You have found supplies at a reasonable cost for three possible ingredients.

cost protein calcium fiber calories
Corn $.03/lb 100 g/lb 2 g/lb 1 g/lb 50 /lb
Fishbones $.005/lb 1 g/lb 50 /lb None 2 /lb
sawdust $.001/lb none None 200 g/lb 1 /lb

You plan to sell each bag for $1. Develop an optimization model and solve. Answer the following questions, treating each independently from the others. (You do not have to make additional solver runs; all questions may be answered from the sensitivity analysis information.)

a. How much would the cost of corn have to drop to make it worth adding more?
b. If the cost of fishbones increased to $.006 per pound, what would the impact be?
c. If the cost of sawdust increased to $.005 per pound, what would the impact be?
d. If you reduced the advertised amount of protein from 200 grams/bag to 100 grams/bag, how much would that enable you to save? Why?
e. If you increased the advertised proportion of fiber from 100 grams/bag to 200 grams/bag, how much would your cost increase? Why?

Solution Preview

All the formulae can be referred to the attached EXCEL file:
I add some grids to the table: Quantity and Cost
Then our target is to minimize total cost "H5"= SUM (cost*Quantity)
Our changing cells are just the Quantity column (G2~G4).
The constraints are:
SUM (Protein*Quantity) >=200
SUM (calcium *Quantity) >=300
SUM (fiber *Quantity) >=100
SUM (calories *Quantity) <=500
SUM (quantity *Quantity) =100
Then SOLVER gives us the optimal result:
cost protein calcium fiber calories Quantity Cost
Corn 0.03 100 2 1 50 1.941 0.058
Fish bones 0.005 1 50 0 2 5.922 0.030
Sawdust 0.001 0 0 200 1 92.137 0.092
Total 0.180 200 300 18429.312 201.020 100 0.180
When there's Corn=1.941, Fish bones=5.922, sawdust=92.137 (lb)
The minimal total ...

Solution Summary

The solution uses the solver (Excel) as requested together with a lenghy narrative to explain the various answers.