1) What did the original solver solution show as the minimum cost per month?
2) What will be the minimum cost per month if Indianapolis is shut down?
3) What will be the minimum cost per month if all the warehousing is moved to Louisville?
4) What other factors should enter the decision process, and what information do they need?
5) Should they experiment with other warehousing options?
Dashiell read in the local newspaper that the interstate near their Indianapolis warehouse facility is adding an extra lane in a two-year, ongoing construction project. He walked out into the hangar to find Roger. He and Roger would work up a new routing structure to continue minimizing the cost per ton.
"Now we know why the delivery trucks are running late all of the time," said Roger, "and time is money." He was literally correct, with overtime, fuel costs, and maintenance adding to the cost per mile in and around Indianapolis.
The three warehouses—in Indianapolis, Louisville, and Cincinnati—supported three repair depots at Midway in Chicago, Wright-Patterson in Dayton, and Lambert Field in St Louis. The cost per mile was at the industry norm of $1.40 per mile, and a typical truck load of aircraft parts weighed in at 3 tons. Roger pulled up his notes from past optimization work and placed the following mileage chart on the table:
Mileage Chicago Dayton St. Louis
Indy 184 85 239
Louisville 296 140 265
Cincinnati 300 40 355
"So, how much are we shipping?" Dashiell asked as he and Roger looked over the records. Roger observed that the average for the first half year showed 200 tons per month moving among the locations, as follows:
St. Louis 125
Dashiell remembered how Microsoft Excel Solver had made the shipping plan easy in the past. Roger pulled up the spreadsheet and reviewed it with Roger. "Not too bad," Roger said, checking the overall cost. "But what would happen if we shut down Indianapolis?" It had always been a sacred cow, and Dashiell looked surprised, and then pensive. "Maybe Cincinnati, too. Let's do the math." Together they ran the numbers and considered their options.
1) If we solve using the same existing supply and demand levels, Solver shows the minimum cost per month is $79,265, which is due to a total of 56,875 miles of shipping. However, if we constrain demand only and allow supply to be sourced from any of the three available warehouses, then the minimum cost per month goes down to $59,129 from 42,235 miles. This solution sources all Chicago repairs from Indy, all Dayton repairs from ...
This problem goes over a linear programming question on how to minimize costs for a company. The Excel spreadsheet is included along with explanations.