Good Candy Inc. (GCI) has recently realized that some of their bags of Super Assorted Mix (32.0 oz or approximately 907.17 grams, 70 count) have been lacking some of its customers’ favorite pieces of candy with some having disproportionately more (some with over 50 pieces) of Milk Duddies. GCI would like to determine a “better” bag of candy that would satisfy more people.

GCI would like to determine an “optimal” bag of Super Assorted Mix candy based upon the following information.

1. A national survey of candy lovers has determined a “candy value” (CV) for the different types of candy. The candy found in the Super Assorted Mix bag of candy include: Keith Bar (CV of 16), Bit Bat (CV of 17), Milk Duddies (CV of 5), Meeses (CV of 14), and Kolo’s (CV of 12). GCI would like to create a product mix that provides for the greatest amount of candy value given the following constraints.

2. Sugar content (in grams) are as follows: Keith Bar (8.7 grams), Bit Bat (7.85 grams), Milk Duddies (6.7 grams), Meeses (7.4 grams), and Kolo’s (9 grams). The average sugar content, according to the National Institute for Healthy Teeth, should be no more than 8 grams (again, average).

3. The Super Assorted Mix bag of candy should have at least 68 pieces of candy but no more than 75 pieces, inclusive.

4. No more than 35 pieces (inclusive) Bit Bats and Milk Duddies should be included in each bag. Additionally, the number of Keith Bars must be at least 10% of the total number of pieces of candy in the Super Assorted Mix Assorted bag of candy.

5. The weight in grams are as follows: Keith Bar (16 g), Bit Bat (6 g), Milk Duddies (12 g), Meeses (10 g), and Kolo’s (14 g). The maximum weight of all the candy combined should not exceed 932 grams.

6. According to the National Institute for Denistry, a chewiness (Ch) measure (based on a scale of 1 (very hard) to 10 (completely sticky)) has been performed. The following represents the chewiness (Ch) scores for: Keith Bar (Ch of 1), Bit Bat (Ch of 5), Milk Duddies (Ch of 10), Meeses (Ch of 6), and Kolo’s (Ch of 7). The average chewiness score should be greater than or equal to 3. (again, average)

7. When building your model in Excel, round the number of pieces of candy for each type of candy—using the appropriate Excel function and place them in a separate row.

Decision Variables---Let:

KB = The number of to Keith Bar candy bars to include in that Super Assorted Mix bag.

BB = The number of Bit Bat candy bars to include in that Super Assorted Mix bag.

MD = The number of Milk Duddies candy to include in that Super Assorted Mix bag.

Me = The number of Meeses candy bars to include in that Super Assorted Mix bag.

Ko = The number of Kolos candy bars to include in a Super Assorted Mix bag.

Z = Candy value (Higher scores are better.)

Objective Function:

Maximize: Z = 16KB + 17BB + 5MD + 14Me + 12Ko (Maximize Candy Value Scores)

Subject to the constraints:

8.7KB + 7.85BB + 6.7MD + 7.4Me + 9Ko <= 8 * (KB + BB +MD + Me + Ko) (Ave. Sugar Limit for Super Assorted Mix bag.)

1KBB + 1BB + 1MD + 1Me + 1Ko => 68 (Min. number of candy pieces in Super Assorted Mix bag.)

1KBB + 1BB + 1MD + 1Me + 1Ko <= 75 (Max. number of candy pieces in Super Assorted Mix bag.)

0KBB + 1BB + 1MD + 0Me + 0Ko <= 35 (Max. number of Bit Bats & Milk Duddies candy pieces in Super Assorted Mix bag.)

16KBB + 6BB + 12MD + 10Me + 14Ko <= 932 (Weight in grams should not exceed (Maximum) the weight in grams limit for Super Assorted Mix bag.)

1KB + 0BB + 0MD + 0Me + 0Ko => 10% * (KB + BB +MD + Me + Ko) (Keith Bars must be at least 10% of the candy pieces in the Super Assorted Mix bag.)

1KB + 5BB + 10MD + 6Me + 7Ko => 6.3 * (KB + BB +MD + Me + Ko) (Ave. chewiness score should be at or above the minimum for Super Assorted Mix bag.)

KB, BB, MD, Me + Ko => 0 (nonnegativity)

The following is the answer report to double check the answers:

 

Transcribed Image Text:

