network modeling

he purpose of this assignment is to introduce how network models can

be used to solve a business problem.

Using specified data files, chapter example files, and templatesfrom the “Topic 6 Student Data, Template, and ExampleFiles” topic material, complete Chapter 14, Problems 15, 16, 17,20, and 88. Use Microsoft Excel’s Solver Add-In to completethese problems. For problems 17, 20, and 88, run the Solver’sAnswer and Sensitivity Reports. Interpret and summarize the key results.

To receive full credit on the assignment, complete the following.

Ensure that all Solver settings are defined through the use ofthe Solver dialog box.Ensure that Excel files include theassociated cell functions and/or formulas if functions and/orformulas are used.Include a written response to allnarrative questions presented in the problem by placing it in theassociated Excel file.Include Answer and SensitivityReports interpretation and summary of key results.Placeeach problem in its own Excel file. Ensure that your first and lastname are in your Excel file names.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior tobeginning the assignment to become familiar with the expectations forsuccessful completion. Question 1- Most transshipment network modeling problems assume the costs areconstant. For example, the costs of shipping a product from one city toanother are assumed fixed. This can change over time if fuel costschange. If you knew the distribution of fuel costs, how could thedistribution of fuel costs be incorporated into the transshipmentproblem? Discuss the benefits of employing this approach. Question 2- Minimumspanning trees were initially design to solve electrical grid problemsbut now have many more applications such as computer networks,transportation networks, and supply networks. Describe a businessproblem where minimum spanning trees can be used to find a solution. Unit shipping costs (per 1000 barrels)
To
Well 1
Well 2
From
Well 1
Well 2
Mobile
Galveston
NY
LA
Mobile Galveston
$10
$13
$15
$12
$6
$6
NY
$25
$26
$16
$14
$15
LA
$28
$25
$17
$16
$15
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
B
C
D
E
F
G
H
I
J
K
L
M
DE
1398
1949
1008
0
1019
1059
1273
1771
1411
504
1235
1307
HO
789
1804
1067
1019
0
1538
356
1608
1313
1438
1912
2274
LA
2182
2979
2054
1059
1538
0
1883
2786
2426
715
379
1131
NO
479
1507
912
1273
356
1883
0
1311
1070
1738
2249
2574
NY
841
222
802
1771
1608
2786
1311
0
368
2182
2934
2815
PI
687
574
452
1411
1313
2426
1070
368
0
1826
2578
2465
SL
1878
2343
1390
504
1438
715
1738
2182
1826
0
752
836
SF
2496
3095
2142
1235
1912
379
2249
2934
2578
752
0
808
SE
2618
2976
2013
1307
2274
1131
2574
2815
2465
836
808
0
NO
NY
PI
SL
SF
SE
NO
NY
PI
SL
SF
SE
Western Airlines hub location model with distances
Input data
Mile limit
1000
Distance from each city to each other city
AT
BO
AT
0 1037
BO
1037
0
CH
674 1005
DE
1398 1949
HO
789 1804
LA
2182 2979
NO
479 1507
NY
841 222
PI
687 574
SL
1878 2343
SF
2496 3095
SE
2618 2976
CH
674
1005
0
1008
1067
2054
912
802
452
1390
2142
2013
Which cities are covered by which potential hubs with this mile limit
Potential hub
City
AT
BO
CH
DE
HO
LA
AT
BO
CH
DE
HO
LA
NO
NY
PI
SL
SF
SE
Decisions: which cities to use as hubs
AT
BO
Used as hub?
CH
DE
HO
Constraints that each city must be covered by at least one hub
City
Covered by
Required
AT
BO
CH
DE
HO
LA
NO
NY
PI
SL
SF
SE
Objective to minimize
Total hubs
LA
c. Use Solverlable to determine how a change in the
year 3 return of investment B changes the optimal
solution to the problem.
85. An oil company produces two types of gasoline, G1
and G2, from two types of crude oil, C1 and C2. G1
is allowed to contain up to 4% impurities, and G2 is
allowed to contain up to 3% impurities. G1 sells for
$48 per barrel, whereas G2 sells for $72 per barrel.
Up to 4200 barrels of G1 and up to 4300 barrels of G2
can be sold. The cost per barrel of each crude, their
availability, and the level of impurities in each crude
are listed in the file P14_85.xlsx. Before blending the
Houston and Tampa are allowed at a cost OI D per car.
88. An oil company produces oil from two wells. Well 1
can produce up to 150,000 barrels per day, and well
2 can produce up to 200,000 barrels per day. It is
possible to ship oil directly from the wells to the
company’s customers in Los Angeles and New York.
Alternatively, the company could transport oil to the
ports of Mobile and Galveston and then ship it by
tanker to New York or Los Angeles, respectively. Los
Angeles requires 160,000 barrels per day, and New
York requires 140,000 barrels per day. The costs
of shipping 1000 barrels between various locations
752 Chapter 14 Optimization Models
9780357233344, Business Analytics: Data Analysis & Decision Making, Sixth Edition, S. Christian Albright – © Cengage Learning.
All Rights Reserved. No distribution allowed without express authorization. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-247. Distributed by Grand Canyon University.
are shown in the file P14_88.xlsx, where a blank
indicates shipments that are not allowed. Determine
how to minimize the transport costs in meeting the oil
demands of Los Angeles and New York.
89. Based on Bean et al. (1987). Boris Milkem’s firm
owns six assets. The expected selling price (in millions
of dollars) for each asset is given in the file P14_89
.xlsx. For example, if asset 1 is sold in year 2, the firm
The cost of shipping a ton of sauce from each
warehouse to each customer
The customer requirements (in tons) of sauce
The fixed annual cost of operating each plant and
warehouse.
The company must decide which plants and
warehouses to open, and which routes from plants to
warehouses and from warehouses to customers to use.
ti
Levo
22. I
t
I
shipped along this route.
15. In the RedBrand example, suppose the plants cannot
ship to each other and the customers cannot ship to
each other. Modify the model appropriately, and rerun
Solver. How much does the total cost increase because
of these disallowed routes?
16. Modify the RedBrand example so that all flows must
be from plants to warehouses and from warehouses to
customers. Disallow all other arcs. How much does
this restriction cost RedBrand, relative to the original
optimal shipping cost?
17. In the RedBrand example, the costs for
shipping from plants or warehouses to customer
2 were purposely made high so that it would
be optimal to ship to customer 1 and then let
customer 1 ship to customer 2. Use SolverTable
appropriately to do the following. Decrease the
unit shipping costs from plants and warehouses to
customer 1, all by the same amount, until it is no
longer optimal for customer 1 to ship to customer 2.
Describe what happens to the optimal shipping plan
at this point.
18. In the RedBrand example the arc capacity is the same
a
a
6
23. d
a
L
20. Suppose in the original Grand Prix example that the
routes from plant 2 to region 1 and from plant 3 to
region 3 are not allowed. (Perhaps there are no rail-
road lines for these routes.) How would you modify
the original model (Figure 14.14) to rule out these
routes? How would you modify the alternative model
(Figure 14.19) to do so? Discuss the pros and cons of
these two approaches.
21. The RedBrand model in the file RedBrand Logistics
Multiple Product Finished.xlsx assumes that the
unit shipping costs are the same for both prod-
ucts. Modify the model so that each product has