Linear Programming attached pictures of example of how it should be set up in excel
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