# 8.5 Assignment 8

## Even' Star Organic Farm

Even' Star Organic Farm was founded in 1997 by Brett Grohsgal, a former chef in Washington DC. The company owns a 104-acre farm in southern Maryland, and grows and sells organic produce. For more information, see Even' Star's Facebook page. This problem describes the business issues faced by Brett, and the data is based on actual observations.

Brett has decided to grow eight different types of produce: large tomatoes, small tomatoes, watermelon, okra, basil, cucumbers, sweet potatoes, and winter squash. He distributes his produce through three different channels: Restaurants, Community-Supported Agriculture, and Farmers' Markets.

## Problem 1.3 - Formulating the Problem

Now, let's formulate the constraints for our model. Brett can't sell negative cases, and he can't sell more cases than he produces. Cells B6:B13 in the spreadsheet list the number of available cases of each type of produce. For large tomatoes, which of the following constraints should we add to our model to capture these restrictions? Select all that apply.

Exercise 3

Explanation

We need to add constraints to restrict the total number of cases sold to each client (B26:D26) to be greater than or equal to zero, and we need to make sure that the total number of cases sold (SUM(B26:D26)) is no more than the total number produced, B6.

We should have similar constraints for each type of produce.

## Problem 1.4 - Formulating the Problem

Due to the truck capacity, the number of cases sold at the farmers' market can't be more than 600. Which constraint(s) captures this restriction?

Exercise 4

Explanation

We need to total number of cases sold at the farmers' market, SUM(D26:D33) to be less than or equal to 600.

## Problem 1.5 - Formulating the Problem

Brett knows that at most 20 restaurants will buy his produce. Which constraint(s) captures this restriction? HINT: Each restaurant buys 119 cases.

Exercise 5

Explanation

We first need to compute the total number of restaurant clients. We saw while computing the objective that this is SUM(B26:B33)/119. This should be less than or equal to 20.

Brett knows that at most 90 CSA customers will buy his produce. Which constraint(s) captures this restriction? HINT: Each CSA customer buys $400 worth of produce. Exercise 6 Explanation We first need to compute the total number of CSA clients. We saw while computing the objective that this is SUMPRODUCT(C26:C33;D6:D13)/400. This should be less than or equal to 90. Add all of these constraints to your model in LibreOffice (or in the spreadsheet software you are using). Here is a list of all of the constraints you should be adding: 1) Brett can't sell negative cases, and he can't sell more cases than he produces, for each type of produce. 2) The number of cases sold at the farmer's market can't be more than 600. 3) Brett can't sell produce to more than 20 restaurants. 4) Brett can't sell produce to more than 90 CSA customers. ## Problem 2.1 - Solving the Model Solve your model, and answer the following questions about the solution: What is the objective function value (in dollars)? Exercise 7 Explanation After solving the problem in LibreOffice, the objective value is$49,956.39.

You should have added the following constraints:

Large Tomato Limit: SUM(B26:D26) <= 406

Small Tomato Limit: SUM(B27:D27) <= 608

Watermelon Limit: SUM(B28:D28) <= 167

Okra Limit: SUM(B29:D29) <= 76

Basil Limit: SUM(B30:D30) <= 72

Cucumbers Limit: SUM(B31:D31) <= 251

Sweet Potatoes Limit: SUM(B32:D32) <= 107

Winter Squash Limit: SUM(B33:D33) <= 133

Farmers' Market Limit: SUM(D26:D33) <= 600

Restaurant Limit: SUM(B26:B33)/119 <= 20

CSA Limit: SUMPRODUCT(C26:C33;D6:D13)/400 <= 90

Also, make sure that you are maxiizing the objective, and that all of the variables are constrainted to be non-negative.

## Problem 2.2 - Solving the Model

How many cases of large tomatoes are given to CSA customers?

Exercise 8

Explanation

The decision variable corresponding to large tomatoes and CSA has value 0 in the solution.

## Problem 2.3 - Solving the Model

How many cases of watermelon are given to farmers' market customers?

Exercise 9

Explanation

The decision variable correponding to watermelon and the farmer's market has value 167 in the solution.

## Problem 2.4 - Solving the Model

How many CSA customers does Brett provide produce for? Remember that this might be fractional - go ahead and enter the exact number even though Brett can't really serve "fractional customers".

Exercise 10

Explanation

If we look at the constraint for CSA customers, we see that the left-hand side of the constraint has value 65.88. This means that Brett will technically sell produce to 66 customers (65 will get $400 worth of produce, and one will get 0.88*$400 worth of produce).

## Problem 3.1 - Sensitivity Analysis

Suppose that Brett can pay $1,000 to trade in his truck for a larger truck. This would allow him to transport 200 more cases of produce to the farmers' market (for a total of 800 cases). Should he do it? HINT: Adjust the constraints in your model, re-solve it, and compare the increase in objective function value to the cost of buying the larger truck. Exercise 11 Explanation If you increase the right hand side of the constraint for farmers' market cases to 800 (increase by 200) and re-solve the model, the new objective value is$50,181.76. Compared to the old objective value of $49,956.39, this is an increase in profit of$50,181.76 - $49,956.39 =$225.37. Since this is less than the cost of the truck, he shouldn't buy the larger truck.

## Problem 3.2 - Sensitivity Analysis

One of Brett's workers has offered to use his truck to help Brett transport 200 more cases of produce to the farmer's market (for a total of 800 cases). Which of the following choices would increase Brett's profit? Select all that apply.

Exercise 12

Explanation

We saw in the previous question that increasing the farmers' market cases to 800 increases profits by $225.37. Thus Brett should hire the worker, and pay him$150, since that will give him an additional profit of $225.37 -$150.00 = \$75.37.

## Problem 3.3 - Sensitivity Analysis

Now suppose that Brett has found 10 more customers who would like to join the CSA program, for a total of 100 potential CSA customers. Should he sell produce to these customers? If you have changed any values in the constraints, change them back to their original values before answering this question (600 cases at the farmers' market).

Exercise 13

Explanation

Since the constraint for CSA customers is not binding (we sell to 65.88 customers, when we know of 90) it is not beneficial to add 10 more CSA customers.

## Problem 3.4 - Sensitivity Analysis

Now suppose that Brett has purchased 5 additional acres of land, which allows him to produce 10 additional cases of one of his vegetables. Which vegetable should he plant on these 5 additional acres?

If you have changed any values in the constraints, change them back to their original values before answering this question (600 cases at the farmers' market, and 90 potential CSA customers). Assume for this problem that the production cost is the same for all types of produce. For your reference, here is a list of the number of cases of each type of produce that Brett currently produces: 406 cases of Large Tomatoes, 608 cases of Small Tomatoes, 167 cases of Watermelon, 76 cases of Okra, 72 cases of Basil, 251 cases of Cucumbers, 107 cases of Sweet Potatoes, and 133 cases of Winter Squash.

Exercise 14

Explanation

If you increase the total number of cases of each type of produce one at a time by 10, the large tomatoes give the largest increase in the objective function value. Thus, Brett should plant large tomatoes on the additional acres of land.

Acknowledgements: This problem is based on the case study "Introducing Integer Modeling with Excel Solver" by Dessislava Pachamanova, INFORMS Transactions on Education 7:1(88-98). Publication year 2006.