In this assignment, you will use the Excel data analysis tool known as Solver. Read the...

  

Transcribed Image Text:

In this assignment, you will use the Excel data analysis tool known as Solver. Read the example in Chapter 6 on The Foster Generator Transportation Problem. The Excel setup for this problem can be located on page 312 in your text; however, there is no need to set up your own Excel file. To get started, download this Excel file that has all formulas included. You need only to plug in the different values shown below to solve the problem. Given the following: Assume that the cost per unit to ship is $9.00 for Origin of Cleveland to all four destinations of Boston, Chicago, St. Louis, and Lexington. 1. What is the minimal transportation cost if all other variables remain equal? 2. What is the minimal cost of transportation if the cost per unit to ship is $7.00 for all origins to all destinations? 3. What are the implications for this business if the total costs to all destinations increase? If the total costs decrease? 4. Provide a brief narrative on your transportation problem. Describe the importance of the use of Solver and this Excel file for business and industry. Insert this narrative as a text box in the Excel file. FIGURE 6.22 EXCEL SOLUTION OF THE FOSTER GENERATORS PROBLEM Foster.xlsx - Microsoft Excel Home Insert Page Layout Formulas Data Review View Acrobat Clear Data Analysis Get External Refresh Data Sert Text to Remove Outline Advanced All- Columns Duplicates Connections Sort & Filter Data Tools A25 1 Foster Generators 2 3 Destination 4 Origin to Boston to Chicago to St. Louis to Lexington Supply 5 Cleveland 3 2 7 5000 6 Bedford 7 5 2 3 6000 7 York 5 4 5 2500 8 Demand 6000 4000 2000 1500 9 10 11 Model 12 Minimize Total Cost 13 39500 14 15 Destination 16 Origin to Boston to Chicago to St. Louis to Lexington Total 17 Cleveland 3500 1500 5000 18 Bedford 0 2500 2000 1500 6000 19 York 2500 0 0 2500 20 Total 6000 4000 2000 1500 21 22 6000 4000 2000 1500 23 14 Answer Report Solution Ready 100% 000 CE 2 Solver Analysis H 5000 6000 2500 >14 In this assignment, you will use the Excel data analysis tool known as Solver. Read the example in Chapter 6 on The Foster Generator Transportation Problem. The Excel setup for this problem can be located on page 312 in your text; however, there is no need to set up your own Excel file. To get started, download this Excel file that has all formulas included. You need only to plug in the different values shown below to solve the problem. Given the following: Assume that the cost per unit to ship is $9.00 for Origin of Cleveland to all four destinations of Boston, Chicago, St. Louis, and Lexington. 1. What is the minimal transportation cost if all other variables remain equal? 2. What is the minimal cost of transportation if the cost per unit to ship is $7.00 for all origins to all destinations? 3. What are the implications for this business if the total costs to all destinations increase? If the total costs decrease? 4. Provide a brief narrative on your transportation problem. Describe the importance of the use of Solver and this Excel file for business and industry. Insert this narrative as a text box in the Excel file. FIGURE 6.22 EXCEL SOLUTION OF THE FOSTER GENERATORS PROBLEM Foster.xlsx - Microsoft Excel Home Insert Page Layout Formulas Data Review View Acrobat Clear Data Analysis Get External Refresh Data Sert Text to Remove Outline Advanced All- Columns Duplicates Connections Sort & Filter Data Tools A25 1 Foster Generators 2 3 Destination 4 Origin to Boston to Chicago to St. Louis to Lexington Supply 5 Cleveland 3 2 7 5000 6 Bedford 7 5 2 3 6000 7 York 5 4 5 2500 8 Demand 6000 4000 2000 1500 9 10 11 Model 12 Minimize Total Cost 13 39500 14 15 Destination 16 Origin to Boston to Chicago to St. Louis to Lexington Total 17 Cleveland 3500 1500 5000 18 Bedford 0 2500 2000 1500 6000 19 York 2500 0 0 2500 20 Total 6000 4000 2000 1500 21 22 6000 4000 2000 1500 23 14 Answer Report Solution Ready 100% 000 CE 2 Solver Analysis H 5000 6000 2500 >14

Answer rating: 100% (QA)

The Solution is Decision Variables FromTo Boston Cleveland 3500 Bedford 0 Yor... View the full answer