# CropSolverProblem by huangyuarong

VIEWS: 0 PAGES: 21

• pg 1
```									Hello Class,

In compliance with the Bradley-Miller Act (CVPIA), the Bureau of Reclamation is reducing diversions from the De
Under the current plan, 200,000 AF/yr are going to be cut from the Central California Irrigation District (CCID), wh

Program your solver to optimize the amount of water applied to each crop area.
Assume that before the change, CCID farmers get to apply the optimal water level.

Using the tables of crop area and type and decision variables, program your solver to predict optimal production
In the first 4 scenarios, farmers can trade with each other, so crop areas are variable among all farmers

In the final scenario, each farmer must take a proportional cut without trading, so each crop has it's water allocat
Remember, they are still watering each area in production optimally; they just fallow some of their maximum ac
We can use ratios to calculate total production and fallowed acres.

Create a table that summarizes effects of reductions on: Revenue, total tons produced, total land fallowed, and t

On the final page, please discuss each of the 4 questions in no more than 2 or 3 sentences.

Note: these prices, yields, and water requirements are based on 2010 NASS data.

Hint: Save yourself time by copy/pasting your equations for total production, revenue, production loss, land fallo
Hint: Remember, you can double-click the small black box in the lower right corner of a highlighted cell to fill in t
diversions from the Delta by 800,000 AF/yr in the Central Valley.
tion District (CCID), which has hired you as a consultant for their lobbying effort in Sacramento.

ict optimal production when deliveries are reduced by 50, 100, 150, and 200 Thousand AF below the optimal application
g all farmers

op has it's water allocation reduced by the same percent and total water available is reduced by 100 TAF.
e of their maximum acres, taking them out of production.

tal land fallowed, and the top 2 crops that lost the most production

oduction loss, land fallowed, and summary statistics when you complete them in one scenario.
ghlighted cell to fill in the cells below with the same function.
ptimal application

AF.
Crop                                            Optimal Water (AF) Production (Tons)
Area (Ac.) Water Requirement (AF/Ac.)              Max
Alfalfa          20000                      4.5           90000           120000
Broccoli          7000                      1.7           11900             49000
Carrots           9000                      0.6            5400           135000
Lettuce           8000                      0.7            5600           160000
Almonds          12000                      3.3           39600             14000
Walnuts          10000                      3.2           32000             12000
Cotton            8000                        4           32000              6000
Corn             14000                      2.1           29400             70000
Soybeans         11000                      1.9           20900             18000
Oranges           2000                        3            6000             25000
Pistachios        3000                        6           18000              6000
Tangerines        1000                      3.1            3100             11000
Spinach           1000                        1            1000              8000
Onions            4000                      0.5            2000             94000
Bell Peppers      2000                      0.6            1200             40000
Celery            3000                      1.8            5400           120000
Cauliflower       2000                      1.5            3000             22000
Tomatoes         10000                      2.1           21000           150000
Grapes            2000                      2.4            4800             24000
Nectarines        3000                      2.7            8100             25000
Totals          132000 N/A                               340400          1109000
Decision Var
Total Production (Tons)
Water Applied (AF)                                    Revenue (\$)
Output Price (\$/Ton)                Production Loss (Tons)
0                                 \$117
0                                 \$720
0                                 \$510
0                                 \$486
0                               \$3,461
0                               \$2,000
0                               \$3,005
0                                 \$182
0                                 \$840
0                                 \$342
0                               \$4,440
0                                 \$395
0                                 \$860
0                                 \$237
0                                 \$575
0                                 \$395
0                                 \$766
0                                 \$120
0                                 \$385
0                                 \$520
0                   0 N/A                             0                    0
Land Fallowed   Water Constraint     290400
Total Revenue Lost
Total Tonnage Lost
Most Reduced Crop
2nd Most Reduced
Land Fallowed

0
Crop                                            Optimal Water (AF) Production (Tons)
Area (Ac.) Water Requirement (AF/Ac.)              Max
Alfalfa          20000                      4.5           90000           120000
Broccoli          7000                      1.7           11900             49000
Carrots           9000                      0.6            5400           135000
Lettuce           8000                      0.7            5600           160000
Almonds          12000                      3.3           39600             14000
Walnuts          10000                      3.2           32000             12000
Cotton            8000                        4           32000              6000
Corn             14000                      2.1           29400             70000
Soybeans         11000                      1.9           20900             18000
Oranges           2000                        3            6000             25000
Pistachios        3000                        6           18000              6000
Tangerines        1000                      3.1            3100             11000
Spinach           1000                        1            1000              8000
Onions            4000                      0.5            2000             94000
Bell Peppers      2000                      0.6            1200             40000
Celery            3000                      1.8            5400           120000
Cauliflower       2000                      1.5            3000             22000
Tomatoes         10000                      2.1           21000           150000
Grapes            2000                      2.4            4800             24000
Nectarines        3000                      2.7            8100             25000
Totals          132000 N/A                               340400          1109000
Decision Var
Total Production (Tons)
Water Applied (AF)                                    Revenue (\$)
Output Price (\$/Ton)                Production Loss (Tons)
0                                 \$117
0                                 \$720
0                                 \$510
0                                 \$486
0                               \$3,461
0                               \$2,000
0                               \$3,005
0                                 \$182
0                                 \$840
0                                 \$342
0                               \$4,440
0                                 \$395
0                                 \$860
0                                 \$237
0                                 \$575
0                                 \$395
0                                 \$766
0                                 \$120
0                                 \$385
0                                 \$520
0                   0 N/A                             0                    0
Land Fallowed   Water Constraint     240400
Total Revenue Lost
Total Tonnage Lost
Most Reduced Crop
2nd Most Reduced
Land Fallowed

0
Crop                                            Optimal Water (AF) Production (Tons)
Area (Ac.) Water Requirement (AF/Ac.)              Max
Alfalfa          20000                      4.5           90000           120000
Broccoli          7000                      1.7           11900             49000
Carrots           9000                      0.6            5400           135000
Lettuce           8000                      0.7            5600           160000
Almonds          12000                      3.3           39600             14000
Walnuts          10000                      3.2           32000             12000
Cotton            8000                        4           32000              6000
Corn             14000                      2.1           29400             70000
Soybeans         11000                      1.9           20900             18000
Oranges           2000                        3            6000             25000
Pistachios        3000                        6           18000              6000
Tangerines        1000                      3.1            3100             11000
Spinach           1000                        1            1000              8000
Onions            4000                      0.5            2000             94000
Bell Peppers      2000                      0.6            1200             40000
Celery            3000                      1.8            5400           120000
Cauliflower       2000                      1.5            3000             22000
Tomatoes         10000                      2.1           21000           150000
Grapes            2000                      2.4            4800             24000
Nectarines        3000                      2.7            8100             25000
Totals          132000 N/A                               340400          1109000
Decision Var
Total Production (Tons)
Water Applied (AF)                                    Revenue (\$)
Output Price (\$/Ton)                Production Loss (Tons)
0                                 \$117
0                                 \$720
0                                 \$510
0                                 \$486
0                               \$3,461
0                               \$2,000
0                               \$3,005
0                                 \$182
0                                 \$840
0                                 \$342
0                               \$4,440
0                                 \$395
0                                 \$860
0                                 \$237
0                                 \$575
0                                 \$395
0                                 \$766
0                                 \$120
0                                 \$385
0                                 \$520
0                   0 N/A                             0                    0
Land Fallowed   Water Constraint     190400
Total Revenue Lost
Total Tonnage Lost
Most Reduced Crop
2nd Most Reduced
Land Fallowed

0
Crop                                            Optimal Water (AF) Production (Tons)
Area (Ac.) Water Requirement (AF/Ac.)              Max
Alfalfa          20000                      4.5           90000           120000
Broccoli          7000                      1.7           11900             49000
Carrots           9000                      0.6            5400           135000
Lettuce           8000                      0.7            5600           160000
Almonds          12000                      3.3           39600             14000
Walnuts          10000                      3.2           32000             12000
Cotton            8000                        4           32000              6000
Corn             14000                      2.1           29400             70000
Soybeans         11000                      1.9           20900             18000
Oranges           2000                        3            6000             25000
Pistachios        3000                        6           18000              6000
Tangerines        1000                      3.1            3100             11000
Spinach           1000                        1            1000              8000
Onions            4000                      0.5            2000             94000
Bell Peppers      2000                      0.6            1200             40000
Celery            3000                      1.8            5400           120000
Cauliflower       2000                      1.5            3000             22000
Tomatoes         10000                      2.1           21000           150000
Grapes            2000                      2.4            4800             24000
Nectarines        3000                      2.7            8100             25000
Totals          132000 N/A                               340400          1109000
Decision Var
Total Production (Tons)
Water Applied (AF)                                    Revenue (\$)
Output Price (\$/Ton)                Production Loss (Tons)
0                                 \$117
0                                 \$720
0                                 \$510
0                                 \$486
0                               \$3,461
0                               \$2,000
0                               \$3,005
0                                 \$182
0                                 \$840
0                                 \$342
0                               \$4,440
0                                 \$395
0                                 \$860
0                                 \$237
0                                 \$575
0                                 \$395
0                                 \$766
0                                 \$120
0                                 \$385
0                                 \$520
0                   0 N/A                             0                    0
Land Fallowed   Water Constraint     #REF!
Total Revenue Lost
Total Tonnage Lost
Most Reduced Crop
2nd Most Reduced
Land Fallowed

0
Crop                                            Optimal Water (AF) Production (Tons)
Area (Ac.) Water Requirement (AF/Ac.)              Max
Alfalfa          20000                      4.5           90000           120000
Broccoli          7000                      1.7           11900             49000
Carrots           9000                      0.6            5400           135000
Lettuce           8000                      0.7            5600           160000
Almonds          12000                      3.3           39600             14000
Walnuts          10000                      3.2           32000             12000
Cotton            8000                        4           32000              6000
Corn             14000                      2.1           29400             70000
Soybeans         11000                      1.9           20900             18000
Oranges           2000                        3            6000             25000
Pistachios        3000                        6           18000              6000
Tangerines        1000                      3.1            3100             11000
Spinach           1000                        1            1000              8000
Onions            4000                      0.5            2000             94000
Bell Peppers      2000                      0.6            1200             40000
Celery            3000                      1.8            5400           120000
Cauliflower       2000                      1.5            3000             22000
Tomatoes         10000                      2.1           21000           150000
Grapes            2000                      2.4            4800             24000
Nectarines        3000                      2.7            8100             25000
Totals          132000 N/A                               340400          1109000
Decision Var
Total Production (Tons)
Water Applied (AF)                                    Revenue (\$)
Output Price (\$/Ton)                Production Loss (Tons)
0                                 \$117
0                                 \$720
0                                 \$510
0                                 \$486
0                               \$3,461
0                               \$2,000
0                               \$3,005
0                                 \$182
0                                 \$840
0                                 \$342
0                               \$4,440
0                                 \$395
0                                 \$860
0                                 \$237
0                                 \$575
0                                 \$395
0                                 \$766
0                                 \$120
0                                 \$385
0                                 \$520
0                   0 N/A                             0                    0
Land Fallowed   Water Constraint     240400
Total Revenue Lost
Total Tonnage Lost
Most Reduced Crop
2nd Most Reduced
Land Fallowed

0
1) What did you notice about the relationship between increasing the diversion reductions and the amount of re

2) What did you notice about which crops lost more area first? Was this consistent across all the scenarios?

3) Compare the 100 TAF NO TRADE and 100 TAF with trade scenarios on revenue lost and land fallowed. What out

4) What would you tell a legislator on behalf of the CCID area farmers that hired you to consult for them? What p
Note: This question will give you full credit for effort, so just have fun with it and be creative.
s and the amount of revenue lost?

all the scenarios?

and fallowed. What outcome did trading allow for in crop area allocation that the no-trade scenario did not?

nsult for them? What policies would you suggest or what would you say to persuade them to make cuts somewhere else?
ive.
d not?

ts somewhere else?

```
To top