Need help with
this Linear Programming Problem(Solver)?
To make one package of all beef hot dogs, a manufacturer uses 1 lb of
beef; to make one package of regular hot dogs, the manufacturer uses
1/2 lb each of beef and pork. The profit on the all-beef hot dogs is 40
cents per pack and the profit on regular hot dogs is 30 cents per pack.
If there are 200 lbs of beef and 150 lbs of pork available, how many
packs of all-beef and regular hot dogs should the manufacturer make to
maximize profit? What is the profit?
You can solve the problem in many ways. We can use simple Algebra or
Solver and the advantages of the latter become obvious with the
increase in the complexity of the problem.
Entering the data in the Microsoft Excel worksheet properly is half the
battle won. The easiest way is to enter the data as the user narrates
it. So we have entered the data for manufacturing one hot dog of each
kind in cells B4:C5 and calculated the profit in range E4. The
ingredients required, the quantity used in each hot dog, the total
quantity used and the total quantity of meat available is entered into
range B8 to E9 with appropriate headers.The quantity of meat used to
manufacture the hot dogs is calculated based on the quantity of hot
dogs produced. We also apply logical constraints like the calculations
cannot have negative values in the production of hot dogs and the
quantity of raw material used for production can never exceed what is
available in the ware-house. Although these things are quite logical to
the human brain the computer and its program need to be told this
clearly!
Watch the video (12.3MB) below to see how Microsoft Excel's Solver
performs its
magic once you tell it what to do: