Using optimization tools such as solver in Excel allows you to conduct presciptive analytics. The following case is a version of the transportation problem.

A Transportation Problem Using optimization tools such as Solver in Excel allows you to conduct prescriptive analytics. The following case is a version of the transportation problem. Your company has a goal of delivering enough product to each of your retail outlets to exactly meet the predicted demand (Table 1) in the most cost-effective way possible. In order to ensure product availability, the purchasing manager has signed several contracts with various plants or distribution centers that need to be fulfilled and has obtained an agreement to be able to pull some additional product from each plant although each plant has a maximum that can be obtained (Table 2). You are tasked to minimize the cost while fulfilling the retail outlets demand need and pulling the minimum product ordered per plant while not exceeding the maximum available. Table 1 Retail Outlet Cost by Plant and Estimated Demand Estimated Demand AN Table 1 Retail Outlet Cost by Plant and Estimated Demand Retail Cost Cost Cost Outlet Plant 1 Plant 2 Plant 3 1 17 19 9 10 11 9 20 12 14 4 10 17 16 5 19 13 19 6 18 14 17 7 17 11 10 8 10 20 20 9 8 17 7 10 8 20 7 11 19 20 16 12 8 20 8 13 17 8 9 14 8 19 10 15 8 12 11 16 10 14 20 17 20 17 8 18 16 10 19 19 18 8 10 20 12 20 Cost Plant 4 8 18 11 7 7 8 16 13 7 14 10 8 18 11 10 16 12 10 13 10 Cost Plant 5 16 12 13 19 9 7 15 16 16 13 20 16 14 16 15 19 15 13 8 19 Cost Plant 6 12 20 12 7 15 12 10 17 10 16 14 7 19 9 19 7 11 11 17 8 210 150 200 170 210 180 190 180 160 170 210 190 300 120 180 140 120 160 190 120 11 Table 2 Minimum and Maximum Product by Plant Plant Min Product 1 2 3 4 400 500 300 350 650 200 2,400 Maximum Product 700 1,000 800 750 1,000 500 5 6 4,750