Decision Analysis Excel part 2

For problem chapter 4 problem 13, 14 only do (a-f)

For problem chapter 4 problem 18 only do (a-d)

these are the same problems from chapter 3 problems 14,15,22

Just asking a few more questions related to those problems !! ??

Chapter: cm – Problem:
Use Solver to create a Sensitivity Report for. and answer the following questions:
a. Is the solution degenerate?
b. Is the solution unique?
ci Suppose the profit per pallet of Tahoe panels went down by $40. Would the optimal solution change?
d. Suppose the profit per pallet of Aspen panels went up by $40. Would the optimal solution change?
e. How much should the company be willing to pay to obtain 1.000 additional hours of pressing capacity?
f. Suppose the company has received an offer to buy 5,000 pounds of its pine chips for $1,250. Should it accept this offer?
Explain your answer.
g. Create a Spider Plot illustrating how the optimal profit changes as the availability of each of the four resources varies
from 9096 to 11096 of their given values. Explain the relationship between this graph and the shadow prices of the
resources given on the Sensitivity Report.
h. Suppose there is some uncertainty about the amount of pressing capacity that might be available. Create a Solver Table
Report summarizing how the optimal solution and profit changes as the amount of pressing capacity available changes
from 5,000 to 7.000 in increments of 500. What information does this report convey about the optimal product mix as a
function of the available pressing capacity?
Question
Bearland Manufacturing produces 4 different types of wood paneling. Each type of paneling is made by gluing and
pressing together a different mixture of pine and oak chips. The following table summarizes the required amount of
gluing, pressing, and mixture of wood chips required to produce a pallet of 50 units of each type of paneling:

Resources Required per Pallet of Paneling Type

Paneling Type Tahoe Pacific Savannah Aspen
Glue (quarts) 50 50 100 50
Pressing (hours) 50 150 100 50
Pine chips (pounds) 500 400 300 200
Oak chips (pounds) 500 750 250 500

Chapter: cm – Problem:
Use Solver to create a Sensitivity Report for. and answer the following questions:
a. Is the solution degenerate?
b. Is the solution unique?
c Explain the signs of the reduced costs for each of the decision variables. That is. considering the optimal value of each
decision variable. why does the sign of its associated reduced cost make economic sense?
d. Suppose the cost per pound for Feed 3 increased by 53. Would the optimal solution change? Would the optimal
objective function value change?
e. If the company could reduce any of the nutrient requirements. which one should it choose and why?
f. If the company could increase any of the nutrient requirements, which one should it choose and why?
g. Suppose there is some uncertainty about the cost estimates for each type of feed. Prepare a Spider Plot varying the
cost per pound of each feed type between plus and minus $0.25 of its stated value in $0.05 increments and tracking the
impact on the optimal cost. What does this chart reveal. and what are the managerial implications?
Question
The Beef-Up Ranch feeds cattle for Midwestern farmers and delivers them to processing plants in Topeka. Kansas, and
Tulsa, Oklahoma. The ranch must determine the amounts of cattle feed to buy so that various nutritional requirements are
met while minimizing total feed costs. The mixture fed to the cows must contain different levels of four key nutrients and
can be made by blending three different feeds. The amount of each nutrient (in ounces) found in each pound of feed is
summarized as follows:
Nutrient (in ounces) per Pound of Feed

Nutrient Feed 1 Feed 2 Feed 3

A 3 2 4

B 3 1 3

C 1 0 2

D 6 8 4
Chapter: cm – Problem:
Use Solver to create a Sensitivity Report for. and answer the following questions:
a. How much would electric trimmers have to cost in order for the company to consider purchasing these items rather
than making them?
b. If the cost to make gas trimmers increased to $90 per unit. how would the optimal solution change?
o How much should the company be willing to pay to acquire additional capacity in the assembly area? Explain.
d. How much should the company be willing to pay to acquire additional capacity in the production area? Explain.
e. Prepare a Spider Plot showing the sensitivity of the total cost to the changes in costs to make and the costs to buy
(adjusting the original values by of 9096. 9296. 11096). Which of these costs is the total cost most sensitive to?
f. Suppose the hours of production capacity available are uncertain and could vary from 9.500 to 10.500. How does the
optimal solution change for every loo-hour change in production capacity within this range?
Question
The Weedwacker Company manufactures two types of lawn trimmers: an electric model and a gas model. The company
has contracted to supply a national discount retail chain with a total of 30,000 electric trimmers and 15.000 gas trimmers.
However. Weedwacker’s production capability is limited in three departments: production. assembly. and packaging. The
following table summarizes the hours of processing time available and the processing time required by each department.
for both types of trimmers:

Hours Required per Trimmer
Electric Gas Hours Available

Production 020 0.40 10.000
Assembly 030 0.50 15.000
Packaging 0.10 0.10 5.000

