Question

The Minnow Bucket Bait Shop in Land O’ lakes, Wi raises trout, their current inventory is 7,000 fish (trout). They want to buy a mix of feed for their trout that both provides the nutrients needed and comes at the lowest cost. In order to do this, they are going to buy and mix two brands of feed. Each pound of Brand-1 feed contains 3.5 ounces of ingredient “A”, 4.0 ounces of ingredient “B” and 0.75 ounces of ingredient “C”. Each pound of Brand-2 contains 10.0 ounces of ingredient “A”, 3.25 ounces of ingredient “B” and no (zero) ounces of ingredient “C”. Brand-1 costs $2.25 per pound and Brand-2 costs $3.10 per pound. Each trout needs 87.0 ounces of ingredient “A”, 52.0 ounces of ingredient “B” and 3.5 ounces of ingredient “C” per month.

How many total pounds of each brand should they buy per month to keep their cost as low as possible and still meet the dietary requirements of the trout? What will the cost per month be?

Include your spreadsheet with any formulas noted. Also include a list of the constraints you used. Leave it as is in ounces.

Answer 1

Calculation to be made as under:

Trouts	7,000
	Ingredient A	Ingredient B	Ingredient C	cost per pound	Pounds to be purchased in a month	Total cost per month
1 Pound of Brand 1 contains ounces of	3.50	4.00	0.75	2.25
1 Pound of Brand 2 contains ounces of	10.00	3.25	-	3.10
each trout requires per month	87.00	52.00	3.50
Total requirements for 7000 trouts	609,000	364,000	24,500
Brand 1 pounds to be purchased					32,666.67
Balance requirement to be purchased after purchasing 32,666.67 pounds of Brand 1	494,666.67	233,333.33	-
Pounds of Brand-2 to be purchased	49,466.67	71,794.87			71,794.87
Total cost of Brand-1						$          73,500
Total cost of Brand-2						$        222,564
					104,461.54	$        296,064

Explanation:

First Minnow need to purchase as many pounds of Brand-1 as required to satisfy requirement of Ingredient C because Ingredient C is not there in Brand-2

Pounds of Brand-1 to be purchased = (7000 *3.5) / 0.75 = 32,666.67 pounds

After that Minnow need to find out how much requirement is fulfilled after buying pounds of Brand-1 and then Minnow need to find out the balance requirement to be fulfilled by buying pounds of Brand-2

After buying 32,666.67 pounds of Brand-1, to satisfy requirement of Ingredient A Minnow need to buy pounds of Brand-2 = ((7000*87) - (32,666.67 * 3.50)) / 10 = 49,466.67 pounds

and to satisfy requirement of Ingredient B Minnow need to buy pounds of Brand-2 = ( (7000*52) - (32,666.67 * 4)) / 3.25 = 71,794.87 pounds of Brand-2.

Ultimately Minnow should buy 71,794.87 pounds of Brand-2 so it will satisfy requirements of Ingredient A and Ingredient B both.

Total cost of Brand-1 =32,666.67 pounds * 2.25 =$ 73,500

Total cost of Brand-2 = 71,794.87 pounds * 3.10 = $ 222,564

Total cost for the month = $ 73,500 + 222,564 = $ 296,064

Formula used pasted below:

1 Trouts 7000 Ingredient A Ingredient B Ingredient C cost per pound Pounds to be purchased in Total cost per month a month 2.25 3.1 3.5 0.75 10 3.25 =$F$1*F6 =$F$1*G6 =$F$1*H6 9 =H7/H3 2 3 1 Pound of Brand 1 4 1 Pound of Brand 2 6 each trout requires per month 7 Total requirements for 7000 trouts Brand 1 pounds to be purchased Balance requirement to be purhased after purchasing 32,666.67 pounds of 10 Brand 1 11 Pounds of Brand-2 to be purchased 12 13 14 Total cost of Brand-1 15 Total cost of Brand-2 =F7-($J$9*F3) =F10/F4 =G7-($J$9*63) =H7-($J$9*H3) =G10/64 =IF(F11>G11,F11,611) =29*13 =J11*14