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. We do need at least 90% of oranges to be used at each Grove in the distribution plan.

There are five different warehouse sites that will be used. Each site has a capacity roll-through limit (in crates). 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.

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 using EXCEL SOLVER - 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.

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