A New Hampshire produce company supplies organically grown

apples to four New England specialty stores. After the apples are collected at the

company's orchard, they are transported to any of three preparation centers where

they are prepared for retail (by undergoing extensive cleaning and then packaging), after which the prepared apples are shipped to the specialty stores. The cost

of transporting organic fruit is rather high due to the fragility of the product.

The company has three preparation centers available for use. Table 7.43 shows

the unit shipping and preparation costs (in $/lb.) from the company's orchard to the

preparation centers, as well as the preparation centers' monthly capacities. Demand

at the specialty stores is shown in Table 7.44. The unit costs of transporting the apples

from the preparation centers to the specialty stores is shown in Table 7.45.

The produce company must decide the amount of apples to prepare on each of

the three preparation centers and how much of each specialty store's demand to supply from each preparation center, at least cost.

Table 7.43

prep. center unit trans and prep cost($/lb) monthly capacity

1 0.30$ 200

2 0.60$ 450

3 0.90$ 760

Table 7.44

store monthly demand (lbs)

organic orchard 300

fresh& local 500

healthy pantry 400

seasons harverst 200

Table 7.45

prep center store

org. orch frech&loc healthy p. season h.

1 0.40$ 0.60$ 0.30$ 0.70$

2 0.60$ 0.60$ 0.30$ 0.70$

3 0.10$ 0.70$ 0.60$ 0.90$

(a) Construct a linear optimization model to determine the amount of apples to be prepared at each of the three preparation centers, and how much of each specialty store's demand to supply from each preparation center, in order to minimize the total cost of the operation.

Hint: Notice that the produce company must decide both the amount of apples to prepare in each preparation center and how many apples to ship from each preparation center to each store. Let X1O , for example, be the decision variable

defined to be the quantity of apples prepared at preparation center 1 and shipped to the Organic Orchard store. Let X1F, X1H, and X1S be defined likewise for the other three specialty stores, and define the appropriate decision variables for the

other two preparation centers in a similar manner. Then the constraints in the problem should include preparation center capacity constraints, such as:

X1O+X1F+X1H+X1S=<200

and store demand constraints, such as:

X1O+X2O+X3O>= 300 for example.

(b) Solve the linear optimization model on the computer. What managerial actions

would you recommend based on the optimal solution?

Please provide an excel sheet if possible.