Using Solver for Cost of Corn, Fishbones, and Sawdust
Not what you're looking for?
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?
Purchase this Solution
Solution Summary
The solution uses the solver (Excel) as requested together with a lenghy narrative to explain the various answers.
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 ...
Purchase this Solution
Free BrainMass Quizzes
Six Sigma for Process Improvement
A high level understanding of Six Sigma and what it is all about. This just gives you a glimpse of Six Sigma which entails more in-depth knowledge of processes and techniques.
Understanding Management
This quiz will help you understand the dimensions of employee diversity as well as how to manage a culturally diverse workforce.
Employee Orientation
Test your knowledge of employee orientation with this fun and informative quiz. This quiz is meant for beginner and advanced students as well as professionals already working in the HR field.
Cost Concepts: Analyzing Costs in Managerial Accounting
This quiz gives students the opportunity to assess their knowledge of cost concepts used in managerial accounting such as opportunity costs, marginal costs, relevant costs and the benefits and relationships that derive from them.
Basic Social Media Concepts
The quiz will test your knowledge on basic social media concepts.