Homework Help is Here – Start Your Trial Now!
Business
Operations Management
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
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...
See similar textbooks
icon
Related questions
Question
100%
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
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
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 4 steps with 6 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Similar questions
Recommended textbooks for you
Practical Management Science
Practical Management Science
Operations Management
ISBN:
9781337406659
Author:
WINSTON, Wayne L.
Publisher:
Cengage,
Operations Management
Operations Management
Operations Management
ISBN:
9781259667473
Author:
William J Stevenson
Publisher:
McGraw-Hill Education
Operations and Supply Chain Management (Mcgraw-hi…
Operations and Supply Chain Management (Mcgraw-hi…
Operations Management
ISBN:
9781259666100
Author:
F. Robert Jacobs, Richard B Chase
Publisher:
McGraw-Hill Education
Business in Action
Business in Action
Operations Management
ISBN:
9780135198100
Author:
BOVEE
Publisher:
PEARSON CO
Purchasing and Supply Chain Management
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…
Production and Operations Analysis, Seventh Editi…
Operations Management
ISBN:
9781478623069
Author:
Steven Nahmias, Tava Lennon Olsen
Publisher:
Waveland Press, Inc.
SEE MORE TEXTBOOKS