Decision Analysis Excel part 2

For problem chapter 4 problem 13, 14 only do (a-f)

For problem chapter 4 problem 18 only do (a-d)

these are the same problems from chapter 3 problems 14,15,22

Just asking a few more questions related to those problems !! ??

Chapter: cm – Problem:
Use Solver to create a Sensitivity Report for. and answer the following questions:
a. Is the solution degenerate?
b. Is the solution unique?
ci Suppose the profit per pallet of Tahoe panels went down by $40. Would the optimal solution change?
d. Suppose the profit per pallet of Aspen panels went up by $40. Would the optimal solution change?
e. How much should the company be willing to pay to obtain 1.000 additional hours of pressing capacity?
f. Suppose the company has received an offer to buy 5,000 pounds of its pine chips for $1,250. Should it accept this offer?
Explain your answer.
g. Create a Spider Plot illustrating how the optimal profit changes as the availability of each of the four resources varies
from 9096 to 11096 of their given values. Explain the relationship between this graph and the shadow prices of the
resources given on the Sensitivity Report.
h. Suppose there is some uncertainty about the amount of pressing capacity that might be available. Create a Solver Table
Report summarizing how the optimal solution and profit changes as the amount of pressing capacity available changes
from 5,000 to 7.000 in increments of 500. What information does this report convey about the optimal product mix as a
function of the available pressing capacity?
Question
Bearland Manufacturing produces 4 different types of wood paneling. Each type of paneling is made by gluing and
pressing together a different mixture of pine and oak chips. The following table summarizes the required amount of
gluing, pressing, and mixture of wood chips required to produce a pallet of 50 units of each type of paneling:

Resources Required per Pallet of Paneling Type

Paneling Type Tahoe Pacific Savannah Aspen
Glue (quarts) 50 50 100 50
Pressing (hours) 50 150 100 50
Pine chips (pounds) 500 400 300 200
Oak chips (pounds) 500 750 250 500

Chapter: cm – Problem:
Use Solver to create a Sensitivity Report for. and answer the following questions:
a. Is the solution degenerate?
b. Is the solution unique?
c Explain the signs of the reduced costs for each of the decision variables. That is. considering the optimal value of each
decision variable. why does the sign of its associated reduced cost make economic sense?
d. Suppose the cost per pound for Feed 3 increased by 53. Would the optimal solution change? Would the optimal
objective function value change?
e. If the company could reduce any of the nutrient requirements. which one should it choose and why?
f. If the company could increase any of the nutrient requirements, which one should it choose and why?
g. Suppose there is some uncertainty about the cost estimates for each type of feed. Prepare a Spider Plot varying the
cost per pound of each feed type between plus and minus $0.25 of its stated value in $0.05 increments and tracking the
impact on the optimal cost. What does this chart reveal. and what are the managerial implications?
Question
The Beef-Up Ranch feeds cattle for Midwestern farmers and delivers them to processing plants in Topeka. Kansas, and
Tulsa, Oklahoma. The ranch must determine the amounts of cattle feed to buy so that various nutritional requirements are
met while minimizing total feed costs. The mixture fed to the cows must contain different levels of four key nutrients and
can be made by blending three different feeds. The amount of each nutrient (in ounces) found in each pound of feed is
summarized as follows:
Nutrient (in ounces) per Pound of Feed

Nutrient Feed 1 Feed 2 Feed 3

A 3 2 4

B 3 1 3

C 1 0 2

D 6 8 4
Chapter: cm – Problem:
Use Solver to create a Sensitivity Report for. and answer the following questions:
a. How much would electric trimmers have to cost in order for the company to consider purchasing these items rather
than making them?
b. If the cost to make gas trimmers increased to $90 per unit. how would the optimal solution change?
o How much should the company be willing to pay to acquire additional capacity in the assembly area? Explain.
d. How much should the company be willing to pay to acquire additional capacity in the production area? Explain.
e. Prepare a Spider Plot showing the sensitivity of the total cost to the changes in costs to make and the costs to buy
(adjusting the original values by of 9096. 9296. 11096). Which of these costs is the total cost most sensitive to?
f. Suppose the hours of production capacity available are uncertain and could vary from 9.500 to 10.500. How does the
optimal solution change for every loo-hour change in production capacity within this range?
Question
The Weedwacker Company manufactures two types of lawn trimmers: an electric model and a gas model. The company
has contracted to supply a national discount retail chain with a total of 30,000 electric trimmers and 15.000 gas trimmers.
However. Weedwacker’s production capability is limited in three departments: production. assembly. and packaging. The
following table summarizes the hours of processing time available and the processing time required by each department.
for both types of trimmers:

Hours Required per Trimmer
Electric Gas Hours Available

Production 020 0.40 10.000
Assembly 030 0.50 15.000
Packaging 0.10 0.10 5.000