Solve using Excel Solver and set the Solving Method to Simplex LP

The Rock Island Railroad (RIRA) has 100 empty railcars at its Sioux City rail yards and 150 rail cars at its North Platte location available for retrofitting for a new project. Ultimately, RIRA must get 75 retro- fitted cars to Chicago, 85 retro-fitted cars to Peoria, and 55 retro-fitted cars to St Louis. To retrofit the cars, two different stages of maintenance must be performed on the rail cars. Stage 1 retrofitting occurs either at Lincoln, Omaha, or Des Moines. Stage 2 retrofitting occurs at Davenport, IA, or Kansas City. Cars must pass first through Stage 1, then Stage 2, before arriving at the final destination. We are not interested in retrofitting costs at the 5 different maintenance locations. Our goal is to find the least distance way to transform the railcars and get them in their retrofitted format) to Chicago, Peoria, and St. Louis. Stage 1 and Stage 2 locations have capacities for retrofitting. They are listed below. Capacity 115 90 Stage 1 1 Location Lincoln Omaha Des Moines Davenport, IA Kansas City 50 95 1 2 135 2 Relevant distances are shown below. Using an appropriate linear programming model, find the optimal way of satisfying the rail car transformation problem for RIRA. Explicitly explain why your solution says in terms of cars transportation. Des Chicago Peoria St Louis Lincoln 188 214 Omaha 162 248 Sioux City N Platte Davenport K City Moines 209 372 156 177 203 320 163 281 167 152 529 76 285 234