A Farmer Is Planning His Spring Planting What Are the Key Formulas for This Excel Spreadsheet Implementation

Essay

A farmer is planning his spring planting. He has 20 acres on which he can plant a combination of Corn, Pumpkins and Beans. He wants to maximize his profit but there is a limited demand for each crop. Each crop also requires fertilizer and irrigation water which are in short supply. The following table summarizes the data for the problem.
$\begin{array} { l | c c c c c } & \text { Profit per } & \text { Yield per } & \text { Maximum } & \text { Irrigation } &\text { Fertilizer }\\\text { Crop } &\text { Acre(\$)}&\text { Acre (lb) } & \text { Demand (lb) } & \text { (acre ft) }&\text { (pounds/acre) }\\ \hline \text { Corn } & 2,100 & 21,000 & 20,000 & 2 & 500 \\\text { pumpkin } & 900 & 10,000 & 180,000 & 3 & 400 \\\text { Beans } & 1,050 & 3,500 & \text { Ba,000 } & 1 & 300\end{array}$
What are the key formulas for this Excel spreadsheet implementation of the following formulation?
$\begin{array} { ll } \text { Let }&\mathbf { X } _ { 1 } = \text { aces of carm } \\&X _ { \mathbf { z } } = \operatorname { acres } \text { of punglan } \\&\mathbf { X } _ { \mathbf { 3 } } = \text { acres of beans } \\\text { MAX: }& \ 2100 X _ { 1 } + 900 X _ { 2 } + 1050 X _ { 3 } \\\text { Subject to: } &21 X _ { 1 } \leq 200 \\&10 \mathrm { X } _ { 2 } \leq 180 \\&3.5 \mathrm { X } _ { 3 } \leq 80 \\&2 X _ { 1 } + 3 X _ { \mathbf { 2 } } + 1 X _ { 3 } \leq 50 \\&5 X _ { 1 } + 4 X _ { 2 } + 3 X _ { 3 } \leq 80 \\&X _ { 1 } , X _ { 2 } X _ { 3 } \geq 0 \\\end{array}$ $$\begin{array} { | c | c | c | c | c | c | c } \hline & { \mathrm { A } } & \mathrm { B } & \mathrm { C } & \mathrm { D } & \mathrm { E } & \mathrm { F } \\\hline 1 & & \text { Farm } & \text { Plenning } & \text { Prablem } & & \\\hline 2 & & & & & & \\\hline 3 & & \text { Carn } & \text { Pumpkin } & \text { Benns } & & \\\hline 4 & \text { Acres ta plant } & & & & & \text { Tatal Prafit: } \\\hline 5 & \text { Prafit per acre } & 2100 & 900 & 1050 & & \\\hline 6 & & & & & & \\\hline 7 & \text { Constraints: } & & & & \text { Used } & \text { Available } \\\hline 8 & \text { Corn denand } & 21000 & & & & \text { 200000 } \\\hline 9 & \text { Pumpkin demand } & & 10000 & & & 180000 \\\hline 10 & \text { Bean demand } & & & 3500 & & \text { 80000 } \\\hline 11 & \text { Water } & 2 & 3 & 1 & & 50 \\\hline 12 & \text { Fetilizer } & 500 & 400 & 300 & & 8000 \\\hline\end{array}$$