Cannagood, Inc. is a recent start up in the production end of the burgeoning cannabis supply chain. The have 4 production facilities in three states and are looking to optimize how they procure their raw material from a selection of 17 farms from 4 states. Relevant data for this decision is shown below.

ID State Quality THC MAX Cost

W1 Wash 78 2 235 8

W2 Wash 82 2.3 150 8.5

W3 Wash 77 2.2 225 7.8

O4 Ore 91 2.6 300 9

O5 Ore 88 1.9 215 10

O6 Ore 68 1.3 125 7.7

O7 Ore 70 2.3 200 9.1

O8 Ore 80 2.1 180 8.2

The table above is the data about the potential farms that Cannagood has tentative agreements with to supply cannabis. Data includes: their state, the quality (100 point scale), their assumed THC level, the maximum they have available to purchase, and the per unit cost.  

State MAX THC MIN Q MAX AMT REV

Ore 2 83 900 20

Ore 1.9 82 850 18

CA 2.1 80 675 22

NV 1.8 84 750 24

The table above shows the relevant data about the 4 production facilities that Cannagood has brought on line.   They make more than 1 product at each location, but we are going to oversimplify the procuring of the cannabis and aggregate the products together. The data shows the location of the production facility, the maximum average THC levels of the cannabis purchased that the plant can have, the minimum average quality of the cannabis purchased that the plant can have, the maximum amount of total cannabis that the production facility can obtain and the revenue per unit that is shipped to that plant.

P1 P2 P3 P4

W    4.1 4.4 6.1 8.1

O 2.1 1.8 4.7 6.8

C 3.1 2.9 1.6 5.4

N 9.3 9.6 6.4 2.8

This final table shows the shipping costs depending on the location of the farm (rows) and the plant (columns). Thus, the shipping cost is the same for W1, W2 and W3 to P1, P2, P3 and P4 respectively.

Other requirements:

1. Each plant has an upper bound of procurement shown, but it also has a minimum amount that needs to be shipped to justify use of the plant – that is 60% of the max.
2. Each plant must procure at least 60% of its cannabis from the same state that it is located (State laws).  
3. Aggregately for the 17 farms under contract, each of the 4 states represented must provide at least 18% of the total cannabis. This is company policy. (Example: The amount of cannabis purchased from the 3 farms in Washington must make up at least 18% of the total amount purchased).
4. No more than 130 units can go from any one farm to any one plant. (“Truck size”).

Use Excel Solver to Find the optimal way to match supply from the farms to demand for the plants subject to the limitations stated and to maximize profit.

Profit is revenue less purchase cost (by farm) less transportation cost (by farm to plant).

No integer solutions. I don’t think the solver will like it.

You might also uncheck the Automated scaling button too. New version of the Solver didn’t like it. See options in the Solver.

Summarize your solution by showing how each plant is sourced (by the farms). Nothing too elaborate.

Answer rating: 100% (QA)

Answer The optimal solution was determined using Excel Solver The maximum profit that can be achieved is 23919 The total amount of cannabis purchased ... View the full answer