Supply Chain Management Problem

Fertilizer company trying to optimize (i.e. minimize while meeting demand) its costs. Delivery from 2 warehouses to 8 retail locations. Looking at costing out how to manage the "Projected Annual Throughput" per Table 1 from the warehouses to the retail outlets.

I simply need help setting up the Solver problem table to come to a reasonable calculation of the cost.

Key data (other than the tables)

The Kamloops warehouse has a storage capacity of 1,400,000 kg

The Abbotsford warehouse has a storage capacity of 1,600,000 kg

For the secondary distribution from Kamloops and Abbotsford to the retail locations, a third-party logistics provider (3PL) is hired. The 3PL charges up to the destination point at a freight rate of $20 per kilometre, and an average truck load is 15,000 kg of fertilizers. Often the choice of the route is left to the driver. Steve thought that by identifying reasonable and acceptable routes (see Table 2) between two locations and by negotiating the future transportation freight costs based on these fixed distances, there might be a chance to control these costs. Based on the current turnover ratio of 10 maintained in the warehouses, an average truck load fills up to average 15,000 kg before it needs to be shipped to the retailer.

Recommendation Problem to Cost Out

The eight retail stores are fairly spacious and have their own storage at the back. Steve may propose to the vice president to allow him to increase his turnover ratio (the current ratio for each warehouse is 10)?a policy variable in the two warehouses. The turnover ratio can be changed for either one or both warehouses depending on the need. This will allow Steve to turn over the product to retailers faster and release the space in warehouses. However, such a faster turnover will reduce the truckload size and increase the number of trips. The resultant increase in transportation costs will be somewhat proportional to the increase in turnover ratio. What would be the optimized cost for this?

Table 1: Distribution Costs and Throughputs Retailer Serving Distribution Recent Annual Recent Secondary Projected Annual Centre Throughput (kg) Transport Costs Throughput (kg) Kelowna Kamloops 5,000.000.00 $1, 180,000.00 6.000.000.00 Salmon Arm Kamloops 2,500.000.00 $367,450.00 3.250,000.00 Merritt Kamloops 2000,000.00 $241,300.00 2,500,000.00 Williams Lake Kamloops 1,000,000.00 $388,750.00 1,500,000.00 Langley Abbotsford 6,200,000.00 $295,500.00 7,500,000.00 Richmond Abbotsford 5,000 000.00 $541,000.00 5 500,000.00 Mission Abbotsford 3,500,000.00 $70,250.00 4,000.000.00 Hope Abbotsford 250,000.00 $29,000.00 3,000,000.00Table 2: Acceptable Distances and Coordinates Acceptable Distances (km) Coordinates Retail Locations in BC Kamloops Abbotsford X-coordinate Y-coordinate Kelowna 170 325 128.00 80.00 Salmon Arm 110 395 130.00 128.00 Merritt 90 205 98.00 95.00 Williams Lake 290 480 72.00 190.00 Langley 320 35 70.00 40.00 Richmond 370 80 65.00 50.00 Mission 290 15 75.00 43.00 Hope 205 85 90.00 60.00 Kamloops 0 290 105.00 125.00 Abbotsford 290 0 75.00 41.00