Yancey Basins Inc. manufactures kitchen sink and faucet combo units. The company has provided forecasts of...

  
 

Transcribed Image Text:

Yancey Basins Inc. manufactures kitchen sink and faucet combo units. The company has provided forecasts of its demand for each of 4 regional markets (East, South, Midwest, West) in the attached Excel file. Managers at Yancey Basins have selected four potential sites - Pittsburgh, Charlotte, Denver, and Los Angeles. Facilities can have a capacity of 200,000 or 400,000 units. The annual fixed costs at the four locations along with the per unit production and transportation costs associated with shipping an item from each potential site to each potential market are shown in the tables provided in the attached Excel file. Assume that Yancey Basins will open at most 1 facility of either type at each potential site.. Yancey Basins 2 student.xlsx Use Excel Solver to develop a linear programming to determine where and which type of production facilities Yancey Basins should build and how many units Yancey Basins should ship from each facility to each regional market in order to minimize the total facility, production, and transportation cost. Facilities plan Once you have solved for the optimal solution, enter the following values from the optimal solution. Enter the optimal total cost (facility + production + transportation) rounded to the nearest dollar: $ Enter the total number of units shipped from Charlotte to the Midwest Enter the total number of units shipped from Denver to the West Enter the total number of units leaving Denver Enter the total number of small facilities Enter the total number of large facilities. Check C22 1 Potential Site Pittsburgh Charlo Denver Los Angeles Potential Ste Patsburgh Charlotte Denver Los Angeles Total Demand Objective Function Total Production & Transportation Cost Total Facilities Cost Total Cost Facilities Production Transportation) С Inputs-Costs, Capacities, Demands Demand Region Production and Transportation Cost perunt East South Midwest 121 West 131 102 105 107 106 110 152 118 113 105 110 158 138 135 112 Demand Region Production Abocation East South Midwest West DE 2 M X 120,000 190,000 130,000 140.000 Total G H Low Capacity Fixed Cost Fixed Cost Capacity $2,200,000 200,000 $4,000,000 $2,400,000 200,000 $4,100,000 $2,100,000 200,000 $1,900,000 $3,000,000 200,000 $3,900,000 Capacity co a CE C High Capacity Capacity 400,000 400,000 400,000 400,000 Open Small Plant? (open) Open Large Plant? (1-Yes) Total Plants Opened Co C C Total Allowed Yancey Basins Inc. manufactures kitchen sink and faucet combo units. The company has provided forecasts of its demand for each of 4 regional markets (East, South, Midwest, West) in the attached Excel file. Managers at Yancey Basins have selected four potential sites - Pittsburgh, Charlotte, Denver, and Los Angeles. Facilities can have a capacity of 200,000 or 400,000 units. The annual fixed costs at the four locations along with the per unit production and transportation costs associated with shipping an item from each potential site to each potential market are shown in the tables provided in the attached Excel file. Assume that Yancey Basins will open at most 1 facility of either type at each potential site.. Yancey Basins 2 student.xlsx Use Excel Solver to develop a linear programming to determine where and which type of production facilities Yancey Basins should build and how many units Yancey Basins should ship from each facility to each regional market in order to minimize the total facility, production, and transportation cost. Facilities plan Once you have solved for the optimal solution, enter the following values from the optimal solution. Enter the optimal total cost (facility + production + transportation) rounded to the nearest dollar: $ Enter the total number of units shipped from Charlotte to the Midwest Enter the total number of units shipped from Denver to the West Enter the total number of units leaving Denver Enter the total number of small facilities Enter the total number of large facilities. Check C22 1 Potential Site Pittsburgh Charlo Denver Los Angeles Potential Ste Patsburgh Charlotte Denver Los Angeles Total Demand Objective Function Total Production & Transportation Cost Total Facilities Cost Total Cost Facilities Production Transportation) С Inputs-Costs, Capacities, Demands Demand Region Production and Transportation Cost perunt East South Midwest 121 West 131 102 105 107 106 110 152 118 113 105 110 158 138 135 112 Demand Region Production Abocation East South Midwest West DE 2 M X 120,000 190,000 130,000 140.000 Total G H Low Capacity Fixed Cost Fixed Cost Capacity $2,200,000 200,000 $4,000,000 $2,400,000 200,000 $4,100,000 $2,100,000 200,000 $1,900,000 $3,000,000 200,000 $3,900,000 Capacity co a CE C High Capacity Capacity 400,000 400,000 400,000 400,000 Open Small Plant? (open) Open Large Plant? (1-Yes) Total Plants Opened Co C C Total Allowed

Answer rating: 100% (QA)

Demand Region Production transportation cost per unit Low Capacity H... View the full answer