Using the Solver add-in in Microsoft Excel, the "Supply Chain Analytics Case Study Template," run a linear optimization analysis to determine the optimal number of shipments from each of the four distribution centers to the retail stores in order to minimize total weekly shipping cost. Utilize the following information when completing the "Supply Chain Analytic Case Study Template." There are four distribution center locations: Phoenix, Tucson, Flagstaff, and Safford. Note that these locations can only ship product. There are 10 retail stores: Quartzsite, Window Rock, Holbrook, Sedona, Kingman, Prescott, Snowflake, Alpine, Yuma, and Sierra Vista. Note that these locations can only receive product. Use the capacity, demand, and unit shipping cost information in the "Supply Chain Analytics Case Study Template" file. Ensure that the "Simplex LP" option is specified as the solving method in Solver. Locations (Arizona) Phoenix Tucson Flagstaff Safford Quartzsite Window Rock Holbrook Sedona Kingman Prescott Snowflake Alpine Yuma Sierra Vista Unit Shipping Costs (From/To) Phoenix Tucson Flagstaff Safford Weekly Demand Weekly Capacity 556 268 157 86 98 58 155 97 133 55 267 95 55 35 Quartzite Window Rock $2.80 $6.71 $4.61 $7.34 $5.61 $5.57 $6.97 $3.84 U Holbrook $2.70 $4.66 $2.06 $6.72 Sedona $5.69 $6.70 $2.56 $6.05 Kingman $8.11 $5.32 $4.05 $5.56 Prescott $4.80 $7.52 $5.50 $3.44 n Snowflake $4.52 $4.82 $4.76 $6.94 . Alpine $3.81 $4.73 56.89 $2.47 $6.32 $4.48 $3.65 $7.01 Sierra Vista $6.37 $3.16 $4.49 $4.49
Using the Solver add-in in Microsoft Excel, the "Supply Chain Analytics Case Study Template," run a linear optimization analysis to determine the optimal number of shipments from each of the four distribution centers to the retail stores in order to minimize total weekly shipping cost. Utilize the following information when completing the "Supply Chain Analytic Case Study Template." There are four distribution center locations: Phoenix, Tucson, Flagstaff, and Safford. Note that these locations can only ship product. There are 10 retail stores: Quartzsite, Window Rock, Holbrook, Sedona, Kingman, Prescott, Snowflake, Alpine, Yuma, and Sierra Vista. Note that these locations can only receive product. Use the capacity, demand, and unit shipping cost information in the "Supply Chain Analytics Case Study Template" file. Ensure that the "Simplex LP" option is specified as the solving method in Solver. Locations (Arizona) Phoenix Tucson Flagstaff Safford Quartzsite Window Rock Holbrook Sedona Kingman Prescott Snowflake Alpine Yuma Sierra Vista Unit Shipping Costs (From/To) Phoenix Tucson Flagstaff Safford Weekly Demand Weekly Capacity 556 268 157 86 98 58 155 97 133 55 267 95 55 35 Quartzite Window Rock $2.80 $6.71 $4.61 $7.34 $5.61 $5.57 $6.97 $3.84 U Holbrook $2.70 $4.66 $2.06 $6.72 Sedona $5.69 $6.70 $2.56 $6.05 Kingman $8.11 $5.32 $4.05 $5.56 Prescott $4.80 $7.52 $5.50 $3.44 n Snowflake $4.52 $4.82 $4.76 $6.94 . Alpine $3.81 $4.73 56.89 $2.47 $6.32 $4.48 $3.65 $7.01 Sierra Vista $6.37 $3.16 $4.49 $4.49
Practical Management Science
6th Edition
ISBN:9781337406659
Author:WINSTON, Wayne L.
Publisher:WINSTON, Wayne L.
Chapter2: Introduction To Spreadsheet Modeling
Section: Chapter Questions
Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...
Related questions
Question
100%
Transcribed Image Text:Using the Solver add-in in Microsoft Excel, the "Supply Chain Analytics Case Study Template," run a
linear optimization analysis to determine the optimal number of shipments from each of the four
distribution centers to the retail stores in order to minimize total weekly shipping cost. Utilize the
following information when completing the "Supply Chain Analytic Case Study Template."
There are four distribution center locations: Phoenix, Tucson, Flagstaff, and Safford. Note that these
locations can only ship product.
There are 10 retail stores: Quartzsite, Window Rock, Holbrook, Sedona, Kingman, Prescott, Snowflake,
Alpine, Yuma, and Sierra Vista. Note that these locations can only receive product.
Use the capacity, demand, and unit shipping cost information in the "Supply Chain Analytics Case
Study Template" file.
Ensure that the "Simplex LP" option is specified as the solving method in Solver.
Locations (Arizona)
Phoenix
Tucson
Flagstaff
Safford
Quartzsite
Window Rock
Holbrook
Sedona
Kingman
Prescott
Snowflake
Alpine
Yuma
Sierra Vista
A
Unit Shipping Costs (From/To)
Phoenix
Tucson
Flagstaff
Safford
Problem Data
"To" Stores
Quartzsite
Window Rock
Holbrook
Sedona
Kingman
Prescott
Snowflake
Alpine
Yuma
Sierra Vista
Weekly Demand Weekly Capacity
98
58
155
97
133
55
267
95
55
35
$6.71
$4.61
$7.34
Quartzsite Window Rock
$2.80
$5.61
$5.57
556
268
$6.97
$3.84
157
86
Holbrook
$2.70
Minimum Demand
$4.66
$2.06
$6.72
Maximum Supply.
E
Sedona
$5.69
$6.70
$2.56
$6.05
Phoenix
Kingman
$8.11
$5.32
$4.05
$5.56
Prescott
$4,80
$7.52
$5.50
$3.44
Snowflake
$4,52
$4.82
$4.76
$6.94
Tucson
Alpine
$3.81
"From" Distribution Centers
Flagstaff
Unit Shipping Costs
$4.73
$6.89
$2.47
3
Yuma
$6.32
$4.48
$3.65
$7.01
Sierra Vista
$6.37
$3.16
$4.49
$4,49
Safford
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 4 steps with 6 images
Recommended textbooks for you
Practical Management Science
Operations Management
ISBN:
9781337406659
Author:
WINSTON, Wayne L.
Publisher:
Cengage,
Operations Management
Operations Management
ISBN:
9781259667473
Author:
William J Stevenson
Publisher:
McGraw-Hill Education
Operations and Supply Chain Management (Mcgraw-hi…
Operations Management
ISBN:
9781259666100
Author:
F. Robert Jacobs, Richard B Chase
Publisher:
McGraw-Hill Education
Practical Management Science
Operations Management
ISBN:
9781337406659
Author:
WINSTON, Wayne L.
Publisher:
Cengage,
Operations Management
Operations Management
ISBN:
9781259667473
Author:
William J Stevenson
Publisher:
McGraw-Hill Education
Operations and Supply Chain Management (Mcgraw-hi…
Operations Management
ISBN:
9781259666100
Author:
F. Robert Jacobs, Richard B Chase
Publisher:
McGraw-Hill Education
Purchasing and Supply Chain Management
Operations Management
ISBN:
9781285869681
Author:
Robert M. Monczka, Robert B. Handfield, Larry C. Giunipero, James L. Patterson
Publisher:
Cengage Learning
Production and Operations Analysis, Seventh Editi…
Operations Management
ISBN:
9781478623069
Author:
Steven Nahmias, Tava Lennon Olsen
Publisher:
Waveland Press, Inc.