Explore BrainMass

Explore BrainMass

    Using Solver for Cost of Corn, Fishbones, and Sawdust

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    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?

    © BrainMass Inc. brainmass.com February 24, 2021, 2:09 pm ad1c9bdddf
    https://brainmass.com/business/management-accounting/using-solver-cost-corn-fishbones-sawdust-4495

    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.

    $2.19

    ADVERTISEMENT