Linear Programming - attached pictures of example of how it should be set up in excel

User Generated

fnzzlunqnzf

Business Finance

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):

  1. Give a typed formulation with decision variables clearly defined and all constraints clearly defined.
  2. 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:
  1. Give a typed formulation with decision variables clearly defined and all constraints clearly defined.
  2. 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:
  1. 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

Assignment 4 – 20 Points Problem: 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: Candy Capacity (Pounds/Day) Cashews 40 Raisins 45 Caramels 35 Chocolates 40 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: Candy Profit per Pound Regular $0.80 Deluxe $0.90 Cashews $0.70 Raisins $0.60 Caramels $0.50 Chocolates $0.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: Candy Olga Labor (Minutes) Sven Labor (Minutes) Regular 11 10.5 Deluxe 10 12 Cashews 4 6 Raisins 6 5 Caramels 7 4 Chocolates 7 6 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): 1) Give a typed formulation with decision variables clearly defined and all constraints clearly defined. 2) 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: 1) Give a typed formulation with decision variables clearly defined and all constraints clearly defined. 2) 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: 1) Comment on this requirement. Instructions Submit your assignment using the Assignments folder. The file name should follow the format: Your-last-name Assgn4 ao APA || || 《上, Paragraph Normalno Spad... Headings Habboel az Subtitle Silen. En Thie Font Shies Wyndor Glass Company The Wyndor Glass Co. produces high-quality class products, including windows and class doors. It has three plants. Aluminum frames and hardware are produced in Plant 1. wood frames are produced in Plant 2, and Plant 3 produces the glass and assembles the products, Because of declining eamings, top management has decided to revamp the company's product line. Uaprofitable products are being discontinued releasing production capacity to launch two new products having large nalex potential: Produa 1: An 8-foot glass door with aluminum framing, Product 2: A 4 by 6 foot double-bung waad-framed window. The company must determine the product mix' that will maximize profits. The following table lists pertineat data: Plant 1 2 3 Profit.Unit Production Time per Unit (Hours) Product 1 Product 2 ! 0 0 2 3 2 S3DDD SSODD Production Time Available (Hours) 나 12 18 a 0 x will BIG Problema Microsoft 360 Home Iniert Page Layout Formulas Data Review VOW Developer Calibri AA for Wrap Tent TAutoou X Cut Copy Paste Format Painter toboma F=3 Merge de Center - Insert Delete Tommet $ -% | Condition format Cell Formatting" at Table Styles Number Styles 2 Cear Alignment Font A A30 B D F G K M N O P H L 1 Our task is to determine how many units to produce of Product 1 and Product 2 so that we maximize profits 2 This is called a "product-mix" problem am confident that each of you could solve this problem given time, however these problems can become complicated very quickly 3 4 5 6 Plant 7 1 Production Time Per Unit (Hours) Product 1 Product 2 Production Time Available (Hours) 1 o 4 0 2 12 3 2 18 $3,000.00 $5,000.00 8 2 9 3 10 Profit/Unit 12 + 12 Decision varlables: 13 How many units produced of Product 1 14 How many units produced of Product 2 15 16 Objective function: 17 Maximize profits 18 (3000* units produced of Product 1) + (5000 * units produced of Product 2) 19 20 Constraints: * " Intro Wyndor Sheet2 Reads O X W: 国BASE P Problem - Micro File Home Page Layout Farzul REVIEW VIEW Developer 高 1 X Cut a copy * Fornst Painter General Calibri A A Wrap Tent BIU.B.A.FI 23 Merge & Center Insert Delete ? Clipboard S.%9Conditonal Format Cell Fomafting as Table Styles Hlumber Styles T Font Alignment Cells B D E F L @ 13 How many units produced of Product 1 14 How many units produced of Product 2 16 Objective function: 17 Maximize profits 18 (3000 * units produced of Product 1) + (5000 * units produced of Product 2) 20 Constraints: 21 Hours avallable in Plant 1 22 Hours avallable in Plant 2 23 Hours available in Plant 3 24 Nonnegativity Number of hours required to produce Products 1 & 2
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

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...


Anonymous
Really helped me to better understand my coursework. Super recommended.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags