Homework 2

Use the shells as the basis for solving the problems.Submit the workbook via Blackboard by the assigned due date.

1. A farmer in Iowa owns 46 acres of land. She is going to plant each acre with wheat or corn. Each acre planted with wheat yields $200 profit; each with corn yields $300 profit. The labor and fertilizer used for each acre are given in Table 1. One hundred workers and 120 tons of fertilizer are available.

a) Use Solver to help the farmer maximize the profit from her land.
b) Confirm graphically that the solution from part a maximizes the farmer’s profit from her land. (Use the Draw tool bar.)
TABLE 1
Data for Farmer Problem
Wheat
Corn

Labor
3 workers
2 workers

Fertilizer
2 tons
4 tons
c) Use SolverTable to see what happens to the decision variables and the total profit when the availability of fertilizer varies from 20 tons to 220 tons in 20-ton increments.

When does the farmer discontinue producing wheat? When does she start producing corn?
How does the profit change for each 20-ton increment?

d) Using Solver’s Sensitivity Report, determine how much the profit per acre of wheat can increase and not change the acres planted. How much can it decrease and not change the acres planted?

e) Using Solver’s Sensitivity Report, if the amount of workers available is increased by 15, what is the change in total profit? What is the change in total profit if it is decreased by 43?

2. Bloomington Brewery produces beer and ale. Beer sells for $5 per barrel, and ale sells for $2 per barrel. Producing a barrel of beer requires 5 pounds of corn and 2 pounds of hops. Producing a barrel of ale requires 2 pounds of corn and 1 pound of hops. The brewery has 60 pounds of corn and 25 pounds of hops.

a) Use Solver to maximize Bloomington Brewery’s revenue.

b) Confirm graphically that the solution in part a maximizes Bloomington Brewery’s revenue.(Use the Draw tool bar.)

c) Starting with the optimal solution to the previous problem, use Solver’s Sensitivity Report to either substantiate or refute the following statements: The availability of corn can decrease by any amount (up to 60 pounds), and each unit decrease will cost Bloomington Brewery the same amount in terms of lost revenue. On the other hand, increases in the availability of corn do not have a constant effect on total revenue; the first few extra units have a larger effect than subsequent units.

d) Use SolverTable to see how total revenue changes as the price of a barrel of beer varies from $2 to $10 per barrel in increments of $1 and simultaneously the available corn varies from 50 pounds to 70 pounds in increments of 2 pounds
3.
a) For a telephone survey, a marketing research group needs to contact at least 150 wives, 120 husbands, 100 single adult males, and 110 single adult females. It costs $2 to make a daytime call and (because of higher labor costs) $5 to make an evening call. Table 5 lists the results that can be expected. For example, 30% of all daytime calls are answered by a wife, and 15% of all evening calls are answered by a single male. Because of a limited staff, at most half of all phone calls can be evening calls. Determine how to minimize the cost of completing the survey.

TABLE 5
Data for Phone Problem

Person
Responding
Percentage of Daytime Calls
Percentage of Evening Calls

Wife
30
30

Husband
10
30

Single male
10
15

Single female
10
20

None
40
5

b) Using Solver’s Sensitivity Report, determine how much the cost of a daytime call can increase and still have the current solution remain optimal?

c) Using Solver’s Sensitivity Report, determine the change in total cost if five additional husband made calls are required.

Homework 2

Use the shells as the basis for solving the problems.Submit the workbook via Blackboard by the assigned due date.

1. A farmer in Iowa owns 46 acres of land. She is going to plant each acre with wheat or corn. Each acre planted with wheat yields $200 profit; each with corn yields $300 profit. The labor and fertilizer used for each acre are given in Table 1. One hundred workers and 120 tons of fertilizer are available.

a) Use Solver to help the farmer maximize the profit from her land.
b) Confirm graphically that the solution from part a maximizes the farmer’s profit from her land. (Use the Draw tool bar.)
TABLE 1
Data for Farmer Problem
Wheat
Corn

Labor
3 workers
2 workers

Fertilizer
2 tons
4 tons
c) Use SolverTable to see what happens to the decision variables and the total profit when the availability of fertilizer varies from 20 tons to 220 tons in 20-ton increments.

When does the farmer discontinue producing wheat? When does she start producing corn?
How does the profit change for each 20-ton increment?

d) Using Solver’s Sensitivity Report, determine how much the profit per acre of wheat can increase and not change the acres planted. How much can it decrease and not change the acres planted?

e) Using Solver’s Sensitivity Report, if the amount of workers available is increased by 15, what is the change in total profit? What is the change in total profit if it is decreased by 43?

2. Bloomington Brewery produces beer and ale. Beer sells for $5 per barrel, and ale sells for $2 per barrel. Producing a barrel of beer requires 5 pounds of corn and 2 pounds of hops. Producing a barrel of ale requires 2 pounds of corn and 1 pound of hops. The brewery has 60 pounds of corn and 25 pounds of hops.

a) Use Solver to maximize Bloomington Brewery’s revenue.

b) Confirm graphically that the solution in part a maximizes Bloomington Brewery’s revenue.(Use the Draw tool bar.)

c) Starting with the optimal solution to the previous problem, use Solver’s Sensitivity Report to either substantiate or refute the following statements: The availability of corn can decrease by any amount (up to 60 pounds), and each unit decrease will cost Bloomington Brewery the same amount in terms of lost revenue. On the other hand, increases in the availability of corn do not have a constant effect on total revenue; the first few extra units have a larger effect than subsequent units.

d) Use SolverTable to see how total revenue changes as the price of a barrel of beer varies from $2 to $10 per barrel in increments of $1 and simultaneously the available corn varies from 50 pounds to 70 pounds in increments of 2 pounds
3.
a) For a telephone survey, a marketing research group needs to contact at least 150 wives, 120 husbands, 100 single adult males, and 110 single adult females. It costs $2 to make a daytime call and (because of higher labor costs) $5 to make an evening call. Table 5 lists the results that can be expected. For example, 30% of all daytime calls are answered by a wife, and 15% of all evening calls are answered by a single male. Because of a limited staff, at most half of all phone calls can be evening calls. Determine how to minimize the cost of completing the survey.

TABLE 5
Data for Phone Problem

Person
Responding
Percentage of Daytime Calls
Percentage of Evening Calls

Wife
30
30

Husband
10
30

Single male
10
15

Single female
10
20

None
40
5

b) Using Solver’s Sensitivity Report, determine how much the cost of a daytime call can increase and still have the current solution remain optimal?

c) Using Solver’s Sensitivity Report, determine the change in total cost if five additional husband made calls are required.