Question

Potato Foods Co, a major producer of mashed potato, is considering the purchase of 4 potato farms, see below.

Farm	Annual Costs ($1,000s)	Projected Annual Harvest (1000s of tons)
Idaho	404	11.2
Washington	361	8.5
Wisconsin	338	6.8
Colorado	307	5.3

The company will be processing the potato from the farms at its three plants, see below.

Plant	Potato Projected Annual Consumption (1000s of tons)
Tennessee	6.1
Kansas	5.2
Utah	8.9
Total	20.2

Ignoring shipping costs, the company needs to determine what farms to purchase to satisfy the projected potato consumption at the minimum total annual cost. With the purpose of a balanced geographic distribution, follow the guidelines:

-Exactly one farm between Idaho and Washington must be purchased; in other words, at least one of the two farms but NOT both.

- At most one farm between Idaho and Wisconsin must be purchased; in other words, none or either the Idaho farm or the Wisconsin farm can be purchased, but NOT both

-At least one of the following farms must be purchased: Wisconsin, Colorado

-If Washington farm is purchased, then Wisconsin farm must also be purchased

-No more than three farms must be purchased

Define the variables, formulate the problem, and SOLVE it in Excel (generate the Answer report)

Please answer in excel and post pictures so i can see how to do this on my own.

Answer 1

Let indicate that the farm i is purchased and indicate that the farm i is not purchased, where i=1,2,3,4 indicate that the farm is Idaho, Washington, Wisconsin, Colorado respectively.

These indicator variables are the decision variables.

The total annual cost is

the company needs to determine what farms to purchase to satisfy the projected potato consumption at the minimum total annual cost and hence the above is the objective function.

The constraints are

satisfy the projected potato consumption

The total projected annual Harvest is

This has to meet the Projected total Annual Consumption of 20.2 (1000s of tons)

-Exactly one farm between Idaho and Washington must be purchased; in other words, at least one of the two farms but NOT both.

- At most one farm between Idaho and Wisconsin must be purchased; in other words, none or either the Idaho farm or the Wisconsin farm can be purchased, but NOT both

-At least one of the following farms must be purchased: Wisconsin, Colorado

-If Washington farm is purchased, then Wisconsin farm must also be purchased

-No more than three farms must be purchased

The LP model can be stated as

Minimize

s.t.

Prepare this

get this

set up the solver using data--->solver

get this

ans: To minimize the cost buy the farms at Washington, Wisconsin, Colorado at at total annual cost of $1006 ($1,000s)