Objective Cell (Max) Cell $G$9 Cell Name $B$8 Number of Candy Pieces Keith Bars $C$8 Number of Candy Pieces Bit Bats $D$8 $E$8 $F$8 Name Candy Value Score / Candy Piece Cell Name $G$15 Minimum number of candy pieces $G$16 At least 10% must be KBs $G$17 Average Chewiness score must exceed $G$11 Average Sugar Limit / bag $G$12 Maximum number of candy pieces $G$13 Maximum number of BBs & MDs $G$14 Maximum weight in grams Number of Candy Pieces Milk Quddies, Number of Candy Pieces Meessa Number of Candy Pieces Kolos Microsoft Excel 15.0 Sensitivity Report Cell $B$8 Variable Cells $C$8 $D$8 $E$8 $F$8 Name Number of Candy Pieces Keith Bars Number of Candy Pieces Bit Bats Number of Candy Pieces Milk Quddies, Number of Candy Pieces Meeses Number of Candy Pieces Kolos Constraints Cell Name $G$15 Minimum number of candy pieces $G$16 At least 10% must be KBs $G$17 Average Chewiness score must exceed Average Sugar Limit / bag Maximum number of candy pieces Maximum number of BBs & MDs $G$11 $G$12 $G$13 $G$14 Maximum weight in grams Original Value Original Value Cell Value Final Value Final Value 7.5 20.51912568 14.48087432 9.904371585 22.59562842 75 7.5 472.5 600 75 35 0 832.2677596 0 0 0 0 0 Formula 75.00 $G$15>=$I$15 7.50 $G$16>=$I$16 472.50 $G$17>=$I$17 600.00 $G$11<=$I$11 75.00 $G$12<=$I$12 35.00 $G$13<=$I$13 832.27 $G$14<=$I$14 Final Value 951.0382514 Final Value Contin 20.51912568 Contin 14.48087432 Contin 9.904371585 Contin 22.59562842 Contin Reduced Cost Shadow Price 0 0 0 0 0 0 -10.03278689 -2.349726776 0.218579235 12.42295082 0.551912568 0 Integer 7.5 Status Not Binding Binding Binding Binding Binding Binding Not Binding Objective Coefficient 16 17 5 14 12 Constraint R.H. Side 0 0 0 0 0 0 0 Slack Allowable Increase 10.03278689 140.3333333 99.73224044 2 0.4 4.590909091 Allowable Increase 7 8.473073352 64.26408451 18.125 7.940050466 11.25776398 7.00 0.00 0.00 1E+30 0 0 0 Allowable Decrease 124.2295082 0.711267606 2.463414634 10.92857143 0.4 Allowable Decrease 1E+30 7.5 82.8125 41.35 7 26.44366197 99.73224044 Objective Cell (Max) Cell $G$9 Cell Name $B$8 Number of Candy Pieces Keith Bars $C$8 Number of Candy Pieces Bit Bats $D$8 $E$8 $F$8 Name Candy Value Score / Candy Piece Cell Name $G$15 Minimum number of candy pieces $G$16 At least 10% must be KBs $G$17 Average Chewiness score must exceed $G$11 Average Sugar Limit / bag $G$12 Maximum number of candy pieces $G$13 Maximum number of BBs & MDs $G$14 Maximum weight in grams Number of Candy Pieces Milk Quddies, Number of Candy Pieces Meessa Number of Candy Pieces Kolos Microsoft Excel 15.0 Sensitivity Report Cell $B$8 Variable Cells $C$8 $D$8 $E$8 $F$8 Name Number of Candy Pieces Keith Bars Number of Candy Pieces Bit Bats Number of Candy Pieces Milk Quddies, Number of Candy Pieces Meeses Number of Candy Pieces Kolos Constraints Cell Name $G$15 Minimum number of candy pieces $G$16 At least 10% must be KBs $G$17 Average Chewiness score must exceed Average Sugar Limit / bag Maximum number of candy pieces Maximum number of BBs & MDs $G$11 $G$12 $G$13 $G$14 Maximum weight in grams Original Value Original Value Cell Value Final Value Final Value 7.5 20.51912568 14.48087432 9.904371585 22.59562842 75 7.5 472.5 600 75 35 0 832.2677596 0 0 0 0 0 Formula 75.00 $G$15>=$I$15 7.50 $G$16>=$I$16 472.50 $G$17>=$I$17 600.00 $G$11<=$I$11 75.00 $G$12<=$I$12 35.00 $G$13<=$I$13 832.27 $G$14<=$I$14 Final Value 951.0382514 Final Value Contin 20.51912568 Contin 14.48087432 Contin 9.904371585 Contin 22.59562842 Contin Reduced Cost Shadow Price 0 0 0 0 0 0 -10.03278689 -2.349726776 0.218579235 12.42295082 0.551912568 0 Integer 7.5 Status Not Binding Binding Binding Binding Binding Binding Not Binding Objective Coefficient 16 17 5 14 12 Constraint R.H. Side 0 0 0 0 0 0 0 Slack Allowable Increase 10.03278689 140.3333333 99.73224044 2 0.4 4.590909091 Allowable Increase 7 8.473073352 64.26408451 18.125 7.940050466 11.25776398 7.00 0.00 0.00 1E+30 0 0 0 Allowable Decrease 124.2295082 0.711267606 2.463414634 10.92857143 0.4 Allowable Decrease 1E+30 7.5 82.8125 41.35 7 26.44366197 99.73224044

Answer rating: 100% (QA)

Decision Variables KB The number of to Keith Bar candy bars to ... View the full answer