Spreadsheet Modeling and Decision Making 7th edition Chapter 4 #12 Usse Solve to

Question

Spreadsheet Modeling and Decision Making 7th edition Chapter 4 #12

Usse Solve to create a Sensitivity Report for question 13 at the end of Chapter 3, and answer the following questions:

a. If the company could get 50 more units of routing capacity, should they do it? If so, how much should they be willing to pay for it?

b. If the company could get 50 more units of sanding capacity, should they do it? If so, how much should they be willing to pay for it?

c. Suppose the polishing time on country tables could be reduced from 2.5 to 2 units per table. How much should the company be willing to pay to achieve this improvement in efficiency?

d. Contemporary tables sell for $450. By how much would the selling price have to decrease before the company would no longer be willing to produce contemporary tables? Does this make sense? Explain.

Explanation / Answer

Let

x = number of country tables and

y = number of contemporary tables manufactured.

The total revenue z obtained from selling x country tables and y contemporary tables is

z = 350 x + 450 y.

The constraints from machine availability are

1.5 x +2.0 y 1,000

3.0 x + 4.5 y 2,000

2.5 x + 1.5 y 1,500

The constraints due to management’s determination are

x 0.20(x + y) or 0.80 x – 0.20 y   0

y   0.30(x + y) or -0.30 x + 0.70 y   0

Since negative quantities cannot be manufactured, we have x 0 and y 0.

Since tables cannot be manufactured in fractions, x and y are integers.

So the LPP is

Maximize       

z = 350 x + 450 y

subject to

1.5 x +2.0 y 1,000

3.0 x + 4.5 y 2,000

2.5 x + 1.5 y 1,500

0.80 x – 0.20 y 0

-0.30 x+ 0.70 y 0

and                 

x 0, y   0

x and y are integers.

The optimal solution is

x = 405, y = 174, and z = 220,050

a)

They should not do it. At present, there is a slack of 43.5. the Lagrangian multiplier is 0. So they should not pay

anything for the additional capacity because 50 *0 = 0.

b)

They should do it. They will be willing to pay a maximum of 50 * 110.1449273 = $5507.25

c)

At present, there is a slack of 224.64 slack in polishing time. The Lagrangian multiplier is 0. So there is no

improvement in revenue. The company should not pay anything for this.

d)

Since the management has decided that at least 30% of the tables should be contemporary, we have to produce

contemporary tables as long we are producing tables.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.