Description
Problem: in excel solver should be used
A chocolate maker has contracted to operate a small candy counter in a fashionable store. To start with, the selection of offerings will be intentionally limited. The counter will offer a regular mix of candy made up of equal parts of cashews, raisins, caramels, and chocolates, and a deluxe mix that is one-half cashews and one-half chocolates. In addition, the candy counter will sell the four candies individually.
A major attraction of the candy counter is that all candies are made fresh at the counter. However, storage space for supplies and ingredients is limited. Bins are available that can hold the amounts shown in the following table:
CandyCapacity (Pounds/Day)
Cashews40
Raisins45
Caramels35
Chocolates40
In order to present a good image and to encourage purchases, the counter will make at least 20 pounds of each type of candy each day. Assume that demand of the candies is such that regardless of candy mix all candy can be sold every day. Formulate the problem in order to maximize profit.
The profit per pound for the various candies is as follows:
CandyProfit per Pound
Regular.80
Deluxe.90
Cashews.70
Raisins.60
Caramels.50
Chocolates.75
The chocolate maker has two employees, Olga and Sven, who prepare the chocolates for sale. Each employee works 480 minutes per day but the amount of time each employee takes to prepare a pound of the various candies varies. The labor time per pound is listed below:
CandyOlga Labor (Minutes)Sven Labor (Minutes)
Regular1110.5
Deluxe1012
Cashews46
Raisins65
Caramels74
Chocolates76
In order to avoid quarrels between Olga and Sven, the chocolate maker wants each to prepare the same number of pounds of candies each day.
Deliverable 1 (10 points):
- Give a typed formulation with decision variables clearly defined and all constraints clearly defined.
- Solve the formulation in solver. Deliverable 2 (7 points):The chocolate maker is somewhat quirky, to put it mildly! He does not want to ‘slight’ the four main candies, (Cashews, raisins, caramels, chocolates) so he wants to use the same proportion of the available pounds of each of the four candies each day.Requirements:
- Give a typed formulation with decision variables clearly defined and all constraints clearly defined.
- Solve the formulation in solver. Deliverable 3 (3 points):The chocolate maker would like to simplify the daily production so has decided that the production of each of the six candies each day will be in full pounds only.Requirements:
- Comment on this requirement.InstructionsSubmit your assignment using the Assignments folder.The file name should follow the format: Your-last-name Assgn4
Unformatted Attachment Preview
Purchase answer to see full attachment
Explanation & Answer
sorry I forgot to change the name to Your-last-name Assgn4, please use the following files instead. (I also did some change in the updated solution)
Deliverable 1:
1) We define the following six variables as
x1 = amount of regular candy
x2 = amount of deluxe candy
x3 = amount of cashew candy
x4 = amount of raisin candy
x5 = amount of caramel candy
x6 = amount of chocolate candy
The objective function that we want to maximize can be written as
Z 0.8x1 0.9 x2 0.7 x3 0.6 x4 0.5x5 0.75x6
Since it’s given that the regular mix has equal parts of cashews, raisins, caramels and chocolates,
it follows that each pound of regular mix has 0.25 pounds of each cashews, raisins, caramels
and chocolates.
Since it’s given that the deluxe mix has equal parts of cashews, and chocolates. It follows that
each pound of regular mix has 0.5 pounds of each cashews and chocolates.
Hence, the constraints in inequality form are:
Cashews:
x1 1.5x2 x3 40
Raisins:
x1 x4 45
Caramels:
x1 x5 35
Chocolates:
x1 1.5x2 x6 40
Since each employee works 480 minutes per day but the amount of time each employee takes
to prepare a pound of the various candies varies, we have the following constraints for both
Olga and Sven’s labor time per pound.
For Olga:
11x1 10 x2 4 x3 6 x4 7 x5 7 x6 480
For Sven:
10.5x1 12 x2 6 x3 5x4 4 x5 6 x6 480
Minimum requirements for number of candy per person to be produced:
x1 20
x2 20
x3 20
x4 20
x5 20
x6 20
In summary, we have the following optimization problem:
Maximize
Z 0.8x1 0.9 x2 0.7 x3 0.6 x4 0.5x5 0.75x6
Subjected to
x1 1.5 x2 x3 40
x1 x4 45
x1 x5 35
x1 1.5 x2 x6 40
11x1 10 x2 4 x3 6 x4 7 x5 7 x6 480
10.5x1 12 x2 6 x3 5x4 4 x5 6 x6 480
x1 , x2 , x3 , x4 , x5 , x6 20
2) We use Solver in Excel to find the optimal solution to the maximized problem, and its result is
presented below:
Regular
Deluxe
Cashew
Raisins
Cashew
1
1.5
1
0
Raisins
1
0
0
1
Caramel
1
0
0
0
Choco...