In: Statistics and Probability
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.
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)