# Identify any challenges you have in setting up a linear programming problem in Excel, and solving it with Solver

Discuss sensitivity analysis Select one (1) of the following topics for your primary discussion posting: Identify any challenges you have in setting up a linear programming problem in Excel, and solving it with Solver. Explain exactly what the challenges are and why they are challenging. Identify resources that can help you with that. Explain what the shadow price means in a maximization problem. Explain what this tells us from a management perspective.

MAT540 Week 7 Homework Chapter 3 1. Southern Sporting Good Company makes basketballs and footballs. Each product is produced from two resources rubber and leather. The resource requirements for each product and the total resources available are as follows:

Resource Requirements per Unit Product Rubber(lb.) Leather (ft2) Basketball 3 4 Football 2 5 Total resources available 500 lb. 800 ft2

a. State the optimal solution. b. What would be the effect on the optimal solution if the profit for a basketball changed from $12 to $13? What would be the effect if the profit for a football changed from $16 to $15? c. What would be the effect on the optimal solution if 500 additional pounds of rubber could be obtained? What would be the effect if 500 additional square feet of leather could be obtained? 2. A company produces two products, A and B, which have profits of $9 and $7, respectively. Each unit of product must be processed on two assembly lines, where the required production times are as follows: Hours/ Unit Product Line 1 Line2 A 12 4 B 4 8 Total Hours 60 40

a. Formulate a linear programming model to determine the optimal product mix that will maximize profit. b. Transform this model into standard form. 3. Solve problem 2 using the computer. a. State the optimal solution. b. What would be the effect on the optimal solution if the production time on line 1 was reduced to 40 hours? c. What would be the effect on the optimal soluttion if the profit for product B was increased from $7 to $15 to $20? 4. For the linear programming model formulated in Problem 2 and solved in Problem 3. a. What are the sensitivity ranges for the objective function coefficients? b. Determine the shadow prices for additional hours of production time on line 1 and line 2 and indicate whether the company would prefer additional line 1 or line 2 hours. 5. Formulate and solve the model for the following problem: Irwin Textile Mills produces two types of cotton cloth – denim and corduroy. Corduroy is a heavier grade of cotton cloth and, as such, requires 7.5 pounds of raw cotton per yard, whereas denim requires 5 pounds of raw cotton per yard. A yard of corduroy requires 3.2 hours of processing time; a yard of denim requires 3.0 hours. Although the demand for denim is practically unlimited, the maximum demand for corduroy is 510 yards per month. The manufacturer has 6,500 pounds of cotton and 3,000 hours of processing time available each month. The manufacturer makes a profit of $2.25 per yard of denim and $3.10 per yard of corduroy. The manufacturer wants to know how many yards of each type of cloth to produce to maximize profit. Formulate the model and put it into standard form. Solve it. . a. How much extra cotton and processing time are left over at the optimal solution? Is the demand for corduroy met? b. What is the effect on the optimal solution if the profit per yard of denim is increased from $2.25 to $3.00? What is the effect if the profit per yard of corduroy is increased from $3.10 to $4.00? c. What would be the effect on the optimal solution if Irwin Mils could obtain only 6,000 pounds of cotton per month? 6. Continuing the model from Problem 5. a. If Irwin Mills can obtain additional cotton or processing time, but not both, which should it select? How much? Explain your answer. b. Identify the sensitivity ranges for the objective function coefficients and for the constraint quantity values. Then explain the sensitivity range for the demand for corduroy. 7. United Aluminum Company of Cincinnati produces three grades (high, medium, and low) of aluminum at two mills. Each mill has a different production capacity (in tons per day) for each grade as f0llows:

Mill Aluminum Grade 1 2 High 6 2 Medium 2 2 Low 4 10

The company has contracted with a manufacturing firm to supply at least 12 tons of high-grade aluminum, and 5 tons of low-grade aluminum. It costs United $6,000 per day to operate mill 1 and $7,000 per day to operate mill 2. The company wants to know the number of days to operate each mill in order to meet the contract at minimum cost. a. Formulate a linear programming model for this problem. 8. Solve the linear programming model formulated in Problem 16 for Unite Aluminum Company by using the computer. a. Identify and explain the shadow prices for each of the aluminum grade contract requirements. b. Identify the sensitivity ranges for the objective function coefficients and the constraint quantity values. c. Would the solution values change if the contract requirements for high-grade alumimum were increased from 12 tons to 20 tons? If yes, what would the new solution values be?

9. Solve the linear programming model developed in Problem 22 for the Burger Doodle restaurant by using the computer. a. Identify and explain the shadow prices for each of the resource constraints b. Which of the resources constrains profit the most? c. Identify the sensitivity ranges for the profit of a sausage biscuit and the amount of sausage available. Explain these sensitivity ranges. Reference Problem 22. The manager of a Burger Doodle franchise wants to determine how many sausage biscuits and ham biscuits to prepare each morning for breakfast customers. The two types of biscuits require the following resources: Biscuit Labor (hr.) Sausage (lb.) Ham (lb.) Flour (lb.) Sausage 0.010 0.10 — 0.04 Ham 0.024 — 0.15 0.04

The franchise has 6 hours of labor available each morning. The manager has a contract with a local grocer for 30 pounds of sausage and 30 pounds of ham each morning. The manager also purchases 16 pounds of flour. The profit for a sausage biscuit is $0.60; the profit for a ham biscuit is $0.50. The manager wants to know the number of each type of biscuit to prepare each morning in order to maximize profit. Formulate a linear programming model for this problem.

** **