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:

 

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