Using Excel Simplex Solver

(This problem is based on an old Business Week Coca-Cola problem setting talking about the millions of dollars of savings the optimization program implemented provided) . Consider six orange groves that grow Valencia Oranges. Based on accurate and detailed historical data, we have a fairly accurate idea of how many crates of oranges we can expect to harvest each month at each grove, along with orange quality (on a 5 point scale). We are going to design our supply chain using this data. Grove A should produce 100 crates of Valencia, with quality of 5.0. Grove B should produce 75 crates of Valencia with quality of 4.6. Grove C should produce 66 crates of Valencia with quality of 4.4. Grove D should produce 91 crates of Valencia with quality of 4.3. Grove E should produce 56 crates of Valencia with quality of 4.75. Grove F should produce 82 crates of Valencia with quality of 4.7. These groves serve three OJ production facilities in St. Augustine, Deltona and Ocala. Note that in our planned design, the groves will NOT ship crates directly to the production facilities (see more info below). Based on the average months anticipated juice production schedule, the following amount of oranges are needed at each location: St. Augustine: 140 crates, Deltona: 180 crates, Ocala: 125 crates. In order to deal with periodic demand and supply fluctuation, oranges will be shipped to warehouses (Site 1 through Site 5) before being shipped to the OJ Production facilities (the supply chain literature talks about how to manage variance in products by having supply and demand buffers to reduce stock-out risk). WE WILL DESIGN THE SUPPLY CHAIN TO EXACTLY MEET ORANGE REQUIREMENTS AT THE PRODUCTION FACILITIES, WITH NO INVENTORY AT THE WAREHOUSES. (Oranges not accounted for will be left at the groves for selling locally like apple orchards in Nebraska! Thats not something for your model other than how you model supply/demand). We do need at least 90% of oranges to be used at each Grove in the distribution plan. That should be part of your model. There are five different warehouse sites that will be used. Each site has a capacity roll-through limit (in crates) (the final products are round, right?). That limit represents the total number of crates that flow through the site. Also note that at least 60% of each sites capacity must be used in the distribution of oranges.

Also note that the quality of arriving oranges at each warehouse must average at least 4.55 for the Valencia oranges. Also, due to truck load limitations, at most 70 TOTAL crates of oranges can be shipped between any one grove and any one warehouse, AND from any one warehouse to any one production facility. (Truck size limitations). Below are the costs per crate between the groves and the warehouse locations, and the costs per crate between the warehouse sites and the production facilities. Also shown warehouse capacity. Create an appropriate LP model - its objective should minimize the sum of the shipping costs of the crates in meeting the demand requirements (and other requirements) of orange juice production throughout the entire supply chain. Obviously, oranges should not magically appear or disappear in the supply chain. Note that an integer number of crates must be delivered. As usual, Id suggest waiting until the end to add the requirement to make sure integer crates are delivered. Summarize your solution (in words or a table) that illustrates this distribution plan for the next month. It doesnt have to be lengthy, but it does need to explain the solution (i.e., where do the oranges go?).

Variable Cost A B c D E F Site 1 1.31 0.87 1.76 2.13 3.11 2.34 Site 2 1.75 2.3 1.12 2.41 2.12 1.66 Site 3 1.53 0.93 2.01 0.77 1.37 2.45 Site 4 1.99 1.65 1.45 1.35 2.11 3.42 Site 5 2.13 1.66 1.55 1.74 3.26 2.33 Variable cost: St. A Deltona Ocala Site 1 2.31 0.99 2.05 Site 2 2.51 1.34 1.77 Site 3 1.75 1.13 1.93 Site 4 2.66 1.67 2.23 Site 5 2.32 1.52 1.88 Capacity 80 90 120 110 100 Variable Cost A B c D E F Site 1 1.31 0.87 1.76 2.13 3.11 2.34 Site 2 1.75 2.3 1.12 2.41 2.12 1.66 Site 3 1.53 0.93 2.01 0.77 1.37 2.45 Site 4 1.99 1.65 1.45 1.35 2.11 3.42 Site 5 2.13 1.66 1.55 1.74 3.26 2.33 Variable cost: St. A Deltona Ocala Site 1 2.31 0.99 2.05 Site 2 2.51 1.34 1.77 Site 3 1.75 1.13 1.93 Site 4 2.66 1.67 2.23 Site 5 2.32 1.52 1.88 Capacity 80 90 120 110 100