Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Conditional Format Formatting Table Cal Style Merge Center $ % ) x fe A B C DE F G Digital Solutions manufactures routers in plants

image text in transcribed
image text in transcribed
Conditional Format Formatting Table Cal Style Merge Center $ % ) x fe A B C DE F G Digital Solutions manufactures routers in plants located in Seattle, Columbus and Albany for distribution through 4 warehouses. LM Unit shipping costs, limit on supply and demand for each of the warehouses are provided below. W1 Plant Seattle Columbus Albany Demand Supply 20 2150 32 2550 193550 1250 3000 2000 How many routers should be shipped from each of the 3 plants to the three warehouses to meet the demand and minimize the overall transportation cost? Develop an Excel model of this transportation problem, use Solver to find minimum cost solution and generate Answer and Sensitivity Reports. On the sheet with your Solver solution use the information provided on the Sensitivity Report to answer the following questions: 1 How much will the current minimum cost change if additional 4 routers could be shipped out of the Seattle plant? Be specific - what information from the Sensitivity report did you use to recalculate the minimum cost? 2 Not a single router is currently shipped from Columbus to the W4 warehouse. How much would the unit shipping cost from Columbus to the W4 warehouse need to change prior to shipping through this route? Be specific - explain what information from the Sensitivity Report allowed you to answer this question. 3 Is there an alternative optimal solution to this problem (different shipping schedule with the same minimum transportation cost)? On a separte sheet named ALTERNATE provide answer to this question (refer to section 12.7 on the textbook on how to generate an alternative optimal solution if one exists). Prob description + 20,402 , - - - Formatting Table A B C D E F G H I J K Digital Solutions manufactures routers in plants located in Seattle, Columbus and Albany for distribution through 4 warehouses. L M Unit shipping costs, limit on supply and demand for each of the warehouses are provided below. W1 Plant Seattle Columbus Albany Demand W2 14 35 20 1750 W3 26 30 22 3000 W4 17 27 29 2000 Supply 20 2150 32 2550 19 3550 1250 How many routers should be shipped from each of the 3 plants to the three warehouses to meet the demand and minimize the overall transportation cost? Develop an Excel model of this transportation problem, use Solver to find minimum cost solution and generate Answer and Sensitivity Reports. On the sheet with your Solver solution use the information provided on the Sensitivity Report to answer the following questions: 1 How much will the current minimum cost change if additional 4 routers could be shipped out of the Seattle plant? Be specific - what information from the Sensitivity report did you use to recalculate the minimum cost? 2 Not a single router is currently shipped from Columbus to the W4 warehouse How much would the unit shipping cost from Columbus to the W4 warehouse need to change prior to shipping through this route? Be specific - explain what information from the Sensitivity Report allowed you to answer this question. 3 Is there an alternative optimal solution to this problem (different shipping schedule with the same minimum transportation cost)? On a separte sheet named ALTERNATE provide answer to this question (refer to section 12.7 on the textbook on how to generate an alternative optimal solution if one exists). Prob description Conditional Format Formatting Table Cal Style Merge Center $ % ) x fe A B C DE F G Digital Solutions manufactures routers in plants located in Seattle, Columbus and Albany for distribution through 4 warehouses. LM Unit shipping costs, limit on supply and demand for each of the warehouses are provided below. W1 Plant Seattle Columbus Albany Demand Supply 20 2150 32 2550 193550 1250 3000 2000 How many routers should be shipped from each of the 3 plants to the three warehouses to meet the demand and minimize the overall transportation cost? Develop an Excel model of this transportation problem, use Solver to find minimum cost solution and generate Answer and Sensitivity Reports. On the sheet with your Solver solution use the information provided on the Sensitivity Report to answer the following questions: 1 How much will the current minimum cost change if additional 4 routers could be shipped out of the Seattle plant? Be specific - what information from the Sensitivity report did you use to recalculate the minimum cost? 2 Not a single router is currently shipped from Columbus to the W4 warehouse. How much would the unit shipping cost from Columbus to the W4 warehouse need to change prior to shipping through this route? Be specific - explain what information from the Sensitivity Report allowed you to answer this question. 3 Is there an alternative optimal solution to this problem (different shipping schedule with the same minimum transportation cost)? On a separte sheet named ALTERNATE provide answer to this question (refer to section 12.7 on the textbook on how to generate an alternative optimal solution if one exists). Prob description + 20,402 , - - - Formatting Table A B C D E F G H I J K Digital Solutions manufactures routers in plants located in Seattle, Columbus and Albany for distribution through 4 warehouses. L M Unit shipping costs, limit on supply and demand for each of the warehouses are provided below. W1 Plant Seattle Columbus Albany Demand W2 14 35 20 1750 W3 26 30 22 3000 W4 17 27 29 2000 Supply 20 2150 32 2550 19 3550 1250 How many routers should be shipped from each of the 3 plants to the three warehouses to meet the demand and minimize the overall transportation cost? Develop an Excel model of this transportation problem, use Solver to find minimum cost solution and generate Answer and Sensitivity Reports. On the sheet with your Solver solution use the information provided on the Sensitivity Report to answer the following questions: 1 How much will the current minimum cost change if additional 4 routers could be shipped out of the Seattle plant? Be specific - what information from the Sensitivity report did you use to recalculate the minimum cost? 2 Not a single router is currently shipped from Columbus to the W4 warehouse How much would the unit shipping cost from Columbus to the W4 warehouse need to change prior to shipping through this route? Be specific - explain what information from the Sensitivity Report allowed you to answer this question. 3 Is there an alternative optimal solution to this problem (different shipping schedule with the same minimum transportation cost)? On a separte sheet named ALTERNATE provide answer to this question (refer to section 12.7 on the textbook on how to generate an alternative optimal solution if one exists). Prob description

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

QS 9000 Handbook A Guide To Registration And Audit

Authors: Jayanta Bandyopadhyay

1st Edition

157444011X, 978-1574440119

More Books

Students also viewed these Accounting questions

Question

What has been your desire for leadership in CVS Health?

Answered: 1 week ago

Question

Question 5) Let n = N and Y Answered: 1 week ago

Answered: 1 week ago