Expert answer:Optimization and modeling question, management hom

Answer & Explanation:please follow the formatting instructions:Homework Formatting Instructions(1).pdfthis is an example => Template – Example for HW(1).pdf  and now here is the question :-HW2.pdfHW2-Data.xlsx hw2_data.xlsx
homework_formatting_instructions_1_.pdf

hw2.pdf

hw2_data.xlsx

hw2_data.xlsx

template____example_for_hw_1_.pdf

Unformatted Attachment Preview

Optimization for Decision Making DNSC6251
Homework Formatting Instructions
You are required to follow the following instructions for formatting your turned-in
homework. All homework assignments must be submitted through Blackboard.
1. You must provide the formulations of the optimization models (i.e. algebraic
form of constraints, objective, and variable definitions), as well as answers
to short answer questions. You can type them, or written them down and
scan them (as long as your handwriting is clearly legible). Graphs must be
clearly legible. For example, axes should be clearly labeled and be roughly
to scale.
2. Please use the provided Excel file (if any) to begin solving the questions that
make use of Excel. In many cases, the data will already be laid out in a
useful way to start you off. Sometimes, the problem will already be partially
solved, to lead you in the right direction.
3. You will need to print out each Excel spreadsheet that you update/create as
part of the homework that you hand in. Please follow the following
instructions to produce printouts that help your work to be properly graded:
a. Make sure to always “Print Headings” of the spreadsheet so that the
column labels (e.g. A, B, C) and row labels (e.g. 1, 2, 3) are visible on
the printout. In Excel 2007, 2010, and 2013, clicking the Page Layout
tab of the ribbon and then placing a check mark in the “Print
Headings” option will do this.
If you are using Excel 2003, click the File menu, then Page Setup, go
to the Sheet tab, and place a check mark in the “Print Row and
Column Headings” box (as seen below).
b. Please print each spreadsheet twice. Once with all cells showing their
respective formulas (“formula view”), and once with the “normal
view” where the cells show the numbers that the formulas evaluate to.
In Excel 2007, 2010, and 2013, you can switch between the “formula”
view and “normal” view by clicking the Formulas tab and then
clicking the “Show Formulas” button. Alternatively, Clicking Ctrl-`
also toggles between “formula” and “normal” view (hold down the
Ctrl key and press the button with the ~ symbol on it to the left of the
1 key). Make sure that column widths are large enough so that any
formulas which are seen in the “formula view” can be fully seen onscreen (and in your printout!). Additional help on how to do this is
here: http://www.mrexcel.com/articles/show-formulas-in-cells.php
c. For each spreadsheet you solve, please copy-and-paste the Excel
Solver settings into a MS Word document which gets handed in with
your assignment. To do this, click Alt-[Print Screen] to take a picture
of the Solver settings and Paste the resulting picture in MS Word. For
example, the following is a screen capture using this method.
d. Highlight or circle the important quantities (i.e. cells that contain the
optimal solution and/or optimal value)
Once you will have done the above, build a single pdf file and submit it
through Blackboard and the link associated with the corresponding
assignment.
Homework 2
Due: Friday, February 12, 11PM, online
Please read “Homework Formatting Instructions.doc” on Blackboard
HW assignment that do not follow the formatting instructions will not be graded.
1. Grandma’s Little Bakery is trying to figure out how much grain to order for the next
month to mix into its most favorite flourless multi-grain bread. The grains that can be
bought from local suppliers have the following composition and price:
Grain
1
2
3
% of Nutrient in the Grain
Starch
Fiber
Protein Gluten
30
40
20
10
20
65
15
0
40
35
5
20
Price
(Cents/Kg.)
70
40
60
In order for the dough to bake smoothly, the amount of starch in the mixture should be
kept within 20 to 30 percent. Because of taste considerations, the amount of grain #2
in the mix cannot exceed 20%. Finally, for proper nutritional balance, the mixture
should have at least 15% protein, at least 40% fiber, and no more than 12% gluten.
To satisfy the demand for next month, the bakery needs to produce exactly 150
kilograms of multi-grain dough mix. Help Mama’s Little Bakery find the least costly
mix of grains that will satisfy demand.
a. Formulate the problem as a linear program (i.e., define the variables, and write
down the objective function and all constraints in algebraic form).
b. Create a spreadsheet model for this problem and solve it with Solver.
c. What is the optimal solution? What is the optimal value?
2. Maine Air is a local charter airline that operates in the northeast. The company offers
the following three non-stop flights that fly out of Bangor, ME:
Network Segments
Bangor, ME → Burlington, VT
Bangor, ME → Boston, MA
Bangor, ME → New York City, NY
Daily Capacity
300
550
340
Due to high demand for travel to Los Angeles, the company has decided to partner with
American Airlines and use a portion of their excess capacity to route passengers from
Bangor to LA via connecting flights. American Airlines is willing to offer Maine Air a
certain daily capacity on the following flights:
Network Segments
Burlington, VT → New York City, NY
Boston, MA → Chicago, IL
New York City, NY → Boston, MA
New York City, NY → Chicago, IL
New York City, NY → Los Angeles, LA
Chicago, IL → Los Angeles, LA
Daily Capacity
200
470
50
200
250
800
American Airlines and Maine Air need to jointly decide how to route passengers
through their network to maximize the number of passengers that can be routed from
Bangor to Los Angeles.
a. Draw a network flow model for this problem.
b. Formulate the problem as a linear program (i.e., define the variables, and write
down the objective function and all constraints in algebraic form).
c. Create a spreadsheet model for this problem and solve it with Solver.
d. What is the optimal solution? What is the optimal value?
3. Vitro Company manufactures, assembles, and rebuilds material handling equipment
used in warehouses and distribution centers. One product, called a Liftmaster, is
assembled from four components: a frame, a motor, two supports, and a metal strap.
Vitro’s production schedule calls for at least 5000 Liftmasters to be made next month.
Vitro purchases the motors from an outside supplier, but the frames, supports, and
straps may be either manufactured by the company or purchased from an outside
supplier. Manufacturing and purchase costs per unit are as follows:
Component
Frame
Support
Strap
Manufacturing Cost
$38.00
$11.50
$6.50
Purchase Cost
$51.00
$15.00
$7.50
Three departments are involved in the production of these components. The time (in
minutes per unit) required to process each component in each department is as
follows:
Component
Frame
Support
Strap
Cutting
3.5
1.3
0.8
Milling
2.2
1.7
—
Shaping
3.1
2.6
1.7
The available capacity (in hours) for the three departments is as follows:
Capacity (hours)
Cutting
350
Milling
420
Shaping
680
a. Formulate the problem as a linear program (i.e., define the variables, and write
down the objective function and all constraints in algebraic form).
b. Create a spreadsheet model for this problem and solve it with Solver. Generate and
provide a copy of the Sensitivity Analysis report with the solution.
c. What is the optimal solution? What is the optimal value?
d. What are the binding constraints?
e. How much should Vitro be willing to pay for an additional hour of time in the
shaping department?
f. Another manufacturer has offered to sell frames to Vitro for $45 each. Could Vitro
improve its position by pursuing this opportunity? Why or why not?
Grain
1
2
3
Starch
30%
20%
40%
Nutrition in the Grain
Fiber
Protein
40%
20%
65%
15%
35%
5%
Gluten
10%
0%
20%
Price
(cent/kg)
70
40
60
Flight Leg
Bang -> Burlington
Bang -> Boston
Bang -> NYC
Burlington -> NYC
Boston -> Chicago
NYC -> Boston
NYC -> Chicago
NYC -> LA
Chicago -> LA
Capacity
300
550
340
200
470
50
200
250
800
Grain
1
2
3
Starch
30%
20%
40%
Nutrition in the Grain
Fiber
Protein
40%
20%
65%
15%
35%
5%
Gluten
10%
0%
20%
Price
(cent/kg)
70
40
60
Flight Leg
Bang -> Burlington
Bang -> Boston
Bang -> NYC
Burlington -> NYC
Boston -> Chicago
NYC -> Boston
NYC -> Chicago
NYC -> LA
Chicago -> LA
Capacity
300
550
340
200
470
50
200
250
800
Glickman Electronics Example
The Glickman Electronics Company in Washington, DC, produces two products: (1) the
Glickman x-pod, a portable music player, and (2) the Glickman BlueBerry, an internet-connected
color telephone. The production process for each product is similar in that both require a certain
number of hours of electronic work and a certain number of labor-hours in the assembly
department. Each x-pod takes 4 hours of electronic work and 2 hours in the assembly shop. Each
BlueBerry requires 3 hors in electronics and 1 hour in assembly. During the current production
period, 240 hours of electronic time are available, and 100 hours of assembly department time are
available. Each x-pod sold yields a profit of $7; each BlueBerry produced may be sold for a $5
profit.
Glickman’s problem is to determine the best possible combination of x-pod and BlueBerrys
to manufacture to reach the maximum profit.
Solution:
a. Formulate the problem as a linear programming model (that is, define the variables, and
write down the objective function and all constraints mathematically).
We begin by summarizing the information needed to formulate and solve this problem. Further,
let’s introduce some simple notation for use in the objective function and constraints.
Decision Variables
X1 = number of x-pods to be produced
X2 = number of BlueBerry to be produced
Objective Function and constraints
Max ($7X1 + $5X2)
Subject to:
4X1 + 3X2 ≤ 240
2X1 + 1X2 ≤ 100
X1 , X2 ≥ 0
The objective function created this way represents the overall profit from the operation. Since
each x-pod yields a profit of $7, X1 of them yield 7X1 dollars. Similarly, each BlueBerry yields a
profit of $5, which means X2 BlueBerrys yield 5X2 dollars. The overall profit will therefore be =
$7X1 + $5X2, which is the objective function we are trying to maximize.
As for the constraints, one general relationship is that the amount of a resource used is to be less
than or equal to the amount of resource available. Both these constraints represent production
capacity restrictions and, of course, affect the total profit.
First constraint:
Electronic time used is ≤ Electronic time available.
Second constraint:
Assembly time used is ≤ Assembly time available.
For example, Glickman Electronics cannot produce 70 x-pods during the production period
because if X1 = 70, both constraints will be violated. It also cannot make X1 = 50 x-pods and Xx
= 10 BlueBerrys.
What is more, since the decision variables represent units of production, we should add a nonnegativity constraint for each variable.
b. Create a spreadsheet model for this problem and solve with Excel Solver.
Figure 1 – Solver parameters
A
1
2
3
4
5
Production
Electronic Constraint
Assembly Constraint
Objective Function
(Maximization)
B
X-PODS
(X1)
30
4
2
C
BLUEBERRYS
(X2)
40
3
1
7
5
D
E
F
240 <= 240 100 <= 100 410 A 1 2 Production B C X-PODS BLUEBERRYS (X1) (X2) 30 D E F 40 3 Electronic Constraint 4 3 =SUMPRODUCT(B2:C2,B3:C3) <= 240 4 Assembly Constraint 2 Objective Function (Maximization) 7 1 =SUMPRODUCT(B2:C2,B4:C4) <= 100 5 =SUMPRODUCT(B2:C2,B5:C5) 5 c. What is the optimal solution? What is the optimal value? The Optimal Solution: X1 = 30 X2 = 40 The optimal value: Maximum profit = $410 ... Purchase answer to see full attachment

How it works

1. Paste your instructions in the instructions box. You can also attach an instructions file
2. Select the writer category, deadline, education level and review the instructions 
3. Make a payment for the order to be assignment to a writer
4.  Download the paper after the writer uploads it 

Will the writer plagiarize my essay?

You will get a plagiarism-free paper and you can get an originality report upon request.

Is this service safe?

All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades