Question 1192590: Show the linear program setup: Identify the variables; give the objective function and the constraints. Then use Solver (excel) and write the answer along with the value of the optimal objective function.
#4 A lawn and garden store sells 3 different potting mixes sold in 20 pound bags.
Mix A has 12 lbs peat, 5 lbs perlite, 3 lbs organic material. It earns a profit of $3 per bag
Mix B has 10 lbs peat, 6 lbs perlite, 4 lbs organic material. It earns a profit of $5 per bag
Mix C has 8 lbs peat, 8 lbs perlite, 4 lbs organic material. It earns a profit of $6 per bag.
There are 1200 lbs peat, 800 lbs perlite, 600 lbs organic material available.
How many 20 pound bags of each mix should be made to maximize profit?
Answer by Theo(13342)   (Show Source):
You can put this solution on YOUR website! here is my linear program setup that i used to create the excel solver inputs.
the three potting mixes are a, b, and c.
equation for peat is 12a + 10b + 8c <= 1200
equation for perlite is 5a + 6b + 8c <= 800
equation for organic is 3a + 4b + 4c <= 600
those are the constraints.
the objective function is 3a + 5b + 6c which is what you want to maximize.
here's the excel displays.
the first display is what excel shows.
you are in the data tab and solver is on the top right.
the second display is what you see when you click on solver.
the third display is what you see when you are looking at solver and selecting options.
i used excel 2007.
the solver is not loaded automatically.
you have to load it and then it will be available.
since the profit is so much money for each 20 pound bag, the profit function had to adjust the pounds to bags, using the roundup function of excel, because you can't have a partial bag.
this is a debatable part of the problem.
do you charge for a partially loaded 20 pound bag as a fully loaded 20 pound bag or do you just charge for a fully loaded bag 20 pound bag and not charge for the excess, or do you prorate the charge for a partially loaded 20 pound bag.
there are 3 possible answers.
the one shown is charging for a full bag even if it's not fully loaded.
that would be a profit of 3 * 0 + 5 * 5 + 6 * 2 = 37
if you only charge for a fully loaded 20 pound bag and do not charge for the excess, then the profit will be 3 * 0 + 5 * 5 + 6 * 1 = 31.
if you prorate the charge, then the profit will be 3 * 0 + 5 * 100/20 + 6 * 25/20 = 32.5.
the constraints were in pounds.
the profit was in 20 pound bags.
the profit function shown was charging for a full bag even if the bag wasn't filled to capacity.
the profit formula for that was:
=3*ROUNDUP(F4/20,0) + 5*ROUNDUP(G4/20,0) + 6 * ROUNDUP(H4/20,0)
if you only charged for a fully loaded bag and forgot about the rest, then the profit formula would have been:
=3*ROUNDDOWNP(F4/20,0) + 5*ROUNDDOWN(G4/20,0) + 6 * ROUNDDOWN(H4/20,0)
if you prorated the charge for a partially loaded bag, then the profit formula would have been:
=3*F4/20 + 5*G4/20 + *H4/20
i would probably go with the prorated charge.
that solution gets you 0 bags of mix A and 5 bags of mix B and 1.25 bags of mix C for a profit of 0 * 3 + 5 * 5 + 6 * 1.25 = 32.5
i redid the excel to show that.
it is shown below.
there's a lot here, so let me know if you have any questions.
theo
|
|
|
